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
MetaLink 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
levels);
-
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).
SQLTXPLAIN.SQL -
Enhanced Explain Plan and related diagnostic info for one SQL statement
(8.1.5-9.2.0)
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
restrictions.
SQLTXPLAIN.SQL differs from COE_XPLAIN.SQL in the following
ways:
-
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.
