A common complain for those
migrating from Oracle8 or Oracle8i to Oracle10g is that the
performance gets WORSE! By the way, I recommend that you
migrate directly to Oracle10g because the automated statistics
collection and optimization can help a lot, especially if you are
not familiar with Oracle tuning.
So, what do you do if you find that your
well-optimized database performs poorly after a migration to a new
release? In my experience it?s most likely sub-optimal SQL
execution, and rarely, sub-optimal PL/SQL. I follow these
1 ? Compare an
execution plan from before the migration to a post-migration
2 ? Ensure that you
have re-analyzed your schema using the dbms_stats utility
3 ? Check the Oracle
docs for recommended changes to init.ora parameters.
Here are details:
1 - Find a super bad-performing SQL and dive-in
to see exactly why the SQL is executing in a sub-optimal fashion.
Use the "set autotrace traceonly explain" command in
SQL<*Plus, get the execution plan, and compare the execution plan to
the same SQL in your old database. For details on evaluating
execution plans, see this article:
2 - Make sure to re-analyze your schema using
dbms_stats. High-quality metadata can make all the difference.
3 - Try re-setting Oracle initialization
- optimizer_mode ? If you have an
OLTP database, try first_rows. Also, if your old
optimizer_mode was ?choose? you will need to pick a new one in
Oracle10g, usually all_rows or first_rows
(sometimes first_rows_100 too).
- optimizer_index_cost_adj = 25 ?
This adjusts the optimizer to favor index access
- optimizer_index_caching = set to
percentage of indexes that are buffered according to x$bh buffer
data. A script to give you the best initial setting can be
Remember, the all_rows optimizer mode is designed to minimize
computing resources and it favors full-table scans. Index
access (first_rows) adds additional I/O overhead, but they return
rows faster, back to the originating query:
Oracle full-table scan
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.