User Tracing Oracle

Tracing Users' SQL Statements

Administration Tips

Tracing Users' SQL Statements When tuning a database, it's important to know what SQL is being issued by Users, and how it is being parsed and executed. There are a number of approaches to obtaining such information.

EXPLAIN PLAN If you know the SQL being submitted, you can simply generate an explain plan for it, and have that plan sotred within a table created specially for the purpose. To create the explain plan table, you execute the utlxplan.sql script provided by Oracle in the ORACLE_HOME/rdbms/admin directory. It creates a table called, cunningly enough, PLAN_TABLE. Once that's done (and, since it's a genuine table, it only needs to be done once), you issue this sort of command: EXPLAIN PLAN FOR SELECT

*

FROM SCOTT.EMP;

This doesn't actually execute the query (so there are no performance or I/O complications to worry about), but the plan that would be produced were it to be submitted by a User for real is written into PLAN_TABLE. You can thus simply query the table, and see what sort of execution plan is being produced for each statement. You probably want to truncate the table after each analysis, so it doesn't get cluttered with a myriad of execution plans.

SQL TRACE and TKPROF Instead of writing the plan into a static table, Oracle is able to generate a user-process trace file containing the crucial information. Unfortunately, the tracefile output is only readable by humans if they have advanced degrees in hieroglyphics and the subtleties of Sumerian palace intrigues. Therefore, we have to pipe the raw stuff through the TKPROF utility to turn it into something mere mortals can make sense of. To enable this functionality, you need first to make sure that the init.ora parameter timed_statistics is set to TRUE, and that user_dump_dest is set to something sensible (a directory where trace files can be found easily). You might also want to check that max_dump_file_size is set to, say, 1M so as to prevent absolutely mammoth trace files being generated by mistake. Then you need simply switch tracing on. If it's your own session, you simply issue the command: Copyright © Howard Rogers 2001

10/18/2001

Page 1 of 4

Tracing Users' SQL Statements

Administration Tips

ALTER SESSION SET SQL_TRACE=TRUE

But if you are doing this on behalf of another User, you need first to determine the SID and SERIAL# that uniquely identify their session: SELECT USERNAME, SID, SERIAL# FROM V$SESSION;

...followed by: EXECUTE DBMS_SESSION.SET_SQL_TRACE_IN_SESSION(7,32,TRUE)

Those two numbers in the brackets are the SID and SERIAL# just determined by querying v$session. Note that you'll probably need to be connected as SYS for this to work. Any new SQL statements issued by the specified session will now be traced, and the output will be going to some trace file in the directory pointed at by the user_dump_dest parameter. You may find it tricky to work out which tracefile belongs to which session, because if you do tracing even vaguely regularly, there will probably be dozens of likely candidates to choose from. The simplest way to sort that out is to use the time and date stamp on the files themselves, but if you need a more accurate way to determine the filename in which to get interested, try this query: SELECT P.SPID FROM V$PROCESS P,V$SESSION S WHERE P.ADDR=S.PADDR AND S.SID=&SID AND S.SERIAL#=&SERIALNO;

When run, you'll be prompted to supply the SID and SERIAL# values previously used when switching tracing on in the first place ...and that gives you the process number that will be included in the trace file name (on NT, you'll have a file called ORA0<process_number>.TRC, and on Unix it will generally be called sid_ora_<process_no>.trc). Do not attempt to work with the tracefile, though, until you have remembered to switch SQL tracing OFF for the relevant session -otherwise the tracefile doesn't get closed properly, and if it has any contents at all, they probably won't be useable. To switch tracing off for a session, simply enter the command: EXECUTE DBMS_SESSION.SET_SQL_TRACE_IN_SESSION(7,32,FALSE)

Again, the two numbers specified there are the SID and SERIAL# numbers, determined from v$session, that uniquely identify any Oracle session. Copyright © Howard Rogers 2001

10/18/2001

Page 2 of 4

Tracing Users' SQL Statements

Administration Tips

Now you have a tracefile, properly identifiable, it's time to format it using the tkprof utility. Just type the following command at the operating system prompt: TKPROF TRACEFILE.TRC OUTPUTFILE.TXT

...and that will produce a text file (of whatever name you specify) which contains the full parse and execute statistics for the previously-selected session for the time during which tracing was switched on. Obviously, when you do this for real, substitute in the correct name for the trace file being formatted, and choose an appropriate output file name. The tkprof utility takes a number of options, but probably the most useful one would be to switch off recursive SQL. This means that all the internal queries that go on under the hood when a User issues a SQL command are stripped out of the output file, leaving behind only the commands that the User would identify as having been issued by himself. To do that, you'd type the following command: TKPROF TRACEFILE.TRC OUTPUT.TXT SYS=NO

AUTOTRACE An alternative way of obtaining trace information is to use the SQL Plus feature called 'autotrace'. For this to work, you still need an explain plan table (see above for how to create one, but the script involved is called utlxplan.sql). Additionally, you need to execute the plustrace.sql script (supplied by Oracle, and available in the ORACLE_HOME/sqlplus/admin directory), in order to create a PLUSTRACE role. You can then grant that role to Users who don't have the DBA role, and they'll be able to generate traces successfully. Without this step, only DBAs can enable autotrace. The final step is to issue the command: SET AUTOTRACE ON

Note that this is an internal SQL Plus command, not a SQL statement -so there's no concluding semi-colon, and the command won't work in something like Server Manager. After that, every SQL statement issued in the session will not only cause the statement to be executed, but will display trace statistics for that statement, too. The advantages of this over setting sql traceing on are obvious: the feedback is immediate, and there's no need to run tkprof to format the output before it makes sense. There are severe drawbacks, too, though: you can't set autotrace on on behalf of another User -it has to be enabled by a session, and then only that session's SQL statements are traced. What's more, unlike the Explain Plan, the SQL statements being issued within the session Copyright © Howard Rogers 2001

10/18/2001

Page 3 of 4

Tracing Users' SQL Statements

Administration Tips

are actually executed. When you use the "explain plan for..." method, the ensuing SQL statement is not actually executed, but merely parsed. The final killer drawback is that autotrace is SQL Plus specific -it can't work in 3rd party applications, nor in Server Manager. Note that autotrace can be set to various other states than simply ON and OFF. For example, there are these variants: SET AUTOTRACE TRACEONLY SET AUTOTRACE ON EXPLAIN SET AUTOTRACE TRACEONLY STATISTICS

The first of these variants suppresses the display of the actual results of the SQL statements being issued. The execution of the SQL still takes place, but no results are actually returned to the session. All you see are the trace statistics for the parse and execute phases. The second option displays the result set of the SQL statement being traced, along with the execution plan, but no parse or execute statistics are displayed. And the third option suppresses the result set of the SQL statement being traced, along with the parse and execute statistics, but the execution plan is not shown When you've finished tracing a session, just issue the command: SET AUTOTRACE OFF

...and SQL Plus will revert to its normal behaviour.

Copyright © Howard Rogers 2001

10/18/2001

Page 4 of 4