Tracing forms an important method when it comes to database and/or SQL performance tuning. In this blog post let's see the various methods we can trace a session or an application or an entire database and how to read the trace files generated
Types of tracing:
- Session level
- Application level
- Database level
- Check timed_statistics value is TRUE;
- Check statistics_level is typical or all
- Set max_dump_file_size to unlimited so that the trace is not abruptly ended due to file size reached
Example tracing own session:
When we need to trace the same session that we are connected to trouble shoot a query, we can perform the below
SQL_TRACE
[oracle@linux75-2 ~]$ sql soe/soe
SQLcl: Release 12.2.0.1.0 RC on Thu Dec 02 23:53:32 2021
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show parameter timed_statistics
NAME TYPE VALUE
---------------- ------- -----
timed_statistics boolean TRUE
SQL> show parameter statistics_level
NAME TYPE VALUE
---------------- ------ -------
statistics_level string TYPICAL
SQL> show parameter max_dump_file_size
NAME TYPE VALUE
------------------ ------ ---------
max_dump_file_size string unlimited
SQL> alter session set tracefile_identifier='MyTrace';
Session altered.
SQL> alter session set sql_trace=true;
Session altered.
SQL> -- Run the query that needs to be traced now
SQL> select count(*) from soe.addresses where country='India';
COUNT(*)
----------
19536
SQL> alter session set sql_trace=false;
Session altered.
SQL>
10046 tracing
SQL> -- set the identifier to find the trace file easily in the trace directory
SQL> alter session set tracefile_identifier='My10046';
Session altered.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> -- Run the query to be traced now
SQL> select count(*) from soe.addresses where country='India';
COUNT(*)
----------
19536
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL> show parameter diag
NAME TYPE VALUE
--------------- ------ ---------
diagnostic_dest string /u01/orcl
SQL> exit
For 10046 tracing, the following values are valid for trace level
- 0 - Off
- 2 - Similar to regular sql_trace
- 4 - Same as 2, but with addition of bind variable values
- 8 - Same as 2, but with addition of wait events
- 12 - same as 2, but with both bind variable values and wait events
DBMS_SESSION package
Own session can also be traced using dbms_session package
SQL> alter session set tracefile_identifier='MySess';
Session altered.
SQL> exec DBMS_SESSION.SESSION_TRACE_ENABLE(waits=> true, binds=> true);
PL/SQL procedure successfully completed.
SQL> select count(*) from soe.addresses where country='India';
COUNT(*)
----------
19536
SQL> exec DBMS_SESSION.SESSION_TRACE_DISABLE();
PL/SQL procedure successfully completed.
SQL> exit
All the trace files will be available by default in the trace directory under diagnostic_dest.
[oracle@linux75-2 ~]$ cd /u01/orcl/diag/rdbms/orcl/orcl/trace/ [oracle@linux75-2 trace]$ ls -lrt *My* -rw-r-----. 1 oracle oinstall 1310 Dec 2 23:55 orcl_ora_8930_MyTrace.trm -rw-r-----. 1 oracle oinstall 2560 Dec 2 23:55 orcl_ora_8930_MyTrace.trc -rw-r-----. 1 oracle oinstall 2984 Dec 2 23:55 orcl_ora_8930_My10046.trm -rw-r-----. 1 oracle oinstall 26449 Dec 2 23:55 orcl_ora_8930_My10046.trc -rw-r-----. 1 oracle oinstall 3404 Dec 3 00:38 orcl_ora_12142_MySess.trm -rw-r-----. 1 oracle oinstall 33729 Dec 3 00:38 orcl_ora_12142_MySess.trc [oracle@linux75-2 trace]$
By default, the trace files will be generated by it's server process id. In the above, 8930 and 12142 are the process id of the sessions I logged into the database. Since there will be many processes running and by default oracle trace will be enabled for many background processes, we will be in a position that we are lost when searching for the trace file generated. To find the trace files easily, we are setting the tracefile_identifier as our first step of tracing.
In most circumstances as a DBA, we will not be tracing our own session but will have to another session whether a user is already logged in or is about to login. Let's take a look on how to we do this
Start trace as soon as logon
We can create an after logon trigger to enable tracing for the session whenever a user logon to the database as below
WHEN ( user LIKE '&USERNAME' ) DECLARE
lcommand VARCHAR(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
DBMS_MONITOR.SESSION_TRACE_ENABLE (WAITS=> TRUE , BINDS=> TRUE);
END SET_TRACE ;
/
Note: The user on which the tracing should be enabled should explicitly have execute privilege on alter session and DBMS_MONITOR package. Otherwise the logon will fail due to insufficient privilege issue.
Start tracing when a session is identified for tracing
Many a times, when an issue is identified we will be in a situation where already a session is established in the database. So all we need to do is to get the details of SID and Serial# of the session to begin tracing of that session. Mostly these sessions are running with high cpu consumption or high waits or consuming high DB time.
So once we figured out the SID and Serial#, the session trace can be done as below
SQL> -- Finding session id and serial # to enable trace SQL> select username, sid, serial# from v$session where username='SOE'; USERNAME SID SERIAL# ------------------------------ ---------- ---------- SOE 783 48974 SQL> -- Enable trace for the session SQL> EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID=>783, SERIAL_NUM=>48974, WAITS=>TRUE, BINDS=>FALSE); PL/SQL procedure successfully completed. SQL> -- Now all the activities on session 783,48974 will be traced SQL> -- We can disable trace once we have confirmation from user he/she completed their work to investigate SQL> SQL> EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(SESSION_ID=>783, SERIAL_NUM=>48974); PL/SQL procedure successfully completed. SQL>If the session is disconnected before we stop tracing, tracing will automatically be stopped and upon trying to stop the tracing, we will get the below error
BEGIN DBMS_MONITOR.SESSION_TRACE_DISABLE(SESSION_ID=>7, SERIAL_NUM=>44); END; * ERROR at line 1: ORA-00030: User session ID does not exist. ORA-06512: at "SYS.DBMS_MONITOR", line 144 ORA-06512: at line 1
We can also use the DBMS_SYSTEM package to trace the session as below. DBMS_MONITOR explained above gives better control of what we need in terms of tracing
SQL> select username, sid, serial# from v$session where username='SOE'; USERNAME SID SERIAL# ------------------------------ ---------- ---------- SOE 1151 6659 SQL> exec sys.dbms_system.set_sql_trace_in_session(1151, 6659, TRUE); PL/SQL procedure successfully completed. SQL> exec sys.dbms_system.set_sql_trace_in_session(1151, 6659, FALSE); PL/SQL procedure successfully completed. SQL> desc sys.dbms_system ... ... PROCEDURE SET_SQL_TRACE_IN_SESSION Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SID NUMBER IN SERIAL# NUMBER IN SQL_TRACE BOOLEAN IN ... ... SQL>
So we now conclude with different methods of tracing a session. In the next post, we will take a look at Application tracing methods. Please check this link to learn on Application tracing methods
To convert raw trace file to human readable format, check this link on tkprof
References:
Happy tracing...!!!
