오라클 성능 고도화 원리와 해법 II (2016년)
h1.02 파티션 Pruning
*파티션 Pruning(=Elimination)은 하드파싱이나 실행 시점에 SQL 조건절을 분석하여 읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외시커는 기능
h5.정적 (Static) 파티션 Pruning
파티션 키 컬럼을 상수 조건으로 조회하는 경우에 작동하며, 액세스할 파티션이 쿼리 최적화 시점에 미리 결정되는 것이 특정이다. 실행계획의Pstart(partition start)와 Pstop(partition stop) 컬럼에는 액세스할 파티션 변호가 출력된다.
h5.동적 (Dynamic) 파티션 Pruning
파티션 카 컬럼을 바인드 변수로 조회하면 쿼리 최적화 시점에는 액세스할파티션을 미리 결정할수 없다. 실행 시점이 돼서야사용자가 입력한값에 따라 결정되며， 실행계획의 Pstart와 Pstop 컬럼에는 'KEY' 라고 표시된다. NL 조인할 때도 Inner 테이블이 조인 컬럼 기준으로 파티셔닝 돼 있다면 동적 Pruning이 작동한다
h5.파티션 Pruning 기능에 따른 실행계획 비교
create table t ( key, no, data )
partition by range(no) (
partition p01 values less than(11)
,partition p02 values less than(21)
,partition p03 values less than(31)
,partition p04 values less than(41)
,partition p05 values less than(51)
,partition p06 values less than(61)
,partition p07 values less than(71)
,partition p08 values less than(81)
,partition p09 values less than(91)
,partition p10 values less than(maxvalue)
)
as
select lpad(rownum, 6,'O'), mod(rownum,100 ) +1, lpad(rownum , 10, '0')
from dual
connect by level <= 999999
-----------------------------------------------------------------------
select count(*) from t where no between 30 and 50
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 313 (2)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 222K| 650K| 313 (2)| 00:00:01 | 3 | 5 |
|* 3 | TABLE ACCESS FULL | T | 222K| 650K| 313 (2)| 00:00:01 | 3 | 5 |
--------------------------------------------------------------------------------------------------
-- 상수 조건을 사용해 정적Pruning을 태스트한 것으로서，10개 중 3개(3~5번) 파티션만 읽는 것을 알 수 있다.
-----------------------------------------------------------------------
select count(*) from t where no between :a and :b
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1043 (2)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | PARTITION RANGE ITERATOR| | 10001 | 30003 | 1043 (2)| 00:00:01 | KEY | KEY |
|* 4 | TABLE ACCESS FULL | T | 10001 | 30003 | 1043 (2)| 00:00:01 | KEY | KEY |
---------------------------------------------------------------------------------------------------
-- 시작과 종료 파티션 변호 대신 'KEY 라고 표시
-- 하드파싱 시점에 액세스할 파티션을 결정할 수 없기 때문
------------------------------------------------------------------------
select count(*) from t where no in (30, 50)
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 210 (2)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | | |
| 2 | PARTITION RANGE INLIST| | 20000 | 60000 | 210 (2)| 00:00:01 |KEY(I) |KEY(I) |
|* 3 | TABLE ACCESS FULL | T | 20000 | 60000 | 210 (2)| 00:00:01 |KEY(I) |KEY(I) |
------------------------------------------------------------------------------------------------
-- 파티션 컬럼에 IN-List 조건을 사용하면 상수 값이더라도 아래처럼 'KEy (I)' 라고 표시된
h5.NL 조인을 테스트
create table n
as
select level no from dual connect by level <= 100;
select /*+ leading (n) use_nl(t) */ *
from n, t
where t.no = n.no;
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999K| 22M| 10370 (2)| 00:00:01 | | |
| 1 | NESTED LOOPS | | 999K| 22M| 10370 (2)| 00:00:01 | | |
| 2 | TABLE ACCESS FULL | N | 100 | 300 | 3 (0)| 00:00:01 | | |
| 3 | PARTITION RANGE ITERATOR| | 10000 | 205K| 104 (2)| 00:00:01 | KEY | KEY |
|* 4 | TABLE ACCESS FULL | T | 10000 | 205K| 104 (2)| 00:00:01 | KEY | KEY |
--------------------------------------------------------------------------------------------------
-- NL 조인에서도 n 테이블로부터 얽히는 값에 따라 t 테이블에 동적 Pruning이 일어난다.
h5.결합 파티션 Test
drop table t ;
create table t ( key, no, data )
partition by range(no) subpartition by hash(key) subpartitions 16
(
partition p01 values less than(11)
, partition p02 values less than(21)
, partition p03 values less than(31)
, partition p04 values less than(41)
, partition p05 values less than(51)
, partition p06 values less than(61)
, partition p07 values less than(71)
, partition p08 values less than(81)
, partition p09 values less than(91)
, partition p10 values less than(maxvalue)
)
as
select lpad(rownum, 6, '0'), mod(rownum, 50) + 1 , lpad(rownum, 10, '0')
from dual
connect by level <= 999999;
select count(*) from t between 30 and 50 ;
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 834 (1)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 428K| 1254K| 834 (1)| 00:00:01 | 3 | 5 |
| 3 | PARTITION HASH ALL | | 428K| 1254K| 834 (1)| 00:00:01 | 1 | 16 |
|* 4 | TABLE ACCESS FULL | T | 428K| 1254K| 834 (1)| 00:00:01 | 33 | 80 |
--------------------------------------------------------------------------------------------------
-- id 2번 라인은 Range 파티션에 대한 Pruning 정보
-- id 3번 라인은 해시 파티션에 대한 Pruning 정보
-- Range 파티션에선 10개 중 3개 (3~5번)를 읽었지만 각각 서브파티션을 16개 씩 읽어 총48(=3 x 16)개
------------------------------------------------------------------------
select count(*) from t where no between :a and :b ;
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1203 (2)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | PARTITION RANGE ITERATOR| | 20001 | 60003 | 1203 (2)| 00:00:01 | KEY | KEY |
| 4 | PARTITION HASH ALL | | 20001 | 60003 | 1203 (2)| 00:00:01 | 1 | 16 |
|* 5 | TABLE ACCESS FULL | T | 20001 | 60003 | 1203 (2)| 00:00:01 | KEY | KEY |
---------------------------------------------------------------------------------------------------
-- Range 파티션에는 파티션 목록을 확정할 수 없어 'KEY' 라고 표시
-- 서브 파티션에선 16개 읽음
h5.파티션 Pruning 기능에 따른 I/0 수행량 비교
select * from t where no = 1 and key = '000100'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 47 52 2 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.03 0.01 47 52 2 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 107
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=52 pr=47 pw=0 time=2692 us starts=1 cost=140 size=21 card=1)
1 1 1 PARTITION HASH SINGLE PARTITION: 6 6 (cr=52 pr=47 pw=0 time=2681 us starts=1 cost=140 size=21 card=1)
1 1 1 TABLE ACCESS FULL T PARTITION: 6 6 (cr=52 pr=47 pw=0 time=2676 us starts=1 cost=140 size=21 card=1)
-- 형변환
select * from t where no = 1 and to_number(key) = 100 ;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.03 0.03 697 824 32 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.03 0.03 697 824 32 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 107
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=824 pr=697 pw=0 time=36058 us starts=1 cost=140 size=21 card=1)
1 1 1 PARTITION HASH ALL PARTITION: 1 16 (cr=824 pr=697 pw=0 time=36053 us starts=1 cost=140 size=21 card=1)
1 1 1 TABLE ACCESS FULL T PARTITION: 1 16 (cr=824 pr=697 pw=0 time=35940 us starts=16 cost=140 size=21 card=1)
-- 묵시적 형변환
select * from t where no = 1 and key = 100
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 824 32 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 824 32 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 107
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=824 pr=0 pw=0 time=7835 us starts=1 cost=140 size=21 card=1)
1 1 1 PARTITION HASH ALL PARTITION: 1 16 (cr=824 pr=0 pw=0 time=7831 us starts=1 cost=140 size=21 card=1)
1 1 1 TABLE ACCESS FULL T PARTITION: 1 16 (cr=824 pr=0 pw=0 time=7800 us starts=16 cost=140 size=21 card=1)
-- 서브 파티션 키 컬럼에 묵시적 형변환
select * from t where to_char(no) = '1' and key = 100
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.15 0.24 2970 4114 160 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.15 0.24 2970 4114 160 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 107
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 PARTITION RANGE ALL PARTITION: 1 10 (cr=4114 pr=2970 pw=0 time=244541 us starts=1 cost=1393 size=21 card=1)
1 1 1 PARTITION HASH ALL PARTITION: 1 16 (cr=4114 pr=2970 pw=0 time=244534 us starts=10 cost=1393 size=21 card=1)
1 1 1 TABLE ACCESS FULL T PARTITION: 1 160 (cr=4114 pr=2970 pw=0 time=243825 us starts=160 cost=1393 size=21 card=1)
h5.동적 파티션 Pruning 시 테이블 레벨 통계 사용
- 바인드 변수를 사용하면 최적화 시점에 파티션을 확정할 수 없어 동적 파티션 Pruning이 일어난다.
- 같은 이유로 쿼리 최적화에 테이블 레벨 통계가 시용된다.
- 정적 파티션 Pruning 일 때는 파티션 레벨 통계가 사용된다.
- 태이블 레벨 통계는 파티션 레벨 통계보다 다소 부정확하다.
h3.(2) 서브쿼리 Pruning
select d.분기， o.주문일자， o.고객ID， o.상품ID， o.주문수량， o.주문긍액
from 일자 d, 주문 o
where o. 주문일자 = d. 일자
and d. 분기 >= 'Q20071'
- NL 조인할 때 Inner 테이블이 조인 컬럼 기준으로 파티셔닝 돼 있다변 동적 Pruning이 작동한다
- 주문테이블은 주문일자 기준으로 월별 Range 파티셔닝되어있다 가정시 NL 조인은 성능을 기대하기 힘들다.
- 오라클은 Recursive 서브쿼리를 이용한 동적 파티션 Pruning을 고려한다.
select distinct TBL$OR$IDX$PART$NUM (주문, 0, 1, 0, a.일자)
from (select 일자 frOM 일자 where 분기 >= 'Q20071' ) a
order by 1
*실행계획
h3.(3)조인 필터 Pruning
- 서브쿼리 Pruning은 드라이빙 테이블을 한 번 더 액세스핸 추가 비용이 발생
- 11g부터 오라클은 블룸 필터 (Bloom Filler) 알고리즘을 기반으로 한 조인 펼터 (Join Filler, Bloom Filler) Pruning 도입
h5.조인 필터(=블룸 필터) Pruning
- 파티션 Pruning - 파티션 테이블과 조인할 때 읽지 않아도 되는 파티션을 제거해 주는 것
h3.(4) SQL 조건절 작성시 주의사항
- 파티션키에 조인시 LIKE 조건을 걸면 불필요한 파티션까지 읽게된다. ( between 권장)
"구루비 주주클럽 스터디모임" 에서 2016년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3379
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.