오라클 SQL 튜닝 & 서버 튜닝 #3

조인기법


조인 종류
1) Sort-Merge Join : 조인에 참여하는 컬럼에 모두 인덱스 없는 경우. 성능이 가장 나쁨
사용자 삽입 이미지

2) Nested-Loops Join : 가장 보편적, 조인에 참여하는 컬럼 둘다 또는 하나에 인덱스가 사용되는 경우
사용자 삽입 이미지사용자 삽입 이미지

3) Outer Join : 외부키에 null이 포함되어도 검색대상에 포함..
사용자 삽입 이미지사용자 삽입 이미지
4) Hash Join : Master-Detail, Parent-Child 관계의 테이블에서 사용
사용자 삽입 이미지

  • NL, SM join 보다는 성능이 좋지만 CBO에서만 사용할 수 있음
  • 성능을 향상하는 파라메터
    • HASH_JOIN_ENABLE : 10g부터 default
    • HASH_AREA_SIZE : 해쉬조인시 사용되는 메모리 최대크기 (기본값은 SORT_AREA_SIZE 의 2배)
    • HASH_MULTIBLOCK_IO_COUNT : 9i까지, 10g부터는 DB_MULTIBLOCK_READ_COUNT 와 통합됨

 

5) Cluster Join :

6) Star Join : 대용량의 fact테이블과 이를 참조하는 차원테이블이 많을때, 데이터웨어하우스 & CBO 에서

사용자 삽입 이미지

 

조인시 성능에 영향을 주는 요소


(1) Driving Table 선택에 의한 성능 차이 : 데이터양과 분포도에 따라 선택

  • 먼저 실행되는 테이블은 Driving Table 또는 Outer Table, 나중에 실행되는 테이블은 Inner Table
  • 동등 조건이면 가장 오른쪽 테이블부터 : 모두 같은 인덱스 있거나 모두 인덱스 없거나..
    ex) select * from A(3), B(2), C(1)
  • 싱글 인덱스 < 결합 인덱스, non-unique 인덱스 < unique 인덱스
    ex) select * from A(싱글,3), B(결합,1), C(싱글,2)
  • and 일때 왼쪽부터
    ex) select * from A from deptno=10(1) and deptno=180(2)
  • or 일때 오른쪽부터
    ex) select * from A from deptno=10(3) or deptno=180(2) or deptno=180(1)
    ==> 내부적으로는 union all
    ==> order by 필요없이 deptno=30 or deptno=20 or deptno=10으로 쿼리하면 10,20,30 순으로 출력

 

(2) 테이블의 조인 순서에 의한 성능 차이

  • 구동테이블이 선택되면 나머지들의 순서도 잘 정해줘야함.
    ex) 테이블 A, B, C 일때 순서에 대한 경우의 수는 6가지 (n!)
  • 조인하는 테이블수가 많으면 CBO일때 cost계산에 부하가 너무 많이 걸림
    –> 어느정도에서 cut off됨
    –> 더 좋은 실행계획을 위해 hint을 통해 join 순서를 정해줄 수도 있다.

사용자 삽입 이미지
(3) 테이블의 검색 양에 의한 성능 차이 : 넓은->좁은 범위로, full_table_scan –> index_scan 으로

(4) Index 사용 여부에 의한 성능 차이 : 잘못된 SQL로 인해 인덱스를 사용못할 수도 있다.

SQL> create index i_emp_empno_deptno on big_emp (deptno, sal, empno);
인덱스가 생성되었습니다.

SQL> create index i_dept_deptno on big_emp (deptno);
인덱스가 생성되었습니다.

SQL> create index i_account_customer on account (close_status, customer);
인덱스가 생성되었습니다.

SQL> select a.empno, a.ename, a.deptno, b.dname, c.no
2  from big_dept b, big_emp a, account c
3  where b.deptno = a.deptno
4  and a.empno = c.customer
5  and a.deptno = 10 and a.sal > 2000 and groupno=’2′
6  and c.close_status = ‘Y’;

     EMPNO ENAME          DEPTNO DNAME          NO
———- ———- ———- ————– ———-
5497 KING               10 RESEARCH       1300005497
5427 KING               10 RESEARCH       1300005427
5455 KING               10 RESEARCH       1300005455

SQL> @tkprof.sql

********************************************************************************

select
a.empno, a.ename, a.deptno, b.dname, c.no
from big_dept b, big_emp a, account c
where b.deptno = a.deptno
and a.empno = c.customer
and a.deptno = :”SYS_B_0″ and a.sal > :”SYS_B_1″ and groupno=:”SYS_B_2″
and c.close_status = :”SYS_B_3″

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.04         43        522          0           3
——- ——  ——– ———- ———- ———- ———-  ———-
total        4      0.00       0.04         43        522          0           3

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: 61  (SCOTT)

Rows     Row Source Operation
——-  —————————————————
3  TABLE ACCESS BY INDEX ROWID ACCOUNT (cr=522 pr=43 pw=0 time=20036 us)
7   NESTED LOOPS  (cr=520 pr=43 pw=0 time=120048 us)
3    NESTED LOOPS  (cr=514 pr=43 pw=0 time=20050 us)
289     TABLE ACCESS FULL BIG_DEPT (cr=4 pr=0 pw=0 time=647 us)
3     TABLE ACCESS BY INDEX ROWID BIG_EMP (cr=510 pr=43 pw=0 time=40506 us)
5336      INDEX RANGE SCAN I_DEPT_DEPTNO (cr=329 pr=43 pw=0 time=40557 us)(object id 52644)
3    INDEX RANGE SCAN I_ACCOUNT_CUSTOMER (cr=6 pr=0 pw=0 time=41 us)(object id 52645)

Rows     Execution Plan
——-  —————————————————
0  SELECT STATEMENT   MODE: RULE
3   TABLE ACCESS (BY INDEX ROWID) OF ‘ACCOUNT’ (TABLE) ==> 3
7    NESTED LOOPS
3     NESTED LOOPS
289      TABLE ACCESS (FULL) OF ‘BIG_DEPT’ (TABLE)  ==> 2
3      TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF ‘BIG_EMP’ (TABLE) ==> 1
5336       INDEX   MODE: ANALYZED (RANGE SCAN) OF ‘I_DEPT_DEPTNO’ (INDEX)
3     INDEX   MODE: ANALYZED (RANGE SCAN) OF ‘I_ACCOUNT_CUSTOMER’ (INDEX)

********************************************************************************

==> 조인 순서를 바꾼후 성능 좋아짐

SQL> select –+ ORDERED
  2  a.empno, a.ename, a.deptno, b.dname, c.no
3  from big_dept b, big_emp a, account c
4  where b.deptno = a.deptno
5  and a.empno = c.customer
6  and a.deptno = 10 and a.sal > 2000 and groupno=’2′
7  and c.close_status = ‘Y’;

********************************************************************************

select –+ ORDERED
a.empno, a.ename, a.deptno, b.dname, c.no
from big_dept b, big_emp a, account c
where b.deptno = a.deptno
and a.empno = c.customer
and a.deptno = :”SYS_B_0″ and a.sal > :”SYS_B_1″ and groupno=:”SYS_B_2″
and c.close_status = :”SYS_B_3″

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          3          0           0
Fetch        2      0.01       0.00          0        399          0           3
——- ——  ——– ———- ———- ———- ———-  ———-
total        4      0.01       0.01          0        402          0           3

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: 61  (SCOTT)

Rows     Row Source Operation
——-  —————————————————
3  HASH JOIN  (cr=399 pr=0 pw=0 time=8456 us)
3   HASH JOIN  (cr=188 pr=0 pw=0 time=3777 us)
1    TABLE ACCESS FULL BIG_DEPT (cr=3 pr=0 pw=0 time=91 us)
3    TABLE ACCESS FULL BIG_EMP (cr=185 pr=0 pw=0 time=3383 us)
2868   TABLE ACCESS FULL ACCOUNT (cr=211 pr=0 pw=0 time=2951 us)

Rows     Execution Plan
——-  —————————————————
0  SELECT STATEMENT   MODE: RULE
3   HASH JOIN
3    TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF ‘BIG_EMP’ (TABLE) ==> 2
1     NESTED LOOPS
3      TABLE ACCESS (FULL) OF ‘BIG_DEPT’ (TABLE) ==> 1
2868      INDEX   MODE: ANALYZED (RANGE SCAN) OF ‘I_EMP_EMPNO_DEPTNO’ (INDEX)
0    TABLE ACCESS (BY INDEX ROWID) OF ‘ACCOUNT’ (TABLE)  ==> 3
0     INDEX   MODE: ANALYZED (RANGE SCAN) OF ‘I_ACCOUNT_CUSTOMER’ (INDEX)

********************************************************************************

 

 

Driving Table(구동테이블) 선택 기준


Sort Merge Join 경우
2개의 테이블의 컬럼에 index가 없으므로 from 절의 순서를 따름 –> 우측 테이블이 구동테이블

Nested Loop Join 경우
한쪽에 index가 없으면 어차피 full table scan 일어난다 –> index 없는 테이블이 구동테이블
index있는 테이블과 unique-index 있는 테이블 –> unique-index가 더 성능이 좋으므로 구동테이블
모두 같은 레벨의 index가 있는 테이블 –> 오른쪽 테이블이 구동테이블

Outer Join 경우
모든값을 가진 테이블쪽이 구동테이블 : select ~~ from emp, detp where emp.detpno (+)= dept.deptno;

 

조인문 작성 방법

  • 추출하고자 하는 테이블의 행수를 분석
  • 부분처리가 가능한 테이블 중에서 분석된 행수와 가장 유사한 행수를 가진 테이블을 구동테이블로 선정
  • 인덱스 종류를 분석후 select list 컬럼절을 결정 (fast-index scan)
  • 조인컬럼과 인덱스 구조를 분석하여 구동테이블 결정 (검색 범위가 좁은 놈으로)
  • 구동 테이블과 내부 테이블 간의 관계 고려 (from 절에서 구동테이블을 우측순으로)
  • sql 작성 완료후 분석/통계생성 (성능이 안 나오면 hint 등을 사용)

사용자 삽입 이미지

 

 

 

보고서 작성

(1) decode 함수

SQL> select job, sal, decode(job, ‘ANALYST’, sal*1.10, ‘CLERK’, sal*1.15, sal) salary from emp;

JOB              SAL     SALARY
——— ———- ———-
SALESMAN        2532       2532
SALESMAN      2182.5     2182.5
MANAGER         3908       3908
SALESMAN      2183.5     2183.5
MANAGER         3784       3784
MANAGER       3384.5     3384.5
ANALYST         3935     4328.5

 

(2) case 함수 : decode는 내부적으로 sort 발생하므로 8i 이후 추가된 기능

SQL> select job,sal,
  2         case when job=’ANAYST’ then sal*1.10
  3              when job=’CLERK’ then sal*1.15
  4              else sal
  5         end as salary
  6  from emp;

JOB              SAL     SALARY
——— ———- ———-
CLERK            800        920
SALESMAN        1600       1600
SALESMAN        1250       1250
MANAGER         2975       2975
SALESMAN        1250       1250
MANAGER         2850       2850
MANAGER         2450       2450
ANALYST         3000       3000
PRESIDENT       5000       5000
SALESMAN        1500       1500
CLERK           1100       1265
CLERK            950     1092.5
ANALYST         3000       3000
CLERK           1300       1495

 

(3) rollup, cube : 주로 corsstab(좌우로 합계를 내는 보고서)를 만들때 사용

SQL> select deptno, grouping(deptno), job, grouping(job), count(*), avg(sal)*12
  2  from emp
  3  group by rollup(deptno, job);

    DEPTNO GROUPING(DEPTNO) JOB       GROUPING(JOB)   COUNT(*) AVG(SAL)*12
———- —————- ——— ————- ———- ———–
10                0 CLERK                 0          1       15600
10                0 MANAGER               0          1       29400
10                0 PRESIDENT             0          1       60000
10                0                       1          3       35000 ==> 부서 바뀔때마다 합
20                0 CLERK                 0          2       11400
20                0 ANALYST               0          2       36000
20                0 MANAGER               0          1       35700
20                0                       1          5       26100
==> 부서 바뀔때마다 합
        30                0 CLERK                 0          1       11400
30                0 MANAGER               0          1       34200
30                0 SALESMAN              0          4       16800
30                0                       1          6       18800
==> 부서 바뀔때마다 합
                          1                       1         14  24878.5714 ==> 전체합
SQL> select deptno, grouping(deptno), job, grouping(job), count(*), avg(sal)*12
2  from emp
3  group by cube(deptno, job);
DEPTNO GROUPING(DEPTNO) JOB       GROUPING(JOB)   COUNT(*) AVG(SAL)*12
———- —————- ——— ————- ———- ———–
                          1                       1         14  24878.5714 ==> 총 집계
                          1 CLERK                 0          4       12450 ==> 직업별 집계
                          1 ANALYST               0          2       36000
                          1 MANAGER               0          3       33100
                          1 SALESMAN              0          4       16800
                          1 PRESIDENT             0          1       60000
10                0                       1          3       35000 ==> 부서별 집계
10                0 CLERK                 0          1       15600
10                0 MANAGER               0          1       29400
10                0 PRESIDENT             0          1       60000
20                0                       1          5       26100 ==> 부서별 집계
20                0 CLERK                 0          2       11400
20                0 ANALYST               0          2       36000
20                0 MANAGER               0          1       35700
30                0                       1          6       18800 ==> 부서별 집계
30                0 CLERK                 0          1       11400
30                0 MANAGER               0          1       34200
30                0 SALESMAN              0          4       16800

 

 

튜닝실습 #1

 

 

 

 

튜닝실습 #2

 

1. cost-based optimizer 로 실행시
********************************************************************************

SELECT A.SUJU_NO, B.ILLYEON_NO, C.GOGAEK_NM, D.PROM_CD, B.SANGPUM_CD,
B.SURYANG, B.DANGA
FROM S_SUJU A, S_SUJU_DTL B, S_GOGAEK C, S_PROMOTION D
WHERE A.SUJU_NO = B.SUJU_NO AND
A.GOGAEK_NO = C.GOGAEK_NO AND
A.PROM_CD = D.PROM_CD AND
A.JUMUN_DT LIKE :”SYS_B_0″

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.04       0.03          0          0          0           0
Fetch      817      3.71       9.44      70382      71206          0       12229
——- ——  ——– ———- ———- ———- ———-  ———-
total      819      3.76       9.48      70382      71206          0       12229

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61  (SCOTT)

Rows     Row Source Operation
——-  —————————————————
12229  HASH JOIN  (cr=71206 pr=70382 pw=0 time=5417700 us)
1063   INDEX FAST FULL SCAN SYS_C005421 (cr=6 pr=5 pw=0 time=21753 us)(object id 52567)
12229   HASH JOIN  (cr=71200 pr=70377 pw=0 time=5358177 us)
3238    HASH JOIN  (cr=43927 pr=43920 pw=0 time=3499826 us)
3238     TABLE ACCESS FULL S_SUJU (cr=29436 pr=29432 pw=0 time=231249 us)
378789     TABLE ACCESS FULL S_GOGAEK (cr=14491 pr=14488 pw=0 time=1906429 us)
4136970    TABLE ACCESS FULL S_SUJU_DTL (cr=27273 pr=26457 pw=0 time=4150124 us)

Rows     Execution Plan
——-  —————————————————
0  SELECT STATEMENT   MODE: ALL_ROWS
12229   HASH JOIN
1063    INDEX   MODE: ANALYZED (FAST FULL SCAN) OF ‘SYS_C005421’
(INDEX (UNIQUE))
12229    HASH JOIN
3238     HASH JOIN
3238      TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF ‘S_SUJU’
(TABLE)
378789       INDEX   MODE: ANALYZED (RANGE SCAN) OF ‘I_S_SUJU’ (INDEX)

4136970      TABLE ACCESS   MODE: ANALYZED (FULL) OF ‘S_GOGAEK’ (TABLE)

0     TABLE ACCESS   MODE: ANALYZED (FULL) OF ‘S_SUJU_DTL’ (TABLE)

********************************************************************************

 

2. rule-based optimizer로 실행시
********************************************************************************

SELECT /*+ rule */
A.SUJU_NO, B.ILLYEON_NO, C.GOGAEK_NM, D.PROM_CD,
B.SANGPUM_CD, B.SURYANG, B.DANGA
FROM S_SUJU A, S_SUJU_DTL B, S_GOGAEK C, S_PROMOTION D
WHERE A.SUJU_NO = B.SUJU_NO AND
A.GOGAEK_NO = C.GOGAEK_NO AND
A.PROM_CD = D.PROM_CD AND
A.JUMUN_DT LIKE :”SYS_B_0″

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      817      0.28       2.58        480      28125          0       12229
——- ——  ——– ———- ———- ———- ———-  ———-
total      819      0.29       2.59        480      28125          0       12229

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: 61  (SCOTT)

Rows     Row Source Operation
——-  —————————————————
12229  TABLE ACCESS BY INDEX ROWID S_SUJU_DTL (cr=28125 pr=480 pw=0 time=5447157 us)
15468   NESTED LOOPS  (cr=25705 pr=432 pw=0 time=11043563 us)
3238    NESTED LOOPS  (cr=17565 pr=261 pw=0 time=1140478 us)
3238     NESTED LOOPS  (cr=7035 pr=14 pw=0 time=87740 us)
3238      TABLE ACCESS BY INDEX ROWID S_SUJU (cr=2981 pr=14 pw=0 time=48875 us)
3238       INDEX RANGE SCAN I_S_SUJU (cr=828 pr=14 pw=0 time=35908 us)(object id 52575)
3238      INDEX UNIQUE SCAN SYS_C005421 (cr=4054 pr=0 pw=0 time=25687 us)(object id 52567)
3238     TABLE ACCESS BY INDEX ROWID S_GOGAEK (cr=10530 pr=247 pw=0 time=1695294 us)
3238      INDEX UNIQUE SCAN PK_T_GOGAEK (cr=7292 pr=10 pw=0 time=123482 us)(object id 52560)
12229    INDEX RANGE SCAN PK_T_SUJU_DTL (cr=8140 pr=171 pw=0 time=454625 us)(object id 52579)

Rows     Execution Plan
——-  —————————————————
0  SELECT STATEMENT   MODE: HINT: RULE
12229   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF ‘S_SUJU_DTL’
(TABLE)
15468    NESTED LOOPS
3238     NESTED LOOPS
3238      NESTED LOOPS
3238       TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
‘S_SUJU’ (TABLE)
3238        INDEX   MODE: ANALYZED (RANGE SCAN) OF ‘I_S_SUJU’
(INDEX)
3238       INDEX   MODE: ANALYZED (UNIQUE SCAN) OF ‘SYS_C005421’
(INDEX (UNIQUE))
3238      TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
‘S_GOGAEK’ (TABLE)
3238       INDEX   MODE: ANALYZED (UNIQUE SCAN) OF ‘PK_T_GOGAEK’
(INDEX (UNIQUE))
12229     INDEX   MODE: ANALYZED (RANGE SCAN) OF ‘PK_T_SUJU_DTL’
(INDEX (UNIQUE))

********************************************************************************

3. 튜닝
1) rule-based가 유리함을 알았고, from절에서 구동테이블이 S_SUJU 임을 판단함
2) S_SUJU의 인덱스를 조사
Table                                Pct Cluster
Table           # Rows Index                  Space      U Column          # Dist Free  Factor BLevel  Leaf BALANCE
————- ——– ——————–   ———- – ————  ——– —- ——- —— —– ——-
S_SUJU          828209 I_S_SUJU_CHULHAJISI_NO SYSTEM     N SUJU_NO         828209   10  ######      2  4701
SYSTEM       CHULHAJISI_NO   794199   10  ######      2  4701
828209 I_S_SUJU               SYSTEM     N PROM_CD           1023   10  ######      2  2877
SYSTEM       JUMUN_DT          1886   10  ######      2  2877
828209 PK_T_SUJU              SYSTEM     U SUJU_NO         828209    3  ######      2  2780

3) S_SUJU의 검색조건중에 A.JUMUN_DT LIKE ‘2001%’; 와 같은 구문이 있어서 인덱스 추가함
CREATE INDEX I_SUJU_JUMUN_DT ON S_SUJU(JUMUN_DT);

********************************************************************************

SELECT /*+ rule */
A.SUJU_NO, B.ILLYEON_NO, C.GOGAEK_NM, D.PROM_CD,
B.SANGPUM_CD, B.SURYANG, B.DANGA
FROM S_SUJU A, S_SUJU_DTL B, S_GOGAEK C, S_PROMOTION D
WHERE A.SUJU_NO = B.SUJU_NO AND
A.GOGAEK_NO = C.GOGAEK_NO AND
A.PROM_CD = D.PROM_CD AND
A.JUMUN_DT LIKE :”SYS_B_0″

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      817      0.21       0.29         13      26508          0       12229
——- ——  ——– ———- ———- ———- ———-  ———-
total      819      0.21       0.30         13      26508          0       12229

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: 61  (SCOTT)

Rows     Row Source Operation
——-  —————————————————
12229  TABLE ACCESS BY INDEX ROWID S_SUJU_DTL (cr=26508 pr=13 pw=0 time=335889 us)
15468   NESTED LOOPS  (cr=24939 pr=13 pw=0 time=2583119 us)
3238    NESTED LOOPS  (cr=16802 pr=13 pw=0 time=140907 us)
3238     NESTED LOOPS  (cr=6275 pr=13 pw=0 time=82616 us)
3238      TABLE ACCESS BY INDEX ROWID S_SUJU (cr=2224 pr=13 pw=0 time=46990 us)
3238       INDEX RANGE SCAN I_SUJU_JUMUN_DT (cr=822 pr=13 pw=0 time=34023 us)(object id 52656)
3238      INDEX UNIQUE SCAN SYS_C005421 (cr=4051 pr=0 pw=0 time=28656 us)(object id 52567)
3238     TABLE ACCESS BY INDEX ROWID S_GOGAEK (cr=10527 pr=0 pw=0 time=56800 us)
3238      INDEX UNIQUE SCAN PK_T_GOGAEK (cr=7289 pr=0 pw=0 time=26977 us)(object id 52560)
12229    INDEX RANGE SCAN PK_T_SUJU_DTL (cr=8137 pr=0 pw=0 time=52960 us)(object id 52579)

Rows     Execution Plan
——-  —————————————————
0  SELECT STATEMENT   MODE: HINT: RULE
12229   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF ‘S_SUJU_DTL’
(TABLE)
15468    NESTED LOOPS
3238     NESTED LOOPS
3238      NESTED LOOPS
3238       TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
‘S_SUJU’ (TABLE)
3238        INDEX   MODE: ANALYZED (RANGE SCAN) OF
‘I_SUJU_JUMUN_DT’ (INDEX)
3238       INDEX   MODE: ANALYZED (UNIQUE SCAN) OF ‘SYS_C005421’
(INDEX (UNIQUE))
3238      TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
‘S_GOGAEK’ (TABLE)
3238       INDEX   MODE: ANALYZED (UNIQUE SCAN) OF ‘PK_T_GOGAEK’
(INDEX (UNIQUE))
12229     INDEX   MODE: ANALYZED (RANGE SCAN) OF ‘PK_T_SUJU_DTL’
(INDEX (UNIQUE))

********************************************************************************

 

 

사용자 삽입 이미지

 

구체화된 뷰

  • View  : 실시간의 데이터는 유지하지만 시간이 많이 걸린다는 단점, data없음. Select만 됨. 논리적 존재.
  • Table : Data있음. Select/Insert/Update/Delete 됨. 물리적 존재.
  • CTAS  : Summary table (CREATE TABLE AS SELECT ~~), 실시간 데이터는 아니지만 성능 좋음
  • MView : Materialized View, 물리적 존재. Data 있음. Select만 됨
  • 대용량의 데이터를 SUM, MIN, MAX, AVG, COUNT(*)이런 명령어를 사용해 자주 조회하는 Query를 수행속도를 향상을 위해서 Query 결과 만큼의 새로운 테이블을 생성해 놓는 벙법

파라메터 확인

SQL> col parameter format a30
SQL> col value format a20
SQL> select * from v$option;

PARAMETER                           VALUE
———————————– ——————–
Materialized view rewrite           TRUE
Materialized view warehouse refresh TRUE

==> 테스트한 버전은 10g 엔터프라이즈여서 true가 디폴트임

권한 생성

SQL> connect system/비번
SQL> grant create materialized view to scott;
SQL> grant global query Rewrite to scott;

구체화된 뷰 생성
SQL> connect scott/tiger
SQL> create materialized view reg_count
tablespace users
build immediate             ┌-> 주기적으로 갱신하려면 : start with sysdate+1/24 (1시간마다)
refresh complete on commit -┴-> 변경/입력시 새로생성 (refresh fast는 변경/입력내용만 갱신함)
enable query rewrite  –> as 이하의 쿼리가 오면 MView 내용을 보도록 실행계획 잡는다.
as select a.empno, a.ename, b.dname from emp a, dept b where a.deptno = b.deptno;

구체화된 뷰가 생성되었습니다.

구체화된 뷰가 생성되었는지 확인

SQL> select * from user_objects where object_name like ‘%REG_COUNT%’;
OBJECT_NAME
——————————————————————————–
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
—————————— ———- ————– ——————-
CREATED  LAST_DDL TIMESTAMP           STATUS  T G S
——– ——– ——————- ——- – – –
REG_COUNT
52659          52659 TABLE
08/06/04 08/06/04 2008-06-04:16:39:55 VALID   N N N

REG_COUNT
52660                MATERIALIZED VIEW
08/06/04 08/06/04 2008-06-04:16:39:55 VALID   N N N

Query Rewrite를 사용하는지 실행계획 확인 #1

SQL> set autotrace on
SQL> select /*+rewrite*/ a.empno, a.ename, b.dname
2  from emp a, dept b
3  where a.deptno = b.deptno;

Execution Plan
———————————————————-
Plan hash value: 2074428718

——————————————————————————————
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)|Time     |
——————————————————————————————
|   0 | SELECT STATEMENT             |           |    14 |   406 |     3   (0)|00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| REG_COUNT |    14 |   406 |     3   (0)|00:00:01 |  ==> rewrite 실행함
——————————————————————————————

Query Rewrite를 사용하는지 실행계획 확인 #2

SQL> select /*+norewrite*/ a.empno, a.ename, b.dname
2  from emp a, dept b
3  where a.deptno = b.deptno;

Execution Plan
———————————————————-
Plan hash value: 615168685

—————————————————————————
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      |    14 |   770 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    14 |   770 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    88 |     2   (0)| 00:00:01 |  ==> rewrite 실행 안함
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   462 |     2   (0)| 00:00:01 |
—————————————————————————

Query Rewrite를 사용하는지 실행계획 확인 #3

SQL> alter session set query_rewrite_enabled = false;

세션이 변경되었습니다.

SQL> select /*+rewrite*/ a.empno, a.ename, b.dname
2  from emp a, dept b
3  where a.deptno = b.deptno;

Execution Plan
———————————————————-
Plan hash value: 615168685

—————————————————————————
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      |    14 |   770 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    14 |   770 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    88 |     2   (0)| 00:00:01 | ==> 환경설정으로 인해 rewrite 실행 안함
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   462 |     2   (0)| 00:00:01 |
—————————————————————————

SQL> alter session set query_rewrite_enabled = true;
세션이 변경되었습니다.

커밋되었을때 구체화된뷰에 입력되는지 확인

SQL> create materialized view tot_count
2  build immediate
3  refresh complete
on commit
  4  enable query rewrite
5  as select deptno, sum(sal) from emp group by deptno;

구체화된 뷰가 생성되었습니다.

SQL> select * from tot_count;

    DEPTNO   SUM(SAL)
———- ———-
30       9400
20      10875
10       8750

SQL> insert into emp(empno, deptno, sal) values (11, 10, 1000);
1 개의 행이 만들어졌습니다.

SQL> commit;
커밋이 완료되었습니다.

SQL> select * from tot_count;

    DEPTNO   SUM(SAL)
———- ———-
30       9400
20      10875
10       9750  ==> 변경됨

 

클러스터

(1) 종류

  • Index Cluster : 2개 이상 table이 자주 join 되는 경우 사용.
  • Hash Cluster : 1개의 table 일때.

사용자 삽입 이미지

(2) Index Cluster

  • join 되는 테이블을 처음부터 블록 구조내에 join하여 결합된 형태로 저장
  • I/O 개선, 저장공간 줄임
  • 입력,수정,삭제가 빈번한 테이블에서는 성능이 저하됨

사용자 삽입 이미지

  • 생성 절차 : index cluster → cluster index → table1 → table2
SQL> create cluster clu_emp_dept
2  (dept number(3))
3  size 1000;

클러스터가 생성되었습니다.

SQL> create index i_clu_emp_edpt
2  on cluster clu_emp_dept;

인덱스가 생성되었습니다.

SQL> create table dept
2  (deptno number(3),
3  dname varchar2(14))
4  cluster clu_emp_dept(deptno);

테이블이 생성되었습니다.     –>  각각의 테이블에 데이터를 입력한다.

SQL> create table emp
2  (empno number(4),
3  ename varchar2(10),
4  deptno number(3))
5  cluster clu_emp_dept(deptno);

테이블이 생성되었습니다.     –>  각각의 테이블에 데이터를 입력한다.

 

SQL> select rowid, empno, ename, deptno from emp;

ROWID                   EMPNO ENAME          DEPTNO
—————— ———- ———- ———-
AAAHckAAJAAAABGAAA        111 일지매             10
AAAHckAAJAAAABGAAB        112 황진희             20
AAAHckAAJAAAABGAAC        113 홍길동             30

EMP 테이블과 DEPT 테이블에서 데이터를 저장하기 위해 저장한 블럭이
AAAABG 로 같다는 것을 확인 할수 있다.
클러스터를 이용하기 전에는 위에서 처럼 블록이 서로 달랐다.
SQL> select rowid, deptno, dname from dept;

ROWID                  DEPTNO DNAME
—————— ———- ————–
AAAHckAAJAAAABGAAA         10 전산과
AAAHckAAJAAAABGAAB         20 인사과
AAAHckAAJAAAABGAAC         30 총무과
일반적인 경우에는 서로 다른 블록에 데이터가 저장되어 있지만 인덱스 크러스트는
조인될때 조인되는 컬럼 값을 기준으로 같은 블록 내에 행이 저장된다.
그리고 SELECT문이 실행되면 하나의 블록에 있는 데이터를 읽어서 별도의 분류작업 없이
사용자에게 결과를 보여주게 된다.
즉 2개 이상의 테이블이 조인된 형태로  블록에 저장되는 기법이다.

 

SQL> select emp.empno, emp.ename, dept.dname
2  from emp, dept
3  where emp.deptno = dept.deptno;

EMPNO ENAME      DNAME
———- ———- ————–
111 일지매     전산과
112 황진희     인사과
113 홍길동     총무과

사용된 예제는 실제로 데이터를 얼마 가지고 있지 않기때문에 검색상의 속도 차이는 느낄수 없지만
행수가 많다면 인덱스 크러스트를 이용하는 방법이 빠를 것이다.

 

  • 삭제 절차 #1 : table1 → table2 → cluster
DROP TABLE [테이블명1] CASCADE CONSTRAINTS;
DROP TABLE [테이블명2] CASCADE CONSTRAINTS;
DROP CLUSTER [클러스트명] CASCADE CONSTRAINTS;

 

  • 삭제 절차 #2 : cluster
DROP CLUSTER [클러스트명] INCLUDING TABLES CASCADE CONSTRAINTS;   –> 테이블제약조건을 한꺼번에 삭제함

 

  • 성능 개선이 기대되는 테이블
1) 자주 검색되지만 변경이 되지 않는 테이블
2) Master-Detail 관계에서 Detail 테이블
3) Master-Detail 관계의 테이블
4) 자주 조인되지만 개별적으로는 검색되지 않는 테이블

 

(3) Hash Cluster

  • hash 함수를 통해 빠르게 cluster-key를 찾음
mod(empno/1000) ===> empno=1003 이면 cluster-key는 3 (중복되는 hash값이 나오면 성능저하됨)

 

  • Hash Cluster 생성해야 하는 경우
1) where 조건절에 동등 조건식을 포함할때
(범위로 검색하면 성능 떨어짐)
2) table에 모든행을 저장하는데 저장공간이 얼마인지 알 수 있는 경우
(미리 충분한 공간을 확보하여 생성하는 방식이므로 잘못 예측시 성능이 떨어진다.)
3) cluster에 의해 행의 값이 균등하게 분산된 경우

 

  • 생성 : cluster → table
CREATE CLUSTER clu2
(empno number(4))              –> empno 컬럼은 해쉬함수에 의한 결과값이며 클러스트 키를 의미한다.
HASHKEYS 40                      –> 해쉬 클러스트가 저장하게 될 전체 행수를 의미한다.
HASH IS mod(empno,1000)    –> 해쉬 함수를 정의해 주는 부분 해쉬함수는 사용자가 직접 정의할수도 있고
SIZE 40;              —┐              오라클 서버가 제공하는 해쉬알고리즘을 사용할 수도 있으며,
|               테이블 내의 특정 컬럼을 정의할 수도 있다.

└-> 하나의 블록 내에 저장되는 하나의 행 길이를 의미한다.
CREATE TABLE emp
(empno number(4),
ename varchar2(10),
sal number,
comm number)
CLUSTER clu2(empno);

 

  • 삭제 #1
DROP TABLE [테이블명1] CASCADE CONSTRAINTS;
DROP TABLE [테이블명2] CASCADE CONSTRAINTS;
DROP CLUSTER [클러스트명] CASCADE CONSTRAINTS;

 

  • 삭제 #2
DROP CLUSTER [클러스트명] INCLUDING TABLES CASCADE CONSTRAINTS;

 

파티션 뷰

  • Partition view는 Oracle Release 7.3 에서 대용량 테이블을 지원하기 위해서 추가된 새로운 방법이다.
  • 장점
    • 각 테이블별로 index 생성될 수 있고, 병렬 옵션 사용 가능
    • 병렬 옵션으로 partition view 전체를 병렬 스캔할 수 있음
    • Partition 제거 (where조건을 만족하는 table만 검색)
  • 단점
    • patition view를 통해 table 변경 못함
    • patition view에 대한 index를 생성할 수 없음
  • 관련 parameter : PARTITION_VIEW_ENABLED
  • 예제 : 서로다른 tablespace에 정의된 table을 union all 을 통해 view로 생성 –> 경합이 줄어듬
SQL> create view tot_chul
as select * from jeon1999    —> 서로 다른  디스크에 생성된 테이블 (1999년 전표)
union all
select * from jeon2000;       —> 서로 다른  디스크에 생성된 테이블 (2000년 전표)
SQL> select * from tot_chul;        —> total 전표 view

 

파티션 테이블

  • partition view는 유지보수와 관리가 불편하므로 추가된 기법
  • table의 값을 분할 방법에 따라 서로 다른 tablespace를 할당 (경합을 피하기 위해 tablespace는 각 다른 disk)
  • 종류
    • Range Patition : 컬럼값을 기준으로 범위를 정해서 분할
    • Hash Patition : 오라클서버가 가지고 있는 해시알고리즘으로 알아서 분할
    • List Patition : 정의된 컬럼값 List를 통해 분할

사용자 삽입 이미지

1. partition table 생성하기

create table part_tbl
( in_date number primary key ,
empno   number,
ename   varchar2(20),
job   varchar2(20) )
partition by range (in_date)
(
partition part_tbl_200803  values less than (20080332) tablespace pts_03,
partition part_tbl_200804  values less than (20080432) tablespace pts_04,
partition part_tbl_200805  values less than (20080532) tablespace pts_05,
partition part_tbl_max  values less than (maxvalue) tablespace pts_max
);

 

2. add partition 하기

6월 partition을 add하고 싶은 경우 다음과 같이 할 수 있다.

alter table part_tbl add partition part_tbl_200806 values less than (20080632) tablespace pts_06;

# 단 MAX 파티션 테이블이 있는경우는 add  partition 할 수 없다!
따라서 위 예제에서 생성된 파티션 테이블에서는 add partition 불가!!

 

3. 특정 partition 을 삭제하기

3월에 해당하는 partition을 없애고 싶은 경우는 다음과 같이 실행한다.

alter table part_tbl drop partition part_tbl_200803;

만약..
drop된 후에 새로 3월에 해당하는 데이타가 입력되면
4월의 partition이 less then (20080432) 으로 되어 있으므로
4월에 해당하는 partition에 저장된다.

 

3. partition 나누기
6월에 해당하는 partition을 생성하려면 MAX partition에 add하는 것으로는 불가능하고 MAX partition을 split 해야 한다.

alter table part_tbl split partition part_tbl_max at (20080632)
into (partition part_tbl_200806 tablespace pts_06, partition part_tbl_max tablespace pts_max);
+)

into(partition AAA, partition BBB) -> BBB파티션을 AAA 파티션으로 split한다.
위와 같이 하면 기존의 MAX 파티션 테이블에서 6월31일을 이전 데이터는 part_tbl_200806에 옮겨지고
MAX 파티션 테이블에는 6월31일 이후의 데이터가 들어있게 된다.


4. partition name을 변경하기

partition name 을 바꾸고 싶다면 다음과 같이 실행한다.

alter table part_tbl rename partition part_tbl_200805 to part_tbl_200805_new;
5. partition의 tablespace를 옮기기

partition part_tbl_05을 저장하는 tablespace를 pts_05 에서 pts_new로 바꾸고 싶은 경우는 다음과 같이 실행한다.

alter table part_tbl move partition part_tbl_05 tablespace pts_new nologging;

6. 특정 partition의 data를 truncate하기
partition의 data를 모두 삭제하려면 truncate하는 방법을 사용할 수가 있는 데,
truncate는 rollback 이 불가능하며 특정 partition 전체를 삭제하므로 주의하여 사용하여야 한다.

alter table part_tbl truncate partition part_tbl_03;
7. Partition table의 물리적인 속성 변경하기
partition table은 특정 partition의 속성만 변경할 수 있고,
table의 속성을 변경하여 전체 partition에 대해 동일한 변경을 할 수 있다.
alter table part_tbl storage( next 10M);
-> part_tbl 의 모든 partition의 next 값이 변경된다.
alter table part_tbl modify partition part_tbl_05 storage ( maxextents 1000 );
-> part_tbl_05 partition의 maxextents 값만 변경한다.

 

8. Index의 관리
변경작업을 통해 테이블 파티션이 변경된 경우 해당 테이블에 관련된 인덱스를 rebuild 해야합니다.
 1) local 인덱스인 경우
DDL작업을 통해 변경된 파티션 테이블에 걸려있는 해당 local 인덱스만  “UNUSABLE” 상태가 된다.
-> MAX를 Split 한경우는 MAX 파티션과 Split로 새롭게 생성된 파티션의 인덱스 상태만 “UNUSABLE” 된다.

따라서 해당 파티션 인덱스를 반드시 rebuild 해 주어야 합니다.
— 파티션 상태 확인
select INDEX_NAME, PARTITION_NAME, HIGH_VALUE, STATUS, TABLESPACE_NAME
from user_ind_partitions;

— local Index rebuild 하기
alter index 인덱스이름 rebuild partition 파티션이름;

예제)
part_tbl_max 를 part_tbl_200806로 split 했을경우
각각의 파티션에 걸린 인덱스 part_tbl_indx_pk1 는  UNUSABLE 상태가 된다.

 

NDEX_NAME             PARTITION_NAME           HIGH_VALUE    STATUS           TABLESPACE_NAME
———————— —————————- —————- —————– ——————————
part_tbl_indx_pk1      part_tbl_200804      ‘20080432’      USABLE            pts_04
part_tbl_indx_pk1      part_tbl_200805      ‘20080432’      USABLE            pts_05
part_tbl_indx_pk1      part_tbl_200806      ‘20080632’      UNUSABLE        pts_06
part_tbl_indx_pk1      part_tbl_max            MAXVALUE       UNUSABLE        pts_max

 

해당 파티션 인텍스를 rebuild 해준다.
alter index WEMS_BPEMAAN_PK1 rebuild partition WEMS_BPEMAAN_200806_P6;
alter index WEMS_BPEMAAN_PK1 rebuild partition WEMS_BPEMAAN_MAX;

 2)global Index인 경우
– 변경된 해당 파티션 뿐만 아니라 해당 테이블 파티션에 관련된 모든 global 인덱스 상태를 “UNUSABLE”로 만든다.

따라서 인덱스 전체를 반드시 rebuild 해 주어야 합니다.

— global Index rebuild 하기
alter index 인덱스이름 rebuild;

 

파티션 인덱스

  • 분할된 Table에 생성하는 인덱스
  • 종류 (구조적 기준)
    • Local Index : Table과 Index의 분할구조가 다른 경우
    • Global Index : Table과 Index의 분할구조가 같은 경우
  • 종류 (분할키 사용 기준)
    • prefix Index : 분할 Table의 분할 컬럼으로 생성한 index
    • Non-Prefix  Index : 분할 Table의 분할 컬럼이 아닌 다른 컬럼 생성한 index

 

 

 

사용자 삽입 이미지

CC BY-NC-ND 2.0 KR

이 저작물은 크리에이티브 커먼즈 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용할 수 있습니다. 크리에이티브 커먼즈 라이선스

저작권과 관련된 파일요청 및 작업요청을 받지 않습니다.

댓글 남기기