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

16 views0 comments

Recent Posts

See All

Get DDL for Oracle object

set feed off set long 1000000 set longchunksize 1000000 set pages 0 set lines 32767 set trimspool on set feed off set wrap on set serveroutput on format wrapped set def off set tab off exec dbms_metad

Cancelled scheduled concurrent requests

This SQL query returns all concurrent requests that were scheduled in the past and are not scheduled currently. It can be used for identifying scheduled requests that failed to be resubmitted. select

Foreign key constraint violation

/* This PL/SQL script checks violations of foreign key constraints Example of the tables: CREATE TABLE temp_supplier ( supplier_id numeric(10) not null, supplier_name varchar2(50) not null, contact_na