9i - 10g - Enhanced Explain Plan Instructions

ShowDoc

Page 1 of 4

Bookmarks

Headlines Knowledge Browser

Quick Find

Advanced Search

Knowledge Bug Search

Service Request

Configuration Support Manager

Admin

Profile

Feedback

Patches & Updates

Sign Out

Forums

Help

Certify

Secure Enterprise Search

Knowledge Base Go

Did this article help solve your problem? Select

Secure Enterprise Search

Would you recommend this document to others? Select

TIP: Click help for a detailed explanation of this page.

Advanced

Saved Searches

Submit

Bookmark

Go to End

Subject: SQLTXPLAIN.SQL - Enhanced Explain Plan and related diagnostic info for one SQL statement Doc ID: Note:215187.1 Type: TROUBLESHOOTING Last Revision Date: 22-SEP-2006 Status: PUBLISHED Abstract SQLTXPLAIN Enhanced Explain Plan and related diagnostic info for one SQL statement Given one SQL DML statement, it generates a comprehensive report that includes the particular explain plan for the SQL statement, cost-based optimiz statistics for the objects accessed by the statement, and details of related objects. The output HTML report produced can be used to analyze and diagn performance issues (SQL tuning). RDBMS 9i (9.2), 10g (10.1 and 10.2), or higher. Product Name, Product Version Can be used for Oracle Apps 11i or higher, or for any other application running on top of an Oracle database Platform Date Created Author

Platform independent Version 2.0.2 on September 2006. Carlos Sierra

Instructions Execution Environment: Once this tool is installed (under its own schema), it is executed from SQL*Plus connected as the application schema that generated the SQL statement to be explained.

https://metalink.oracle.com/metalink/plsql/f?p=130:14:2419697382650541698::::p14_database_id,p14_docid,p14_show_header,... 1/2/2007

ShowDoc

Page 2 of 4

For example, if used on an Oracle Applications instance, execute using the APPS user. For installation instructions, read file instructions.txt contained in zip file. Access Privileges: To install or uninstall, it requires a connection as a user with SYSDBA privilege. Once installed, it does not require special privileges, and it can be executed from any application schema user. Usage: sqlplus <usr>/ start sqltxplain.sql ; Note: The application user executing the sqltxplain.sql only needs to have this script and the text file that contains the SQL statement being analyzed. All other files contained in the zip file are only used to install or uninstall the tool. The output HTML report is generated first into the UDUMP directory, then copied over into the local directory using a spool file. Example: sqlplus apps/apps; start sqltxplain.sql sql5.txt; Installation: Instructions, sample report and tool are included in file sqlt.zip. PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text editors, e-mail packages, and operating systems handle text formatting (spaces, tabs, and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected. The script will produce an output file named sqlt_x_x_s.html. This file can be viewed in a browser or uploaded for support analysis. Description The output report includes the following (sample report is provided): 1. Table of contents for an easy navigation within the HTML report. 2. Identification block: host, instance, input filename, RDBMS version, etc. 3. SQL statement being diagnosed. 4. Enhanced explain plan for SQL statement. 5. Object dependencies for SQL statement (schema objects). 6. Tables accessed by the SQL statement, CBO stats and relevant properties. 7. Indexes for tables accessed, CBO stats and relevant properties. 8. Table columns including CBO stats, low-high values and relevant properties. 9. Index columns including CBO stats and relevant properties.

https://metalink.oracle.com/metalink/plsql/f?p=130:14:2419697382650541698::::p14_database_id,p14_docid,p14_show_header,... 1/2/2007

ShowDoc

10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21.

Page 3 of 4

Table level histograms for all columns (data distribution per bucket). Table partitions (when applicable). Index partitions (when applicable). Table partition columns (when applicable). Table partition histograms (when applicable). Table subpartitions (when applicable). Index subpartitions (when applicable). Table subpartition columns (when applicable). Table subpartition histograms (when applicable). Metadata for tables, indexes, views, PL/SQL libraries (object dependencies). Initialization Parameters (for Oracle Apps instances). Cost-based Optimizer Trace (EVENT 10053).

Execution Parameters (only one) input_filename (req) Name of the text file that contains the one SQL statement to be analyzed. This parameter is case sensitive on UNIX/Linux platforms. Example: sqlplus apps/apps; start sqltxplain.sql sql5.txt; Note: The SQL statement contained in the file whose name is passed as the execution parameter, must not contain a semicolon ";" at its end. Blank lines should be avoided within the input file. File sql5.txt included in zip file is provided as an example. References Troubleshooting Oracle Apps Performance Issues 169935.1 Trace Analyzer - Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046 224270.1 Implementing and Using the PL/SQL Profiler 243755.1 SQLAREAT.SQL - SQL Area, Plan and Statistics for Top DML 238684.1 SQLTXPLAIN older version (8.1.5 and higher) is contained in file sqlt_old.zip bde_x.sql - Simple Explain Plan for given SQL Statement (8.1 and higher) 174603.1 coe_xplain_80.sql - Enhanced Explain Plan For Given SQL Statement (8.0) 156959.1 coe_xplain_73.sql - Enhanced Explain Plan For Given SQL Statement (7.3) 156960.1 Script sqlt.zip - Compressed file with tool, sample report and installation instructions (RDBMS 9i or 10g) sqlt_old.zip - Compressed file with older version that can be used for RDBMS 8i or 9.0.1

.

https://metalink.oracle.com/metalink/plsql/f?p=130:14:2419697382650541698::::p14_database_id,p14_docid,p14_show_header,... 1/2/2007

ShowDoc

Page 4 of 4

Bookmarks Copyright © 2006, Oracle. All rights reserved.

Admin

Profile

Feedback

Sign Out

Help Legal Notices and Terms of Use | Priv

https://metalink.oracle.com/metalink/plsql/f?p=130:14:2419697382650541698::::p14_database_id,p14_docid,p14_show_header,... 1/2/2007