DB Performance Tuning

Damon Brown
dabrown@policy-studies.com

Cause of poor performance:
. Stale or missing statistics
. Lack of appropriate indexes to support your query
. SQL that is inefficient by design
. Misuse or poor use of Oracle features
. ..

SQL tuning:
. trial and error
. guessing
. use materialzied views or temporary tables or ebuild tables and indexes

tuning methodoly
. methor R - fast effective easy (book optimizing oracle performance by oreily)
1. identify 2. measure response time 3. optimize 4. repeat until best optimal

understanding reponse time:
. rt = wait time + service time
. st = cpu time => joining, parsing etc
. wt = watiint on a n event

tuning objectives:
. set clear and well defined performance goals
. how do you met your goals?
. remember that timings are ultimately what is imporat to the business
. the scope of a solution to a perfmrnace problem should match the scope of the problem

tools:
. dbmps_xplan
--- retrieves exectuion plan of the query, sequence of operations, rolleup cost of each op, remember this in a prediction
. 10046 sql trace
. awr (dw)

good and bad plans:
. do not automatically jump to the conclusion that a bad plan contains full table scans and a good plan olny uses index
...