インデックス使用の監視

---     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#

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }