행위

"인덱스 모니터링"의 두 판 사이의 차이

DB CAFE

(새 문서: DB 전체 Index 사용현황 분석 개요 현업에서 운영중인 DB내에서 데이터의 액세스를 빠르게 수행하기 위해 인덱스를 생성한다. 생성한 인덱스...)
 
1번째 줄: 1번째 줄:
DB 전체 Index 사용현황 분석
+
== DB 전체 Index 사용현황 ==
개요
+
=== 개요 ===
현업에서 운영중인 DB내에서 데이터의 액세스를 빠르게 수행하기 위해 인덱스를 생성한다. 생성한 인덱스가 쿼리에서 더 이상 사용되지 않는다고 판단이 되면 제거를 하는 것이 테이블의 데이터 조작(DML)시 인덱스 갱신에 대한 부하를 최소화 할 수 있다. 특히 OLTP 업무가 많은 DB의 경우 테이블에 불필요한 인덱스가 많이 생성되어 있다면 DML 작업은 그 만큼 느려질 수 밖에 없다.  
+
# 생성한 인덱스가 쿼리에서 더 이상 사용되지 않는다고 판단되면 제거 하는 것이 테이블의 데이터 조작(DML)시 인덱스 갱신에 대한 부하 최소화.
한편, 통계정보의 갱신이나 튜닝등으로 수행되는 플랜이 변경된 경우에도 인덱스 사용여부를 민감하게 체크해 볼 필요가 있다. 그것이 DBA나 튜너가 의도한 대로 인덱스를 사용하거나 사용하지 않음을 판단한 경우라면, 인덱스의 종속여부를 결정할 수 있다. 하지만 그외 다른 인덱스들에 대해서는 운영중에 사용되고 있는지 곧바로 알기 어렵다.
+
# 통계정보의 갱신이나 튜닝등으로 수행되는 플랜이 변경된 경우에도 인덱스 사용여부를 민감하게 체크해 볼 필요가 있다.  
그래서 DB내에 유저가 생성한 모든 인덱스에 대하여 일정기간 동안 모니터링을 수행하여, 사용여부를 판가름해 볼 수 있다. 앞서 오라클에서 제공하는 인덱스 모니터링 기능을 사용하여 구현할 수 있다.
+
# DBA나 튜너가 의도한 대로 인덱스를 사용하거나 사용하지 않음을 판단한 경우라면, 인덱스의 종속여부를 결정할 수 있다.  
 +
# DB내에 유저가 생성한 모든 인덱스에 대하여 일정기간 동안 모니터링을 수행하여, 사용여부를 판가름해 볼 수 있다.  
  
 인덱스 모니터링 방법
+
=== 인덱스 모니터링 방법 ===
우선 전체 인덱스를 파악해서 유저가 생성한 인덱스 만을 찾아서 모니터링을 설정한다.
+
# 우선 전체 인덱스를 파악해서 유저가 생성한 인덱스 만을 찾아서 모니터링을 설정
다음은 모니터링 대상을 추출하여, 모니터링을 수행하는 스크립트를 작성한다.
+
# 모니터링 대상 추출, 모니터링을 수행하는 스크립트 작성
도메인 인덱스나 LOB 타입 인덱스는 모니터링을 할 수 없다.
+
# 도메인 인덱스나 LOB 타입 인덱스는 모니터링을 할 수 없다.
SYS 유저로 수행한다.
+
# SYS 유저로 수행한다.
  
 
 
 
 
 
 -- create_index_monitoring.sql------------------------------------------------------------
 
 -- create_index_monitoring.sql------------------------------------------------------------
 
+
<source lang=sql>
 set feedback off
+
set feedback off
 
set heading off
 
set heading off
 
set lines 120
 
set lines 120
31번째 줄: 32번째 줄:
 
 set heading on
 
 set heading on
 
set feedback on
 
set feedback on
 +
</source>
  
 다음과 같이 v$object_usage 를 조회하면 아무것도 나오지 않는다.
+
* v$object_usage 를 조회하면 아무것도 나오지 않는다.
 
+
<source lang=sql>
 
 SQL> desc v$object_usage
 
 SQL> desc v$object_usage
 
  Name                                      Null?    Type
 
  Name                                      Null?    Type
55번째 줄: 57번째 줄:
 
   
 
   
 
 no rows selected.
 
 no rows selected.
 
+
</source>
 
 위 쿼리에서 모니터링한 인덱스들이 조회가 되지 않는 이유는 V$OBJECT_USAGE 가 자기 자신의 보유한 오브젝트에 대해서만 보여주기 때문이다. 여기서는 SYS 유저로 쿼리를 실행하고,  SYS에 관련된 인덱스에 대해서는 모니터링 하지 않았기 때문에 아무것도 나타나지 않는다.  
 
 위 쿼리에서 모니터링한 인덱스들이 조회가 되지 않는 이유는 V$OBJECT_USAGE 가 자기 자신의 보유한 오브젝트에 대해서만 보여주기 때문이다. 여기서는 SYS 유저로 쿼리를 실행하고,  SYS에 관련된 인덱스에 대해서는 모니터링 하지 않았기 때문에 아무것도 나타나지 않는다.  
 
V$OBJECT_USAGE 딕셔너리 뷰에 해당하는 실제 데이터는 OBJECT_USAGE에 포함되어 있다.  
 
V$OBJECT_USAGE 딕셔너리 뷰에 해당하는 실제 데이터는 OBJECT_USAGE에 포함되어 있다.  

2022년 9월 5일 (월) 18:21 판

thumb_up 추천메뉴 바로가기


1 DB 전체 Index 사용현황[편집]

1.1 개요[편집]

  1. 생성한 인덱스가 쿼리에서 더 이상 사용되지 않는다고 판단되면 제거 하는 것이 테이블의 데이터 조작(DML)시 인덱스 갱신에 대한 부하 최소화.
  2. 통계정보의 갱신이나 튜닝등으로 수행되는 플랜이 변경된 경우에도 인덱스 사용여부를 민감하게 체크해 볼 필요가 있다.
  3. DBA나 튜너가 의도한 대로 인덱스를 사용하거나 사용하지 않음을 판단한 경우라면, 인덱스의 종속여부를 결정할 수 있다.
  4. DB내에 유저가 생성한 모든 인덱스에 대하여 일정기간 동안 모니터링을 수행하여, 사용여부를 판가름해 볼 수 있다.

1.2 인덱스 모니터링 방법[편집]

  1. 우선 전체 인덱스를 파악해서 유저가 생성한 인덱스 만을 찾아서 모니터링을 설정
  2. 모니터링 대상 추출, 모니터링을 수행하는 스크립트 작성
  3. 도메인 인덱스나 LOB 타입 인덱스는 모니터링을 할 수 없다.
  4. SYS 유저로 수행한다.

   -- create_index_monitoring.sql------------------------------------------------------------

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#; spool off;* 출처: https://otsteam.tistory.com/387?category=535917 [Techdata VINA 지식 공유:티스토리]