oracle 查询索引缺失,Oracle 11g 分析出那些表缺失索引

在设计、开发阶段我们会加索引，不过难免保证在运维阶段索引加全了。此时就需要把执行计划中带有全表扫描的SQL都找出来。

sqlplus TEST/TEST@10.10.15.25  --数据库的用户名和密码,每个实例上都要运行

set serveroutput on size 100000

spool d:/result.txt

set pagesize 200

set linesize 800

declare

cursor c_cursor is select hash_value,CHILD_NUMBER,SQL_FULLTEXT from(

select  /*+use_hash(s,v)*/  s.hash_value,s.CHILD_NUMBER,s.SQL_FULLTEXT,

row_number() over(partition by s.HASH_VALUE order by null) rn

from v$sql s,v$sql_plan v

where s.SQL_ID =v.SQL_ID

and v.operation = 'TABLE ACCESS'

and v.OPTIONS = 'FULL'

and s.CHILD_NUMBER =0

and v.OBJECT_OWNER in ('SPROC1','SPROC2','SPROC3')) where rn=1;

c_row c_cursor%rowtype;

TYPE t_arry_plan IS VARRAY(1000) OF VARCHAR2(200);

array_plan t_arry_plan;

begin

DB