Optimizing Oracle Optimizer (2011년)
목록
Partion Key 0 0 2,449

by 구루비스터디 Cardinility [2018.07.14]

Partition Key

  • Partition Table에 대한 Cardinality 예측 또한 예상치 못한 결과를 보임
  • Partition Elimination이 가능한 경우와 불가능한 경우 많은 차이를 보이며, 가능하더라도 단일 Partition을 Access 하는 경우와 다중 Partiton을 Access 하는 경우에 다른 Patten의 Cardanality 계산이 이루어짐


Test


-- create partition table T1
DROP TABLE T1 PURGE;

CREATE TABLE T1(C1 INT,
                C2 INT)
PARTITION BY LIST(C1)
(PARTITION P1 VALUES (1),
 PARTITION P2 VALUES (2),
 PARTITION P3 VALUES (3),
 PARTITION P4 VALUES (4),
 PARTITION P5 VALUES (5)
);
               
-- create data
INSERT INTO T1
SELECT 1, LEVEL FROM DUAL CONNECT BY LEVEL <= 10000 UNION ALL
SELECT 2, LEVEL FROM DUAL CONNECT BY LEVEL <= 1000  UNION ALL
SELECT 3, LEVEL FROM DUAL CONNECT BY LEVEL <= 100   UNION ALL
SELECT 4, LEVEL FROM DUAL CONNECT BY LEVEL <= 10    UNION ALL
SELECT 5, LEVEL FROM DUAL CONNECT BY LEVEL <= 1 
;

COMMIT;

-- gather stats "without" histogram
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1', METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', NO_INVALIDATE => FALSE);

-- create table T4
DROP TABLE T4 PURGE;

CREATE TABLE T4(C1 INT,
                C2 INT)
;                

-- create data
INSERT INTO T4
SELECT 1, LEVEL FROM DUAL CONNECT BY LEVEL <= 10000 UNION ALL
SELECT 2, LEVEL FROM DUAL CONNECT BY LEVEL <= 1000  UNION ALL
SELECT 3, LEVEL FROM DUAL CONNECT BY LEVEL <= 100   UNION ALL
SELECT 4, LEVEL FROM DUAL CONNECT BY LEVEL <= 10    UNION ALL
SELECT 5, LEVEL FROM DUAL CONNECT BY LEVEL <= 1 
;

COMMIT;

-- gather stats "without" histogram
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T4', METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', NO_INVALIDATE => FALSE);

-- disable bind peeking
ALTER SYSTEM SET "_OPTIM_PEEK_USER_BINDS" = FALSE;


  • 통계 정보를 확인한다.

-- Table
SELECT TABLE_NAME,
       NUM_ROWS,
       BLOCKS,
       SAMPLE_SIZE,
       TO_CHAR(LAST_ANALYZED, 'YYYY/MM/DD HH24:MI:SS') AS LAST_ANAL
FROM   USER_TAB_STATISTICS
WHERE  TABLE_NAME IN ('T1', 'T4')
;

TABLE_NAME	NUM_ROWS	BLOCKS	SAMPLE_SIZE	LAST_ANAL

T1	11111	210	11111	2011/05/13 12:06:36
T4	11111	42	11111	2011/05/13 12:06:51
T1	10000	42	10000	2011/05/13 12:06:36
T1	1000	42	1000	2011/05/13 12:06:36
T1	100	42	100	2011/05/13 12:06:36
T1	10	42	10	2011/05/13 12:06:36
T1	1	42	1	2011/05/13 12:06:36






-- Column
SELECT S.TABLE_NAME,
       S.COLUMN_NAME,
       S.NUM_DISTINCT,
       S.NUM_NULLS,
       S.DENSITY,
       S.LOW_VALUE,
       S.HIGH_VALUE,
       S.HISTOGRAM
FROM   USER_TAB_COLS S
WHERE  S.TABLE_NAME IN ('T1', 'T4')
;

TABLE_NAME	COLUMN_NAME	NUM_DISTINCT	NUM_NULLS	DENSITY	LOW_VALUE	HIGH_VALUE	HISTOGRAM

T4	C2	10000	0	0.0001	C102	C302	NONE
T4	C1	5	0	0.2	C102	C106	NONE
T1	C2	10000	0	0.0001	C102	C302	NONE
T1	C1	5	0	0.2	C102	C106	NONE





-- Histogram
SELECT TABLE_NAME,
       COLUMN_NAME,
       ENDPOINT_NUMBER,
       ENDPOINT_VALUE || '(' || ENDPOINT_ACTUAL_VALUE || ')' AS ENDPOINT_VALUE
FROM   USER_TAB_HISTOGRAMS
WHERE  TABLE_NAME IN ('T1', 'T4')
ORDER  BY COLUMN_NAME,
          ENDPOINT_NUMBER
;

TABLE_NAME	COLUMN_NAME	ENDPOINT_NUMBER	ENDPOINT_VALUE

T1	C1	0	1()
T4	C1	0	1()
T1	C1	1	5()
T4	C1	1	5()
T1	C2	0	1()
T4	C2	0	1()
T4	C2	1	10000()
T1	C2	1	10000()
  • = Base Cardinality * Selectivity(C1) = 11111 * 0.2 = 2222



VAR B1 NUMBER;
VAR B2 NUMBER;

EXEC :B1 := 1;
EXEC :B2 := 3;

SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*)
FROM   T1
WHERE  C1 = :B1
;

select * from table(dbms_xplan.display_cursor(null, null, 'iostats cost last'));

PLAN_TABLE_OUTPUT

Plan hash value: 2215101622
 
------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE        |      |      1 |      1 |            |      1 |00:00:00.01 |      44 |
|   2 |   PARTITION LIST SINGLE|      |      1 |   2222 |    11   (0)|  10000 |00:00:00.01 |      44 |
|   3 |    TABLE ACCESS FULL   | T1   |      1 |   2222 |    11   (0)|  10000 |00:00:00.01 |      44 |
------------------------------------------------------------------------------------------------------





SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*)
FROM   T4
WHERE  C1 = :B1
;

select * from table(dbms_xplan.display_cursor(null, null, 'iostats cost last'));

PLAN_TABLE_OUTPUT

Plan hash value: 4162970584
 
--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |            |      1 |00:00:00.01 |      44 |
|*  2 |   TABLE ACCESS FULL| T4   |      1 |   2222 |    11   (0)|  10000 |00:00:00.01 |      44 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("C1"=TO_NUMBER(:B1))
  • Patition Elimination 전체 Row 10000건 Density = 1이므로 Cardinality = 10000 * 1 = 10000건이 된다. 



SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*)
FROM   T1
WHERE  C1 = 1
;

select * from table(dbms_xplan.display_cursor(null, null, 'iostats cost last'));
PLAN_TABLE_OUTPUT

Plan hash value: 3751617648
 
------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE        |      |      1 |      1 |            |      1 |00:00:00.01 |      44 |
|   2 |   PARTITION LIST SINGLE|      |      1 |  10000 |    11   (0)|  10000 |00:00:00.01 |      44 |
|   3 |    TABLE ACCESS FULL   | T1   |      1 |  10000 |    11   (0)|  10000 |00:00:00.01 |      44 |
------------------------------------------------------------------------------------------------------


  • Non Partition Table이므로 0.2의 Density를 사용한다. 

SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*)
FROM   T4
WHERE  C1 = 1
;

select * from table(dbms_xplan.display_cursor(null, null, 'iostats cost last'));

PLAN_TABLE_OUTPUT

Plan hash value: 4162970584
 
--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |            |      1 |00:00:00.01 |      44 |
|*  2 |   TABLE ACCESS FULL| T4   |      1 |   2222 |    11   (0)|  10000 |00:00:00.01 |      44 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("C1"=1)
  • 다중 Partition에 대한 Access가 이루어지는 경우는 Partition Statistics가 아닌 Global Statistics가 사용된다.

= Cardinality(C1 BETWEEN 1 AND 3)
= Cardinality(C1 = 1) + Cardinality(C1 = 3) + Cardinality(1 < C1 < 3)
= 0 + (11111 * 0.2) + ((3 - 1) * (5 - 1) * 11111) 
= 7777.7 = 7778





SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*)
FROM   T1
WHERE  C1 BETWEEN 1
          AND     3
;

select * from table(dbms_xplan.display_cursor(null, null, 'iostats cost last'));

PLAN_TABLE_OUTPUT

Plan hash value: 2624924945
 
--------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE          |      |      1 |      1 |            |      1 |00:00:00.01 |      86 |
|   2 |   PARTITION LIST ITERATOR|      |      1 |   7778 |    29   (0)|  11100 |00:00:00.01 |      86 |
|   3 |    TABLE ACCESS FULL     | T1   |      3 |   7778 |    29   (0)|  11100 |00:00:00.01 |      86 |
--------------------------------------------------------------------------------------------------------





SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*)
FROM   T4
WHERE  C1 BETWEEN 1
          AND     3
;

select * from table(dbms_xplan.display_cursor(null, null, 'iostats cost last'));

PLAN_TABLE_OUTPUT

Plan hash value: 4162970584
 
--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |            |      1 |00:00:00.01 |      44 |
|*  2 |   TABLE ACCESS FULL| T4   |      1 |   7778 |    11   (0)|  11100 |00:00:00.01 |      44 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("C1"<=3 AND "C1">=1))


  • Distinct Count가 20보다 큰 경우 : Density가 0.05보다 작은 경우에는 5%의 Rule을 사용한다.
  • Distinct Count가 20보다 작은 경우 : Density가 0.05보다 큰 경우에는 Density를 사용한다.
  • Cardinality = 11111 * 0.2 * 0.2 = 444.4 = 445



SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*)
FROM   T1
WHERE  C1 BETWEEN :B1
          AND     :B2
;

select * from table(dbms_xplan.display_cursor(null, null, 'iostats cost last'));

PLAN_TABLE_OUTPUT

Plan hash value: 3650607437
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE           |      |      1 |      1 |            |      1 |00:00:00.01 |      86 |
|*  2 |   FILTER                  |      |      1 |        |            |  11100 |00:00:00.01 |      86 |
|   3 |    PARTITION LIST ITERATOR|      |      1 |    445 |    48   (0)|  11100 |00:00:00.01 |      86 |
|   4 |     TABLE ACCESS FULL     | T1   |      3 |    445 |    48   (0)|  11100 |00:00:00.01 |      86 |
---------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(TO_NUMBER(:B1)<=TO_NUMBER(:B2))
"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3948

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입