SQL Tuning

Things I think important to know for sql tuning:

• Be able to read execution plan

         - right-most first, from top to bottom
         - visual approach to assist understanding   
         - pay attention to the Predicate Information section

• Obtaining the execution plan

         - AUTOTRACE 
         - dbms_xplan.display_curosr

• Cardinality

         - the predicted number of rows generated by an operation
         - cardinality = selectivity * ( number of input rows)

• COST

         - represent the optimizer's best estimate of the time it will take to execute the statement; CBO always chooses the execution plan with minimum cost.

• Tuning by cardinality feedback

         - using dbms_xplan.display_cursor to obtain E-row vs A-row ( set statistical_level=all;  or using /* gather_plan_statistics */ )

• AWR, ADDM, ASH and wait interface - able to identify the offensive SQLs
• bind variable peeking and plan instability
• 10046 trace & tkprof

         - a rule of thumb to check efficiency: query+ current/rows < 20

• 10053 trace
• join method and join order
• Able to check table indexes and table column statistics ( HISTOGRAM)
• Using top-n query to show the data distribution

         ie.  select * from ( select col_of_insterest,count(*) from table_A group by col_of_interest order by 2 desc) where rownun <=50

• Talk to lead developers

         - tune questions not just tune queries

• Fix a bad query or method of improvement

        - index re-design,
        - hints,
        - gather stats,
        - explore parallelism,
        - plan stability: sql profile( sqlt coe* script) and sql plan baseline (sqlt coe* script)