Search

Performance Events

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

15 views0 comments

Recent Posts

See All

After logon trigger - trace user sessions

-- Activate trace for sessions using after logon database trigger -- -- Tables: -- -- SYS.XX_LOGIN_TRACE_CONDITION - add here the conditions for activating trace -- SYS.XX_LOGIN_TRACED_SESSIONS - con

INCLUDE parameter of expdp

-- Export all public database links expdp user/password full=y \ include=db_link:"in (select db_link from dba_db_links where owner = 'PUBLIC')" -- Export one table expdp user/password include=table:"i

Multiplexing Redo Log and Control files

-- List redo log files select group#, member from v$logfile order by group#, member; -- Check redo log file status select group#, members, status from v$log; -- Adding redo log file to a group alter d