10046.pl README

10046.pl is a wait event orientated tool used to analyze
Oracle (10046) trace files containing wait events. The tool
provides overall wait event statistics including a section
listed by onject_id / event statistics as well as histograms
showing event elapsed time distributions. There are also options
to show similar details at the cursor level. 10046.pl also provides an
option to reporrt row source plans for cursors and breaks out
child cursors with differing plan hash values within the report.

Usage:  10046.pl -h | [ -c conn_str -e -p -s sort_key_list -S -w count ]
                     { -t trace_file.trc -o output_file}

 -c conn_str : Connect to database with specified connect string.n -e
 Include cursor events and event histograms
 -p          : Include row source plans and statistics
 -s          : List of sort keys
 -S          : Suppress reporting of SYS executed SQL
 -w count    : Adjusts the histogram display (see below).

By default 10046.pl reports overall wait event statistic.

The -c conn_str option requires that you are connecting to the
database from which the trace was generated. It is used to
translate Object Ids into object names. The connect string should be
for a DBA logon. This option is ignored for pre-10.2 trace files.
This option uses sqlplus to make a connection and also requires
that your operating system environment is conditioned for connection
to the Oracle database from which the trace file was generated.

The -e and -p flags are used to request extra
details relating to cursor operations.

Valid sort keys are:

EXECNT : Number of execute was called
EXECPU : CPU time spent executing
EXECU  : Number of buffers for current read during execute
EXEDSK : Number of disk IOs during execute
EXEELA : Elapsed time executing
EXEMIS : Number of library cache misses during execute
EXEQRY : Number of buffers for consistent read during execute
EXEROW : Number of rows processed during execute
FCHCNT : Number of times fetch was called
FCHCPU : CPU time spent fetching
FCHCU  : Number of buffers for current read during fetch
FCHDSK : Number of disk IOs during fetch
FCHELA : Elapsed time fetching
FCHQRY : Number of buffers for consistent read during fetch
FCHROW : Number of rows fetched
HV     : The Hash Value of the SQL statements
PRSCNT : Number of times parse was called
PRSCPU : Cpu time parsing
PRSCU  : Number of buffers for current read during parse
PRSDSK : Number of disk IOs during parse
PRSELA : Elapsed time parsing
PRSMIS : Number of misses in library cache during parse
PRSQRY : Number of buffers for consistent read during parse
UID    : Userid of user that parsed the cursor

Sort keys, which are case insensitive, can be combined by supplying a list
of one or more keys separated, by comma characters. 10046.pl sorts by the sum
of the specified keys. The sort operation only affects the order of the
Cursor sections of the report. These are reported in descending order of
the sum of the sort keys. The HV and UID sort keys can only be specified
in isolation. The Default sort order is based on PRSELA, EXEELA and FCHELA.


   10046.pl -t my_trace_file.trc -o report.txt -p -e -s prscpu,execpu,fchcpu

10046.pl reads my_trace_file.trc and writes the report to report.txt
We are also requesting cursor level event details (-e) to be reported
as well as cursor operation statistics and row source plans (-p) for the
cursors. The cursor details section is sorted by descending sum of:

- Parse CPU Time.
- Execution CPU Time.
- Fetch CPU Time.

The defaulted report histogram buckets range 200us to 1024ms,
but you can use the -w option to slide the window so that the range reported
is shifted by count buckets.To start with the lowest bucket (50us) use -w 0.
To slide the window view to the right, increase the value of count.
The default value for count is 2 which means that the low end of the
reported bucket range is 200us. The count value can range from 1 to 3.

The script was developed using Perl 5.12. For Windows users you can obtain
Perl from http://www.ActiveState.com.