Trace Session
Oracle provides different options to trace an oracle session. Here we'll discuss all the options and you might choose the best suitable for your situation.
The SQL trace facility provides performance information on individual SQL statements, with the following statistics for each statement:
 | Parse, execute and fetch counts |
| CPU and elapsed times |
| Physical reads and logical reads |
| Number of rows processed |
| Misses on the library cache |
To get the CPU and elapsed time for each SQL statement, the init.ora parameter TIMED_STATISTICS should be set to TRUE. The default is FALSE. Setting this parameter to TRUE causes some overhead to the database.
The trace files are always written to the directory specified by USER_DUMP_DEST parameter in the init.ora file. The other parameter which affects trace files is MAX_DUMP_FILE_SIZE. This specifies the maximum size of trace files in operating system blocks. The default is 500. The files on UNIX platforms have a naming standard of ora_PIDNO.trc, where PIDNO is the UNIX process id corresponding to that session.
Trace Current Session
To trace current session, the SQL command is "ALTER SESSION SET SQL_TRACE = TRUE". To end tracing the command is "ALTER SESSION SET SQL_TRACE = FALSE". All the SQL statements are traced and written to the trace file under USER_DUMP_DEST.
If you are using PL/SQL procedure or you wish to trace the session from a client application, you may use the PL/SQL Oracle supplied package DBMS_SESSION to trace the current session. To begin tracing "DBMS_SESSION.SET_SQL_TRACE (TRUE);" and to end tracing "DBMS_SESSION.SET_SQL_TRACE (FALSE);".
Trace sessions are automatically terminated when the session terminates.
Trace another session
To trace another session, Oracle has provided a PL/SQL package called DBMS_SYSTEM. The procedure name is SET_SQL_TRACE_IN_SESSION, which accepts the SID, SERIAL# and TRUE/FALSE as input parameters. The SID and SERIAL# are obtained from V$SESSION dynamic view. The script curprog.sql provides the username, OS username, SID, serial#, UNIX process id and the program name which initiated the session.
To begin tracing from SQL*Plus, identify the SID and SERIAL# of the session using curprog.sql and do "EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (SID, SERAIL#, TRUE);" If you are invoking the trace from a PL/SQL program, the EXEC in the above statement can be removed. Making the third parameter to FALSE turns off tracing. The trace file is written to USER_DUMP_DEST on the server.
Trace multiple sessions of the same user
To trace multiple sessions of the same user you may use the script tsess.sql. This script reads the v$session view to get the SID and SERIAL# and calls the DBMS_SYTEM.SET_SQL_TRACE_IN_SESSION to begin trace. The username is a parameter to the procedure. The traced sessions are listed on the screen. The trace files can be identified by the UNIX process id numbers in the trace file names and can be easily associated with the program name displayed in the next column.
Trace all sessions in the database
To trace the entire database activity, set the init.ora parameter SQL_TRACE to TRUE and cycle the database (shutdown and startup). This will generate one trace file for each session established to the database. Use this option only if you're absolutely sure of what you want to do. There is a lot of overhead to the database when the traces are turned on and the trace files will consume lot of disk space. If you turn on SQL tracing in the database level, be sure to TURN IT OFF when you're finished.
How to read a trace file?
Though the trace file generated by Oracle is an ASCII text file, you may not understand anything by reading the file. Oracle provides a tool to interpret the trace files - TKPROF translates the trace file to readable output file. There are lot of sorting options available in tkprof, for a complete list of all sorting options, refer to Oracle7 Server Tuning Appendix A. tkprof can be used to generate execution plan of SQL statements also. Optionally, the trace file statistics can be saved in the database also.
The simple tkprof command can be "tkprof inputfile outputfile sys=no explain=userid/password". Where inputfile is the actual trace file to be interpreted, outputfile is the result file, sys=no suppresses all SQL statements issued by SYS (recursive SQL statements), explain=userid/password provides the execution plan of each SQL statement by connecting to the database. Omit the explain clause, if you're not interested to see the execution plan.
Here is a sample listing from the tkprof output file for one SQL statement.
SELECT TO_DATE((TO_CHAR(SYSDATE,'YYYY-MM') || '-01' ),'YYYY-MM-DD')
FROM DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 20 0.01 0.00 0 0 0 20
Fetch 20 0.00 0.00 0 20 40 20
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 43 0.01 0.00 0 20 40 40
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 936 (VIMPS) (recursive depth: 2)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
3 TABLE ACCESS (FULL) OF 'DUAL'
*******************************************************************************
I shall discuss what each column means in a tkprof output file at a different session.
See execution plan without using a trace file
1. EXPLAIN PLAN
What if you are interested only to see the execution plan of a SQL statement? You may not want to trace the session for SQL statement, interpret the trace file using tkprof, etc. To see the execution plan of a SQL statement, the SQL command EXPLAIN PLAN can be used. EXPLAIN PLAN uses a PLAN_TABLE to hold the execution plan. This table can be created by using the Oracle supplied script $ORACLE_HOME/rdbms/admin/utlxplan.sql. You query the PLAN_TABLE to get the execution plan after doing EXPLAIN PLAN.
I use the following script taken from "Oracle Performance Tuning" - O'Reilly & Associates, Inc. to see the execution plan of single SQL statement.
Truncate table plan_table
/
set pages 0 lines 80 feedback off
Explain plan for
{SELECT/UPDATE/DELETE STATEMENT}
/
SELECT LPAD(' ', 2*level) || operation ||' ' ||
options ||' ' || object_name "Execution Plan"
FROM plan_table
CONNECT BY PRIOR id = parent_id
START WITH ID = 1
/
set pages 24 feedback on
2. AUTOTRACE
SQL*Plus provides another way to see the execution plan of SQL statement, with actual
physical reads, hit ratio, sorts and number of rows processed. The syntax of SQL*Plus
tracing is
SET AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
AUTOTRACE processes the SQL statement. The output can be suppressed using the TRACEONLY option. Here are some examples.
SET AUTOTRACE ON - Turns ON tracing for SQL statements; displays retrieved rows, execution plan and Statistics. The following are the Statistics: recursive calls, db block gets, consistent gets, physical reads, redo size, bytes sent via SQL*Net to client, bytes received via SQL*Net from client, SQL*Net roundtrips to/from client, sorts (memory), sorts (disk) and rows processed.
SET AUTOTRACE TRACEONLY - Turns ON tracing, suppress rows, displays execution plan and statistics.
SET AUTOTRACE TRACEONLY EXPLAIN - Displays only the execution plan of SQL statement.
SET AUTOTRACE TRACEONLY STATISTICS - Displays only the statistics for the SQL statement.
SET AUTOTRACE OFF - Turn OFF tracing.
Another useful SQL*Plus SET command which can be used with the above is "SET TIMING ON", which shows the elapsed time of the SQL statement.
To use the EXPLAIN option, you must first create the table PLAN_TABLE in your schema, using the script $ORACLE_HOME/rdbms/admin/utlxplan.sql.
To access STATISTICS data, user must have access to several Dynamic Performance tables. Access can be granted using the role created in PLUSTRCE.SQL. This script creates a role called PLUSTRACE. Run plustrce.sql as SYS and grant the role to users who will use SET AUTOTRACE. The script can be found under $ORACLE_HOME/sqlplus/admin.
When SQL*Plus produces a STATISTICS report, a second connection to the database is automatically created. This connection is closed when the STATISTICS option is set to OFF, or you log out of SQL*Plus.
 Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, Oracle8i OCP and Oracle9i OCA/OCP Certified DBA |
Questions/Comments? Write to webmaster@bijoos.com. © The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster. |