Event 10046 (Query Execution Plan trace)
alter session set timed_statistics = true;
alter session set statistics_level = ALL;
alter session set max_dump_file_size = UNLIMITED;
alter session set events '10046 trace name context forever, level 12';
alter session set tracefile_identifier = 'xxx';
Event 10053 (Cost Based Optimizer trace)
alter session set events '10053 trace name context forever, level 1';
alter session set tracefile_identifier = 'xxx';
Explain Plan for a query
-- Change the query to whatever you need
explain plan for select * from fnd_user;
set lines 130
set head off
alter session set cursor_sharing = EXACT;
select plan_table_output from table(dbms_xplan.display('PLAN_TABLE', null, 'ALL'));
Autotrace for a query
-- Change the query to whatever you need
set autotrace traceonly explain
select * from fnd_user;
Set trace (event 10046) for a running session
-- Change SID and SERIAL to the running session's identifiers
sqlplus system
exec dbms_system.set_ev ('SID', 'SERIAL', 10046, 12, '');
Unset trace for a running session
-- Change SID and SERIAL to the running session's identifiers
sqlplus system
exec dbms_system.set_ev ('SID', 'SERIAL', 10046, 0, '');
Find a trace file
-- Change SID to the running session's identifier
select a.tracefile
from v$process a, v$session b
where a.addr = b.paddr
and b.sid = SID;
Gather schema statistics
-- Change schema name (SYSTEM) to whatever you need
begin
dbms_stats.delete_schema_stats('SYSTEM');
dbms_stats.gather_schema_stats('SYSTEM',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
cascade => true,
options=> 'GATHER AUTO');
end;
Tip: use FND_STATS instead of DBMS_STATS for Oracle Applications
Gather table statistics
-- Change schema name (SYSTEM) and table name (REWRITE_TABLE) to whatever you
-- need
begin
dbms_stats.gather_table_stats('SYSTEM',
tabname => 'REWRITE_TABLE',
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
cascade => TRUE,
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
end;
Tip: use FND_STATS instead of DBMS_STATS for Oracle Applications