Saturday, August 16, 2008

Get explain plan for select statement

explain plan for select * from gl_balances order by period_name;
select plan_table_output from table(dbms_xplan.display('plan_table'));

or

set autotrace on statistics;
select x from y;

or

alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
sys.dbms_support.start_trace(waits=>true, binds=>true)
{{{{ Put the code to be traced here}}}}
sys.dbms_support.stop_trace()

# turn on trace for a running session

select 'exec sys.dbms_system.set_ev('||sid||','||serial#||',10046,12,'''');' from v$session where sid in ('75');

begin
sys.dbms_system.set_sql_trace_in_session(165,31125,TRUE );
end;

tkprof sambprd_ora_29857.trc gps.tkp explain=username/password

ORADEBUG SETOSPID 21237
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 4