SQLTXPLAIN is a tool that generates a comprehensive report about a SQL statement which has been provided as input to the tool. The report includes the explain plan for a given SQL statement, cost-based optimizer statistics for the objects accessed by the statement, and details of related objects. The output HTML report produced can be used to analyze and diagnose SQL performance issues (SQL tuning).

Unlike SQL statements "Explain Plan" and "Autotrace", SQLTXPLAIN offers complete environment and configuration reports relating the SQL we exmaines. The report is categorized as followings.

• Environment
• SQL Identification
• SQL Statement
• Explain Plan
• Observations
• Object Dependency
• Objects
• Tables
• Indexes
• Table Columns
• Index Columns
• Constraints
• Column Histograms
• Metadata
• Tablespaces
• Initialization Parameters
• Optimizer Trace (EVENT 10053)
• Tool Configuration Parameters

During the installation of SQLTXPLAIN, the following parameters will be asked for value.

• Specify SQLTXPLAIN password:
  
• Enter value for host_string: (For instance, it your db name is prod, then input @prod)
• Enter value for application_schema: (the schema under which you examine sql statement)
• Enter value for default_tablespace:
• Enter value for temporary_tablespace:

The process of installation is quite straightforward. From my understanding, the complicated report offered is very helpful because SQL statements "Explain Plan" and "Autotrace" can only produce the explain plan. Mostly, we have to find other information to figure out performance issue of sql statemetn, such as constraints, column histograms, init parameter and so on.

This tool is available at Metalink Note: 215187.1. Also,  take a look at  attached  doc about SQLTXPLAIN Overview.  

User Guide:

• XPLAIN Entry point -- @sqltxplain.sql [name of your file with one SQL]
• XTRACT -- 1. find sql_id or hash_value in V$SQL or v$SQLTEXT (see following post)2. @sqltxtract.sql [hash_value or sql_id for one SQL]
• XECUTE -- @sqltxecute.sql [name of your script with one SQL]

Also, see attached comprehensive report for XPLAIN entry point running.

When we use XTRACT entry point, we must be executing this script connected as
the application user that executed original SQL. Therefore, finding owner of examining sql is necessary.

The following sql is good for doing that.

select     a.USERNAME, hash_value,    DISK_READS,
    EXECUTIONS,

[url=http://www.oraclepoint.com]TbhlWTeaPOraclePointbCPjAbKlo[/url]

    round(DISK_READS / decode(EXECUTIONS, 0, 1, EXECUTIONS)) "Reads/Execs",
    SQL_TEXT
from     dba_users a, v$session, v$sqlarea

[url=http://www.oraclepoint.com]tkVqY86MdOraclePointtEg2X8Z0U[/url]

where     PARSING_USER_ID=USER_ID
and     ADDRESS=SQL_ADDRESS(+)
and     DISK_READS > 1000

[url=http://www.oraclepoint.com]tdv05xOexOraclePointpoZUX0pEY[/url]

order      by DISK_READS desc, EXECUTIONS desc

Hi all

I am executing sqltxecute.sql as follows
connect as application user (Schema Owner)
execute command as follows

SQL> @sqltxecute.sql

Value passed to sqltxecute.sql:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SCRIPT_WITH_SQL: script1.txt

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Producti
on
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production




I checked the log file and find following entry

ERROR at line 1:
ORA-20200: *** SQL with tag /* &&unique_id */ could not be found in memory
ORA-06512: at line 3

would anybody plz help me why I am getting this error

Script files which I am using are attached for your help

\

 

Hi Hassan,

I checked with script1.txt attached and I'd like to suggest you to add semicolon for your sql statement in script1.txt. Without semicolon, the "SQL command not properly ended" will raise.

Please see below code for error you experienced.

SELECT /* exclude myself */
      hash_value sql_hash_value, address sql_address, child_number sql_child_number,
      sqltxplain.sqlt$r.session_trace_filename('10046_10053_&&execution_id' ) sql_10046_10053_trace,
      SUBSTR(sql_text, 1, 80) sql_text
 FROM v$sql
WHERE sql_text LIKE '%&&unique_id%'
  AND sql_text NOT LIKE '%exclude myself%'
  AND ROWNUM = 1;

If no row is selected by above sql statement, you absolutely experience this error. I doubt that you may have insufficient privilege to access v$sql. Therefore, go double check log file.

I don't have oracle 9i database here and I'm unable to reproduce the problem you experienced. If you need more help, why don't you publish the log file here for my information.