quick&dirty: index monitoring for a whole schema

…a customer needed a quick way for enabling index monitoring for a whole schema:

don’t know what index monitoring is? check the documentation

example calls:

@index_monitoring_bulk USER ON Y
@index_monitoring_bulk USER OFF Y

Script:

/**
enable index monitoring for a given user
   parameters: user, on/off, debug
       e.g. @index_monitoring_bulk USER ON Y
**/
SET SERVEROUT ON
SET LINES 164
SET VERIFY OFF
SET FEEDBACK OFF
CLEAR COLUMNS
VARIABLE for_user CHAR(30)
VARIABLE on_off CHAR(3)
VARIABLE debug_on CHAR(1)
BEGIN
  :for_user := '&1';
  :on_off := '&2';
  :debug_on := '&3';
END;
/
DECLARE
  lcb_debug CONSTANT BOOLEAN := :debug_on = 'Y';
  lcv_user CONSTANT all_users.username%TYPE := UPPER ( REPLACE ( :for_user, ' ','' ) );
  lcv_on_off CONSTANT VARCHAR2(3) := NVL ( REPLACE ( :on_off, ' ', '' ), 'OFF' );
  TYPE tab_index_names IS TABLE OF all_indexes.index_name%TYPE INDEX BY PLS_INTEGER;
  ltab_index_names tab_index_names;
  lv_statement VARCHAR2(2000);
  lv_monitoring_clause VARCHAR2(12);
  le_iot EXCEPTION;
  le_resouce_busy EXCEPTION;
  PRAGMA EXCEPTION_INIT(le_iot, -25176);
  PRAGMA EXCEPTION_INIT(le_resouce_busy, -00054);
  PROCEDURE debug ( pv_text IN VARCHAR2 )
  IS
  BEGIN
    IF ( lcb_debug )
    THEN
      dbms_output.put_line ( pv_text );
    END IF;
  END debug;
  FUNCTION user_exists ( pv_user IN all_users.username%TYPE )
                       RETURN BOOLEAN
  IS
    CURSOR cur_check_user
    IS SELECT 'does_exist'
         FROM all_users
        WHERE username = pv_user
    ;
    lv_cursor_result VARCHAR2(10);
  BEGIN
    debug ( ' Checking database for user: '|| lcv_user );
    OPEN cur_check_user;
      FETCH cur_check_user INTO lv_cursor_result;
    CLOSE cur_check_user;
    RETURN lv_cursor_result IS NOT NULL;
  END user_exists;
  PROCEDURE get_indexes_for_user ( pv_user IN all_users.username%TYPE )
  IS
    CURSOR cur_get_indexes_for_user
    IS SELECT index_name
         FROM all_indexes
        WHERE owner = pv_user
    ;
  BEGIN
    OPEN cur_get_indexes_for_user;
      FETCH cur_get_indexes_for_user BULK COLLECT INTO ltab_index_names;
    CLOSE cur_get_indexes_for_user;
  END get_indexes_for_user;
BEGIN
  IF user_exists ( pv_user => lcv_user )
  THEN
    IF lcv_on_off IN ('ON','OFF')
    THEN
      debug ( ' User: '||lcv_user||' does exist ');
      debug ( ' ON/OFF flag is: '||lcv_on_off );
      debug ( ' Loading indexes ' );
      get_indexes_for_user ( pv_user => lcv_user );
      IF ( lcv_on_off = 'ON' )
      THEN
        lv_monitoring_clause := 'MONITORING';
      ELSE
        lv_monitoring_clause := 'NOMONITORING';
      END IF;
      IF ltab_index_names IS NOT NULL
         AND
         ltab_index_names.COUNT > 0
      THEN
         FOR i IN ltab_index_names.FIRST..ltab_index_names.LAST
         LOOP
           debug ( ' Processing index: '||lcv_user||'.'||ltab_index_names(i) );
           lv_statement := 'ALTER INDEX "'||lcv_user||'"."'||ltab_index_names(i)||'" '||lv_monitoring_clause||' USAGE';
           debug ( ' Statement is: '||lv_statement );
           BEGIN
             EXECUTE IMMEDIATE lv_statement;
           EXCEPTION WHEN le_iot THEN debug ( ' --- Index monitoring not possible on IOTs, skipping' );
                     WHEN le_resouce_busy THEN debug ( ' --- Resource busy, propably SYS_ indexes ' );
           END;
         END LOOP;
      END IF;
    ELSE
      dbms_output.put_line ( ' ON/OFF flag must be ON or OFF ' );
    END IF;
  ELSE
    dbms_output.put_line ( ' User '||lcv_user|| ' does not exist ! ');
  END IF;
END;
/

After your usual workload check the results in v$object_usage:

SQL> desc v$object_usage;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 INDEX_NAME				   NOT NULL VARCHAR2(30)
 TABLE_NAME				   NOT NULL VARCHAR2(30)
 MONITORING					    VARCHAR2(3)
 USED						    VARCHAR2(3)
 START_MONITORING				    VARCHAR2(19)
 END_MONITORING 				    VARCHAR2(19)
SQL>