Gvplanid (gvplanid.sql)

What This Script Does

This script displays detailed execution plans for a specific SQL ID, showing the hierarchical structure with costs, cardinality estimates, and access predicates. Essential for SQL tuning and performance analysis.

The Script

rem  gvplanid.sql
rem
rem
ttitle 'Query Plan'
rem
set linesize 200
set pagesize 200
ttitle off

clear columns
column inst_id format 9999 heading 'INST|ID'
column child_number format 999999 head "Child#"
column op format a150 wrap head "Operation"
clear breaks
break on child_number nodup

select
       child_number,
       cpu_cost,
       io_cost,
       lpad(' ',2 * (depth),'| ') || operation
       || decode(options,
                        null, null, ' (' || initcap(options) || ')')
       || ' ' || object_owner
       || decode(object_owner, null, null,'.')
       || object_name
       || decode(partition_start, null, null, ' Partition: Start='
                 || partition_start||' Stop='||partition_stop)
--KMG Start
       ||decode(optimizer, null, null, ' optimizer: ')|| optimizer
       ||decode(cost, null, null, ' [cost=')||cost
         ||decode(cardinality, null, null, decode(cost, null, ' [card=', ' card='))||cardinality
         ||decode(search_columns, null, null,0, null, decode(cost, null, ' [match_col=', ' match_col='))||search_columns
       ||decode(cost, null, decode(cardinality, null, null, ']'), ']')
--KMG End
       || decode(access_predicates, null, null, ' ('||substr(access_predicates,1,3000)||'....)')
       || decode(other_tag, null, null, ' ('||other_tag||')') op
  from gv$sql_plan
 where inst_id like nvl(upper('&inst_id'),'%')
   and sql_id = '&sql_id'
 order by child_number, id;
ttitle off

Parameters

The script prompts for:

&RAC - instance ID to filter (use % for all)
&SQL - ID to analyze (required)

Usage

-- Basic usage
@gvplanid.sql

-- When prompted, enter:
-- inst_id: RAC instance ID to filter (use % for all)
-- sql_id: SQL ID to analyze (required)

Sample Output

Child#  CPU_COST    IO_COST Operation
------ ---------- ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------
     0      15234       1234 | SELECT STATEMENT optimizer: ALL_ROWS [cost=15234 card=100]
                             | | HASH JOIN (Outer) [cost=15234 card=100]
                             | | | TABLE ACCESS (Full) HR.EMPLOYEES [cost=3 card=107] (EMPLOYEE_ID IS NOT NULL....)
                             | | | TABLE ACCESS (By Index Rowid) HR.DEPARTMENTS [cost=1 card=27]
                             | | | | INDEX (Unique Scan) HR.DEPT_ID_PK [cost=0 card=1 match_col=1] (DEPARTMENT_ID=:B1....)
                             | | SORT (Order By) [cost=15234 card=100]

     1      12500       1100 | SELECT STATEMENT optimizer: ALL_ROWS [cost=12500 card=50]
                             | | NESTED LOOPS [cost=12500 card=50]
                             | | | TABLE ACCESS (By Index Rowid) HR.EMPLOYEES Partition: Start=1 Stop=4 [cost=100 card=50]
                             | | | | INDEX (Range Scan) HR.EMP_DEPT_IX [cost=2 card=50 match_col=1] (DEPARTMENT_ID=:B1....)
                             | | | TABLE ACCESS (By Index Rowid) HR.DEPARTMENTS [cost=1 card=1]
                             | | | | INDEX (Unique Scan) HR.DEPT_ID_PK [cost=0 card=1 match_col=1]