28 March, 2010

Extracting Application / User SQLs from a TraceFile

The tkprof utility has an option to extract Application / User (i.e. other than "recursive") SQLs from a Trace File and place them in another file for review.
Such a file can be used to "playback" all the SQLs of a user session -- e.g. when attempting load / performance testing.
Note that it does NOT capture user "think" time -- the time that occurs on the client side (or application server) and is a SQL*Net wait on the database server.

For example, I have run these SQLs in a session that is being traced (the tracing can be level 1 tracing without WAITs and BINDs) :

select Table_name,Num_Rows from User_Tables order by 1;
select num_rows, blocks from user_tables where table_name = 'SOURCE_TABLE';
select COUNT(DISTINCT(OWNER)) FROM SOURCE_TABLE;

I then review the Trace file for the session, adding the paramater "record=":

ora10204>tkprof ort24fs_ora_5117.trc my_session.prf record=Session_SQLs.sql

TKPROF: Release 10.2.0.4.0 - Production on Sun Mar 28 20:05:18 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.


ora10204>
ora10204>
ora10204>cat Session_SQLs.sql
select Table_name,Num_Rows from User_Tables order by 1 ;
select num_rows, blocks from user_tables where table_name = 'SOURCE_TABLE' ;
select COUNT(DISTINCT(OWNER)) FROM SOURCE_TABLE ;
ora10204>

I can see the actual SQLs, not including the recursive SQLs.

Thus if you have a very large trace file of a session having run dozens of SQL statements, you can either review the tkprof output file (my_session.prf in my example above) or use the output of the record output -- the latter presents the SQL in a "cleaner" fashion.

.
.
.

No comments: