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:
http://MOSC.oracle.com/MOSC/plsql/showdoc?db=NOT&id=224270.1
I have reproduced it below for your convenience. 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).
Regards,
Don Burleson
www.dba-oracle.com
www.remote-dba.net
*************************************************************
This article is being delivered in Draft form and may contain
errors. Please use the MOSC "Feedback" button to advise
Oracle of any issues related to this article.
*************************************************************
SQLTXPLAIN.SQL - Enhanced Explain Plan and related diagnostic info for
one SQL statement (8.1.5-9.2.0)
Carlos Sierra
Apps AOL-BDE Performance
Oracle Support
October 2002 (updated on
12-NOV-02)
Given one SQL Statement as input, generate a comprehensive report that
includes:
- Enhanced Explain Plan,
- Details about all schema objects in which the SQL statement depends
on,
- Rows count for all Tables accessed by the SQL Statement provided as
input,
- All Indexes and indexed columns for Tables reported,
- CBO Stats at all levels for these Tables and Indexes,
- Relevant INIT.ORA Parameters
The generated report includes extensive information that can be used to:
- Expedite the data collection process requested by Oracle APPS Support
when reporting one FORM or Concurrent Program performing poorly (Note:169935.1),
- Diagnose and troubleshoot typical SQL Tuning issues (specially
valuable for both APPS and non-APPS databases).
The scripts in this note supersede these two: COE_XPLAIN.SQL (Note:156958.1)
and COE_IMPORT_STATTAB.SQL (Note:156964.1).
This document (same as Note:215187.1)
can be used by technical analysts engaged in reporting, diagnosing or
troubleshooting SQL Tuning issues.
The report produced by the main script on this note includes the
following sections, directly related to the SQL statement <sql.txt> provided
as input to the SQLTXPLAIN.SQL:
- 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).
This note IS NOT restricted to Oracle APPS databases. It can be used for
SQL Tuning exercises on any RDBMS with Release 8.1.5 or higher. If you need
to generate Explain Plans for Oracle 8.0 or 7.3, use Note:156959.1
or Note:156960.1
respectively.
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.
SQLTXPLAIN.SQL has only one user parameter, which is the filename
referenced in this document as <sql.txt>. This file sql.txt contains ONE
SQL Statement for which the user wants to create a comprehensive report.
SQLTXPLAIN.SQL uses the SQL*Plus EXPLAIN command to generate the Explain
Plan into one of the SQLT$ staging tables. It finds back this particular
SQL command in the SQL Area and gets its object dependencies. From the
PLAN_TABLE and the V$OBJECT_DEPENDENCY, it finds all accessed Tables. Then
it gets all related Indexes, and it continues cascading into the 'ALL_' and
'DBA_' views to retrieve all the related data.
SQLTXPLAIN.SQL makes several calls to Procedures on dedicated Package
SQLT$XPLAIN. All the data extraction and subsequent storage into staging
tables is performed by the Procedures called from the main script
SQLTXPLAIN.SQL.
All staging SQLT$ Tables include a column STATEMENT_ID, which contains
the host name, the instance name and one sequence number. Column
STATEMENT_ID guarantees that each execution of the SQLTXPLAIN.SQL script
generates a unique identifier. Therefore, data on these staging Tables can
be easily migrated across similar instances.
Once all staging data has been extracted, the SQLTXPLAIN.SQL script
generates the spool file that becomes the final report. This report is
divided into sections with a 'tag' representing the 'ALL_', 'DBA_' or 'V$'
view from where the displayed information was extracted. The meaning of
each column is usually self-explanatory. The user of the report can always
refer to the DATABASE REFERENCE documentation for a detailed explanation of
the extracted columns. Some derived values are better understood by finding
how they are calculated directly from the SQLTXPLAIN.SQL script itself, and
the following two others: SQLTCTAB.SQL and SQLTCPKGB.PLS (see list of
Supplied Scripts
below).
The basic information on the generated report can be understood by
anybody: Tables, Indexes, and so on. More detailed information displayed in
the report is only valuable to analysts familiar with the SQL Tuning
process.
If you find the output of the SQLTXPLAIN.SQL too detailed, and just need
the basics, use SQLTX.SQL instead. This included script provides: one
Explain Plan, a list of accessed Tables (including NUM_ROWS), and a list of
related Indexes (including their columns). All in a very quick execution.
SQLTXPLAIN.SQL includes 6 seeded parameters. Leave the default seeded
values unless you need to modify according to particular requirements, or as
per Support instructions:
-
p_days_to_keep_repo
(30): Every execution of SQLTXPLAIN.SQL purges data from the staging
repository that is older than value specified.
-
p_compute_count_star
(YES): Performs a COUNT(*) on all accessed Tables. It is useful to
determine if CBO Stats should be re-gathered.
-
p_include_col_stats
(YES): CBO Stats for all indexed columns are always reported. When this
parameter is YES, CBO Stats for all table columns (besides indexed
columns) are also reported.
-
p_include_extents
(NO): When set to YES, Segments and Extents are reported. This info can
be valuable when using in conjunction to raw SQL Trace generated by EVENT
10046 Level 8 (see Note:171647.1).
Seeded value is NO because of known performance issues on 'ALL_' and
'DBA_' views.
-
p_include_histograms
(YES): When set to YES, Table, Partition and Sub-partition Histograms are
included for all indexed columns or table columns (the latter if
p_include_col_stats is also set to YES).
-
p_include_source
(YES): Controls if source code, on which the SQL Statement depends, is
reported or not (Triggers, Views, Packages, Procedures and Functions).
Before the staging SQLT$ repository is created (during the installation),
you may want to change the Tablespace to be used. See
Installing
SQLTXPLAIN.SQL and Staging Repository below.
- Download latest file
SQLT.zip
and uncompress into dedicated directory from where SQL*Plus can be
executed.
- If a different Tablespace other than USER's default needs to be used,
modify script SQLTCTAB.SQL accordingly (one line). Tablespace specified
must exists prior to installation. Estimated space utilization is in the
range of 10-50MB.
- To install or re-install, connect into SQL*Plus with USER that has
access to all Tables used by SQL Statement on <sql.txt>, plus access to
'ALL_', 'DBA_' and 'V$' views. If using on an APPS database, connect with
USER APPS.
# sqlplus apps/<apps_pwd>
SQL> START SQLTCREATE.SQL;
If not sure if Staging Repository has been previously installed or not,
simply re-install following same instructions above. Once installed, you
are ready to use the
SQLTXPLAIN.SQL
and
SQLTSTATTAB.SQL scripts concurrently.
If SQLTCPKGB.PLS errors with PLS-00201 while executed by SQLTCREATE.SQL,
execute then SQLTGRANT.SQL connecting into SQL*Plus with a user that has the
privilege of granting SELECT ON data dictionary views and ANALYZE ANY to
another user (SYSTEM, SYS or INTERNAL). Inline parameter is USER which will
be executing SQLTXPLAIN.SQL and which tried to install SQLTCREATE.SQL.
# sqlplus system/<system_pwd>
SQL> START SQLTGRANT.SQL <user>;
To uninstall the whole tool and repository, execute SQLTDROP.SQL and
remove SQLT* scripts from dedicated OS directory that contains them. If
script SQLTGRANT.SQL was ever used, execute also SQLTREVOKE.SQL after
SQLTDROP.SQL and before removing all SQLT* scripts.
For brief instructions and feedback, please refer to the INSTRUCTIONS.TXT
file included on same
SQLT.zip
downloaded file.
Start by uncompressing latest file
SQLT.zip
into one dedicated directory from where you can connect into SQL*Plus.
The file sql.txt, whose filename is provided as an inline parameter when
SQLTXPLAIN.SQL is executed, has some restrictions and characteristics
explained below. If you were not provided with one sql.txt file, create it
and place under same dedicated directory where you placed all SQLT scripts.
SQLTXPLAIN.SQL will try to open file <sql.txt> under same directory where
you placed all SQLT scripts.
Restrictions and characteristics for file sql.txt:
- It is a plain text file (flat file) with one and only one valid SQL
Statement to be explained (SELECT, INSERT, UPDATE or DELETE),
- It cannot have empty lines (blank lines),
- At the very end of the file, after the very last character of the SQL
Statement, one and only one 'carriage return' ('enter' or 'line feed')
should be provided, with no spaces before or after it (review file
sql0.txt provided as an example),
- The SQL Statement should NOT have a semicolon ';' at the end,
- If you get an error similar to 'Bind variable "b2" not declared', you
have empty lines within the SQL Statement, or at the end (review sql0.txt
provided as a correct example),
- Do NOT replace bind variables with literals. Since the SQLTXPLAIN.SQL
script does not execute the SQL Statement provided on the <sql.txt> file,
there is no need to replace the bind variables on it. Actually, by
replacing the bind variables with literals, the resulting Explain Plan can
change substantially and may lead to confusion or false conclusions,
- The filename <sql.txt> is NOT hard-coded. Therefore, if multiple SQL
Statements are being diagnosed, use filenames sql1.txt, sql2.txt,
sql3.txt, etc.; or any other set of names,
- File sql.txt is usually created out of the TKPROF by extracting a
specific expensive SQL Statement. File sql.txt is normally created with a
simple Cut&Paste OS command into one new flat file,
- File sql0.txt is provided as an example only (use it to test
SQLTXPLAIN.SQL on APPS databases)
To execute the SQLTXPLAIN.SQL script, login into SQL*Plus. If using
Oracle APPS, login with APPS USER and password. If using on a non-APPS
database, connect into SQL*Plus with same USER that CAN execute the SQL
Statement provided within the <sql.txt> file. Be aware that the USER
executing the SQLTXPLAIN.SQL script must have access to the objects
specified in the sql.txt file, PLUS to the 'ALL_', 'DBA_' and 'V$' views
(see also SQLTGRANT.SQL).
# sqlplus apps/<apps_pwd>
SQL> START SQLTXPLAIN.SQL sql.txt;
SQLTXPLAIN.SQL creates three files: SQLTXPLAIN.LOG, SQLT_<statement_id>.TXT
and a Raw SQL Trace. The first two are the log file and the actual report.
Use any text editor to review them (TextPad and WordPad work fine). Be sure
your lines are not wrapping around. These two files are usually created
under the same directory from where SQLTXPLAIN.SQL was executed. On NT,
these files may get created under $ORACLE_HOME/bin instead.
In most cases, the *.TXT report is enough to understand a specific
Explain Plan. In some cases, two additional files may be required:
- Raw SQL Trace generated by SQLTXPLAIN.SQL. Generated always under the
'udump' directory on the database server. Filename and exact location can
be found on the report or log files. Please do not TKPROF this particular
raw SQL Trace.
- Export file of the table SQLT$STATTAB, which contains the CBO Stats
related to the SQL Statement <sql.txt>. See
Migrating CBO
Stats across Similar Instances below for details on its use. To
export this table SQLT$STATTAB, use ORACLE_HOME 8.0.6 (if using Oracle
APPS), and execute command below. Keep in mind that exported file is
always BINARY and should be treated as such (if XFR as ASCII, this BINARY
file gets corrupted).
# exp apps/<apps_pwd>
file=SQLT tables='SQLT$STATTAB'
If you have problems executing the script SQLTXPLAIN.SQL, read this note
in detail and review/test example file provided sql0.txt (for Oracle APPS
databases).
If you notice in the output report that CBO Stats are outdated, or just
want to refresh them to test if the Explain Plan changes, use provided
script SQLTGSTATS.SQL. This script requires one parameter, which is the
STATEMENT_ID identifying your unique execution of the SQLTXPLAIN.SQL. It
then executes either FND_STATS or DBMS_STATS (APPS or non-APPS) and gathers
all CBO Stats related to Tables and Indexes shown in the SQLTXPLAIN.SQL
output report. If after gathering Stats the Explain Plan changes, try
testing the performance of the new plan.
During a SQL Tuning exercise it is common that in order to reproduce the
same Explain Plan into another but similar instance, the set of CBO Stats
used by the CBO to generate a 'bad' Explain Plan needs to be migrated from a
'Source' Instance with the 'bad' Explain Plan into a 'Destination' instance
with the currently 'good' Explain Plan.
To migrate CBO Stats from the Source instance into the Destination one,
simply:
- Execute
SQLTXPLAIN.SQL for same SQL Statement <sql.txt> in both Source and
Destination instances.
- Review and compare both *.TXT reports generated.
-
Export Table SQLT$STATTAB on Source instance (use Oracle Home 8.0.6 if
on APPS 11i).
- XFR exported BINARY file SQLT.dmp from source to destination server
(FTP or email).
-
Import Table SQLT$STATTAB on Destination instance.
- Restore CBO Stats for STATEMENT_ID into Data Dictionary using the
SQLTSTATTAB.SQL script on destination instance.
- You are ready to execute
SQLTXPLAIN.SQL on destination instance using CBO Stats from source
instance.
# imp apps/<apps_pwd>
file=SQLT tables='SQLT$STATTAB' ignore=y
This script requires only one parameter, which is the STATEMENT_ID for
which the corresponding CBO Stats are being restored from Table SQLT$STATTAB
into the Data Dictionary. Since STATEMENT_ID is always unique across
instances, Table SQLT$STATTAB supports CBO Stats for same <sql.txt> file
from different instances (for example Test and Production).
Execute this SQLTSTATTAB.SQL on the destination instance passing as
inline parameter the full or partial name of the STATEMENT_ID from the
source instance. Table SQLT$STATTAB should be
exported from the source instance and
imported into the destination instance, prior to execution of
SQLTSTATTAB.SQL.
To execute the SQLTSTATTAB.SQL script, login into SQL*Plus. If using
Oracle APPS, login with APPS USER and password. If using on a non-APPS
database, connect into SQL*Plus with same USER that will execute the
SQLTXPLAIN.SQL script in the destination instance. Be aware that the USER
executing the SQLTXPLAIN.SQL and SQLTSTATTAB.SQL scripts must have access
to the objects specified in the sql.txt file, PLUS ANALYZE ANY privilege
(see also SQLTGRANT.SQL).
# sqlplus apps/<apps_pwd>
SQL> START SQLTSTATTAB.SQL <statement_id>;
If SQLTSTATTAB.SQL finds CBO Stats for columns that exist in the source
instance but not on the destination one, it just skips the CBO Stats for
these columns and reports them as 'MISSING COLUMNS'.
Source and Destination instances should be very similar (like Test and
Production, or two APPS instances on same release).
Scripts for installation and maintenance
-
SQLTCREATE.SQL:
installs and re-installs the entire SQLT environment (calls SQLTDROP.SQL,
SQLTCTAB.SQL, SQLTCPKGS.PLS and SQLTCPKGB.PLS).
-
SQLTDROP.SQL:
uninstalls the entire SQLT environment by dropping SQLT Package, Views,
Tables and Sequence.
-
SQLTCTAB.SQL:
creates all SQLT$ schema objects except Package SQLT$XPLAIN (creates
Sequence, Tables and Views). It also seeds required and recommended
INIT.ORA parameters for APPS 11i. If a different Tablespace is required
other than default for USER, modify this script (only one line) prior to
execution.
-
SQLTCPKGS.PLS:
creates Package Specs for SQLT$XPLAIN.
-
SQLTCPKGB.PLS:
creates Package Body for SQLT$XPLAIN.
-
SQLTGRANT.SQL:
grants SELECT ON some data dictionary views, and ANALYZE ANY, to user of
SQLTXPLAIN.SQL.
-
SQLTREVOKE.SQL:
revokes grants created by SQLTGRANT.SQL to user of SQLTXPLAIN.SQL.
-
SQLTRUNC.SQL:
truncates all staging Tables on the SQLT environment, with the exception
of the seeded required and recommended INIT.ORA parameters for APPS 11i.
-
SQLTPURGE.SQL:
purges old data out of the staging Tables on the SQLT environment, leaving
only 'X' number of days (where 'X' is an input execution parameter).
Scripts for SQL Tuning
-
SQLTXPLAIN.SQL:
generates a SQLTXPLAIN report and a raw SQL Trace. It also dumps CBO
Stats for one STATEMENT_ID from the Data Dictionary into the Table
SQLT$STATTAB.
-
SQLTX.SQL: This is
the light version of the SQLTXPLAIN.SQL. Executes using same syntax and
generates just an Explain Plan, list of Tables (including NUM_ROWS), and
list of Indexes (and indexed columns).
-
SQLTGSTATS.SQL:
Uses FND_STATS (APPS) or DBMS_STATS (non-APPS) to gather the CBO Stats for
all Tables and Indexes related to one execution of the SQLTXPLAIN.SQL.
-
SQLTSTATTAB.SQL:
restores CBO Stats for one STATEMENT_ID from the Table SQLT$STATTAB back
into the Data Dictionary.
Other files included inside
SQLT.zip
-
INSTRUCTIONS.TXT:
Brief instructions in how to install and execute SQLTXPLAIN.SQL.
-
NOTE.HTML: This
document (same as Note:215187.1)
-
sql0.txt: Example
of one <sql.txt> file that can be used to test SQLTXPLAIN.SQL on APPS
10.7, 11.0 or 11i databases (RDBMS releases 8.1.5 or higher).
All SQLT scripts mentioned in this note may be obtained within compressed
file SQLT.zip from the following external FTP directory. Get always latest
version. Current version of SQLT scripts is 12-NOV-02.
ftp://oracle-ftp.oracle.com/apps/patchsets/AOL/SCRIPTS/PERFORMANCE/
If interested in learning more in Troubleshooting Oracle Apps Performance
Issues, read Note:169935.1
