태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

인덱스 생성과 V$SESSION_LONGOPS 뷰

오라클 2009.09.11 10:45
이런 취지의 질문을 받았습니다.
인덱스 생성이 오랜 시간(몇 분 이상?) 수행 중인데 이를 V$SESSION_LONGOPS 뷰를 통해 모니터링 가능한가?
이에 대한 대답은 V$SESSION_LONGOPS 뷰에 대한 매뉴얼에서 어느 정도 얻을 수 있습니다.

아마 예상하건대 6초 이상 걸리는 Query라면 V$SESSION_LONGOPS 뷰를 통해 모니터링 가능할 것 같습니다.

       
UKJA@ukja1021> create table t2
  2  as select level as c1, rpad('x',4000) as c2, rpad('x',4000) as c3
  3  from dual
  4  connect by level <= 1000
  5  ;

Table created.

Elapsed: 00:00:01.17

-- Index 생성 시간을 일부러 지연시키기 위한 함수(6초 이상)
UKJA@ukja1021> create or replace function fdelay(p1 number, p2 number)
  2  return number
  3  deterministic
  4  is
  5  begin
  6     dbms_lock.sleep(p2);
  7     return 1;
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.00
Session #1에서 Index를 만드는 도중에 Session #2에서 V$SESSION_LONGOPS 뷰를 모니터링해봅니다.
UKJA@ukja1021> -- session #1
UKJA@ukja1021> create index t2_n1 on t2(fdelay(c1,1)+c1);
결과는 실망적입니다. 6초 아니라 6분이 지나도 V$SESSION_LONGOPS 뷰에는 나타나지 않죠.
UKJA@ukja1021> exec print_table('select * from v$session_longops where sid = 141');

PL/SQL procedure successfully completed.
문제가 무엇일까요? 매뉴얼에서 설명하지 않고 있는 몇 가지 제한이 있기 때문입니다. 예를 들어 이런 제한들이 있습니다.
  • Full Table Scan의 경우 Table Block 수가 10,000개 이상일 경우에만 모니터링됩니다.
  • Index Fast Full Scan의 경우에는 Index Block 수가 1,000개 이상일 경우에만 모니터링됩니다.
  • Hash Join은 모니터링되지만 Nested Loops Join은 모니터링되지 않습니다.
  • 비슷한 이유로 Index Range Scan등은 모니터링되지 않습니다.
(자세한 정보는 여기를 참조하세요)

테이블의 블록 수를 20,000개로 키워보겠습니다.

UKJA@ukja1021> -- increase the data over 10,000 block
UKJA@ukja1021> insert into t2
  2  select level as c1, rpad('x',4000) as c2, rpad('x',4000) as c3
  3  from dual
  4  connect by level <= 20000
  5  ;

20000 rows created.

Elapsed: 00:00:56.23
그리고 동일하게 모니터링해보면 다음과 같이 V$SESSION_LONGOPS 뷰에 나타납니다.
UKJA@ukja1021> -- session #1
UKJA@ukja1021> create index t2_n1 on t2(fdelay(c1,1)+c1);
UKJA@ukja1021> exec print_table('select * from v$session_longops where sid = 141');
SID                           : 141
SERIAL#                       : 593
OPNAME                        : Table Scan
TARGET                        : UKJA.T2
TARGET_DESC                   :
SOFAR                         : 17
TOTALWORK                     : 42276
UNITS                         : Blocks
START_TIME                    : 2009/09/11 09:48:48
LAST_UPDATE_TIME              : 2009/09/11 09:48:57
TIMESTAMP                     :
TIME_REMAINING                : 22372
ELAPSED_SECONDS               : 9
CONTEXT                       : 0
MESSAGE                       : Table Scan:  UKJA.T2: 17 out of 42276 Blocks
done
USERNAME                      : UKJA
SQL_ADDRESS                   : 2FBD3900
SQL_HASH_VALUE                : 3367173127
SQL_ID                        : 2d01s2z4b5z07
QCSID                         : 0
-----------------

UKJA@ukja1021> exec print_table('select * from v$session_longops where sid = 141');

SID                           : 141
SERIAL#                       : 593
OPNAME                        : Table Scan
TARGET                        : UKJA.T2
TARGET_DESC                   :
SOFAR                         : 61
TOTALWORK                     : 42276
UNITS                         : Blocks
START_TIME                    : 2009/09/11 09:48:48
LAST_UPDATE_TIME              : 2009/09/11 09:49:18
TIMESTAMP                     :
TIME_REMAINING                : 20761
ELAPSED_SECONDS               : 30
CONTEXT                       : 0
MESSAGE                       : Table Scan:  UKJA.T2: 61 out of 42276 Blocks
done
USERNAME                      : UKJA
SQL_ADDRESS                   : 2FBD3900
SQL_HASH_VALUE                : 3367173127
SQL_ID                        : 2d01s2z4b5z07
QCSID                         : 0
-----------------
여기서 주의할 것은 인덱스를 생성하는 작업 자체가 모니터링되는 것이 아니라, 인덱스를 생성하기 위해 Table Full Scan을 하는 작업이 모니터링 대상이라는 것입니다. 인덱스 리빌드 작업도 같은 원리에서 모니터링이 됩니다.

V$SESSION_LONGOPS 뷰는 이름만 보면 정말 유용할 것 같지만 실제로 사용하려고 해보면 이런 저런 제약때문에 한숨이 나올 때가 많죠.

저작자 표시
신고
Trackback 0 : Comments 5
  1. 박용석 2009.09.11 15:30 신고 Modify/Delete Reply

    parallel 옵션을 주고 ( 일명 PQ 주고 ) 테스트 해보시기 바랍니다. 재밌습니다.

  2. 익명 2009.09.12 12:32 신고 Modify/Delete Reply

    박용석님 뭐가 재밌는지 미리 말씀해주시면 안될까요? ㅋㅋ

  3. 박용석 2009.09.16 14:12 신고 Modify/Delete Reply

    pq를 주고 mig 작업 중 이 뷰를 보면 신기한 현상이 보입니다..ㅎㅎ..

    결론만 말씀 드리면 PQ시에도 정확한 TIME 예측이 불가 합니다.

  4. ktlee67@show.co.kr 2009.11.18 14:34 신고 Modify/Delete Reply

    DBMS_APPLICATION_INFO.set_session_longops를 사용해도... 제약이 유효한가요?

    • 욱짜 2009.11.18 14:50 신고 Modify/Delete

      dbms_application_info.set_session_longops는 v$session_longops 뷰에 우리가 원하는 데이터를 직접 세팅하는 방식이기 때문에 위의 제약이 없습니다.

Write a comment

티스토리 툴바