98 lines
2.9 KiB
Plaintext
Executable File
98 lines
2.9 KiB
Plaintext
Executable File
---------------
|
|
- Trace 10046 -
|
|
---------------
|
|
|
|
-- For current session
|
|
----------------------
|
|
ALTER SESSION SET tracefile_identifier=Wookie;
|
|
|
|
exec DBMS_SESSION.session_trace_enable(waits => TRUE, binds=> TRUE);
|
|
exec DBMS_SESSION.session_trace_disable();
|
|
|
|
-- For another session
|
|
----------------------
|
|
exec DBMS_MONITOR.session_trace_enable (session_id =>21, serial_num=>1143, waits => TRUE, binds=> TRUE);
|
|
|
|
-- with DBMS_MONITOR we can also trace service_name, module_name or action_name
|
|
|
|
-- Trace with a TRIGGER "AFTER LOGON"
|
|
---------------------------------------------
|
|
|
|
create or replace trigger TRACE_LOGIN_TRIGGER
|
|
after logon on database
|
|
begin
|
|
if user = 'APRESS' then
|
|
begin
|
|
execute immediate 'alter session set tracefile_identifier=APRESS_LOGON';
|
|
dbms_session.session_trace_enable (waits => TRUE, binds => TRUE);
|
|
end;
|
|
end if;
|
|
end;
|
|
/
|
|
|
|
-- From 11g, we can activate the trace for a specific SQL_ID at session/instance level
|
|
-- http://oraclue.com/2009/03/24/oracle-event-sql_trace-in-11g/
|
|
-- http://tech.e2sn.com/oracle/troubleshooting/oradebug-doc
|
|
|
|
-- example for tracing one SQL_ID at the system level
|
|
alter system set events 'sql_trace [sql:5vy5qjd3fsn5c] wait=true, bind=true, plan_stat=all_executions, level = 12';
|
|
alter system set events 'sql_trace [sql:5vy5qjd3fsn5c] off';
|
|
|
|
|
|
-- classic 1046 at the session level
|
|
alter session set tracefile_identifier='10046';
|
|
alter session set timed_statistics = true;
|
|
alter session set statistics_level=all;
|
|
alter session set max_dump_file_size = unlimited;
|
|
|
|
alter session set events 'sql_trace level 12';
|
|
|
|
-- Execute the queries or operations to be traced here --
|
|
alter session set events 'sql_trace off';
|
|
|
|
|
|
-- same as previous but limited to only 2 SQL_ID
|
|
alter session set tracefile_identifier='10046';
|
|
alter session set timed_statistics = true;
|
|
alter session set statistics_level=all;
|
|
alter session set max_dump_file_size = unlimited;
|
|
|
|
alter session set events 'sql_trace [sql:g3yc1js3g2689|7ujay4u33g337] level 12';
|
|
|
|
-- Execute the queries or operations to be traced here --
|
|
alter session set events 'sql_trace off';
|
|
|
|
|
|
---------------
|
|
- Trace 10035 -
|
|
---------------
|
|
|
|
-- For current session
|
|
alter session set events '10053 trace name context forever, level 1';
|
|
|
|
|
|
-- From 11g, we can dump the trace of a SQL_ID from library cache
|
|
|
|
begin
|
|
dbms_sqldiag.dump_trace(
|
|
p_sql_id=>'3wv7pga0wqxkb',
|
|
p_child_number=>0,
|
|
p_component=>'Compiler',
|
|
p_file_id=>'FIND_SPD_ID_TRACE');
|
|
end;
|
|
/
|
|
|
|
-- trace for a specific SQL_ID
|
|
-- http://laurentleturgez.wordpress.com/2011/11/29/trace-cbo-computation-for-a-specific-sql_id/
|
|
|
|
alter session set max_dump_file_size = unlimited;
|
|
ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*][sql:5vy5qjd3fsn5c]';
|
|
ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*] off';
|
|
|
|
|
|
-- from an external session
|
|
oradebug setospid 28027
|
|
oradebug unlimit
|
|
oradebug event trace[RDBMS.SQL_Optimizer.*][sql:5vy5qjd3fsn5c]
|
|
oradebug tracefile_name
|