Oracle Database - Trace Tool

About

You can enable the Trace feature either:

• within Oracle Enterprise Manager Database Control
• or by entering SQL commands with the DBMS_MONITOR package.

Tools for tracing your work:

• dbms_monitor
• and tkprof.

Articles Related

Analyze

To analyze the information in the Trace file:

• use the following guidelines when reviewing the Trace file:
  • When you have traced many statements at once, such as in batch processes, quickly discard any statements that have acceptable query execution times. Focus on those statements that take the longest times to execute.
  • Check the Query column for block visits for read consistency, including all query and subquery processing. Inefficient statements are often associated with a large number of block visits. The Current column indicates visits not related to read consistency, including segment headers and blocks that will be updated.
  • Check the Disk column for the number of blocks that were read from disk. Because disk reads are slower than memory reads, the value will likely be significantly lower than the sum of the Query and Current columns. If it is not, check for issues with the buffer cache.
  • Locking problems and inefficient PL/SQL loops can lead to high CPU time values even when the number of block visits is low.
  • Watch for multiple parse calls for a single statement, because this indicates a library cache issue.
• After identifying the problem statements in the file, check the execution plan to learn why each problem statement occurred.