|
|
[ÀϹÝ] INDEXÀÇ »ç¿ë¿©ºÎ È®ÀÎÇϱâ |
|
È£¼® ´Ô²²¼ ¾´ ±ÛÀÔ´Ï´Ù - 121.¢½.39.7 |
ÀÐÀ½:1570 |
|
|
[Oracle] INDEXÀÇ »ç¿ë¿©ºÎ È®ÀÎÇϱâ (05.01.13 14:25)
PURPOSE
-------
Oracle9i¿¡¼´Â ¸¸µé¾îÁøÈÄ »ç¿ëµÇÁö ¾ÊÀº indexÀ» ãÀ»¼ö ÀÖ´Â feature°¡
¼Ò°³µÇ¾ú´Ù.
Explanation
------------
ALTER INDEX MONITORING USAGE ÀýÀ» ÀÌ¿ëÇÏ¿© ÀÛ¾÷ ½Ã°£µ¿¾È »ç¿ëµÇÁö ¾Ê´Â
indexÀ» ãÀ» ¼ö ÀÖ´Ù.
Example
--------
< Simple Example >
1. Sample table and data¸¦ »ý¼ºÇÑ´Ù.
create table products
(prod_id number(3),
prod_name_code varchar2(5));
insert into products values(1,'aaaaa');
insert into products values(2,'bbbbb');
insert into products values(3,'ccccc');
insert into products values(4,'ddddd');
commit;
2. 1¹ø¿¡¼ ¸¸µç table¿¡ Primary Key index¸¦ ¸¸µç´Ù.
SQL> alter table products
add (constraint products_pk primary key (prod_id));
3. v$object_usageÀ» queryÇÏ¿© º»´Ù.
: ¾ÆÁ÷ monitoringÀÌ startµÇÁö ¾Ê¾ÒÀ½À» ¾Ë¼ö ÀÖ´Ù.
SQL> column index_name format a12
SQL> column monitoring format a10
SQL> column used format a4
SQL> column start_monitoring format a19
SQL> column end_monitoring format a19
SQL> select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;
no rows selected
4. IndexÀÇ »ç¿ë¿©ºÎ¸¦ È®ÀÎÇϱâ À§ÇØ monitoringÀ» ½ÃÀÛÇÑ´Ù.
SQL> alter index products_pk monitoring usage;
Index altered.
5. v$object_usage¸¦ queryÇÏ¿© monitoringÁßÀÎÁö¸¦ È®ÀÎÇÒ¼ö ÀÖ´Ù.
: MONITORING columnÀÌ 'YES', START_MONITORING columnÀÌ ½ÃÀÛÇÑ ³¯Â¥.
SQL> select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;
INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
PRODUCTS_PK YES NO 04/02/2002 16:11:56
6. Test¸¦ À§ÇØ index¸¦ »ç¿ëÇÏ´Â select ¹®ÀåÀ» ¼öÇàÇÑ´Ù.
ÇÊ¿äÇÏ´Ù¸é indexÀ» Ÿ°í ÀÖ´ÂÁö¸¦ ÇÐÀÎÇϱâ À§ÇØ Autotrace utility ¸¦
»ç¿ëÇϱâ À§ÇØ plan_tableÀ» ¸¸µé¾î ÇÐÀÎÇÑ´Ù.
( Bulletin 10712 : NEW FEATURE:AUTOTRACE IN SQL*PLUS 3.3 ÂüÁ¶)
@$ORACLE_HOME/rdbms/admin/utlxplan
Table created.
SQL> set autotrace on explain
SQL> select * from products where prod_id = 2;
PROD_ID PROD_NAME_
---------- ----------
2 bbbbb
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCTS'
2 1 INDEX (UNIQUE SCAN) OF 'PRODUCTS_PK' (UNIQUE)
SQL> set autotrace off
7. v$object_usageÀ»queryÇÏ¿© index°¡ »ç¿ëµÇ¾îÁø ¿©ºÎÀ» È®ÀÎÇÒ ¼ö ÀÖ´Ù.
: USED columnÀÌ 'YES'
select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;
INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
PRODUCTS_PK YES YES 04/02/2002 16:11:56
8. Index»ç¿ë¿©ºÎÀ» monitoringÇÏ´Â °ÍÀ» stopÇÑ´Ù.
SQL> alter index products_pk nomonitoring usage;
Index altered.
9. v$object_usage¸¦ queryÇÏ¿© monitoringÀÌ stopµÇ¾ú´ÂÁö È®ÀÎÇÒ¼ö ÀÖ´Ù.
: MONITORING columnÀÌ 'NO', END_MONITORING columnÀÌ stopÇÑ ³¯Â¥.
SQL> select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;
INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
PRODUCTS_PK NO YES 04/02/2002 16:11:56 04/03/2002 11:05:30
< Database ÀüüÀÇ ¸ðµç index¿¡ ´ëÇØ monitoring >
1. SYS¿Í SYSTEM user ¼ÒÀ¯ÀÇ indexÀ» Á¦¿ÜÇÏ°í ¸ðµç index¿¡ ´ëÇØ
monitoringÀ» ½ÃÀÛÇϵµ·Ï scriptÀ» ¸¸µé¾î º¸ÀÚ.
set heading off
set echo off
set feedback off
set pages 10000
spool startmonitor.sql
select 'alter index '||owner||'.'||index_name||' monitoring usage;'
from dba_indexes
where owner not in ('SYS','SYSTEM');
spool off
2. SYS¿Í SYSTEM user ¼ÒÀ¯ÀÇ indexÀ» Á¦¿ÜÇÏ°í ¸ðµç index¿¡ ´ëÇØ
monitoring À» stopÇϵµ·Ï scriptÀ» ¸¸µé¾î º¸ÀÚ.
set heading off
set echo off
set feedback off
set pages 10000
spool stopmonitor.sql
select 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
from dba_indexes
where owner not in ('SYS','SYSTEM');
spool off
3. ALTER ANY INDEX system privilegeÀ» °¡Áö°í ÀÖ´Â user¿¡¼ 1¹ø¿¡¼ ¸¸µç
scriptÀ» µ¹¸®µµ·Ï ÇÑ´Ù.
@startmonitor
4. databaseÀÇ Á¤»ó °¡µ¿À» ¼öÇàÇÑ´Ù. (
5. ÀÏÁ¤½Ã°£ÀÌ È帥ÈÄ¿¡ ALTER ANY INDEX system privilegeÀ» °¡Áö°í ÀÖ´Â
user¿¡¼ monitoringÀ» stopÇϱâ À§ÇØ 2¹ø¿¡¼ ¸¸µç scriptÀ» µ¹¸®µµ·Ï
ÇÑ´Ù.
@stopmonitor
6. v$object_usageÀ» queryÇÏ¿© Çѹøµµ »ç¿ëµÇÁö ¾ÊÀº indexÀ» È®ÀÎÇÑ´Ù.
select d.owner, v.index_name
from dba_indexes d, v$object_usage v
where v.used='NO' and d.index_name=v.index_name;
Âü°í > v$object_usage view´Â connectÇÏ´Â user¿¡ ´ëÇÑ ³»¿ë¸¸ display
ÇϹǷΠSYS user¿¡¼ ´Ù¸¥ userÀÇ monitoringÇöȲÀ» º¸°íÀÚ ÇÑ´Ù¸é
¾Æ·¡¿Í °°ÀÌ viewÀ» ¸¸µç´Ù.
SQL> connect / as sysdba;
Connected.
SQL> create or replace view V$ALL_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 i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#;
View created.
SQL> select * from v$all_object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
PK_EMP EMP YES NO 10/12/2001 06:42:35
Reference Document
------------------
Note:144070.1
Note:160712.1
Note:136642.1
Oracle9i Database Administrator's Guide
|
|
|
|
|
|