Wednesday, January 16, 2013

ORACLE SQL Tunning 10046 & 10053 events

Use following script to check a event 10046 and 10053 for that slow query :
$ sqlplus /nolog
connect username/password
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
ALTER SESSION SET TRACEFILE_IDENTIFIER = '10046_QUERY001_SLOW';
alter session set events '10046 trace name context forever, level 8';
alter session set events '10053 trace name context forever, level 1';

My Slow QUERY (select * from table;)
select 'close the cursor' from dual;
exec dbms_session.reset_package;
alter session set events '10046 trace name context off';
alter session set events '10053 trace name context off';
quit


Check the trace file for query issue.

Ref Doc ID : 1274511.1/225598.1

1 comment: