This is just a short note the point out that RECORD option of TKPROF is extremely useful in recording and capturing the non-recursive SQL statements in the order of execution from the SQL trace file. I have not had a requirement to use it until—today—one of my developers lost the original test script used when tracing the session.
Syntax of TKPROF:
C:\>tkprof Usage: tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ] table=schema.tablename Use 'schema.tablename' with 'explain=' option. explain=user/password Connect to ORACLE and issue EXPLAIN PLAN. print=integer List only the first 'integer' SQL statements. aggregate=yes|no insert=filename List SQL statements and data inside INSERT statements. sys=no TKPROF does not list SQL statements run as user SYS. record=filename Record non-recursive statements found in the trace file. waits=yes|no Record summary for any wait events found in the trace file. sort=option Set of zero or more of the following sort options: prscnt number of times parse was called prscpu cpu time parsing prsela elapsed time parsing prsdsk number of disk reads during parse prsqry number of buffers for consistent read during parse prscu number of buffers for current read during parse prsmis number of misses in library cache during parse execnt number of execute was called execpu cpu time spent executing exeela elapsed time executing exedsk number of disk reads during execute exeqry number of buffers for consistent read during execute execu number of buffers for current read during execute exerow number of rows processed during execute exemis number of library cache misses during execute fchcnt number of times fetch was called fchcpu cpu time spent fetching fchela elapsed time fetching fchdsk number of disk reads during fetch fchqry number of buffers for consistent read during fetch fchcu number of buffers for current read during fetch fchrow number of rows fetched userid userid of user that parsed the cursor
I enabled trace and executed series of statements below:
SQL> CREATE TABLE t AS SELECT level id FROM dual CONNECT BY level < 10; Table created. SQL> DELETE t WHERE rownum < 5; 4 rows deleted. SQL> UPDATE t SET id=id*10; 5 rows updated. SQL> SELECT * FROM t; ID ---------- 50 60 70 80 90
I ran TKPROF with RECORD option (masked the path on C: prompt):
C:\app.....lab11r2\trace>tkprof record=mystatements.txt trace=lab11r2_ora_4520.trc output=myoutput.txt TKPROF: Release 126.96.36.199.0 - Development on Mon Apr 4 13:59:13 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
If you view the output file, mystatements.txt, it contains the series of statements executed in a traced session.
C:\app.....lab11r2\trace>type mystatements.txt CREATE TABLE t AS SELECT level id FROM dual CONNECT BY level < 10 ; DELETE t WHERE rownum < 5 ; UPDATE t SET id=id*10 ; SELECT * FROM t ;
This, RECORD, option is available since Oracle 7, see the documentation links below:
- Oracle 11g: http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/sqltrace.htm#PFGRF94987
- Oracle 10g: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm#sthref1517
- Oracle 9i: http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/sqltrace.htm#1256
- Oracle 8i: http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a76992/ch14_str.htm#1256
- Oracle 7: http://download.oracle.com/docs/cd/A57673_01/DOC/server/doc/A48506/stracea.gif