查看索引是否被使用的方法

1、 查看v$segment_statistics，使用逻辑读的值作为参考，如果逻辑读比较大，说明此索引使用率比较高。



SQL> desc v$segment_statistics;



Name            Type         Nullable Default Comments



--------------- ------------ -------- ------- --------



OWNER           VARCHAR2(30)                          



OBJECT_NAME     VARCHAR2(30)                          



SUBOBJECT_NAME  VARCHAR2(30) Y                        



TABLESPACE_NAME VARCHAR2(30)                          



TS#             NUMBER       Y                        



OBJ#            NUMBER                                



DATAOBJ#        NUMBER       Y                        



OBJECT_TYPE     VARCHAR2(18) Y                        



STATISTIC_NAME  VARCHAR2(64) Y                        



STATISTIC#      NUMBER       Y                        



VALUE           NUMBER       Y

SQL> select object_name,object_type,statistic_name,value from v$segment_statistics where owner='GDDC' and object_name='PK_S_U_ORG';

OBJECT_NAME    OBJECT_TYP STATISTIC_NAME                  VALUE



--------------- ---------- ----------------------------------- ----------



PK_S_U_ORG      INDEX      logical reads                              224



PK_S_U_ORG      INDEX      buffer busy waits                            0



PK_S_U_ORG      INDEX      db block changes                             0



PK_S_U_ORG      INDEX      physical reads                               2



PK_S_U_ORG      INDEX      physical writes                              0



PK_S_U_ORG      INDEX      physical reads direct                        0



PK_S_U_ORG      INDEX      physical writes direct                       0



PK_S_U_ORG      INDEX      global cache cr blocks served                0



PK_S_U_ORG      INDEX      global cache current blocks served           0



PK_S_U_ORG      INDEX      ITL waits                                    0



PK_S_U_ORG      INDEX      row lock waits                               0

2、查看v$sql_plan和v$sql_area视图，通过SQL语句判断索引是否被使用。



SQL> desc v$sql_plan;



 名称                                      是否为空? 类型



 ----------------------------------------- -------- ---------------------



 ADDRESS                                            RAW(4)



 HASH_VALUE                                         NUMBER



 CHILD_NUMBER                                       NUMBER



 OPERATION                                          VARCHAR2(60)



 OPTIONS                                            VARCHAR2(60)



 OBJECT_NODE                                        VARCHAR2(20)



 OBJECT#                                            NUMBER



 OBJECT_OWNER                                       VARCHAR2(30)



 OBJECT_NAME                                        VARCHAR2(64)



 OPTIMIZER                                          VARCHAR2(40)



 ID                                                 NUMBER



 PARENT_ID                                          NUMBER



 DEPTH                                              NUMBER



 POSITION                                           NUMBER



 SEARCH_COLUMNS                                     NUMBER



 COST                                               NUMBER



 CARDINALITY                                        NUMBER



 BYTES                                              NUMBER



 OTHER_TAG                                          VARCHAR2(70)



 PARTITION_START                                    VARCHAR2(10)



 PARTITION_STOP                                     VARCHAR2(10)



 PARTITION_ID                                       NUMBER



 OTHER                                              VARCHAR2(4000)



 DISTRIBUTION                                       VARCHAR2(40)



 CPU_COST                                           NUMBER



 IO_COST                                            NUMBER



 TEMP_SPACE                                         NUMBER



 ACCESS_PREDICATES                                  VARCHAR2(4000)



 FILTER_PREDICATES                                  VARCHAR2(4000)

SQL>set linesize 80



SQL> col sql_text format a50



SQL> set pagesize 2000



SQL> Select Sql_Text, Executions



  2  From V$sqlarea



  3  Where Hash_Value In



  4                     (Select Hash_Value From V$sql_Plan Where Object_Name ='P



K_S_U_ORG' And Object_Owner = 'GDDC');

SQL_TEXT                                           EXECUTIONS



-------------------------------------------------- ----------



select * from s_u_org where orgcode='44'                   73



select * from s_u_org where orgcode='440100'                2



select * from s_u_org where orgcode='4402'                  1



select * from s_u_org where orgcode='4401'                  1