TKPROF (Trace Kernel Profile)

 

Tkprof command line options

• explain ( must use username/password)
• table (plan_table)
• sys (statements issued against data dictionary)
• sort (isolate sql statements )
• print (limit number of sql statements to formatted trace file)
• aggregate (should statistics from multiple user of same sql be clubbed together)
• wait (wait statistics happened during trace)

 

Sorting options

 

• Parse (syntax check, privileges …)
• Prscnt = no. of times sql was parsed
• Prscpu = amount of CPU spent for parsing sql
• Prsels = amount of elapsed time spent for parsing sql
• Prsdsk = no. of disk reads while sql was parsed
• Prsqry = no. of “consistent gets” read while parsing
• Execute (create table, alter table …)
• Execnt
• Execpu
• Exeels
• Exedsk
• Exeqry = no. of “consistent gets” read while executing the SQL
• Fetch (only for select)
• Fchcnt
• Fchcpu
• Fchels
• Fchdsk

 

tkprof ora_1234.trc trace.txt sys=no \ explain=shekhar/password print=10 \

insert=trace.sql sort=fchcpu

 

 

How to read formatted trace file

 

• Count : number of time the phase (parse, execute, fetch occur)
• CPU: CPU time (in seconds) used to the phase
• Elapsed: elapsed time (in seconds) for the phase
• Disk: no. of data blocks read from disk
• Query: No of blocks read in “consistent get” mode
• Current: No. of blocks read in “db block” mode
• Rows: no. of rows affected by sql

 

What to look for in formatted trace file

 

SQL Statements that:

• Consume excess cpu resources
• Take long time to parse, execute or fetch
• Read too many data blocks from disk and less from SGA
• Access many data blocks but return few rows
• Do not use bind variables
• Look for utilization of indexes, FTS in Explain plan

 

When to use TKPROF

• When a particular session is having performance issues
• When you want to capture SQL statements from front-end app (especially purchased out-of-the-box systems)

How to create a raw trace file

• ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
• user_dump_dest must be set
• Create plan table (@/rdbms/admin/utlxplan.sql)
• Start trace
• DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true)è use v$session to find sid and serial#
• ALTER SESSION SET SQL_TRACE = TRUE;
• DBMS_SESSION.SET_SQL_TRACE(TRUE);
• Trace file naming convention: instance_ora_pid.trc
• Stop generating trace files  by using same commands (FALSE)

Sample of formatted trace file

 

 

 

Additional Notes

 

A 'db block get' is a current mode get. That is, it's the most up-to-date copy of the data in that block, as it is right now, or currently. There can only be one current copy of a block in the buffer cache at any time. Db block gets generally are used when DML changes data in the database. In that case, row-level locks are implicitly taken on the updated rows. There is also at least one well-known case where a select statement does a db block get, and does not take a lock. That is, when it does a full table scan or fast full index scan, Oracle will read the segment header in current mode (multiple times, the number varies based on Oracle version).

A 'consistent get' is when Oracle gets the data in a block which is consistent with a given point in time, or SCN. The consistent get is at the heart of Oracle's read consistency mechanism. When blocks are fetched in order to satisfy a query result set, they are fetched in consistent mode. If no block in the buffer cache is consistent to the correct point in time, Oracle will (attempt to) reconstruct that block using the information in the rollback segments. If it fails to do so, that's when a query errors out with the much dreaded, much feared, and much misunderstood ORA-1555 "snapshot too old".

 

If non-DBAs need access to trace files, add

_trace_files_public = true to the parameter file to avoid permissions problems on Unix platforms