SQL Plan Finder (vsqlplanfind.sql)

What This Script Does

This script finds SQL execution plans that reference specific database objects, showing the operations performed on those objects along with access and filter predicates. Essential for understanding how tables and indexes are being accessed by current SQL statements.

The Script

set linesize 180

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

select sql_id
      ,timestamp
      ,object_name
      ,operation
      ,options
      ,access_predicates
      ,filter_predicates
 from v$sql_plan
where upper(object_owner) like nvl(upper('&object_owner'),'%')
  and upper(object_name)  like nvl(upper('&object_name'),'%')
  and rownum < 81
;

Parameters

The script prompts for:

object_owner: Object owner name to search for (optional, defaults to all owners)
object_name: Object name to search for (optional, defaults to all objects)

Usage

-- Basic usage (will prompt for parameters)
@vsqlplanfind.sql

-- Example values when prompted:
-- object_owner: SCOTT
-- object_name: EMPLOYEES

Sample Output

SQL_ID        TIMESTAMP            OBJECT_NAME               OPERATION       OPTIONS                   ACCESS_PREDICATES                          FILTER_PREDICATES
------------- -------------------- ------------------------- --------------- ------------------------- ------------------------------------------ -------------------------
3ut7g2v8qx2n5 2025-01-05 14:30:25 EMPLOYEES                 TABLE ACCESS    FULL                                                                 "DEPARTMENT_ID"=10
3ut7g2v8qx2n5 2025-01-05 14:30:25 EMP_DEPT_IDX             INDEX           RANGE SCAN                "DEPARTMENT_ID"=10
4ks8h3w9ty6p7 2025-01-05 14:25:15 EMPLOYEES                 TABLE ACCESS    BY INDEX ROWID            "ROWID"=ROWID
4ks8h3w9ty6p7 2025-01-05 14:25:15 EMP_NAME_IDX             INDEX           RANGE SCAN                "LAST_NAME" LIKE 'SMITH%'
7md9k4x1uz8q3 2025-01-05 14:20:10 EMPLOYEES                 TABLE ACCESS    FULL                                                                 "HIRE_DATE">='2024-01-01'