출처 : SQLP 기출노트 2권 ( p.14 ~ p.)
✍️ 1번 : dbms_xplan.display_cursor 트레이스 정보
소트 수행 과정
- 정렬 대상 집합을 SGA 버퍼 캐시를 통해 읽어들인다.
- 일차적으로 PGA의 Sort Area에서 정렬을 시도한다.
- Optimal Sort : Sort Area 내에서 정렬 작업을 마무리 하는 것
- 양이 많을 때는 정렬된 중간집합을 Temp 테이블스페이스에 임시 세그먼트를 만들어 저장한다.
- Sort Runs : Sort Area가 찰 때마다 Temp 영역에 저장해둔 중간 단계의 집합
- Onepass Sort : 정렬 대상 집합을 디스크에 한 번만 기록하고 작업을 마치는 것
- Multipass Sort : 정렬 대상 집합을 디스크에 여러번 기록하고 작업을 마치는 것
수행 용어
- Optimal Sort : Sort Area 내에서 정렬 작업을 마무리 하는 것
- Sort Runs : Sort Area가 찰 때마다 Temp 영역에 저장해둔 중간 단계의 집합
- Onepass Sort : 정렬 대상 집합을 디스크에 한 번만 기록하고 작업을 마치는 것
- Multipass Sort : 정렬 대상 집합을 디스크에 여러번 기록하고 작업을 마치는 것
수행 기타
- Temp 영역에 임시 저장했다가 다시 읽어 들이는 디스크 소트가 발생하는 순간 성능이 매우 나빠진다.
- 디스크에 갔다와도 실행계획에는 소트 오퍼레이션이 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
🍋 기출 포인트
- Sort 오퍼레이션 개수 : sorts (memory)와 sorts (disk)의 합
- ⭐️ 오퍼레이션 1개당 최대 1개의 Sort가 나타난다.
- sorts (memory)가 1개 sorts (disk)가 1개이면 각 오퍼레이션에서 각각 메모리 소트 , 디스크 소트가 일어난 것이다.
- 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
🍋 기출 포인트
- 디스크 소트 대기 이벤트 종류는
direct path write temp
와 direct path read temp
가 있다.
- group by 를 사용한 쿼리에 order by절을 명시하면 정렬 순서가 보장 되지만, 이때도 실행계획은 똑같이
sort ( group by )
로 표시된다.
- DISTINCT 를 사용한 쿼리에 order by절을 명시하면 정렬 순서가 보장 되지만, 이때도 실행계획은 똑같이
sort ( unique )
로 표시된다.
hash ( unique )
였다면 sort ( unique )
로 바껴 표시된다.
✍️ 4번 : 소트 실행계획
- 10gR2에서 Hash group by 방식이 도입되면서, order by절을 함께 명시하지 않으면 대부분 hash group 방식으로 처리된다.
🍋 기출 포인트
- 실행계획에서
HASH (UNIQUE)
와 SORT ( ORDER BY )
오퍼레이션이 동시에 나타나지는 않는다.
- HASH는 UNIQUE 연산과 GROUP BY만 가능하다.(ORDER BY는 ❌)
HASH (UNIQUE)
HASH (GROUP BY)
- 쿼리에 Order by가 없다면
HASH (UNIQUE)
로 실행될 것이다.
✍️ 5번 : 소트 영역(pga , temp 테이블스페이스) 사용량 비교
- Sort (aggregate) : sum,count,avg 등을 구할 때 나타나며 sort area를 가장 적게 사용한다.
- Sort (order by) : 데이터를 정렬할 때 나타나며 sort area를 가장 많이 사용한다.
- Sort (group by) : 데이터를 전체 정렬하지는 않으며 읽는 레코드마다 값을 찾아가 count,max,sum 등 연산을 수행하며 결과집합 건수만큼의 sort area를 사용한다.
- Hash (group by) : 데이터를 전체 정렬하지는 않으며 읽는 레코드마다 값을 찾아가 count,max,sum 등 연산을 수행하는데 Hash 알고리즘을 사용할 뿐이다.
(해시 영역을 따로 쓴다거나 그런 건 아님 !) 결과집합 건수만큼의 sort area를 사용한다.
🍋 기출 포인트
- Sort (order by) : 데이터를 정렬할 때 나타나며 sort area를 가장 많이 사용한다.
✍️ 6번 : 소트가 불가능한 실행계획 찾기
🍋 기출 포인트
- UNION 사용 쿼리 : 중복 제거를 위해 전체 데이터를 읽어갸 하므로 인덱스로 소트 연산을 대체할 수 없다.
✍️ 7번 : UNION을 UNION ALL로 대체해도 결과집합이 변하지 않는 경우
한 테이블에 두개의 쿼리일 때 UNION을 UNION ALL로 대체해도 결과집합이 변하지 않기 위한 조건
- 조건 1: 두 쿼리의 집합 결과가 중복되지 않아야 한다.
- 조건 2: ⭐️ 한 쿼리 안에서도 중복된 결과가 나오지 않아야 한다.
- 경우의 수
- 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';
🍋 기출 포인트
- select 절의 경우와 where절의 경우를 잘 조합하여 판단해야 한다.
- <1> 과 <3>의 조합은 union all로 바꿔도 결과집합이 변하지 않는다.
- <2> 와 <3>의 조합은 한 쿼리 안에서 중복이 일어날 수 있다.
✍️ 9,10번 : I/O 가장 적게 발생하는 인덱스 구성
I/O 가장 적게 발생하는 인덱스 구성
🍋 기출 포인트
- 9번 : UNION , UNION ALL 연산은 부분범위 처리가 되지 않는다.
- 10번 : 무조건 부분범위 처리가 I/O량이 가장 적게 발생한다고 생각하면 안된다.
- 부분 범위 처리를 하는것 보다 선택도가 가장 작은 컬럼의 인덱스를 액세스하는 게 I/O량이 더 작다.