파티셔닝 성능, Revisited
파티셔닝으로 인한 쿼리성능
- 쿼리를 더 빠르게 한다
- 쿼리 수행 성능에 전혀 영향을 주지 않는다
- 쿼리를 더 느리게 하고, 파티션되지 않은 수행처럼 자원을 많이 사용한다
- 데이터 웨어하우스 : 쿼리성능 향상을 위해 파티셔닝 사용 (가용성,관리적 측면도 만족)
- OLTP : 가용성, 관리적 측면, 높은 동시성 환경 제공을 위하여 파티셔닝을 사용하고, 성능 측면에서는 b 을 확실히 달성하기 위해 노력해야 한다.
-- object_id를 파티션 키로 사용을 하여 16개 해시 파티션으로 구성되는 테이블 T 생성
SQL> create table t
( OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
TEMPORARY, GENERATED, SECONDARY )
partition by hash(object_id)
partitions 16
as
select OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
TEMPORARY, GENERATED, SECONDARY
FROM ALL_OBJECTS ;
Table created.
-- 로컬 인덱스 생성
SQL> create index t_idx on t (owner, object_type, object_name) LOCAL ;
Index created.
SQL> begin dbms_stats.gather_table_stats (user, 'T'); end;
/
PL/SQL procedure successfully completed.
- 자주 실행시키는 OLTP 쿼리 실행 ( SQL_TRACE=TRUE 후 TKPROF 로 확인 )
SQL> ALTER SESSION SET SQL_TRACE = TRUE;
Session altered.
variable o varchar2(30)
variable t varchar2(30)
variable n varchar2(30)
exec :o := 'SCOTT'; :t := 'TABLE'; :n := 'EMP';
select *
from t
where owner = :o
and object_type = :t
and object_name = :n
/
select *
from t
where owner = :o
and object_type = :t
/
select *
from t
where owner = :o
/
select *
from t
where owner = :o
and object_type = :t
and object_name = :n
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.02 0 0 0 0
Fetch 2 0.00 0.00 0 34 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.02 0 34 0 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 PARTITION HASH ALL PARTITION: 1 16 (cr=34 pr=0 pw=0 time=186 us cost=18 size=198 card=2)
1 1 1 TABLE ACCESS BY LOCAL INDEX ROWID T PARTITION: 1 16 (cr=34 pr=0 pw=0 time=194 us cost=18 size=198 card=2)
1 1 1 INDEX RANGE SCAN T_IDX PARTITION: 1 16 (cr=33 pr=0 pw=0 time=151 us cost=17 size=0 card=2)(object id 51556)
일반 테이블과 비교하기 위해 일반테이블 생성
SQL> create table t
( OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
TEMPORARY, GENERATED, SECONDARY )
as
select OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
TEMPORARY, GENERATED, SECONDARY
FROM ALL_OBJECTS
SQL> create index t_idx on t (owner, object_type, object_name) ;
select *
from t
where owner = :o
and object_type = :t
and object_name = :n
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 2 0.00 0.00 2 5 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 2 5 0 1
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 126
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID T (cr=5 pr=2 pw=0 time=177 us)
1 1 1 INDEX RANGE SCAN T_IDX (cr=4 pr=2 pw=0 time=169 us)(object id 51
파티션이 있는경우, 없는경우 query 항목을 확인
select *
from t
where owner = :o
and object_type = :t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 37 0 5 // Partition table
total 4 0.00 0.00 0 6 0 5 // Heap table
select *
from t
where owner = :o
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 605 0 11 // Partition table
total 4 0.00 0.00 0 8 0 11 // Heap table
- Partition table 이 I/O가 높은 이유 : 인덱스 파티셔닝
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
5 5 5 PARTITION HASH ALL PARTITION: 1 16 (cr=37 pr=0 pw=0 time=30 us cost=23 size=1386 card=14)
5 5 5 TABLE ACCESS BY LOCAL INDEX ROWID T PARTITION: 1 16 (cr=37 pr=0 pw=0 time=105 us cost=23 size=1386 card=14)
5 5 5 INDEX RANGE SCAN T_IDX PARTITION: 1 16 (cr=33 pr=0 pw=0 time=68 us cost=17 size=0 card=14)(object id 51556)
- SCOTT에 대한 엔트리가 모든 인덱스 파티션에 존재하기 때문에, 인덱스 파티션 전체를 처리함.
- 글로벌 인덱스 파티션으로 생성.
SQL> create index t_idx on t(owner, object_type, object_name)
global partition by hash(owner) partitions 16 ;
Index created.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 1 20 0 1
total 4 0.00 0.00 0 23 0 5
total 4 0.00 0.00 0 29 0 11
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 PARTITION HASH SINGLE PARTITION: KEY KEY (cr=4 pr=1 pw=0 time=85 us cost=9 size=128 card=1)
1 1 1 TABLE ACCESS BY GLOBAL INDEX ROWID T PARTITION: ROW LOCATION ROW LOCATION (cr=4 pr=1 pw=0 time=78 us cost=9 size=128 card=1)
1 1 1 INDEX RANGE SCAN T_IDX PARTITION: KEY KEY (cr=3 pr=1 pw=0 time=75 us cost=1 size=0 card=455)(object id 51609)
ORDER BY 절의 사용
- 해시 파티션 인덱스는 정렬을 보장하지 않는다
- 정렬이 필요할 경우 ORDER BY 절을 명시해야 한다
SQL> create table t as select * from all_users ;
Table created.
SQL> create index t_idx on t(user_id) global partition by hash(user_id) partitions 4 ;
Index created.
SQL> set autot on explain
SQL> select /*+ index(t t_idx) */ user_id from t where user_id > 0 ;
USER_ID
----------
30
34
40
46
50
.......
96
101
106
114
120
121
97 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3357014883
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 97 | 1261 | 5 (0)| 00:00:01 | | |
| 1 | PARTITION HASH ALL| | 97 | 1261 | 5 (0)| 00:00:01 | 1 | 4 |
|* 2 | INDEX RANGE SCAN | T_IDX | 97 | 1261 | 5 (0)| 00:00:01 | 1 | 4 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("USER_ID">0)