SQL Plan Finder (gvsqlplanfind.sql)

What This Script Does

Searches SQL execution plans across all RAC instances to find plans that reference specific database objects. This is useful for identifying which SQL statements are accessing particular tables, indexes, or other database objects, helping with impact analysis and performance tuning.

The Script

rem  gvsqlplanfind.sql
rem
ttitle 'SQL Execution Plans by Object'
rem
set linesize 180
rem
col sql_id format a13
col object_name format a25
col operation format a15
col options format a25
col access_predicates format a50
col filter_predicates format a25
rem
select inst_id
      ,sql_id
      ,timestamp
      ,object_name
      ,operation
      ,options
      ,access_predicates
      ,filter_predicates
 from gv$sql_plan
where upper(object_owner) like nvl(upper('&object_owner'),'%')
  and upper(object_name)  like nvl(upper('&object_name'),'%')
  and rownum < 81
/

Usage

-- Find all plans accessing objects owned by SCOTT
@gvsqlplanfind.sql
-- Enter: SCOTT (for object owner)
-- Enter: [press enter for all objects]

-- Find plans accessing specific table
@gvsqlplanfind.sql
-- Enter: HR (for object owner)
-- Enter: EMPLOYEES (for specific table)

-- Find all plans in the system (limited to 80 rows)
@gvsqlplanfind.sql
-- Enter: [press enter for all owners]
-- Enter: [press enter for all objects]

Parameters

The script prompts for:

object_owner: Schema/owner name (% for all owners)
object_name: Object name (% for all objects)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT on GV$SQL_PLAN

Sample Output

                                SQL Execution Plans by Object

INST_ID SQL_ID        TIMESTAMP    OBJECT_NAME              OPERATION       OPTIONS                   ACCESS_PREDICATES                          FILTER_PREDICATES
------- ------------- ------------ ------------------------ --------------- ------------------------- ------------------------------------------ -------------------------
      1 abc123def456  01-JUN-25    EMPLOYEES               TABLE ACCESS    FULL                      NULL                                       "DEPARTMENT_ID"=10
      1 def456ghi789  01-JUN-25    IDX_EMP_DEPT            INDEX           RANGE SCAN               "DEPARTMENT_ID"=10                         NULL
      2 ghi789jkl012  01-JUN-25    EMPLOYEES               TABLE ACCESS    BY INDEX ROWID            NULL                                       NULL
      2 ghi789jkl012  01-JUN-25    IDX_EMP_NAME            INDEX           RANGE SCAN               "LAST_NAME" LIKE 'SMITH%'                 NULL

Key Output Columns

INST_ID: RAC instance where the plan was found
SQL_ID: Unique identifier for the SQL statement
TIMESTAMP: When the plan was created/captured
OBJECT_NAME: Name of the database object being accessed
OPERATION: Type of database operation (TABLE ACCESS, INDEX, etc.)
OPTIONS: Additional operation details (FULL, RANGE SCAN, etc.)
ACCESS_PREDICATES: Conditions used to access the object
FILTER_PREDICATES: Additional filter conditions applied

Operation Types

Table Operations

TABLE ACCESS FULL
TABLE ACCESS BY INDEX ROWID
TABLE ACCESS BY USER ROWID

Index Operations

INDEX RANGE SCAN
INDEX UNIQUE SCAN
INDEX FULL SCAN
INDEX FAST FULL SCAN

Join Operations

NESTED LOOPS
HASH JOIN
SORT MERGE JOIN

Common Use Cases

Impact Analysis

-- Find all SQL using a specific table before dropping/modifying
@gvsqlplanfind.sql
-- Enter: SCHEMA_NAME
-- Enter: TABLE_NAME

Index Usage Analysis

-- Check which SQL statements use a specific index
@gvsqlplanfind.sql
-- Enter: SCHEMA_NAME
-- Enter: INDEX_NAME

Performance Tuning

-- Identify SQL plans accessing specific objects
@gvsqlplanfind.sql
-- Analyze access patterns and optimization opportunities

Schema Migration Planning

-- Find all references to objects in a schema
@gvsqlplanfind.sql
-- Enter: OLD_SCHEMA_NAME
-- Enter: [all objects]

SQL Plan Analysis

-- Research execution plans for specific applications
@gvsqlplanfind.sql
-- Analyze predicate usage and access methods

Performance Impact

Low to Moderate: Depends on size of shared pool and number of plans
Limited Results: Restricted to 80 rows to prevent excessive output
RAC Aware: Searches plans across all instances

Limitations

Shared Pool Dependency: Only shows plans currently in memory
Row Limit: Limited to first 80 matching rows
Case Sensitive: Uses UPPER() functions for case-insensitive searches

Interpreting Results

Access Predicates vs Filter Predicates

Access Predicates: Used to locate rows (typically with indexes)
Filter Predicates: Applied after rows are accessed

Performance Implications

Full table scans may indicate missing indexes
Multiple operations on same object may suggest tuning opportunities
Predicate analysis helps understand query logic

sqltext - Get full SQL text by SQL_ID
ep - Display execution plans
gvplanid - Execution plan analysis
dindex - Index analysis