Fine Tune Oracle Execution Plans for Performance Gains

Janis Griffin
Confio/SolarWinds
@DoBoutAnything

What are Execution Plans?

• sequence of operations 
• 
  

How to View Them?

• Explain Plan (realtime guess)
• explain plan for sql statement
• set autotrace (on | trace | exp| stat|off)
• V$SQL_PLAN -- best way
• actual execution plan
• use DBMS_XPLAN for display
• DISPLAY
• DISPLAY_AWR
• DISPLAY_PLAN
• DISPLAY_SQLSET
• DISPLAY_SQL_PLANBASELINE
• Tracing  & TKPROF
• Historical Plans -- AWR
• shows how plans change overtime

Table info script

• http://support.confio.com/kb/1534

Interpret Plan Details

• Understand your table (script above will help)
• *_TAB_COLUMNs.NUM_BUCKETS will indicate histogram usage
• lots of good information here regarding column status
• DBMS_STATS.gather_table_stats to collect this information
• V$SQL_BIND_CAPTURE
• WHERE SQL_ID is sql_id
• shows what bind variables are passed
• collected at ~15 minute intervals
• Adaptive Cursor Sharing
• DB will create new alternative excution plan due to bind peeking
• V$SQL -- IS_BIND_SENSITIVE: optimizer peeked-plan may change
• V$SQL -- IS_BIND_AWARE: 'Y' after query has been marked as bind sensitive
• V$SQL_CS_HISTOGRAM
• V$SQL_CS_SELECTIVITY
• V$SQL_CS_STATISTICS
• V$SQL_SHARED_CURSOR
• can give you clues as to why plans change

Tips and Techniques
How to Tune -- Stuff to look at

• Find the expensive operators
• costs /row count / time
• Table access full
• Review Filter and access Predicates
• shows how query interpreted
• can diagnose data type issues
• evaluate object stats
• table defs
• sizes and row counts
• determine existing indexes 
• index definitions
• index selectivity
• evaluate column stats

SWINGBENCH tool for automation(?)