Oracle 执行计划 SQL 语句自己生成，不用Oracle 工具解读-CSDN博客

本文链接：https://blog.csdn.net/jnrjian/article/details/141532066

Purpose:
========

The purpose of this article is to explain how to obtain the most recent
execution plan for a SQL statement in a simple nested form.


How to Obtain the Most Recent Execution Plan in a Simple Nested Form:
=====================================================================

You want to obtain an execution plan for a SQL statement, in a simple nested 
form.  You do not want to truncate the PLAN_TABLE each time you execute the 
EXPLAIN PLAN command.  You only want the execution plan for the most recent 
EXPLAIN command.

You have to execute the EXPLAIN PLAN command and then perform a formatted
select on the PLAN_TABLE.  This gives you minimum information and can 
be more readable than autotrace, especially when the execution plan is 
fairly complex.  

Example:
--------

   SQL> explain plan for
     2  select e.ename, d.loc from emp e, dept d where e.deptno=d.deptno;
    
   Explained.
    
   SQL> @showplan  <- a script performing SELECT from PLAN_TABLE
    
   Query Plan
   ------------------------------------------------
   SELECT STATEMENT   [CHOOSE] Cost = 5
     NESTED LOOPS
       TABLE ACCESS FULL DEPT [ANALYZED]
       TABLE ACCESS FULL EMP [ANALYZED]


To obtain a simple nested execution plan from the PLAN_TABLE without truncating
the PLAN_TABLE between each EXPLAIN command, you can use the following script.
Because the script uses the TIMESTAMP column information, it only gives you
the execution plan for the most recent EXPLAIN command.  You do not need to
truncate the PLAN_TABLE.

This script can be used in Oracle 7.3.4 to Oracle 9i.

/* showplan.sql */

set echo off
column operation   format a16
column options     format a15
column object_name format a20
column id          format 99
column query       heading "Query Plan" format a80
 
select lpad(' ',2*(level-1))||operation||' '||options||' '
       ||object_name||' '
       ||decode(object_node,'','','['||object_node||'] ')
       ||decode(optimizer,'','','['||optimizer||'] ')
       ||decode(id,0,'Cost = '||position) query
from   plan_table
start with id=0 and timestamp = (select max(timestamp) from plan_table
                                 where id=0 and nvl(statement_id, ' ')
                                 not like 'SYS_LE%')
                and nvl(statement_id, ' ') not like 'SYS_LE%'
connect by (prior id = parent_id
        and prior nvl(statement_id, ' ') = nvl(statement_id, ' ')
        and prior timestamp <= timestamp)
/
set echo on


References:
===========
 
$ORACLE_HOME/rdbms/admin/utlxplp.sql
$ORACLE_HOME/rdbms/admin/utlxpls.sql
Note:235530.1 Recommended Method for Obtaining a Formatted Explain Plan

* How to Obtain a Formatted Explain Plan - Recommended Methods (Doc ID 235530.1)

Enhanced Explain Plan Script (Doc ID 104824.1)

编辑To Bottom