Oracle/Tuning2019. 12. 27. 09:32

계층 코드와 예상 카디널리티

그룹, 본부, 부서로 구성된 부서 테이블은 계층 코드를 가지기 마련이다. 부서 테이블의 계층 코드를 사원 테이블에 상속하고, 조건으로 사용하는 경우 예상 카디널리티가 1로 계산되어 쿼리의 성능이 저하될 수 있다.


테스트를 위해 아래와 같이 테이블을 생성하자. 사월 테이블의 부서는 부서 테이블의 상속 속성이며, 그룹과 본부는 3정규형을 위배한 반정규화 속성이다. 매출 테이블은 조건 조건에 해당하는 인덱스가 없는 것으로 가정하자.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 1
DROP TABLE 사원 PURGE;
DROP TABLE 매출 PURGE;
 
CREATE TABLE 사원 AS
SELECT ROWNUM AS 사번
     , a.c1 AS 그룹
     , a.c1 || b.c1 AS 본부
     , a.c1 || b.c1 || c.c1 AS 부서
  FROM (SELECT LPAD (ROWNUM, 2, '0') AS c1 FROM XMLTABLE ('1 to  10')) a
     , (SELECT LPAD (ROWNUM, 2, '0') AS c1 FROM XMLTABLE ('1 to  10')) b
     , (SELECT LPAD (ROWNUM, 2, '0') AS c1 FROM XMLTABLE ('1 to  10')) c
     , (SELECT LPAD (ROWNUM, 2, '0') AS c1 FROM XMLTABLE ('1 to 100')) d;
 
CREATE TABLE 매출 AS
SELECT ROWNUM AS 사번, DATE '2000-01-01' + ROWNUM - 1 AS 날짜, 1000 AS 금액
  FROM XMLTABLE ('1 to 100000');
 
ALTER TABLE 사원 ADD CONSTRAINTS 사원_PK PRIMARY KEY (사번);
-- ALTER TABLE 매출 ADD CONSTRAINTS 매출_PK PRIMARY KEY (사번, 날짜);
 
CREATE INDEX 사원_x1 ON 사원 (그룹);
CREATE INDEX 사원_x2 ON 사원 (본부);
CREATE INDEX 사원_x3 ON 사원 (부서);
CREATE INDEX 매출_x1 ON 매출 (날짜);
 
EXEC DBMS_STATS.GATHER_TABLE_STATS ('TUNA', '사원');
EXEC DBMS_STATS.GATHER_TABLE_STATS ('TUNA', '매출');

아래는 사원 테이블을 조회한 결과다. 본부, 부서, 팀 코드는 계층 구조를 가진다.

1
2
3
4
5
6
7
8
--2
SELECT * FROM 사원 WHERE 사번 = 1;
 
사번 그룹 본부 부서
---- ---- ---- ------
  1  01   0101 010101
 
1개의 행이 선택되었습니다.

아래 쿼리는 그룹, 본부, 부서에 해당하는 사원의 매출 테이블 정보를 조회한다. 비효율적인 실행 계획으로 인해 7.72초가 소요되었고, 55,411개의 블록 I/O가 발생했다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- 3
VAR v1 VARCHAR2(32)
VAR v2 VARCHAR2(32)
VAR v3 VARCHAR2(32)
VAR v4 VARCHAR2(32)
 
EXEC :v1 := '01'
EXEC :v2 := '0101'
EXEC :v3 := '010101'
EXEC :v4 := '2000-01-01'
 
SELECT SUM (b.금액)
  FROM 사원 a
     , 매출 b
 WHERE a.그룹 = :v1
   AND a.본부 = :v2
   AND a.부서 = :v3
   AND b.사번 = a.사번
   AND b.날짜 >= TO_DATE (:v4, 'YYYY-MM-DD');
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |        |      1 |        |      1 |00:00:07.72 |   55411 |
|   1 |  SORT AGGREGATE                |        |      1 |      1 |      1 |00:00:07.72 |   55411 |
|   2 |   NESTED LOOPS                 |        |      1 |      1 |    100 |00:00:07.72 |   55411 |
|   3 |    NESTED LOOPS                |        |      1 |    900 |     10M|00:00:02.36 |   26411 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| 사원   |      1 |      1 |    100 |00:00:00.01 |       3 | -- !
|*  5 |      INDEX RANGE SCAN          | 사원_X3|      1 |    100 |    100 |00:00:00.01 |       2 |
|*  6 |     INDEX RANGE SCAN           | 매출_X1|    100 |    900 |     10M|00:00:01.50 |   26408 |
|*  7 |    TABLE ACCESS BY INDEX ROWID | 매출   |     10M|      1 |    100 |00:00:03.72 |   29000 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter(("A"."본부"=:V2 AND "A"."그룹"=:V1))
   5 - access("A"."부서"=:V3)
   6 - access("B"."날짜">=TO_DATE(:V4,'YYYY-MM-DD'))
   7 - filter("B"."사번"="A"."사번")

아래는 사원 테이블의 칼럼 통계 정보다. 그룹, 본부, 부서 열을 등호(=)로 조회하면 카디널리티가 1(=100000 * 1/10 * 1/100 * 1/1000)로 계산된다. 예상 카디널리티가 1이므로 매출 테이블을 한번만 액세스할 수 있을 것으로 판단하여 해시 조인이 아닌 NL 조인을 선택한 것이다.[각주:1]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 4
SELECT column_name, num_distinct, density, low_value, high_value, num_buckets
  FROM user_tab_columns
 WHERE table_name = '사원';
 
 
COLUMN_NAME NUM_DISTINCT DENSITY LOW_VALUE    HIGH_VALUE   NUM_BUCKETS
----------- ------------ ------- ------------ ------------ -----------
사번              100000 0.00001 C102         C30B                   1
그룹                  10 0.10000 3031         3130                   1
본부                 100 0.01000 30313031     31303130               1
부서                1000 0.00100 303130313031 313031303130           1
 
4 행이 선택되었습니다.

아래 쿼리는 부서 조건만 입력했다. 예상 카디널리티가 100으로 계산되어 해시 조인으로 조인된 것을 확인할 수 있다. 0.1초가 소요되었고, 559개의 블록 I/O가 발생했다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 5
SELECT SUM (b.금액)
  FROM 사원 a
     , 매출 b
 WHERE a.부서 = :v3
   AND b.사번 = a.사번
   AND b.날짜 >= TO_DATE (:v4, 'YYYY-MM-DD');
 
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |      1 |        |      1 |00:00:00.10 |     559 |          |
|   1 |  SORT AGGREGATE               |        |      1 |      1 |      1 |00:00:00.10 |     559 |          |
|*  2 |   HASH JOIN                   |        |      1 |    100 |    100 |00:00:00.10 |     559 | 1658K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID| 사원   |      1 |    100 |    100 |00:00:00.01 |       3 |          | -- !
|*  4 |     INDEX RANGE SCAN          | 사원_X3|      1 |    100 |    100 |00:00:00.01 |       2 |          |
|   5 |    TABLE ACCESS BY INDEX ROWID| 매출   |      1 |   5000 |    100K|00:00:00.05 |     556 |          |
|*  6 |     INDEX RANGE SCAN          | 매출_X1|      1 |    900 |    100K|00:00:00.02 |     266 |          |
-------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("B"."사번"="A"."사번")
   4 - access("A"."부서"=:V3)
   6 - access("B"."날짜">=TO_DATE(:V4,'YYYY-MM-DD'))

쿼리를 수정하기 어렵다면 통계 정보 설정으로 성능을 개선할 수 있다. 아래와 같이 계층이 코드가 아닌 경우를 감안하여 본부와 부서 칼럼에 대한 통계 정보를 설정하자.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 6
DECLARE
    l_distcnt    NUMBER;
    l_density    NUMBER;
    l_nullcnt    NUMBER;
    l_avgclen    NUMBER;
BEGIN
    DBMS_STATS.SET_COLUMN_STATS (
        ownname              => 'TUNA'
      , tabname              => '사원'
      , colname              => '본부'
      , distcnt              => '10'
      , density              => '0.1'
      , no_invalidate        => FALSE
      , force                => TRUE
    );
 
 
    DBMS_STATS.SET_COLUMN_STATS (
        ownname              => 'TUNA'
      , tabname              => '사원'
      , colname              => '부서'
      , distcnt              => '10'
      , density              => '0.1'
      , no_invalidate        => FALSE
      , force                => TRUE
    );
END;
/
 
PL/SQL 처리가 정상적으로 완료되었습니다.

그룹, 본부, 부서 열을 등호(=)로 조회하면 카디널리티가 100(= 100000 * 1/10 * 1/10 * 1/10)으로 계산된다.

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 7
SELECT column_name, num_distinct, density, low_value, high_value, num_buckets
  FROM user_tab_columns
 WHERE table_name = '사원';
 
COLUMN_NAME NUM_DISTINCT  DENSITY LOW_VALUE    HIGH_VALUE   NUM_BUCKETS
----------- ------------ -------- ------------ ------------ -----------
사번              100000  0.00001 C102         C30B                   1
그룹                  10  0.10000 3031         3130                   1
본부                  10  0.10000 30313031     31303130               1
부서                  10  0.10000 303130313031 313031303130           1
 
4 행이 선택되었습니다.

3번 쿼리를 다시 수행해보면 5번 쿼리처럼 해시 조인으로 수행되는 것을 확인할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 8
SELECT SUM (b.금액)
  FROM 사원 a
     , 매출 b
 WHERE a.그룹 = :v1
   AND a.본부 = :v2
   AND a.부서 = :v3
   AND b.사번 = a.사번
   AND b.날짜 >= TO_DATE (:v4, 'YYYY-MM-DD');
 
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |      1 |        |      1 |00:00:00.16 |     611 |          |
|   1 |  SORT AGGREGATE               |        |      1 |      1 |      1 |00:00:00.16 |     611 |          |
|*  2 |   HASH JOIN                   |        |      1 |    100 |    100 |00:00:00.16 |     611 | 1657K (0)|
|*  3 |    TABLE ACCESS BY INDEX ROWID| 사원   |      1 |    100 |    100 |00:00:00.02 |      55 |          | -- !
|*  4 |     INDEX RANGE SCAN          | 사원_X3|      1 |  10000 |  10000 |00:00:00.01 |      21 |          |
|   5 |    TABLE ACCESS BY INDEX ROWID| 매출   |      1 |   5000 |    100K|00:00:00.06 |     556 |          |
|*  6 |     INDEX RANGE SCAN          | 매출_X1|      1 |    900 |    100K|00:00:00.02 |     266 |          |
-------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("B"."사번"="A"."사번")
   3 - filter(("A"."본부"=:V2 AND "A"."부서"=:V3))
   4 - access("A"."그룹"=:V1)
   6 - access("B"."날짜">=TO_DATE(:V4,'YYYY-MM-DD'))


  1. PGA를 사용하지 않을 수 있기 때문이다. [본문으로]
저작자표시 비영리 변경금지

'Oracle > Tuning' 카테고리의 다른 글

JPPD와 DB 링크  (0) 2020.01.20
소트 머지 조인의 조인 조건에 따른 수행 방식 차이  (0) 2019.12.28
스칼라 서브 쿼리의 버퍼 Pinning 효과  (0) 2019.12.24
SELECT 문과 DML 문의 쿼리 변환 차이  (0) 2019.11.29
POWER 함수의 CPU 연산 #2  (0) 2019.11.23
Posted by 정희락_

티스토리툴바