Monday, May 26, 2014

Is my index used ?

This oracle function will help you quickly check if an index is used or not, you have to enable his monitoring before

CREATE OR REPLACE FUNCTION is_index_used (owner         VARCHAR,
                                               table_name    VARCHAR,
                                               index_name    VARCHAR)
   RETURN VARCHAR
IS
   is_used   VARCHAR (3);
   cnt       NUMBER;
BEGIN
   is_used := 'NO';

   SELECT COUNT (*) CNT
     INTO cnt
     FROM SYS."_CURRENT_EDITION_OBJ" io,
          SYS."_CURRENT_EDITION_OBJ" T,
          sys.ind$ i,
          sys.USER$ iu,
          sys.USER$ tu,
          sys.object_usage ou
    WHERE     io.owner# = iu.USER#
          AND i.obj# = io.obj#
          AND io.obj# = ou.obj#(+)
          AND T.obj# = i.bo#
          AND T.owner# = Tu.USER#
          AND i.type# NOT IN (4, 8, 9)
          AND BITAND (io.flags, 128) <> 128
          AND iu.name = owner
          AND io.name = index_name
          AND T.name = table_name
          AND DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES') = 'YES'
          AND DECODE (BITAND (ou.flags, 1),  0, 'NO',  NULL, NULL,  'YES') =
                 'YES';

   IF cnt > 0
   THEN
      is_used := 'YES';
   ELSE
      is_used := 'NO';
   END IF;

   RETURN is_used;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN is_used;
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END;
/

I recommend to wait minimum one week after enabling monitoring before you check it with a simple call like this ...

select INDEX_NAME,is_index_used(OWNER,TABLE_NAME,INDEX_NAME)  from dba_indexes where owner = 'FOMI' and table_name = 'ACCOUNTS'

No comments:

Post a Comment