Explain SQL ID Report

Script: explain_sqlid.sql

--
-- $Header$
--
-- Program     : run_explain_sql_rpt.sql
--
-- Description : generate Explain SQL reports using the ExplainSQL pipline function
--
-- Location    : toolkit/dba/sql_tracker
--
-- Author      : Keith D. Allen - Allstate Insurance Company
--
-- Revise Date : 13-Nov-2006 kallen -  Initial Creation
-- Version 1.2 - 4/19/2007 7:41AM
--
--
define sql_id='&1'
--column sql_id new_value sql_id;
--set heading off;
--select 'SQL ID Specified: ',lower(decode('&&sql_id','','NULL','&sql_id')) sql_id from dual;
--set heading on;

col spool_name new_value spool_name noprint;
set heading off
SELECT 'explainsql_'||SYS_CONTEXT('USERENV','DB_NAME')||'_'||'&&Sql_id'||'_'||to_char(sysdate,'YYMMDD_HH24MI')||'.txt' spool_name from dual;
set heading on
prompt Report will be saved to &spool_name

set linesize 300
set heading off
set pagesize 0
set trimspool on
set termout off
set feedback off

spool &spool_name
--
select * from table(ExplainSQL('&sql_id','Y'));
--
spool off
--
undefine sql_id
undefine spool_name
--
set heading on
set termout on

Purpose

The Explain SQL ID script generates detailed execution plan reports for specific SQL statements identified by their SQL_ID. It uses a custom pipeline function to format and display comprehensive plan information.

Key Features

SQL ID Based: Analyzes specific SQL statements by their unique identifier
Automated Naming: Creates timestamped output files with database name
Pipeline Function: Uses custom ExplainSQL function for detailed output
Formatted Output: Generates well-formatted execution plan reports

Prerequisites

Custom ExplainSQL pipeline function must be installed
Access to V$SQL and related performance views
Appropriate privileges to query SQL plan information

Usage

Command Line Execution

SQL> @explain_sqlid.sql <sql_id>

Example

SQL> @explain_sqlid.sql abc123def456
Report will be saved to explainsql_PRODDB_abc123def456_240115_1430.txt

Output File Naming

The script automatically generates output filenames using:

Prefix: ‘explainsql_’
Database name from SYS_CONTEXT
SQL_ID provided
Timestamp in YYMMDD_HH24MI format

Example: explainsql_PRODDB_abc123def456_240115_1430.txt

ExplainSQL Function

The script relies on a custom pipeline function that should:

Accept SQL_ID as first parameter
Accept verbose flag (‘Y’) as second parameter
Return formatted execution plan details
Include relevant performance statistics

Script Parameters

sql_id: The SQL identifier to analyze (passed as &1)
Verbose Mode: Hard-coded as ‘Y’ for detailed output

Output Content

Typical report includes:

SQL text
Execution plan with cost estimates
Predicate information
Access methods
Join operations
Performance statistics

Best Practices

Verify SQL_ID: Ensure the SQL_ID exists in the system
Recent Executions: Best results with recently executed SQL
Output Location: Check write permissions for output directory
Function Availability: Verify ExplainSQL function is installed

Troubleshooting

Common Issues

Missing Function: Install the ExplainSQL pipeline function
No Data: SQL_ID may have aged out of shared pool
Permissions: Ensure proper grants on performance views

Alternative Approaches

If ExplainSQL is not available:

-- Use DBMS_XPLAN instead
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

vsqlplanfind.sql: Find SQL plans by various criteria
qplan.sql: Display execution plans
awrrpt.sql: AWR reports including SQL analysis