监控索引使用

---以下是相关PROCEDURE：
CREATE OR REPLACE PROCEDURE start_monitor_index
IS
Cursor cur1 is
select OWNER,INDEX_NAME from dba_indexes di where di.owner like '%BJ';

p_rows cur1%ROWTYPE;
p_str varchar2(200);

begin
open cur1;
loop
fetch cur1 into p_rows;
exit when cur1%NOTFOUND;
p_str:='alter index '||p_rows.owner||'.'||p_rows.index_name||' MONITORING USAGE';
execute immediate p_str;
--dbms_output.put_line(p_str);
end loop;
close cur1;
dbms_output.put_line('finish');
COMMIT;
END;

CREATE OR REPLACE PROCEDURE stop_monitor_index
IS
Cursor cur1 is
select OWNER,INDEX_NAME from dba_indexes di where di.owner like '%BJ';

p_rows cur1%ROWTYPE;
p_str varchar2(200);

begin
open cur1;
loop
fetch cur1 into p_rows;
exit when cur1%NOTFOUND;
p_str:='alter index '||p_rows.owner||'.'||p_rows.index_name||' NOMONITORING USAGE';
execute immediate p_str;
--dbms_output.put_line(p_str);
end loop;
close cur1;
dbms_output.put_line('finish');
COMMIT;
END;





-----特殊
create or replace view v$object_usage
(index_name, table_name, monitoring, used, start_monitoring, end_monitoring)
as
select io.name, t.name,
       decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
       decode(bitand(ou.flags, 1), 0, 'NO', 'YES'), 
       ou.start_monitoring,
       ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where io.owner# = userenv('SCHEMAID')
  and i.obj# = ou.obj#
  and io.obj# = ou.obj#
  and t.obj# = i.bo#

转载于:https://www.cnblogs.com/czjie/archive/2011/11/19/2255648.html