Here is a great new script from Carlos Sierra, a brilliant developer
at Oracle Corporation. This script is an enhancement to the
Center of Excellence (COE) script top produce a super-detailed trace
of SQL execution. This new script is remarkable, and I hope that
you enjoy it as much as I do. The
MOSC page is reproduced
here with Mr. Sierra's permission:
I have reproduced it below for your convenience (see below).
Carlos' script enhances standard execution plan analysis by providing:
Enhanced Explain Plan (including execution order, indexed
columns, rows count and blocks for tables);
Schema Object Attributes for all Tables and Indexes
accessed by the SQL statement being diagnosed (including: object
dependencies, tables, indexes, columns, partitions, sub-partitions,
synonyms, policies, triggers and constraints);
CBO Statistics (for levels: table, index, partition,
sub-partition and column);
Histograms (includes table, partition and sub-partition
Space utilization and administration (for tables, indexes,
partitions and sub-partitions);
Objects, Segments, Extents, Tablespaces and Datafiles
(including current performance of datafiles);
RDBMS Initialization Parameters INIT.ORA (required,
recommended and default for an APPS 11i database, and all other parameters
SET on the INIT.ORA file);
Source Code in which the SQL Statement and accessed Tables
depend on (Triggers description and body, Views columns and text, Packages
specs and bodies, Procedures and Functions).
Enhanced Explain Plan and related diagnostic info for one SQL statement
SQLTXPLAIN.SQL is one SQL*Plus script that using a small
staging repository and a PL/SQL package creates a comprehensive report to
gather relevant information on ONE SQL statement (sql.txt).
COE_XPLAIN.SQL performs the same functionality, with some limitations and
SQLTXPLAIN.SQL differs from COE_XPLAIN.SQL in the following
SQLTXPLAIN.SQL collects more data about the
objects in which the SQL Statement <sql.txt> depends on. It uses
V$OBJECT_DEPENDENCY to find these dependencies.
SQLTXPLAIN.SQL can be used by multiple users concurrently.
It keeps all staging data organized by unique STATEMENT_ID, therefore it
can handle concurrency and historical data.
SQLTXPLAIN.SQL creates a better organized and documented
report output. Report sections that are not needed on a specific SQL
Statement <sql.txt> are just skipped over in the new report without any
headers or references.
SQLTXPLAIN.SQL allows to keep multiple versions of CBO
Stats into the same table SQLT$STATTAB. Therefore, similar sets of
CBO Stats can be restored into the Data Dictionary multiple times during a
SQL Tuning exercise (without loosing original Stats).
SQLTXPLAIN.SQL is subject to future improvements and
additions, while COE_XPLAIN.SQL is not.
SQLTXPLAIN.SQL performs better than COE_XPLAIN.SQL for
same SQL Statement <sql.txt>.
SQLTXPLAIN.SQL reports sup-partitions details.
SQLTXPLAIN.SQL reports actual LOW and HIGH 'boiled' values
of all columns on tables being accessed. It also reports Histograms
in a more comprehensive format.
SQLTXPLAIN.SQL does not report some data shown on
COE_XPLAIN.SQL that was actually not used during SQL Tuning exercises,
making the new report easier to understand.
COE_XPLAIN.SQL evolved during 2 years, while
SQLTXPLAIN.SQL was designed from scratch.