Search My Oracle Blog

Custom Search

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:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016