REM
/*
 * SqlPlanIndex.Sql (c) Tim Onions 2006
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!
 */
col STMT format a64 heading "Statement" WORD
col ADDR format a20 word heading "Address"
col bg format 999,999,999 heading "Buffer|Gets"
col dr format 999,999,999 heading "Disk|Reads"
set feedback off ver off echo off
 
select '| Operation                      | PHV/Object Name     |  Rows | Bytes|   Cost |'  as "Optimizer Plan:" from dual
union all
select * from (select
       rpad('|'||substr(lpad(' ',1*(depth-1))||operation||
            decode(options, null,'',' '||options), 1, 32), 33, ' ')||'|'||
       rpad(decode(id, 0, '----- '||to_char(hash_value)||' ['||to_char(child_number)||'] -----'
                     , substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)
                       ||' ',1, 20)), 21, ' ')||'|'||
       lpad(decode(cardinality,null,'  ',
                decode(sign(cardinality-1000), -1, cardinality||' ', 
                decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K', 
                decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M', 
                       trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
       lpad(decode(bytes,null,' ',
                decode(sign(bytes-1024), -1, bytes||' ', 
                decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K', 
                decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M', 
                       trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
       lpad(decode(cost,null,' ',
                decode(sign(cost-10000000), -1, cost||' ', 
                decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M', 
                       trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"
          from v$sql_plan
WHERE (address,hash_value,child_number) IN
(SELECT address,hash_value,child_number FROM v$sql_plan WHERE object_name LIKE UPPER('&&Index'))
order by hash_value,child_number, id)
/

BREAK ON ADDRESS SKIP
select   sql_fulltext    STMT
  from V$SQL     SQL
WHERE (address,hash_value,child_number) IN
(SELECT address,hash_value,child_number FROM v$sql_plan WHERE object_name LIKE UPPER('&&Index'))
/

undef addr
undef sqlstm
undef child
undef index
set feedback on
TTitle off
SET DOC off
/* SUMMARY - Locate SQL still in the library cache where a specific index was used
 *
 * DETAIL - 
 *
 * Note that this script uses some Oracle 10g specific columns in the V$SQL and V$SQL_PLAN views - it can be adapted to work in Oracle9i by replacing SQL_ID with address/hash_value and SQL_FULLTEXT with SQL_TEXT.
 *
 * Sample output might look like:
SYS@RACDB> start sqlplanindex
Enter value for index: SCHEDULER$_PROGRAM_ARG_PK

Optimizer Plan:
------------------------------------------------------------------------------------------------------------------------------------
| Operation                      | PHV/Object Name     |  Rows | Bytes|   Cost |
|SELECT STATEMENT                |----- 3945791050 [0] |       |      |      1 |
|SORT AGGREGATE                  |                     |     1 |   13 |        |
| INDEX RANGE SCAN               |SCHEDULER$_PROGRAM_A |     1 |   13 |      0 |

Statement
----------------------------------------------------------------
select count(*) from sys.scheduler$_program_argument where oid =
:1
 * (c) Tim Onions 2007
 * Script used at the database owners own risk.

 */
REM