[SQLP필기풀이]6장 고급SQL튜닝(1)-소트튜닝

출처 : SQLP 기출노트 2권 ( p.14 ~ p.)

✍️ 1번 : dbms_xplan.display_cursor 트레이스 정보

소트 수행 과정

1. 정렬 대상 집합을 SGA 버퍼 캐시를 통해 읽어들인다.
2. 일차적으로 PGA의 Sort Area에서 정렬을 시도한다.
   • Optimal Sort : Sort Area 내에서 정렬 작업을 마무리 하는 것
3. 양이 많을 때는 정렬된 중간집합을 Temp 테이블스페이스에 임시 세그먼트를 만들어 저장한다.
   • Sort Runs : Sort Area가 찰 때마다 Temp 영역에 저장해둔 중간 단계의 집합
   • Onepass Sort : 정렬 대상 집합을 디스크에 한 번만 기록하고 작업을 마치는 것
   • Multipass Sort : 정렬 대상 집합을 디스크에 여러번 기록하고 작업을 마치는 것

수행 용어

1. Optimal Sort : Sort Area 내에서 정렬 작업을 마무리 하는 것
2. Sort Runs : Sort Area가 찰 때마다 Temp 영역에 저장해둔 중간 단계의 집합
3. Onepass Sort : 정렬 대상 집합을 디스크에 한 번만 기록하고 작업을 마치는 것
4. Multipass Sort : 정렬 대상 집합을 디스크에 여러번 기록하고 작업을 마치는 것

수행 기타

1. Temp 영역에 임시 저장했다가 다시 읽어 들이는 디스크 소트가 발생하는 순간 성능이 매우 나빠진다.
2. 디스크에 갔다와도 실행계획에는 소트 오퍼레이션이 1번 나타난다.

🍋 기출 포인트

1. ⭐️ 정렬 과정 내에서의 정렬 관련 용어 ⭐️
   • Optimal Sort : Sort Area 내에서 정렬 작업을 마무리 하는 것
   • Sort Runs : Sort Area가 찰 때마다 Temp 영역에 저장해둔 중간 단계의 집합
   • Onepass Sort : 정렬 대상 집합을 디스크에 한 번만 기록하고 작업을 마치는 것
   • Multipass Sort : 정렬 대상 집합을 디스크에 여러번 기록하고 작업을 마치는 것

✍️ 2번 : Oracle AutoTrace 내 소트 통계

Oracle AutoTrace의 모양새

SQL > set autotrace traceonly;

SQL > select * from emp where ename =: ename;

Execution Plan -> 예상 실행계획
 ----------------------------------------------------------------------------- 
Plan hash value: 4024650034

------------------------------------------------------------------------------ 
|ID | Operation                         | Name | Rows | Bytes | Cost (%CPU)  |
------------------------------------------------------------------------------ 
|  0| SELECT STATEMENT                  |      |      |     32|     1     (0)|
|  1|  TABLE ACCESS BY INDEX ROWID      |EMP   |     1|     32|     1     (0)|
|* 2|   INDEX UNIQUE SCAN               |EMP_PK|     1|       |     0     (0)|
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------------------------------------------
  2 - access("EMPNO"=7900)
  
 Statistics
----------------------------------------------------------
 0 recursive calls
 0 db block gets
 0 consistent gets
 0 physical reads -> 실제 디스크에서 읽은 블록 수
 0 redo size -> 실제 기록한 Redo 크기
 519 bytes sent via SQL*Net to client
 523 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed

🍋 기출 포인트

1. Sort 오퍼레이션 개수 : sorts (memory)와 sorts (disk)의 합
2. ⭐️ 오퍼레이션 1개당 최대 1개의 Sort가 나타난다.
   • sorts (memory)가 1개 sorts (disk)가 1개이면 각 오퍼레이션에서 각각 메모리 소트 , 디스크 소트가 일어난 것이다.
3. Group By 또는 중복 제거 연산에 Hash 알고리즘을 사용하면 sorts (memory)와 sorts (disk) 항목 모두 0으로 표시된다.

✍️ 3번 : 디스크 소트 대기 이벤트

디스크 소트 대기 이벤트

• 정렬 대상 데이터를 Temp 테이블스페이스에 쓰고 읽을 때 Direct Path I/O 방식을 사용한다.
• 이 과정에서 I/O Call 대기 이벤트 종류는 다음 2가지이다.
  • direct path write temp
  • direct path read temp

🍋 기출 포인트

1. 디스크 소트 대기 이벤트 종류는 direct path write temp와 direct path read temp가 있다.
2. group by 를 사용한 쿼리에 order by절을 명시하면 정렬 순서가 보장 되지만, 이때도 실행계획은 똑같이 sort ( group by )로 표시된다.
3. DISTINCT 를 사용한 쿼리에 order by절을 명시하면 정렬 순서가 보장 되지만, 이때도 실행계획은 똑같이 sort ( unique )로 표시된다.
   • hash ( unique ) 였다면 sort ( unique )로 바껴 표시된다.

✍️ 4번 : 소트 실행계획

1. 10gR2에서 Hash group by 방식이 도입되면서, order by절을 함께 명시하지 않으면  대부분 hash group 방식으로 처리된다. 

🍋 기출 포인트

1. 실행계획에서 HASH (UNIQUE) 와 SORT ( ORDER BY ) 오퍼레이션이 동시에 나타나지는 않는다.
2. HASH는 UNIQUE 연산과 GROUP BY만 가능하다.(ORDER BY는 ❌)

• HASH (UNIQUE)
• HASH (GROUP BY)

3. 쿼리에 Order by가 없다면 HASH (UNIQUE)로 실행될 것이다.

✍️ 5번 : 소트 영역(pga , temp 테이블스페이스) 사용량 비교

1. Sort (aggregate) : sum,count,avg 등을 구할 때 나타나며 sort area를 가장 적게 사용한다.
2. Sort (order by) : 데이터를 정렬할 때 나타나며 sort area를 가장 많이 사용한다.
3. Sort (group by) : 데이터를 전체 정렬하지는 않으며 읽는 레코드마다 값을 찾아가 count,max,sum 등 연산을 수행하며 결과집합 건수만큼의 sort area를 사용한다.
4. Hash (group by) : 데이터를 전체 정렬하지는 않으며 읽는 레코드마다 값을 찾아가 count,max,sum 등 연산을 수행하는데 Hash 알고리즘을 사용할 뿐이다.
   (해시 영역을 따로 쓴다거나 그런 건 아님 !) 결과집합 건수만큼의 sort area를 사용한다.

🍋 기출 포인트

1. Sort (order by) : 데이터를 정렬할 때 나타나며 sort area를 가장 많이 사용한다.

✍️ 6번 : 소트가 불가능한 실행계획 찾기

🍋 기출 포인트

1. UNION 사용 쿼리 : 중복 제거를 위해 전체 데이터를 읽어갸 하므로 인덱스로 소트 연산을 대체할 수 없다.

✍️ 7번 : UNION을 UNION ALL로 대체해도 결과집합이 변하지 않는 경우

한 테이블에 두개의 쿼리일 때 UNION을 UNION ALL로 대체해도 결과집합이 변하지 않기 위한 조건

1. 조건 1: 두 쿼리의 집합 결과가 중복되지 않아야 한다.
2. 조건 2: ⭐️ 한 쿼리 안에서도 중복된 결과가 나오지 않아야 한다.
3. 경우의 수

• select절
  • <1> select절 경우 1 : 키값을 포함한다.
  • <2> select절 경우 2 : 키값을 포함하지 않는다.
• where절
  • <3> where절 경우 1 : 조건절 컬럼이 같고 조건값은 다른 경우 (조건이 서로 배타적)

         select COL1 , COL2 from A
         	where 결제수단 코드 ='W' AND 결제일자 = '20210424'
         UNION
         select COL1 , COL2 from A
         	where 결제수단 코드 ='R' AND 결제일자 = '20210414';

  • <4> where절 경우 2 : 조건절 컬럼이 다른 경우 (조건이 서로 완전 배타적인 건 아니다.)

        select COL1 , COL2 from A
            where 결제일자 = '20210424'
        UNION
        select COL1 , COL2 from A
            where 주문일자 = '20210424';

🍋 기출 포인트

1. select 절의 경우와 where절의 경우를 잘 조합하여 판단해야 한다.
2. <1> 과 <3>의 조합은 union all로 바꿔도 결과집합이 변하지 않는다.
3. <2> 와 <3>의 조합은 한 쿼리 안에서 중복이 일어날 수 있다.

✍️ 9,10번 : I/O 가장 적게 발생하는 인덱스 구성

I/O 가장 적게 발생하는 인덱스 구성

🍋 기출 포인트

1. 9번 : UNION , UNION ALL 연산은 부분범위 처리가 되지 않는다.
2. 10번 : 무조건 부분범위 처리가 I/O량이 가장 적게 발생한다고 생각하면 안된다.
   • 부분 범위 처리를 하는것 보다 선택도가 가장 작은 컬럼의 인덱스를 액세스하는 게 I/O량이 더 작다.