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/
