?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????洢???????DML????????Oracle ??????????????????????ж?δ??????????????????????????Oracle ?????????

????1??????????????

?????????????????????????????????????????￡????????????CPU??I/O?????????????????:

????a???????????洢???(?????ε?????????)

????b????????DML???????

????c???????????????(????)????????

????d????????????

????f??????????????????

????2?????????????

????a???????????????????????????????????????

alter index <INDEX_NAME> monitoring usage;

????b????????????

alter index <INDEX_NAME> nomonitoring usage;

????c??????????????v$object_usage?????

select * from v$object_usage

????3??schema????????????????SYS?????

????a???????н???????????????

robin@SZDB:~/dba_scripts/custom/sql> more idx_monitor_on.sql
SET HEADING OFF  FEEDBACK OFF  TERMOUT OFF  ECHO OFF;
SET PAGESIZE 0;
SPOOL /tmp/mnt_idx.sql

SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' MONITORING USAGE;'
  FROM dba_indexes
  WHERE owner IN (SELECT username
                   FROM dba_users
                  WHERE account_status = 'OPEN')
       AND owner NOT IN ('SYS'?? 'SYSTEM'?? 'PERFSTAT'?? 'MGMT_VIEW'?? 'MONITOR'?? 'SYSMAN'?? 'DBSNMP');

SPOOL OFF;
@/tmp/mnt_idx.sql;
SET HEADING ON FEEDBACK ON  TERMOUT ON;
SET PAGESIZE 80;

SELECT index_name??
       monitoring??
       used??
       start_monitoring??
       end_monitoring
  FROM v$object_usage;

ho rm -rf /tmp/mnt_idx.sql

????b?????????????

robin@SZDB:~/dba_scripts/custom/sql> more idx_monitor_off.sql
SET HEADING OFF  FEEDBACK OFF  TERMOUT OFF  ECHO OFF;
SET PAGESIZE 0;
SPOOL /tmp/un_mnt_idx.sql
SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' NOMONITORING USAGE;'
  FROM dba_indexes
  WHERE owner IN (SELECT username
                   FROM dba_users
                  WHERE account_status = 'OPEN')
       AND owner NOT IN ('SYS'?? 'SYSTEM'?? 'PERFSTAT'?? 'MGMT_VIEW'?? 'MONITOR'?? 'SYSMAN'?? 'DBSNMP');

SPOOL OFF;
@/tmp/un_mnt_idx.sql;
SET HEADING ON FEEDBACK ON  TERMOUT ON;
SET PAGESIZE 80;

SELECT index_name??
       monitoring??
       used??
       start_monitoring??
       end_monitoring
  FROM v$object_usage;

ho rm -rf /tmp/un_mnt_idx.sql

????c?????????????

set linesize 190
SELECT u.name owner??
       io.name index_name??
       t.name table_name??
       DECODE (BITAND (i.flags?? 65536)?? 0?? 'NO'?? 'YES') monitoring??
       DECODE (BITAND (ou.flags?? 1)?? 0?? 'NO'?? 'YES') used??
       ou.start_monitoring start_monitoring??
       ou.end_monitoring end_monitoring
  FROM sys.user$ u??
       sys.obj$ io??
       sys.obj$ t??
       sys.ind$ i??
       sys.object_usage ou
 WHERE i.obj# = ou.obj# AND io.obj# = ou.obj# AND t.obj# = i.bo# AND u.user# = io.owner#
       AND u.name=decode(upper('&input_owner')??'ALL'??u.name??upper('&input_owner'));