"인덱스 모니터링"의 두 판 사이의 차이
DB CAFE
(→DB 전체 Index 사용현황) |
|||
44번째 줄: | 44번째 줄: | ||
END_MONITORING VARCHAR2(19) | END_MONITORING VARCHAR2(19) | ||
− | + | set lines 110 | |
set pages 999 | set pages 999 | ||
column index_name format a20 | column index_name format a20 | ||
60번째 줄: | 60번째 줄: | ||
* V$OBJECT_USAGE 딕셔너리 뷰에 해당하는 실제 데이터는 OBJECT_USAGE에 포함되어 있다. | * V$OBJECT_USAGE 딕셔너리 뷰에 해당하는 실제 데이터는 OBJECT_USAGE에 포함되어 있다. | ||
− | + | <source lang=sql> | |
− | + | SQL> desc object_usage | |
− | |||
Name Null? Type | Name Null? Type | ||
93번째 줄: | 92번째 줄: | ||
and t.obj# = i.bo# | and t.obj# = i.bo# | ||
and user_id=t.owner#; | and user_id=t.owner#; | ||
− | + | </source> | |
− |
2022년 9월 5일 (월) 18:27 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 DB 전체 Index 사용현황[편집]
1.1 개요[편집]
- 생성한 인덱스가 쿼리에서 더 이상 사용되지 않는다고 판단되면 제거 하는 것이 테이블의 데이터 조작(DML)시 인덱스 갱신에 대한 부하 최소화.
- 통계정보의 갱신이나 튜닝등으로 수행되는 플랜이 변경된 경우에도 인덱스 사용여부를 민감하게 체크해 볼 필요가 있다.
- DBA나 튜너가 의도한 대로 인덱스를 사용하거나 사용하지 않음을 판단한 경우라면, 인덱스의 종속여부를 결정할 수 있다.
- DB내에 유저가 생성한 모든 인덱스에 대하여 일정기간 동안 모니터링을 수행하여, 사용여부를 판가름해 볼 수 있다.
1.2 인덱스 모니터링 방법[편집]
- 우선 전체 인덱스를 파악해서 유저가 생성한 인덱스 만을 찾아서 모니터링을 설정
- 모니터링 대상 추출, 모니터링을 수행하는 스크립트 작성
- 도메인 인덱스나 LOB 타입 인덱스는 모니터링을 할 수 없다.
- SYS 유저로 수행한다.
set feedback off
set heading off
set lines 120
set pages 9999
spool index_monitoring.sql
SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' MONITORING USAGE;'
FROM DBA_INDEXES
WHERE OWNER NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'OLAPSYS', 'DRSYS', 'MDSYS', 'XDB',
'WMSYS', 'ORDSYS', 'ODM', 'WKSYS', 'OUTLN', 'SYSMAN', 'EXFSYS', 'DMSYS', 'RMAN')
AND INDEX_TYPE NOT IN ('DOMAIN', 'LOB');
spool off
set heading on
set feedback on
- 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)
set lines 110
set pages 999
column index_name format a20
column monitoring format a10
column used format a10
column start_monitoring format a20
column end_monitoring format a20
SELECT index_name, monitoring, used, start_monitoring, end_monitoring
FROM v$object_usage;
no rows selected.
- 쿼리에서 모니터링한 인덱스들이 조회가 되지 않는 이유는 V$OBJECT_USAGE 가 자기 자신의 보유한 오브젝트에 대해서만 보여주기 때문
- 여기서는 SYS 유저로 쿼리를 실행하고, SYS에 관련된 인덱스에 대해서는 모니터링 하지 않았기 때문에 아무것도 나타나지 않는다.
- V$OBJECT_USAGE 딕셔너리 뷰에 해당하는 실제 데이터는 OBJECT_USAGE에 포함되어 있다.
SQL> desc object_usage
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJ# NOT NULL NUMBER
FLAGS NOT NULL NUMBER
START_MONITORING VARCHAR2(19)
END_MONITORING VARCHAR2(19)
set lines 140
set pages 9999
col owner format a10
col index_name format a35
col table_name format a30
col monitoring format a10
col start_monitoring format a20
col end_monitoring format a20
spool report_index_usage.txt
select username as owner,
io.name as index_name,
t.name as table_name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES') as monitoring,
decode(bitand(ou.flags, 1), 0, 'NO', 'YES') as used,
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, dba_users u
where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
and user_id=t.owner#;