CHAPTER 13. 정렬과 머지 조인

• 정렬처리 시 많은 시스템 자원 사용
• 정렬처리가 요구되는 처리

order by, group by, distinct, merge join, connect by,

B-tree to Bitmap Conversions, 분석함수, 집합연산, 인덱스 생성 등

• 머지 조인의 메커니즘과 비용 산정  설명
• CPU costing 및 workarea_size_policy 설정에 대한 변동
  (비용 계삭식에 정렬 시 디스크 쓰기와 읽기 부분이 빠져있음)

※ 10053 트레이스 파일 결과,

    수치로 표현된 비용 계산 모델과 실제 작동 사이 몇몇 불일치로 트레이스 파일 이해의 어려움 발생

시작하면서 438 / 위충환

     해시조인과 같이 Optimal, Onepass, Multipass, 효율성 기준으로 3가지로 구분

• Optimal 정렬 - 메모리에서만 수행

• Onepass 정렬 - 읽은 데이터를 메모리가 수용 할 수 없을 때 수행

• Multipass 정렬 - Onepass 와 시작이 유사하나 머지 시 메모리가 불충분할 경우 수행

    ▼ sort_demo_01.sql

create table t1
nologging  -- adjust as necessary
as
with generator as (
 select --+ materialize
  rownum     id,
 substr(dbms_random.string('U',4),1,4) sortcode
 from all_objects
 where rownum <= 5000
)
select
 /*+ ordered use_nl(v2) */
 substr(v2.sortcode,1,4) || substr(v1.sortcode,1,2) sortcode,
 substr(v1.sortcode,2,2)     v2,
 substr(v2.sortcode,2,3)     v3
from
 generator v1,
 generator v2
where
-- rownum <= 12000
 rownum <= 1048576;

 

alter session set workarea_size_policy = manual;

 
alter session set sort_area_size = 1048576;

• snap_events 통계정보 (10gR2 테스트 - 교재와 결과가 많이 다르네요..; 제가 설정을 잘못했나봐요 -_-;)

You will now have to wait a few seconds for a long query to complete

---------------------------------
Session stats - 12-3월  18:35:38
Interval:-  0 seconds
---------------------------------
Name                                                                     Value
----                                                                     -----
opened cursors cumulative                                                   14
user calls                                                                  42
recursive calls                                                              4
recursive cpu usage                                                          3
session logical reads                                                       21
CPU used when call started                                                   4
CPU used by this session                                                     4
DB time                                                                     13
enqueue requests                                                             1
enqueue releases                                                             1
consistent gets                                                             21
consistent gets from cache                                                  21
consistent gets - examination                                               21
calls to get snapshot scn: kcmgss                                            8
table fetch by rowid                                                         7
rows fetched via callback                                                    7
index fetch by key                                                           7
session cursor cache hits                                                    7
buffer is not pinned count                                                  14
parse time cpu                                                               1
parse time elapsed                                                          10
parse count (total)                                                         14
parse count (hard)                                                           7
execute count                                                               15
bytes sent via SQL*Net to client                                         2,849
bytes received via SQL*Net from client                                   4,189
SQL*Net roundtrips to/from client                                           28

PL/SQL 처리가 정상적으로 완료되었습니다.

BEGIN snap_events.end_snap; END;

• 테이블 정보 조회

select   table_name, blocks, avg_row_len
from     user_tables
where    table_name = 'T1'
order by table_name;

TABLE_NAME                         BLOCKS AVG_ROW_LEN
------------------------------ ---------- -----------
T1                                   2753          14

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         20  consistent gets
          0  physical reads
          0  redo size
        550  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

• 컬럼 정보 조회
  

select     table_name, column_name, avg_col_len
from       user_tab_columns
where      table_name = 'T1'
order by   table_name, column_name;

TABLE_NAME                     COLUMN_NAME                    AVG_COL_LEN
------------------------------ ------------------------------ -----------
T1                             SORTCODE                                 7
T1                             V2                                       3
T1                             V3                                       4

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         26  consistent gets
          0  physical reads
          0  redo size
        613  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

☞ 통계수치 v$mystat / v$tempstat 뷰로 부터 추출

    <참고> snap_ts.sql 실행이 되지 않은 관계로 임시 데이터 확인

SQL> select table_name, num_rows, blocks, avg_row_len
  2  from   user_tables
  3  where  table_name = 'T1';

TABLE_NAME             NUM_ROWS     BLOCKS AVG_ROW_LEN
-------------------- ---------- ---------- -----------
T1                      1048576       2753          14

• 교재에 나와있는 세션 통계정보를 바탕으로 정리 (p.440)
  

Name                         Value
----                         -----
consistent gets              2,758
physical reads direct        1,565
physical writes direct       1,565
table scans (long tables)        1
table scan rows gotten   1,048,576
table scan blocks gotten     2,753
sorts (disk)                     1
sorts (rows)             1,048,576

T/S  Reads Blocks Avg Csecs Writes Blocks Avg Csecs
---- ----- ------ --------- ------ ------ ---------
TEMP   993  1,565     2.420    269  1,565      .710

통계 정보		테이블 정보
Name	Value	Name	Value
table scan rows gotten	1,048,576	NUM_ROWS	1,048,576
consistent gets	2,758	BLOCKS	2,753
table scan blocks gotten	2,753	BLOCKS	2,753

 ☞ 통계정보의 스캔 로우 수와 블럭 수는 테이블 정보와 일치. consistent gets 은 거의 일치  

 ☞ 'physical reads direct' 와 'physical writes direct' 값 1,565은 v$tempstat 의 읽기 및 쓰기 개수와 일치

       Q.) physical reads 와 physical reads direct 는 다른 항목인가?

              (∵ physical reads 값이 0으로 나왔기 때문에 의문 발생)

• 10033 이벤트 트레이스 결과 (테스트를 시도했으나 결과가 나오지 않아 교재 내용 인용 p.441)
  

*** 2005-01-20 09:35:02.666
Recording run at 406189 for 62 blocks
Recording run at 4061c8 for 62 blocks
Recording run at 406206 for 62 blocks

   유사한 19개 라인 생략

Recording run at 4066de for 62 blocks
Recording run at 40671c for 62 blocks
Recording run at 40675a for 62 blocks
Recording run at 406798 for 15 blocks
Merging run at 406798 for 15 blocks
Merging run at 406189 for 62 blocks
Merging run at 4061c8 for 62 blocks
   유사한 19개 라인 생략
Merging run at 4066a0 for 62 blocks
Merging run at 4066de for 62 blocks
Merging run at 40671c for 62 blocks
Merging run at 40675a for 62 blocks
Total number of blocks to read: 1565 blocks

406..... 으로 bold로 적힌 부분은 

덤프한 데이터양과 Sort Run이 시작되는 temporary 테이블스페이스의 데이터 블록 주소 정보

☞ 블록 덤프를 통해 어떤 내용이 있는지 확인 가능

Onepass 정렬 시에 읽고 쓴 블록의 개수를 누적 합계는

v$mystat 의 'direct reads and writes' & v$tempstat 의 'blocks read written' 값 과 일치

• sort._area_size 크기를 달리 설정 했을 때의 그 메모리 설정 값, sort run 크기, CPU 소요 시간 등을 보는 테스트 결과
  

▼ 표 13-1 sort._area_size 크기와 자원 사용 비교

• 6MB 크기의 데이터 집합을 메모리 내에서 정렬하기 위해 25.5MB 만큼의 메모리를 할당.
• 메모리 할당량을 늘릴수록 CPU 사용량도 더 증가하는 경향.

위 결과를 통해 오라클이 정렬을 어떻게 수행하는지 가늠할 수 있음

메모리 사용 444 /위충환

• 오라클 정렬 메커니즘은 일종의 이진 삽입 트리에 기초

☞ 메모리 상의 리스트로 데이터 적재와 동시에 데이터에 대한 인덱스도 메모리 내에서 함께 유지

장점 : 데이터를 빠른 순서로 공급 가능, 포인터를 통해 힙영역의 저장된 데이터를 이동하기 용이

단점 : 예상외의 메모리를 요구

         ☞ 트리의 노드 구성 : 3개의 포인터(Parent, LefrChild, RightChild)

             32비트 운영체제 포인터 =  32비트(4byte) ∴ 1노드 당 12byte

             64비트 운영체제 시스템일 경우 포인터의 크기는 배로 증가

sort_area_size 를 운영체제 시스템을 고려하여 설정 필요.

그렇지 않을 경우 정렬을 메모리 내에서 끝낼 수 없음.

sort_area_size를 증가 시켜 디스크 읽고 쓰는 것을 줄이고 메모리에서만 정렬 처리를 하고 싶지만

Test 를 통하여 sort_area_size 크기와 CPU 사용이 비례되어 증가하는 것 확인

▼ 표 13-2  sort_area_size 크기와 비교 횟수 관계

☞ in-memory 정렬에서 머지 정렬로 전환 시 그 값이 급격히 변하지 않음

   [Q.] 위처럼 교재에 나와있는데 어떤 데이터를 보고 그렇게 판단하는 잘 모르겠음

메모리만으로 정렬 시 CPU 사용량이 많은 이유

☞ 메모리가 가득 찬 상태에서 in-memory 정렬 시 탐색 할 트리의 높이가 높아짐 (참고. 트리의 높이 = log₂(Rows))

    적은양의 데이터를 정렬하여 디스크에 덤프한 후 각 단위별 in-memory정렬에 의해 생성된 run이

    낮은 높이의 트리를 갖게되어 트리를 운영하는 CPU시간 감소

∴ CPU 자원을 소모시키는 것은 데이터 비교가 아닌 트리를 구성하는 포인트를 따라 탐색하는 차이에 의해 발생

    데이터는 디스크로 한번은 덤프해야 하기 때문에  CPU에서 부하 발생 시

    in_memory 정렬 대신 onepass 정렬로 수행되도록 메모리양을 최소화하여 정렬 처리로 성능 향상 가능.

    sort_area_size 크기는 multipass 정렬이 되지 않을 정도로 가능한 작게 설정하면 됨(≒ 자동 workarea_size_policy )

• sort_area_retained_size

  • 값 0 (default) : sort_area_retained_size 가 현 sort_area_size 크기와 일치되게 동적으로 조정
  • 오라클이 메모리를 획득하고 반환하는 방식 변함
  • 오라클이 임시 테이블스페이스를 사용하는 방식에 영향
    

※ 공유 서버 P_A_T (pga_aggregate_target)

     9i 까지 workarea_size_policy,10g 이후 pga_aggregate_target 을 통하여

     정렬, 해시조인 등과 같은 대량 메모리 요구 오퍼레이션을 위한 메모리가 제약 받음

▼ 표 13-3 sort_area_retained_size 설정 값이 I/O에 미치는 영향

• 'Physical writes direct' 항목에서 sort_area_retained_size 를 0 이 아닌 값으로 설정 시
  

     ☞ 오라클이 클라이언트로 결과를 반환하기 전에 모든 데이터를 디스크로 덤프

• sorts (disk) 값 0 (v$sesstat에 기록)
  

     ☞ 오라클이 디스크 정렬로 여기지 않는 다는 것을 보여줌 (즉, 디스크로 덤프 되기 전 정렬 작업은 이미 끝남)

※ sort_area_retained_size 값 설정 시

1. 처 음 메모리 할당 UGA에서 이루어짐
   
2. 할당 메모리가 sort_area_retained_size 한도 도달 시 메모리 할당 PGA로 전환
   

3. 정렬이 메모리 안에서 완료 되더라도
   

   메모리 영역이 UGA와 PGA로 분할 시
   

   전체 정렬 데이터 집합을 디스크로 덤프
   

• 메 모리 할당 시점
  

  • sort_area_size & sort_area_retained_size 는 메모리 할당 한도 의미
  • 메모리는 정렬이 필요할 때만 단계적으로 조금씩 할당 됨(한번에 모두 할당하지 않음)
    

• 공 유서버 or MTS 사용 시 메모리 할당 : UGA 는 SGA 내 위치
  

☞ sort_area_retained_size은 SGA 내 large pool 에서 이루어짐 (large_pool_size 미설정 시 shared pool에서 메모리 할당)

    sort_area_retained_size 초과 요구 시 (sort_area_size - sort_area_retained_size) 만큼 PGA 에서 메모리 할당

    공유 서버 프로세스의 메모리로 보여짐 (pga_aggregate_target 값을 고려하여 메모리 할당)

※ sort_area_retained_size < sort_area_size 시

1. 정렬 데이터가 정렬이 완료되기 이전 디스크로 덤프
   

2. PGA에서 초과 메모리 할당된 것은 다른 작업에서 사용 가능
   
3. sort_area_retained_size 는 다음 처리를 위해 덤프된 데이터를 재적재 하는데 사용
   

• 자동방식의 workarea_size_policy 와 pga_aggregate_target를 사용하지 않고 수동방식의 workarea_size_policy 와 sort_area_size를 사용하면 정렬 메커니즘이 변하지 않음

• 자동방식 workarea_size_policy : DBA는 오라클을 위한 적정 수준의 목표치를 설정
  (목표치 : 정렬,해시조인,인덱스 생성,비트맵 인덱스 처리등과 같은 대량 메모리 요구 오퍼레이션을 수행하는 모든 프로세스가 사용해야 하는 메모리 전체 한도를 의미)
  

• 오라클 커널 코드의 상호작용으로 현 메모리 할당량을 조사하여 초과 메모리 할당을 방지하고 불필요한 메모리를 반환
  (pl/sql 처리 관련 메모리는 메커니즘에 의한 통제 범위 바깥에 있지만 설정된 총 목표치를 초과하는지 계산할 목적으로 계속 추적되며 다른 작업영역의 가용메모리에 영향을 미침)
  

• 참고) 한도 확인작업은 매 3초단위로 수행되어 빠르게 세션이 연결되고 끊어지면 문제가 발생할수 있음

• 직렬 처리 오퍼레이션은 디폴트로 작업영역에 pga_aggregate_target의 5%까지 메모리를 할당받을수 잇음
• 병렬 처리 오퍼레이션은 디폴트로 slave포로세스가 사용하는 작업영역 메모리 합계가 pga_aggregate_target의 30%를 초과할수 없음 (각 slave 프로세스는 최대 5%한도의 제약을 받음,  6 이상의 병렬도를 가진쿼리는 30%이내로 제약을 받음)
  

관련 Hidden 파라미터

_smm_max_size: 직렬 처리에 대한 메모리 한도 ( 이차적인 한도는 _pga_max_size의 반)
_smm_px_max_size : 병렬 처리에 대한 메모리 한도
_smm_min_size: 세션이 작업 영역으로 할당 받게 될 최소 유효 메모리 양 (9.2.0.6에서 default는 pga_aggregate_target의 0.1%, 최소 128KB, 최대 1MB)
_pga_max_size: 하나의 세션에서 동시적으로 대량 메모리 요구 오프레이션이 수행될때 하나의 세션에 대한 메모리 할당을 제약
_smm_auto_min_io_size : I/O의 최소 크기
_smm_auto_max_io_size : I/O의 최대 크기
_sort_multiblock_read_count : 프로세스가 하나의 sort run으로부터 한번에 읽을수 읽는 블록 개수를 지정
(읽기 크기가 커지면 동시에 읽고 머지할수 있는 sort run의 개수가 줄어듬)