파티셔닝 성능, Revisited

파티셔닝으로 인한 쿼리성능

1. 쿼리를 더 빠르게 한다
2. 쿼리 수행 성능에 전혀 영향을 주지 않는다
3. 쿼리를 더 느리게 하고, 파티션되지 않은 수행처럼 자원을 많이 사용한다

• 데이터 웨어하우스 : 쿼리성능 향상을 위해 파티셔닝 사용 (가용성,관리적 측면도 만족)
• 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 항목을 확인

• 파티션 유/무에 따른 I/O 비교

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)