Oracle 11g 分析出那些表缺失索引

妖狐艹你老母 2022-06-10 22:38 314阅读 0赞

在设计、开发阶段我们会加索引，不过难免保证在运维阶段索引加全了。此时就需要把执行计划中带有全表扫描的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  
  DBMS\_OUTPUT.ENABLE(buffer\_size=>null);  
  for c\_row in c\_cursor loop  
    begin  
      DBMS\_OUTPUT.PUT\_LINE(c\_row.SQL\_FULLTEXT);   
      select plan\_table\_output bulk collect into array\_plan from  
            table(dbms\_xplan.display\_cursor(c\_row.hash\_value, c\_row.child\_number, 'advanced'));  
      FOR i IN array\_plan.FIRST .. array\_plan.LAST LOOP    
        DBMS\_OUTPUT.PUT\_LINE(array\_plan(i));    
      END LOOP;    
    DBMS\_OUTPUT.PUT\_LINE('\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*');   
    DBMS\_OUTPUT.PUT\_LINE(chr(10)||chr(10));   
    exception       
      When others then   
       DBMS\_OUTPUT.PUT\_LINE('有报错');   
    end;  
  end loop;  
end;  
/

spool off;

**出来的结果如下，可以看到是没有加索引的：**

HASH\_VALUE  844842, child number 0                                   
\----------------------------------                                   
select assetaudit0\_.ID                      as ID1064\_0\_,            
       assetaudit0\_.APPROVER\_UID            as APPROVER7\_1064\_0\_,    
       assetaudit0\_.APPROVER\_UNAME          as APPROVER8\_1064\_0\_,    
       assetaudit0\_.AUDIT\_COMMENT           as AUDIT9\_1064\_0\_,       
       assetaudit0\_.AUDIT\_EXPLAIN           as AUDIT10\_1064\_0\_,      
       assetaudit0\_.AUDIT\_STATUS            as AUDIT11\_1064\_0\_,      
       assetaudit0\_.AUDIT\_TIME              as AUDIT12\_1064\_0\_,      
       assetaudit0\_.BUSINESS\_BILL\_ID        as BUSINESS13\_1064\_0\_,   
       assetaudit0\_.FULL\_PATH               as FULL14\_1064\_0\_,       
       assetaudit0\_.FUNCTION\_LOCATION\_ID    as FUNCTION15\_1064\_0\_,   
       assetaudit0\_.HISTORY\_VERSION         as HISTORY16\_1064\_0\_,    
       assetaudit0\_.OBJECT\_ID               as OBJECT17\_1064\_0\_,     
       assetaudit0\_.OBJECT\_NAME             as OBJECT18\_1064\_0\_,     
       assetaudit0\_.OBJECT\_TYPE             as OBJECT19\_1064\_0\_,     
       assetaudit0\_.TRANSFER\_STATE          as TRANSFER20\_1064\_0\_,   
       assetaudit0\_.WORKSPACE\_ID            as WORKSPACE21\_1064\_0\_   
  from GG\_ASSET\_AUDIT assetaudit0\_                                   
 where assetaudit0\_.ID = :1

Plan hash value: 2126140279                                                            
\------------------------------------------------------------------------------------   
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |   
\------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT  |                |       |       |  2466 (100)|          |   
|\*  1 |  TABLE ACCESS FULL| GG\_ASSET\_AUDIT |     1 |   218 |  2466   (1)| 00:00:30 |   
\------------------------------------------------------------------------------------   
Query Block Name / Object Alias (identified by operation id):                          
\-------------------------------------------------------------                          
1 - SEL$1 / ASSETAUDIT0\_@SEL$1                                                         
Outline Data                                                                           
\-------------

.....................................................................................

\-----------------------------------------------------------                            
Peeked Binds (identified by position):                                                 
\--------------------------------------                                                 
1 - :1 (VARCHAR2(30), CSID=852): '052cc327153f4beba86042bfbcf3b623'                    
Predicate Information (identified by operation id):                                    
\---------------------------------------------------                                    
1 - filter("ASSETAUDIT0\_"."ID"=:1)                                                     
Column Projection Information (identified by operation id):                            
\-----------------------------------------------------------