13. 정렬과 머지 조인

조회 수 13470 추천 수 0 2011.06.14 22:03:05
실천하자 *.115.31.16

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 정렬 - 메모리에서만 수행

  데이터를 연속적으로 읽으면서 정렬하는 과정의 모든 것을 메모리에 두고 진행

  가용 메모리  소진 전까지 데이터가 모두 정렬되어 임시적인 작업열역을 위한 디스크 공간 필요 없음

  메모리 할당은 데이터 읽기를 진행하면서 점차적으로 이루어짐

  (sort_area_size 로 설정된 크기 만큼 메모리가 한번에 할당 하지 않음)


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

  데이터를 읽어 정렬하다가 가용 메모리가 차면 정렬된 데이터 집합을 디스크로 덤프,

  이어서 데이터를 읽어 정렬하다가 또 메모리가 차면 정렬 데이터 집항을 또 디스크로 덤프,

  입력 데이터 모두 정렬 처리 될때까지 반복 후 디스크 상에 정렬된 Run들을 단일 데이터 집합으로 Merge.

  정렬된 모든 Run의 Chunk 단위를 전부 읽어 들일 정도로 메모리가 충분할 경우 Onepass 정렬

  충분하지 않을 경우 Multipass 정렬 수행


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

  디스크 상에 정렬된 Run의 Chunk 단위를 한번에 메모리에 올릴 수 없을 경우

  몇 개의 데이터 스트림을 모아 나의 더 큰 스트림으로 머지하여 디스크로 다시 쓰고,

  또 다시 몇개의 스트림을 모아 더 큰 하나의 스트림으로 머지하여 디스크로 쓰는 과정 반복.

  원래의 스트림을 모두 처리하면 더 커진 스트림 몇 개를 다시 읽어 Merge.

    ※ Merge passes 횟수 - 정렬 데이터를 반복해서 읽은 회수


    ▼ 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 크기와 자원 사용 비교

sort_area_size  sort run 크기
마지막 것 제외

sort run 개수
쓰기 블록 수
CPU 시간(초)
1MB / 128 blocks
62 blocks
26
1,565 4.30
2MB / 256 blocks
122?123 blocks
13 1,552 4.44
4MB / 512 blocks
241?245 blocks
7 1,550 5.09
8MB / 1,024 blocks
484?492 blocks
4 1,549 5.71
12MB / 1,540 blocks
725?748 blocks
3 1,549 6.00
16MB / 2,048 blocks
970 and 577 blocks
2 1,548 6.02
25.5MB / 3,264 blocks
  0   6.21


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

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



메모리 사용 444 /위충환


sort_memory.jpg

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

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


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

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

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

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

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


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

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


 

CPU 사용 446 /위충환

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

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


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


sort_area_size메모리
비교 횟수
Initial Runs머 지
비교 횟수
로 우 수 *
log₂(Initial Runs)
총 비교 횟수
1MB14,869,919264,993,7124,929,00019,863,631
2MB15,969,150133,946,5983,880,00019,915,748
4MB16,984,02972,981,4342,944,00019,965,463
8MB17,967,78842,097,1412,097,15220,064,929
12MB18,564,70431,604,6931,662,00020,169,397
16MB18,884,12221,048,5731,048,57619,932,695
24MB19,622,96021,048,5551,048,57620,671,515
25.5MB19,906,60000n/a19,906,600


☞ 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 450 /위충환

  • 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에 미치는 영향

통계 항목 sort_area_retained_size = 0
sort_area_retained_size = 8MB
session UGA memory max
25.5MB
8.3MB
session PGA memory max
25.8MB
28.5MB
physical writes direct
0 1,547
sorts (disk)
0 0


  • '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 는 다음 처리를 위해 덤프된 데이터를 재적재 하는데 사용


 

pga_aggregate_target 452 /남송휘


  • 자동방식의 workarea_size_policy pga_aggregate_target를 사용하지 않고 수동방식의 workarea_size_policy sort_area_size를 사용하면 정렬 메커니즘이 변하지 않음
  • 자동방식 workarea_size_policy : DBA는 오라클을 위한 적정 수준의 목표치를 설정
    (
    목표치 : 정렬,해시조인,인덱스 생성,비트맵 인덱스 처리등과 같은 대량 메모리 요구 오퍼레이션을 수행하는 모든 프로세스가 사용해야 하는 메모리 전체 한도를 의미)
  • 오라클 커널 코드의 상호작용으로 현 메모리 할당량을 조사하여 초과 메모리 할당을 방지하고 불필요한 메모리를 반환
    (pl/sql
    처리 관련 메모리는 메커니즘에 의한 통제 범위 바깥에 있지만 설정된 총 목표치를 초과하는지 계산할 목적으로 계속 추적되며 다른 작업영역의 가용메모리에 영향을 미침)

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


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



관련 Hidden 파라미터


_smm_max_size: 직렬 처리에 대한 메모리 한도 ( 이차적인 한도는 _pga_max_size의 반)
_smm_px_max_size :
병렬 처리에 대한 메모리 한도
_smm_min_size:
세션이 작업 영역으로 할당 받게 될 최소 유효 메모리 양 (9.2.0.6에서 defaultpga_aggregate_target0.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의 개수가 줄어듬)



  • sort_demo_01b.sql  실행 결과 각 트레이스 파일 확인결과 서로 모순된 기록을 가지만 낙관적으로 자원 소비량할당 런타임 엔진의 메커니즘을 설명할수 있을것으로 예상

  • pga_aggregate_target: 200 으로 설정 , 정렬작업을 위해 할당 받을수있는 메모리는 10MB한도
  • PGA에 할당된 메모리 합계를 비교해보기위해 v$pgastat , v$sql_workarea_active 뷰 확인
  • 정렬 오프레이션은 사실상 10M로 할당된 sort_area_size로 정렬을 수행 첫번째 pass에서 정렬작업이 메모리에서 끝낼수 없음이 분명해지고 결국 세션은 첫번째 run을 디스크로 덤프하고 메모리를 accounting pool로 반환

결론: 오프레이션이 onepass가 될수 있도록 충분한 메모리를 유지하되, cpu비용이 불필요하게 증거하거나 메모리를 요구하는 다른 프로세스가 메모리 부족을 호소하는 일이 없도록 하는것이  이상적


cf) sort runs의 크기가 꾸준히 증가하는 것을보면 세션이 runs를 덤프하면서 매번 전체 pga할당상태를 채크하는것같음



실제 I/O 455 / 남송휘


  • sort_area_size1MB로 설정하고 처음테스트의 10032트레이스를 확인하면

    62블록 크기의 sort runs1565만큼 디스크에 썼다가 그것을 머지 하기 위해 다시 읽음

    v$tempstat 결과를 보면 쓰기 요청횟수 269, 읽기 요청횟수는 993

  • 어떤유형의 읽기과 쓰기가 발생하고 그것이 얼마나 큰크기인지, 그것이 조정될수 있는 것인 체크해볼 필요가있음

    옵티마이저가 정렬에 대한 비용을 어떻게 추정하는지 알아보기 전에 마지막으로 고려해야할것은 I/O의 실질적인 영향도


 

정렬 비용

10053 트레이스 458  /남송휘




  • sort width: 10032 트레이스의 ‘max intermediate merge width’와 동일

  • area size: 데이터 처리 목적으로 사용할수있는 메모리 양

  • max area size: 해당 세션이 할당 받을수 있는 최대 크기

  • degree: 병렬쿼리수행하는 경우 구조적으로 동일한 완전히 분리된 2개의 세션을 통해 정렬 비용 계산 내역을 보여줌

    하나는 degree 1일때

    다른 하나는 쿼리에서 설정한 DOP( degree of parallelism)일때의 비용계산내역

    (dop는 쿼리문의 힌트,테이블 속성, 자동방식등을 통해 정해짐)

  • blocks to sort: 정렬된 데이터양 로우크기 * 로우개수/블록크기

  • row size: 정렬 데이터의 평균 로우 크기, user_tab_columns 뷰의 avg_col_length를 이용해서 산출
    ( 12+ sum(avg_col_length) + (n-1) 

  • rows: 테이블의 계산된 카디널리티

  • initial runs: 오라클이 추산한 디스크로 덤프하게 될 sort runs의 개수

  • merge passes: multipass 정렬일 경우 전체 데이터 집합이 디스크로 쓰여졌다가 다시 읽혀 지는 횟수를 나타냄

  • IO cost /pass : 1회 머지 pass
  • 를 수행하는 비용, 메모리에서 정렬을 끝낼수 없을때 썻다가 다시 읽어야 하는것을 반영
  • total io sort cost: cost per pass와 pass회 수가 결합된 값

  • total cpu sort cost: 비용중 cpu 해당 부분, cpu오퍼레이션 수 단위
  • total temp space used: 정렬 오퍼레이션이 요구하는 임시 영역의 이론적인 크기


 

※ dbms_stats.gather_table_stats() 프로시져와 analyze명령의 차이점

avg_col_len 값이 컬럼길이를 저장하는 length byte를 포함, analyze는 그렇지 않음
10053트리에스에서 정렬 비용계산에 사용된 row size가 달라질수있음





비교 테스트 463 /이창헌


     4가지 환경(pat_cpu_harness.sql, pat_nocpu_harness.sql, sas_cpu_harness.sql, sas_nocpu_harness.sql)에 서 테스트 진행

    테스트 환경 값:

    sort_area_size = 1MB, pga_aggregate_target = 20MB
    Blocks to sort = 588

    *CPU costing을 비활성화 하였을 때 :

       Total IO sort cost = ( Block to sort + IO cost per pass * Merge passes) /2


    *CPU costing을 활성화 하였을 때 :

       Total IO sort cost = ( Block to sort + IO cost per pass * Merge passes)

    결과값 : p465 표 참고

  1. sort_area_size를 사용하는 것에서 pga_aggregate_target를 사용하는것으로 전환하고자 할 때, 작업 영역을 5%로 한정하는 것으로 가정한 수치는 잘 맞지 않는다는 것이다.
  2. 작업 영역을 수동 관리 방식으로 사용하면서 자동 관리  방식으로의 전환 없이 CPU costing을 활성화하고자 한다면, 정렬 비용이 현저히 떨어지 수 있다는 점
  3. 가용 메모리가 큰 경우에 나타나는 수치와 관련한 것이다. 2MB크리의 정렬 데이터는 모든 부가적인 바이트와 포인터를 고려할 때 약 7MB로 바뀌는데, 가용 메모리 크리가 30MB나 되어 메모리만으로 정렬을 완료시킬 수 있을 때 조차 옵티마이저는 높은 비용을 계산



머지 조인 468 / 이창헌

 

  • 머지 조인=소트 머지 조인-양쪽 대상 집합 모두가 조인 컬럼 순으로 정렬되어야하기 때문.

      -해시 조인처럼 조인을 두 개의 독립된 쿼리로 분해하고, 각 결과 집합을 다시 결합

1.액세스와 필터 조건절을 사용하여 첫 번째 데이터 집합을 획득하고 그것을 조인 컬럼 순으로 정렬

2.액세스와 필터 조건절을 사용하여 두 번째 데이터 집합을 획득하고 그것을 조인 컬럼 순으로 정렬

3.첫 번째 데이터 집합의 각 로우에 대해 두 번째 데이터 집합에서 시작점을 찾고 조인에 실패하는 로우를

   만날 때까지 스캔한다.(두 번째 데이터 집합이 정렬되어 있으므로 어느 지점에서 중단해야 하는지를 알 수 있다.)

 

  • 해시 조인과 머지 조인 공통점

            -하 나의 쿼리를 별개의 두 쿼리로 나누게 되는데 그 두 쿼리는 처음 데이터를 회득하는 데 사용

  • 해시 조인과 머지 조인 차이점

              - 두 번째 쿼리 부분을 시작하기 전에 첫 번째 쿼리 부분을 끝내야 할 필요는 없다


 

머지 메커니즘 468 /이창헌

첫 번째 데이터 집합이 이미 정렬되어 있는 경우= 적절한 인덱스가 있으면 두 번째 집합을 획득하기 전에 전체를 처리하지 않아도 됨.

-no_sort.sql

SELECT STATEMENT Optimizer Mode=ALL_ROWS                                                 

  SORT AGGREGATE                                                                            

    MERGE JOIN                                                                       

      TABLE ACCESS BY INDEX ROWID      SCOTT.T1         

             INDEX RANGE SCAN         SCOTT.T1_I1                                                 

      SORT JOIN             

             TABLE ACCESS FULL             SCOTT.T2         

 

메커니즘의 다양한 변형을 열거하면 다음과 같다.

   -일대일

-등식 조인 조건의 일대다(예를 들어, 부모/자식 조인)

-범위 조인 조건의 일대다(예를 들러, t2.dt between t1.dt -3 and t1.dt + 3 )

-등식 조인 조건의 다대다

-범위 조인 조건의 다대다

 

데이터 집합이 정렬되었다고 보고, 이사의 대안들을 도식화하면 그림 13-2와 같다. Page: 469 참조

 

 

select

       count(distinct t1_vc ||t2_vc)

from   (

       select /*+ no_merge ordered use_merge(t2) */

             t1.small_vc  t1_vc,

             t2.small_vc  t2_vc

       from

             t1,

             t2

       where

           t1.n1 <= 1000 and t2.id = t1.id

             -- t1.n1 <= 1000 and t2.n2 = t1.id

             -- t1.n1 <= 1000 and t2.id between t1.id-1 and t1.id+1

             -- t1.n1 <= 1000 and t2.n2 = t1.n2

             -- t1.n1 <= 1000 and t2.n2 between t1.n2 -1 and t1.n2 + 1

       )

;

 

dbms_xplan.display() v$sql_plan_statistics 뷰를 조사 하영 액세스 조건절과 필터 조건절을 얻어 있고, 그리고 row source에서 나온 로우 개수가 개인지 확인, 실행 횟수도 조사가능.


 


 

첫 번째 집합을 정렬할 필요가 없는 머지 조인 474 / 박우창

? 옵티마이저는 아우터 테이블에 대하여 no-sort 옵션을 고려할 수 있다.

- 아우터 조인 테이블에 인덱스가 있고, 적은 로우를 선택할 경우이다.

 

카테지안 머지 조인 475 / 박우창


? 옵티마이저는 이행적폐쇄를 사용하여 정상적인 조인을 카테지안 머지 조인으로 바꾼다.

? 예제 - (cartesian.sql)

  - t1 : 800개 튜플   

Column

type

비고

n1

number

1,2,3,...9,0

mod(rownum,10)

n2

number

1,2,3,...9,0

mod(rownum,10)

small_vc

varchar2(40)

1..800 

(rownum)

padding

varchar2(100)

x

 

- t2 : 1000개 튜플   

Column

type

비고

id

number

1,2,3,...9,0

mod(rownum,10)

id_probe

number

1,2,3,...9,0

mod(rownum,10)

build_vc

number

1..800 

(rownum)

build_padding

varchar2(60)

x

 

? 카티전 프로덕트 질의에서 비용값이 다대다 조건의 경우와 같이 크게 나타난다.

 select count(t1.small_vc), count(t2.small_vc)

 from t1, t2

 where t1.n1 = 5 and t2.n1 = 5;

- 실행계획

 --------------------------------------------------------------

 | Id  | Operation             | Name | Rows  | Bytes | Cost  |

 --------------------------------------------------------------

 |   0 | SELECT STATEMENT      |      |     1 |    14 |   159 |

 |   1 |  SORT AGGREGATE       |      |     1 |    14 |       |

 |   2 |   MERGE JOIN CARTESIAN|      |  8000 |   109K|   159 |

 |*  3 |    TABLE ACCESS FULL  | T2   |   100 |   700 |     4 |

 |   4 |    BUFFER SORT        |      |    80 |   560 |   155 |

 |*  5 |     TABLE ACCESS FULL | T1   |    80 |   560 |    2 |

 --------------------------------------------------------------

- 비정상적인 높은 비용을 계산하나 실제로는 많은일을 하지는 않는다.


 
집계 478 / 박우창


? 정렬이 필요한 질의문의 실행계획을 살펴보고 GROUP BY 비용을 살펴본다.

? 예제 - (agg_sort.sql)

- t1 : 5,000개 튜플   

Column

type

비고

col1

varchar2(4000)

알파벳 대문자 1~2개

random

col2

varchar2(4000)

알파벳 대문자 1~2개

random

? 정렬이 필요한 실행계획(10g)

  - ORDER BY -> SORT ORDER BY

 -----------------------------------------------------------

 | Id  | Operation          | Name | Rows  | Bytes | Cost  |

 -----------------------------------------------------------

 |   0 | SELECT STATEMENT   |      |  5000 | 20000 |    19 |

 |   1 |  SORT ORDER BY     |      |  5000 | 20000 |    19 |

 |   2 |   TABLE ACCESS FULL| T1   |  5000 | 20000 |     3 |

 -----------------------------------------------------------


  - 다른 정렬을 요구하는 SQL 문     

SQL 구문

실행계획

비용(10g)

ORDER BY

SORT ORDER BY

19

GROUP BY

HASH GROUP BY

19

GROUP BY, ORDER BY

SORT GROUP BY

19 (주1)

DISTINCT

HASH UNIQUE

19

MAX, MIN

SORT AGGREGATE

3 (주1)

      (주1) max, min은 정렬없이 수행 가능하다. 알고리즘 복잡도 (n)

      (주2) ORDER BY, GROUP BY가 닽이 있어도 정렬은 1번으로하여 비용은 같다.

  - GROUP BY 를 수행하여 정렬을 하지만 결과는 정렬되지 않는다. 그러나 _gby_onekey_enabledfalse로하면 정렬을 한 결과가 나온다.


? GROUP BY의 카디널리티 계산 방법(예 : group by col1, col2)은 col1의 카디널리티와 col2의 카디널리티를 곱하여 제곱근을 구한다.

 select  col1, col2, count(*)

 from t1

 group by col1, col2;

- 실행계획

 -----------------------------------------------------------

 | Id  | Operation          | Name | Rows  | Bytes | Cost  |

 -----------------------------------------------------------

 |   0 | SELECT STATEMENT   |      |  5000 | 20000 |    19 |

 |   1 |  HASH GROUP BY     |      |  5000 | 20000 |    19 |

 |   2 |   TABLE ACCESS FULL| T1   |  5000 | 20000 |     3 |

 -----------------------------------------------------------

- 비용계산

 Cost=입력블록+출력블록

             =입력로우*로우크기/블록크기+출력로우*로우크기/블록크기

      =5,000*15/8192+5000*15/8192

             =18.3105

 카디널리티 추정을 제곱근으로하면

 Cost=5,000*15/8192+1256*15/8192

             =11.455

  - I/O 코스트는 19를 나타내면서 Total I/O sort cost는 12를 사용한다. 트레이스 파일에 비용값이 기능을 못한다. 


 

인덱스 484 / 위충환

  • 인덱스 생성 비용 : 데이터 정렬이 전체 일량의 대부분
      ☞ 오라클은 데이터 획득 비용만 계산 (colum과 rowid를 얻기 위한 table scan, index fast full scan, index full scan 등 처리)


  • 인덱스 생성 시 사용 메모리 영역
    • workarea_size_policy 수동 설정 or 8i or 9i 사용시  메모리 할당 영역 :  PGA, sort_area_retained_size 설정과 무관
    • orkarea_size_policy 자동 설정 or 10g 이상 시 메모리 할당 영역 : UGA
    • workarea_size_policy 자동 설정 시 인덱스 생성 후 메모리 바로 return


 

집합 연산자 485  / 위충환

  • 집합연산자
    • Union : 합집합
    • Intersect  : 교집합
    • Minus : 차집합

  • ti ⊃ t2 관계의 테이블에서 owner & object_type 컬럼을 집합연산자 처리로 조회

distinct 적용

intersect (차집합, 중복내용 제거가 적용된 집합 연산자 )

 select distinct owner, object_type from t1

 intersect

 select distinct owner, object_type from t2;

 select owner, object_type from t1

 intersect

 select  owner, object_type from t2;

 Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS(Cost=30 Card=15 Bytes=375)
   1    0   INTERSECTION
   2    1     SORT (UNIQUE) (Cost=15 Card=15 Bytes=180)
   3    2       TABLE ACCESS (FULL) OF 'T1' (Cost=6 Card=2500 Bytes=30000)
   4    1     SORT (UNIQUE) (Cost=15 Card=15 Bytes=195)
   5    4       TABLE ACCESS (FULL) OF 'T2' (Cost=6 Card=2000 Bytes=26000)

 Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS(Cost=37Card=2000Bytes=56000)
   1    0   INTERSECTION
   2    1     SORT (UNIQUE) (Cost=19 Card=2500 Bytes=30000)
   3    2       TABLE ACCESS (FULL) OF 'T1' (Cost=6 Card=2500 Bytes=30000)
   4    1     SORT (UNIQUE) (Cost=18 Card=2000 Bytes=26000)
   5    4       TABLE ACCESS (FULL) OF 'T2' (Cost=6 Card=2000 Bytes=26000)

  • 실행처리는 같으나  비용과 카디널리트는 완전 다름.

 ☞ distinct 연산자 사용시 'Group by Cardinality' 계산방식을 사용하여 계산(CPU costing 활성 시 차이 해결)
  • 실제로 distinct 적용 sql처리가 불필요한 작업 추가로 비효율적이나 실행계획에서는 나타나지 않음.


※ 옵티마이저가 알아서 비효율적인 작업을 제외하고 처리했나 10032 이벤트 트레이스를 통하여 보면

    9i는 실제로 불필요한 작업을 하고 겉으로는 나타내지 않지만

   10g 불필요한 작업도 하고 있다는 것을 나타냄.


   ☞ 실행계획은 모든것을 나타내지 않아도 10032 트레이스를 통하여 실제 처리 작업을 알 수 있음.

 


마지막 당부 491 / 위충환


 

요약 493 / 위충환


테스트 스크립트 494 / 위충환