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
$ 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
what are the difference between these two
ReplyDelete