Instance tuning (db_block_buffers, shared pool size, spreading data files
across multiple disks, etc, etc) is important.
But we have experienced that writing SQL statements in the right way is
at least as important as instance tuning is.
Writing efficient SQL is not evident, especially when joins and selects
with sub-selects show up.
We have spent days examining our existing applications and discovered
queries that would consume tens of seconds of cpu time and thousands of
disk blocks per run. After re-arranging them they would return the same
result sometimes 10 times faster, with much less disk i/o. We did this
not only by applying know techniques such as adding indexes. E.g.,
sometimes we had to add an extra table in a join, in order to force the
optimizer to follow a certain path instead of "hoping for the best".
Oracle does document the mechanics of the execution of SQL - the
optimizer - fairly well, altough we have found that much was still left
to trial and error. (server 7.2)
Does anyone know about specialized books or papers on this topic (or
maybe offer services in this domain) ? Especially good stuff on the cost
based optimizer would be welcome.
thanks
