1. HOME
  2. [종료]구루비 DB 스터디
  3. 2012년 하반기 - 오라클 성능 트러블슈팅의 기초 스터디
  4. 파티셔닝과 성능, Revisited

파티셔닝과 성능, Revisited (by dolphhong) [2012.12.07]

파티셔닝 성능, Revisited

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


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 비교
{code:sql}

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가 높은 이유 : 인덱스 파티셔닝

||
{code:sql}
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)
  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2012년 하반기 - 오라클 성능 트러블슈팅의 기초 스터디
  4. 파티셔닝과 성능, Revisited