Re: Performance on complex database

50 tables is nothing for Oracle, if it is properly designed. Here are some tips you might want to consider:
- Create indexes on columns likely to be heavily used in the where
clauses of SQL statements (if the size of the table justifies this. Don't do it for a 10-row table for example!).
- Make sure your foreign keys have indexes on them. Otherwise, some
unnecessary locking issues might slow down the database during DML.
- Since this seems like an OLTP application, stay away from the bitmap
indexes (they lock much more than B*-tree indexes).
- Hopefully, design your database to at least the 3rd normal form (get
a good database design book if you don't know what I'm talking about). That way, for example, you will need to update only one table when some data will change.
- In the application, use bind variables when generating your SQL
statements. You will that way be able to re-use more efficiently the statements in the SQL area.
- If you can figure out the SQL statements which will be used heavily,
look at their execution paths, and see if any hints you could use might be useful.
- Use optimizer_mode = all_rows, and make sure that all tables are
analyzed when their content changes significantly (if you use 9i, "monitoring" them might help pinpoint them). For heavily-skewed indexed columns, you could consider histograms (they can be very useful to the optimizer) or reverse indexes.
- Most of all, get good SQL tuning and database design books.

Daniel Received on Sat Jan 04 2003 - 18:08:21 CET