<12/22> SQL Tuning 7장 8장

테이블 스페이스 move alter table 테이블명 move tablespace 테이블스페이스명;

move 권한 부여 grant connect,resource,create view, create procedure, create sequence to sh;

7장 옵티마이저: 조인 연산자

ordered from 테이블 순서대로 가라 힌트

leading(C A D B) 내가 지정한 순서대로 힌트

NL loop 

선탱 테이블의 데이터와 동일한 데이터를 후행 테이블에서 참조함

선행 테이블의 데이터가 상수로 전환되어 후행 테이블에 공급됨

성능을 좌지우지 하는것<인덱스는 양날의 검 절대로 함부로 이야기 하면 안됨>

인덱스를 적절히 설계하면 가장 빠른 성능 보장(잘못 설계하면 최악)

선행테이블이 사용하는 인덱스 순서대로 정렬된 데이터가 추출됨 그래서 부분범위처리, 소량의

데이터 처리에 유리하기에 OLTP 시스템에 적합하여 부분범위 처리(페이지네이션) 30p

조인의 순서는 선행테이블은 조인 대상 데이터를 가장 많이 걸러줄(filter)수 있는 테이블을

선택해야 함

인덱스는 랜덤 액세스를 유발하기 때문에 적은 범위를 처리하는데 유리하다

Join은 한 row씩 순차적으로 진행 대량의 데이터 조인 시 더욱 비 효율적이다

인덱스 구성 전략이 특히 중요해서 인덱스의 유무? 컬럼의 구조(NDV) 즉 분포도?

선행테이블은 조인 대상 데이터를 가장 많이 걸러줄 수 있는 테이블을 선택

ordered use_nl(e)

leading(C A D B) use_nl(A) use_nl(D) use_nl(B)

use_nl(A,B,C,D)

적은양의 데이터를 가져올 수 있는걸로 선행테이블을 구성해야 한다

alter system set optimizer_features_enable='10.2.0.4';

select ename, e.deptno, d.deptno, d.dname

from emp e, dept d

where e.deptno = d.deptno and ename like 'A%';

alter system set optimizer_features_enable='11.2.0.1';

11g부터 나온 2중 Loop기능이 작동하는걸 알 수 있다.

select /*+ USE_MERGE(d e) NO_INDEX(d) */ 

ename, e.deptno, d.deptno, d.dname

from emp e, dept d

where e.deptno = d.deptno and ename like 'A%';

select /*+ USE_MERGE(d e)  */ 

ename, e.deptno, d.deptno, d.dname

from emp e, dept d

where e.deptno = d.deptno and ename like 'A%';

Sort & (양쪽 집합을 조인 칼럼 기준으로 정렬)

Merge  (정렬된 양쪽 집합을 서로 Merge 수행)

첫 번쨰와 두 번째 행 소스 동일한 정렬키로 정렬

양쪽에서 정렬된 행 병합 -> 행 반환

두 개의 독립된 소스에서 행 조인 시 사용

조인 조건이 부등식 조건일경우 유용하다

SMJ에서는 Driving 테이블 개념이 없다

select /*+ USE_HASH(e d)  */ 

ename, e.deptno, d.deptno, d.dname

from emp e, dept d

where e.deptno = d.deptno and ename like 'A%';

해시 조인

둘 중 작은 집합(biild input)을 읽어 해시 area에 해시 테이블을 생성하고 반대쪽 큰 집합

(probe input)을 읽어 해시 테이블을 탐색하면서 조인하는 방식

NL조인처럼 랜덤 액세스 부하가 없고 양쪽 집합 정렬을 미리 할 필요가 없다

Equi-Join(=)만 가능하다 빌드 테이블이 소량일 경우 성능이 좋고 PGA에서 HASH연산하기에

CPU사용량이 증가 수학적 처리에 가장 적합한 알고리즘

NL에서의 랜덤 액세스가 부담스러울 경우에도 사용가능하고 S-M J의 정렬이 부담스러울때도 사용

Hash Join시 성능을 위한 고려사항

PGA_AGGREGATE_SIZE에 대한 고려 OLTP와 DW는 PGA와 Buffer cache size 고려

PGA에서 TEMP로 보낼경우 파티션단위로 쪼개서 그 단위로 올려서 작업한다 SORT시에도 마찬가지

소트런즈단위

그리하여 파티션 테이블(TEMP 쪼갠)이 생기고 이를 Grace hash join -> temp로 넘겨서 처리하는 것

in-memory hash join 이 두 방식을 같이 적용하는걸 hybrid join방식이라 한다

- Hash Area 

 Build Table의 데이터에 의해 생성되며 빠른 Hash Area 생성을 위해서는 Build Table의 데이터가  적어야함

- Probe Table Access

 해시 조인은 일반적으로 대량 데이터 처리 시에 많이 사용되므로 probe table은 대용량

 Table일 가능성이 많으므로 Parallel Processing을 고려할 수 있음

- Build Table Access

 Build Table 검색 시 인덱스를 이용할 것인지 FTS

클러스터 양쪽 테이블의 공통 컬럼을 클러스터 키로 만들어서

ord_no 하나의 테이블을 읽는것처럼 읽는다

DML 처리 속도가 느리다 양쪽에 모두 영향을 주게 된다

한쪽 FTS시 다른쪽 테이블도 같이 읽어야 하므로 느려진다

조회시에만 사용할 수 있는 테이블을 따로 복사해서 만들어주면 단점 극복이 가능하다

create cluster dept_emp_clu(deptno number(2)) size 1000 tablespace users

한 블록에 저장되는 Cluster Key의 크기

create index dept_emp_clu_idx on cluster dept_emp_clu;

Index Cluster에 dept 컬럼을 클러스터화

create table c_emp cluster dept_emp_clu(deptno)

as select * from emp

create table c_dept cluster dept_emp_clu(deptno)

as select * from emp

해시 클러스터를 키값을 해시로 만든다 엑세스 기법중 제일 빠르다

공간 소비량도 적다

하지만 한쪽 테이블에 대한 FTS는 클러스터화 되지 않은 테이블보다 클러스터화 된 테이블이 느림

orcl@SH> select * from products

  2  where prod_subcategory LIKE 'C%'

  3  and prod_category LIKE 'B%'

  4  order by prod_category DESC;

no rows selected

Elapsed: 00:00:00.63

Execution Plan

----------------------------------------------------------

Plan hash value: 2582277287

--------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                         |     1 |   173 |     3 (34)| 00:00:01 |

|   1 |  SORT ORDER BY               |                         |     1 |   173 |     3 (34)| 00:00:01 |

|*  2 |   TABLE ACCESS BY INDEX ROWID| PRODUCTS                |     1 |   173 |     2  (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | PRODUCTS_PROD_SUBCAT_IX |     6 |       |     1  (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("PROD_CATEGORY" LIKE 'B%')

   3 - access("PROD_SUBCATEGORY" LIKE 'C%')

       filter("PROD_SUBCATEGORY" LIKE 'C%')

Statistics

----------------------------------------------------------

       8485  recursive calls

          0  db block gets

       1942  consistent gets

         77  physical reads

          0  redo size

       1712  bytes sent via SQL*Net to client

        405  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

        106  sorts (memory)

          0  sorts (disk)

          0  rows processed