태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

왜 진단 이벤트를 알아야 하는가?

오라클 2009.10.27 14:25
심지어 개발자라고 하더라도 오라클이 제공하는 진단 이벤트를 이해하는 것이 필요하다고 생각합니다. 가령 다음과 같은 질문을 보세요.

정렬 영역 크기(Sort Area Size)에 따라 정렬이 어떻게 이루어지는를 알기 위해 다양한 테스트를 수행하고 있습니다. 그런데 SORT_AREA_SIZE를 300바이트로 낮게 설정해도 오라클이 최소값을 스스로 조정하는 것 같습니다. 오라클에서 사용 가능한 정렬 영역 크기의 최소값이 얼마인지 어떻게 알 수 있을까요?

매뉴얼에는 다음과 같이 기술되어 있습니다.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams232.htm

즉, 정렬 영역 크기의 최소값은 6*8K(데이터베이스 블록 사이즈) = 48K 바이트라는 것을 알 수 있습니다. 진정한 엔지니어라면 여기서 더 나가서 이 최소값을 눈으로 확인할 수 있는 방법은 없을까?라는 질문을 하게 될 것입니다.

이런 류의 질문에 가장 직접적인 답변을 할 수 있는 것이 진단 이벤트입니다. 가령 10032 이벤트를 활성화하면, 오라클은 정렬 작업의 수행에 대한 상세한 통계 정보를 기록해줍니다.

UKJA@ukja1021> @oerr 10032
10032
 "sort statistics (SOR*)"
// *Cause:
// *Action:
더불어 10033 이벤트를 이용하면 실제 정렬을 수행하면서 개별 Sort Run들을 어떻게 사용하는지까지 알 수 있습니다. 대단히 유용한 정보들이죠.
UKJA@ukja1021> @oerr 10033
10033
 "sort run information (SRD*/SRS*)"
// *Cause:
// *Action:
// *Cause:
// *Action:
10032 이벤트를 이용해서 매뉴얼의 내용을 검증해 볼까요?
drop table t1 purge;
create table t1
as select rpad('x',300,'x') as c1
from dual
connect by level <= 100
;

alter session set workarea_size_policy=manual;
alter session set sort_area_size=300;

alter session set events '10032 trace name context forever, level 1';

select c1 from t1 order by c1;

alter session set events '10032 trace name context off';
트레이스 파일의 내용은 다음과 같습니다. SORT_AREA_SIZE 값이 정확하게 48K바이트라는 것을 확인할 수 있습니다.
---- Sort Parameters ------------------------------
sort_area_size                    49152
sort_area_retained_size           16384
sort_multiblock_read_count        1
max intermediate merge width      2
---- Sort Statistics ------------------------------
Initial runs                              1
Input records                             100
Output records                            100
Disk blocks 1st pass                      4
Total disk blocks used                    6
Total number of comparisons performed     102
  Comparisons performed by in-memory sort 101
  Comparisons while searching for key in-memory 1
Temp segments allocated                   1
Extents allocated                         1
Uses version 2 sort
Uses asynchronous IO
    ---- Run Directory Statistics ----
Run directory block reads (buffer cache)  2
Block pins (for run directory)            1
Block repins (for run directory)          1
    ---- Direct Write Statistics -----
Write slot size                           8192
Write slots used during in-memory sort    2
Number of direct writes                   4
Num blocks written (with direct write)    4
Block pins (for sort records)             4
Cached block repins (for sort records)    1
Waits for async writes                    3
    ---- Direct Read Statistics ------
Size of read slots for output             8192
Number of read slots for output           2
Number of direct sync reads               2
Number of blocks read synchronously       2
Number of direct async reads              2
Number of blocks read asynchronously      2
---- End of Sort Statistics -----------------------
훌륭한 개발자라면 디버거(Debugger)를 잘 사용하죠. 오라클의 진단 이벤트는 오라클 디버거라고 할 수 있습니다. 오라클을 깊이있게 이해하고자 한다면 필수적인 지식이라고 할 수 있습니다.
저작자 표시
신고
Trackback 0 : Comments 6
  1. feelie 2009.10.29 17:44 신고 Modify/Delete Reply

    내용 잘 봤습니다. 아직 많이 부족해서 예제를 그냥 따라 하는 정도 입니다.
    지금 사용하는 환경에서 해보니 조금 달라 문의 드립니다,
    version : 10.2.0.3
    block_size = 8192
    말씀대로라면 48K가 나와야 하는데 트레이스 파일에는 65536 입니다.

    트레이스 파일
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options
    ORACLE_HOME = /oracle/app/oracle/product/10.2.0/db_1
    System name: AIX
    Node name: oracle
    Release: 3
    Version: 5
    Machine: 00390B1A4C00
    Instance name: oracle
    Redo thread mounted by this instance: 1
    Oracle process number: 22
    Unix process pid: 4681774, image: oracle

    *** 2009-10-29 17:34:18.685
    *** ACTION NAME:() 2009-10-29 17:34:18.676
    *** MODULE NAME:() 2009-10-29 17:34:18.676
    *** SERVICE NAME:(SYS$USERS) 2009-10-29 17:34:18.676
    *** SESSION ID:(2229.51718) 2009-10-29 17:34:18.676
    ---- Sort Parameters ------------------------------
    sort_area_size 65536
    sort_area_retained_size 65536
    sort_multiblock_read_count 1
    max intermediate merge width 3
    ---- Sort Statistics ------------------------------
    Input records 100
    Output records 100
    Total number of comparisons performed 102
    Comparisons performed by in-memory sort 102
    Total amount of memory used 36864
    Uses version 2 sort
    Does not use asynchronous IO
    ---- End of Sort Statistics -----------------------

    • 욱짜 2009.10.29 17:48 신고 Modify/Delete

      workarea_size_policy, pga_aggregate_target, sort_area_size, sort_area_retained_size의 현재 설정값이 어떻게 됩니까? 이들 파라미터에 따라 해당 값이 결정됩니다.

  2. feelie 2009.10.29 18:56 신고 Modify/Delete Reply

    해당 파라메터 정보 입니다.
    workarea_size_policy : manual
    pga_aggregate_target : 418381824
    sort_area_size : 300
    sort_area_retained_size : 0

    • 욱짜 2009.10.29 19:25 신고 Modify/Delete

      아마 특정 버전 이상부터는 매뉴얼에 기록된 방식이 아닌 다른 방식을 사용하는 것 같습니다. 테스트의 결과를 보면 sort_area_size와 sort_area_retained_size의 값이 8개의 Database Block 크기를 따르는 것 같습니다.

      여기에 대해서는 추가적으로 조사가 필요하겠는데요?

  3. feelie 2009.10.29 20:17 신고 Modify/Delete Reply

    감사합니다.
    앞으로도 좋은 내용 부탁드립니다.

  4. 타락천사 2009.11.03 18:24 신고 Modify/Delete Reply

    항상 대단하다고 감탄만 하게 하네요 ^^;
    PGA 관련 체크 할일이 있는데..
    이샘플로 어느정도 가능할것 같네요
    항상 감사합니다

Write a comment

티스토리 툴바