| Performance Tuning Sticky [message #135617] |
Thu, 01 September 2005 09:41 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
In addition to the information found in the sql and plsql sticky, please consider the following when tuning your query.
1. The first step is to read the oracle 10g performance tuning guide in its entirety. It describes the different access paths, and teaches you how to use explain plan and tkprof to tune your query. It also covers the new 10g automated tuning features that may be at your disposal. While you are reading, you can proceed with the remaining steps below.
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14211/toc.htm
2. The second step to tuning your query is to gather comprehensive statistics, and re-gather them every time your data undergoes significant change. This means using dbms_stats with cascade => true and a method_opt of at least 'for all indexed columns size 250'.
3. The third step is to make sure that all of the columns that should not / will not contain null values are set to not null, that all columns that are used as primary and foreign keys have a primary or foreign key constraint on them, and seriously consider putting indexes on all foreign key columns.
4. The fourth step is to re-evaluate your physical database design. Ideally, this step was conducted in development, before the system went live. But regardless, consider all of the available oracle data structures (heap tables, index organized tables, hash clusters, btree clusters, btree indexes, bitmap indexes, partitions, materialized views, and compression).
5. The fifth step is to maintain your system at a reasonably up to date patch and version level. Each new version eliminates many older bugs, provides added functionality that you can use for performance, and typically has inherent optimizations at the internal level that cause your existing code to run faster. At the time of this writing, you should be at, or currently upgrading to, at least version 10gR1.
DURING THE TUNING PROCESS, KEEP IN MIND THE FOLLOWING:
a) Full Table Scans are not always bad, and Index Scans are not always good.
b) Routinely rebuilding btree indexes often does more harm than good. Only take action for a reason, and measure/evaluate whether your goal was achieved by the action you took.
c) Unless you have a particularly strange circumstance with a single query, and have exhausted all other options, it is generally best to only provide informational hints (such as cardinality, first_rows, and all_rows), rather than to provide specific access plan hints (like full and index).
[Updated to add link to docs]
[Updated on: Mon, 27 August 2007 11:16] by Moderator Report message to a moderator |
|
|
|
) 4 Votes
] 
Blog
Search
Help
Members
Register
Login
Home
