Oracle tools for SQL Optimizer and Execution Plans

Oracle SQL Optimizer and Execution Plan details:

• SQL Trace 10046

• SQL*Plus "set autotrace"

• Extended execution plan statistics (v$sql_workarea)

• The gather_plan_statistics hint (or statistics_level=all)

Select /*+ gather_plan_statistics */
* From xxx where....

Select * From v$sql Order By last_active_time desc

Select * From Table(dbms_xplan.display_cursor('77w1cgpppqu79','0','allstats +peeked_binds'))

Select * From Table(dbms_xplan.display_cursor(Null, Null, 'ALLSTATS LAST'));

Oracle Ref: http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams044.htm#REFRN10028

• alter session set events 'immediate trace name flush_cache';

• alter system flush buffer_cache;

• alter system flush shared_pool;

• ALTER TABLESPACE  OFFLINE;

• ALTER TABLESPACE  ONLINE;

• v$sql_optimizer_env - cursor in the SGA
• v$ses_optimizer_env - session
• v$sys_optimizer_env - system

• select level n from dual connect by level <= 10000;

• Great explanation about Consistent Gets & DB Block Gets: http://mwidlake.wordpress.com/2009/06/02/what-are-consistent-gets/