DBA Hub

📋Steps in this guide1/9

How to Enable Trace

How to Enable Trace 1. Trace levels 2. Trace own session 3. Trace other’s session 4. Service Level Trace 5. Module Level Trace 6. Database Level Trace 7. How to identify Trace file 8. trcsess Utility 9. TKPROF Trace levels. Level 0 - No trace. Just like switching sql_trace off. Level 2 - The equivalent … Continue reading Trace →

oracle clusteringintermediate
by OracleDba
13 views
1

Overview

Trace levels. Trace own session set timing on timi on 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=’MY_TRACE’;

Code/Command (click line numbers to comment):

1
2
3
4
5
Level 0 - No trace. Just like switching sql_trace off.
Level 2 - The equivalent of regular sql_trace.
Level 4 - The same as level 2, but with the addition of bind variable values.
Level 8 - The same as level 2, but with the addition of wait events.
Level 12 - The same as level 2, but with the addition of both bind variable values and wait events.
2

Section 2

Trace other’s session set timing on timi on 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=’MY_TRACE’; SPID is the operating system Process identifier (os pid) PID is the Oracle Process identifier (ora pid)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
SQL>
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
-- Execute the queries or operations to be traced here --
SQL>
ALTER SESSION SET EVENTS '10046 trace name context off';
OR

SQL> EXEC DBMS_MONITOR.session_trace_enable; (OR)
SQL>
EXEC DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>FALSE);
-- Execute the queries or operations to be traced here --
SQL>
EXEC DBMS_MONITOR.session_trace_disable;
OR

SQL>
EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);
-- Execute the queries or operations to be traced here --
SQL>
EXEC DBMS_SESSION.SESSION_TRACE_DISABLE();
OR

SQL>
EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);
-- Execute the queries or operations to be traced here --
SQL>
EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);
OR

SQL>
ALTER SESSION SET sql_trace=TRUE;
-- Execute the queries or operations to be traced here --
SQL>
ALTER SESSION SET sql_trace=FALSE;
OR

SQL>
EXEC DBMS_SUPPORT.start_trace(waits=>TRUE, binds=>FALSE);
-- Execute the queries or operations to be traced here --
SQL>
EXEC DBMS_SUPPORT.stop_trace;
Note: Need to install before use
SQL> connect / AS SYSDBA
SQL> @?\rdbms\admin\dbmssupp.sql
SQL> GRANT execute ON dbms_support TO schema_owner;
SQL> CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;

-- Enable 10046 Trace on other's session
SQL>
EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>12, nm=>' ');
-- Execute the queries or operations to be traced here --
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' ');
OR

SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id=>1234, serial_num=>1234); (OR)
SQL>
EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234, waits=>TRUE, binds=>FALSE);
-- Execute the queries or operations to be traced here --
SQL>
EXEC DBMS_MONITOR.session_trace_disable(session_id=>1234, serial_num=>1234);
OR

SQL>
EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);
-- Execute the queries or operations to be traced here --
SQL>
EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);
OR

SQL>
EXEC DBMS_SUPPORT.start_trace_in_session(sid=>123, serial=>1234, waits=>TRUE, binds=>FALSE);
-- Execute the queries or operations to be traced here --
SQL>
EXEC DBMS_SUPPORT.stop_trace_in_session(sid=>123, serial=>1234);
OR
Tracing a process after it has started. Please find out PID, SPID using below query
select p.PID,p.SPID,s.SID 
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = '&SESSION_ID'
/
SPID is the operating system Process identifier (os pid)
PID is the Oracle Process identifier (ora pid)
Using SPID (os pid)
connect / as sysdba
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
-- Execute the queries or operations to be traced here --
oradebug tracefile_name; -- display the current trace file.
oradebug event 10046 trace name context off
OR
using PID (ora pid)
connect / as sysdba
oradebug setorapid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
-- Execute the queries or operations to be traced here --
oradebug tracefile_name;
-- display the current trace file.
oradebug event 10046 trace name context off
3

Section 3

Using SPID (os pid) using PID (ora pid) Service Level Trace SELECT sid, serial#, client_identifier, service_name, action, module FROM V$SESSION where username=’&USERNAME’; select * from DBA_ENABLED_TRACES; select trace_type, primary_id, QUALIFIER_ID1, waits, binds from DBA_ENABLED_TRACES; select * from sys.wri$_tracing_enabled; select * from sys.wri$_tracing_enabled;
4

Section 4

-- Level 12 Trace on service_name (waits=>true,binds=>true) Module Level Trace -- Level 12 Trace on Module (waits=>true,binds=>true)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Level 12 Trace on service_name (waits=>true,binds=>true)
SQL>
EXEC dbms_monitor.serv_mod_act_trace_enable (service_name=>'orcl',waits=>true,binds=>true);
-- Execute the queries or operations to be traced here --
SQL>
EXEC dbms_monitor.serv_mod_act_trace_disable (service_name=>'orcl');

-- Level 12 Trace on Module (waits=>true,binds=>true)
SQL>
exec dbms_monitor.serv_mod_act_trace_enable(service_name=>'orcl', module_name=>'TOAD 9.5.0.31', waits => TRUE, binds => TRUE);
-- Execute the queries or operations to be traced here --
SQL>
exec dbms_monitor.serv_mod_act_trace_disable(service_name=>'orcl', module_name=>'TOAD 9.5.0.31');
5

Section 5

Database Level Trace Warning: Setting the 10046 event for the entire database is not recommended. This can cause severe performance issues. Existing sessions will not be traced. alter system set events '10046 trace name context forever,level 12'; alter system set events '10046 trace name context off'; How to identify Trace file

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
For all sessions in the database
NOTE: Please be cautious when setting system wide, as this will impact performance due to every session being traced.
Warning: Setting the 10046 event for the entire database is not recommended. This can cause severe performance issues.
Existing sessions will not be traced.
EXEC dbms_monitor.DATABASE_TRACE_ENABLE(TRUE,TRUE);
EXEC dbms_monitor.DATABASE_TRACE_DISABLE();
alter system set events '10046 trace name context forever,level 12';
alter system set events '10046 trace name context off';
6

Section 6

How to identify Trace file -- To find SPID instancename_or a spid orcl_ora_4393.trc

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Trace file named with spid under user_dump_dest location
-- To find SPID
select p.PID,p.SPID,s.SID 
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = '&SESSION_ID'
/
eg:
instancename_or
a
_
spid
.trc
i.e
orcl_ora_4393.trc
show parameter user_dump_dest
cd user_dump_dest location
ls -ltr *ora_
spid
*.trc
eg: ls -ltr *ora_4393*.trc
7

Section 7

spid trcsess Utility trcsess output=one_big.trc service=orcl *.trc TKPROF

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
When using the DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE procedure, tracing
information is present in multiple trace files and you must use the trcsess tool to collect it into a single file.
The trcsess utility is useful for consolidating the tracing of a particular session or service for performance or debugging purposes.
trcsess [output=output_file_name]
[session=session_id]
[clientid=client_identifier]
[service=service_name]
[action=action_name]
[module=module_name]
[]
eg:
cd user_dump_dest
trcsess output=one_big.trc service=orcl *.trc
ls -ltr one_big.trc
8

Section 8

TKPROF tkprof one_big.trc output=one_big.txt sys=no Reference: How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues (Doc ID 376442.1) https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_monitor.htm#CFAHBEAB http://www.juliandyke.com/Diagnostics/Packages/DBMS_MONITOR/SERV_MOD_ACT_TRACE_ENABLE.php https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
The tkprof utility parses SQL trace files to produce more readable output.
tkprof does not report COMMITs and ROLLBACKs that are recorded in the trace file.
Note: Set the TIMED_STATISTICS parameter to TRUE when tracing sessions because no time-based comparisons can be made without this. TRUE is the default value with Oracle Database 11g.
eg:
tkprof one_big.trc output=one_big.txt sys=no
[oracle@rac1 trace]$ tkprof one_big.trc output=one_big.txt sys=no

TKPROF: Release 11.2.0.3.0 - Development on Sun Sep 11 16:50:56 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


[oracle@rac1 trace]$
ls -ltr one_big.txt
9

Section 9

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!