[SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 6. 고급SQL튜닝

SQLP : 제3과목은 SQL 고급활용 및 튜닝 이다.
 
 
3과목. SQL 고급활용 및 튜닝
1. SQL 수행 구조

2. SQL 분석 도구

3. 인덱스 튜닝

4. 조인 튜닝

5. SQL 옵티마이저

6. 고급 SQL 튜닝

7. Lock과 트랜잭션 동시성 제어

 

 

 

 

 

소트
- Sort 양이 많을때 중간 정렬된 집합을 Temp 영역에 저장해둔다. 이 집합을 Sort Runs 라고 한다.

Optimal 소트
- 정렬 대상 집합이 크지 않아 Sort Area 에서 정렬 작업을 완료하는 것

Onepass 소트
- 정렬  대상 집합을 디스크에 한번만 기록하는 것

Multipass 소트
- 정렬  대상 집합을 디스크에 여러번 기록하는 것


Sort 실행계획

SORT (ORDER BY) : Order By 절
SORT (GROUP BY) : Group By 절 + Order By 절
SORT (UNIQUE) : Group By 와 Unique 또는 Distinct 연산, Minus, Intersect, Union 연산
SORT (AGGREGATE) : Count, Avg, Sum, Min, Max 등의 연산
HASH (GROUP BY) : Group By 절

 

페이징처리 표준 패턴

SELECT * FROM ( SELECT ROWNUM NO, A.* FROM ( /* SQL BODY */ ) WHERE ROWNUM <= (:page * 10) ) WHERE NO >= (:page-1) * 10 + 1

WHERE ROWNUM <= (:page * 10) 절에 의해 top n stopkey 알고리즘이 적용된다. 부분 범위처리 가능

First Row Min/Max
- min/max 연산을 사용할때, 조건절와 min/max 컬럼값이 모두 인덱스에 있어야 효율적이다.
- First Row Min/Max 이 안되는 경우, order by 와 rownum <= 절로 변경하는것이 효율적이다.

DML 성능에 영향을 미치는 요소
- 인덱스, 무결성제약, 조건절, 서브쿼리, redo/undo 로깅, Lock, 커밋

Array Processing기능
- 한 번의 SQL 수행으로 다량의 로우를 동시에 insert/update/delete
- 네트워크 부하를 줄이고, 데이터베이스 Call 횟수를 줄여준다.
- OLTP 시스템에서 성능 개선

Direct Path Insert
- 버퍼캐시를 거치지 않고, 작업 데이터파일에 기록
- Exclusive 모드 TM Lock 을 설정
- Freelist 를 참조하지 않고 HWM 바깥 영역에 데이터를 순차적으로 입력
- Undo 로깅을 최소화

alter table tablename nologging; // redo 로그를 생성하지 않음 : insert 에서만 작동 insert /*+ append */ into tablename // Direct Path Insert 작동 select * from sourcetable;

Recursive Call 은 Parse Call / Execute Call / Fetch Call 단계에서 모두 나타날 수 있다.
Array Size 를 늘리면, Fetch Call 을 줄일 수 있다.
Array Processing 을 늘리면, Execute Call 을 줄일 수 있다.

 

병렬처리 Insert

인덱스 비활성화 / 활성화
alter index PK_index_name on tablename DISABLE;
alter index PK_index_name on tablename ENABLE;

 

Merge Into 사용법

Merge Into 고객 c Using 고객 d On ( c.법정대리인_고객번호 = d.고객번호 and c.성인여부 = 'N' ) when matched then -- update set c.법정대리인연락처 = d.연락처 when not matched then -- insert (col1, col2) values (d.col1, d.col2) ;

대용량 데이터 UPDATE 튜닝
- update 구문을 truncate / insert 로 대체

update 주문 set 상태코드 = '9999' => create table tmp as select * from 주문; truncate table 주문; insert into tmp select ..., '9999' as 상태코드, ... from tmp; drop table tmp;

어플리케이션 커서를 캐싱 하면, Parse Call 이 1번만 나타난다.
Array Size 를 늘리면, Fetch Call 이 줄어든다 : fetch call * array size = rows
Array Processing 을 하면, Execute Call 을 줄인다.


DB저장형 사용자정의 함수/프로시저
- 가상머신 상에서 실행되는 Interpreting 언어다.
- 함수가 실행될때마다 Recursive Call 이 발행한다.
- Join 대신, 함수를 사용하여 코드명 등을 조회하면, 성능이 나빠진다.

DB저장형 함수 성능개선
- Join 문으로 변경
- Deterministic 함수로 전환
- Result 캐시 사용

DB저장형 함수 Recursive Call 횟수 개선
- 1. 함수 호출을 스칼라 서브쿼리로 변환한다 (스칼라 서브쿼리 캐싱) : ( select code_nm(1, 2) from dual ) cd_name
- 2. Deterministic 함수 캐싱
- 3. Result 캐시
- 4. Native 컴파일




파티셔닝 (Partitioning)
- 테이블데이터 또는 인덱스 데이터를 나누어서 저장하는 기능
- 다른 파티션에 대량 변경/백업/복구 중에도, 조회/변경/삭제 가능

Range 파티션
- 여러 컬럼 지정 가능
- 숫자,문자,날짜 컬럼 사용 가능
- 범위에 없는 값이 들어왔을 때 오류를 방지하기 위해 MAXVALUE 파티션을 사용해야 한다.
- 신규 파티션을 추가해야 하는 관리 이슈가 있다.

partion by range(주문일시) (      partition p20201 values less than( to_date('20200701', 'YYYYMMDD') ),      partition p20202 values less than( to_date('20210101', 'YYYYMMDD') ),      partition p20211 values less than( to_date('20210701', 'YYYYMMDD') ),      partition p20212 values less than( to_date('20220101', 'YYYYMMDD') ),      partition pMAX values less than( MAXVALUE ) )

List 파티션
- 단일 컬럼만 지정 가능
- 불연속적인 값의 목록으로 분할할때 사용
- 범위에 없는 값이 들어왔을 때 오류를 방지하기 위해 DEFAULT 파티션을 사용해야 한다.

 

partition by list(gubun) (      partition p_list1 values(10),      partition p_list2 values(20),      partition p_list3 values(30),      partition p_list3 values(40),      partition p_list4 values(DEFAULT) );

Hash 파티션
- 여러 컬럼 지정 가능
- DML 경합을 분산하는데 효과적이다.
- 값의 범위가 고르지 않으면, 특정 파티션에 데이터가 몰리게 된다.
- Hash 알고리즘으로 분산이 되기 때문에, 입력공간을 못 찾는 오류는 발생하지 않는다.

 

PARTITION BY HASH ( EmpNo ) (      PARTITION P_H1,      PARTITION P_H2,      PARTITION P_H3,      PARTITION P_H4 );

PARTITION BY HASH ( EmpNo )  PARTITIONS 4;

 

Pruning
- SQL 파드파싱이나 실행시점에 조건절을 분석해서, 읽지말아야 할 파티션을 제외하는 기능
- 문자형 날짜값에 Like 조건을 사용하면 불필요한 파티션을 읽게 된다. Between 조건으로 변경하는것이 좋다.
- 문자형 날짜값에 숫자로 검색하면, 형변환이 발생해 전체 파티션을 조회하게 된다.
- 정적 Pruning / 동적 Pruning 성능 차이는 없다.

PARTITION RANGE ALL
- 파티션 컬럼을 가공하면, 파티션 Pruning 이 불가능 해, 전체 파티션을 조회 하게 된다.
- 파티션 컬럼 조건에 다른 타입의 값을 넣을경우, 강제 형변환이 발생하여, 전체 파티션을 조회 할수 있으니 주의해야 한다.

정적(Static) 파티션 Pruning
- 조건절에 파티션 키 컬럼이 상수로 들어있는 경우
- 쿼리 최적화 시점에 액세스할 파티션 번호를 알 수 있다.
- 실행계획 Pstart, Pstop 에 파티션 번호가 표시된다.

동적(Dynamic) 파티션 Pruning
- 조건절에 파티션 키 컬럼이 바인드 변수로 들어있는 경우, 다른 테이블과의 Join 조건일 경우.
- 실행 시점에 액세스할 파티션을 알 수 있다.
- 실행계획 Pstart, Pstop 에 KEY 라고 표시된다.


Partition Index
- Local Partition Index 와 Global Partition Index 로 구성된다.

Local Partition Index
- 파티션 테이블 내의 데이터로만 인덱스가 구성된다.
- Prefixed Index 와 Non-Prefixed Index 로 구성된다.
- 테이블 파티션을 재구성하면, Local Index 는 자동으로 Rebuild 되어 관리의 편의성이 있다.

create index dept_local_ix1 on dept(deptno) LOCAL;


Local Prefixed Index
- 파티션 컬럼이 인덱스 인 경우 (인덱스가 여러 컬럼일때 선두 컬럼)

Local Non-Prefixed Index
- 인덱스된 컬럼이 파티션 컬럼이 아닌 경우 (인덱스가 여러 컬럼일때, 선두 컬럼이 파티션 컬럼이 아닐 때)


Global Partition Index
- 파티션 테이블과는 별도로 인덱스 파티션 규칙으로 파티션을 구성한다.
- Non-Partition Table 에도 생성 가능하다.
- Prefixed Index 만 존재한다. (Non-Prefixed Index 불가)
- 테이블 파티션을 재구성하면, Index Unusable 상태가 되어, Rebuild 가 필요하다.

create index sales_global_ix1 on sales (cust_nm) GLOBAL
partition by hash (cust_nm)
partitions 4;



Index Unusable(IU) 상태
- Partition Index / Non-Partition Index 가 사용불가 상태가 되는 경우.
- IU 파티션을 사용하려고 하면 오류가 발생한다. IU 파티션을 Rebuild 해야 한다.
- 1. Direct Path Load 시 index 가 해당 테이블 data 보다 이전것인 경우
- 2. Alter Table Move Partiton : ROWID 를 변화시키는 작업
- 3. Alter Table Truncate Partition / Drop Partition 과 같은 테이블의 행을 지우는 작업
- 4. Alter Table Split Partition
- 5. Alter Index Split Partition



배치(Batch) 프로그램
- 일련의 대량 작업을 묶어서 일괄 처리

배치프로그램 튜닝
- 전체 처리속도 최적화에 목표
- Array Processing 을 활용
- 임시 테이블을 활용
- 병렬처리를 적절히 활용. 단, 필요 이상의 병렬도(DOP) 를 지정하지 않아야 한다.

병렬처리
- 병렬 처리란 SQL문이 수행해야 할 작업 범위를 여러 개의 작은 단위로 나누어 여러 프로세스가 동시에 처리

QC(Query Coordinator)
- 병렬 SQL문을 발행한 세션
- 병렬 서버 집합을 할당
- 각 병렬 서버에게 작업을 할당
- 병렬로 처리하도록 사용자가 지시하지 않은 테이블은 QC가 처리

Intra-Operation Parallelism
- 병렬 처리에서 서로 배타적인 범위를 독립적으로 동시에 처리하는 것

Inter-Operation Parallelism
- 프로세스 간의 통신을 뜻한다.
- 다른 서버집합에 분배하거나 정렬된 결과를 QC에서 전송하는 작업을 병렬로 동시에 진행하는 것
- 병렬도(DOP) 의 2배의 프로세스가 필요하고, 병렬도(DOP) 의 제곱만큼 파이프 라인(테이블 큐)이 필요

S->P (Parallel from Serial)
- QC가 읽은 데이터를 테이블 큐를 통해 모든 병렬 서버 프로세스에게 전송
- BROADCAST 라고 표시하며, Inter-Operation Parallelism 에는 속하지 않는다.
- 데이터 크기가 작을수록 유리

P->S (Parallel to Serial)
- 각 병렬 서버 프로세스가 처리한 데이터를 QC에게 전송하는 것
- Inter-Operation Parallelism 에 속한다.
- order by가 있을 때는 QC(ORDER)
- order by가 없을 때는 QC(RANDOM)

P->P (Parallel to Parallel) 오퍼레이션
- 한 서버 집합이 반대편 서버 집합에 데이터를 전송하는 것
- 데이터를 재분배하는 오퍼레이션으로 데이터를 정렬하거나 그룹핑, 조인을 위한 동적으로 파티셔닝을 할 때 발생
- Inter-Operation Parallelism 에 속한다.

PCWP (Parallel Combined with Parent)
- 한 서버 집합이 현재 스텝과 그 부모 스텝을 모두 처리
- Intra-Operation Parallelism 에 속한다.

PCWC (Parallel Combined with Child)
- 한 서버 집합이 현재 스텝과 그 자식 스텝을 모두 처리
- Intra-Operation Parallelism에 속한다.


parallel_index
- 블록 기반 Granule : Full Table Scan 일때는 병렬도(DOP) 를 파티션 사이즈보다 크게 지정할 수 있다.
- 파티션 기반 Granule : Index Range Scan 일때는 병렬도(DOP) 를 파티션 사이즈만큼 지정해야 효율적이다.


Hint

PARALLEL
- 병렬 처리 하도록 한다.
- PARALLEL(table, dop)
- 반드시 FULL 힌트를 같이 사용한다.

PARALLEL_INDEX
- 인덱스 범위 스캔을 병렬로 처리
- PARALLEL_INDEX(table, index, /* dop */)
- 반드시 INDEX 또는 INDEX_FFS 힌트를 같이 사용한다.


group by / order by / hash join 을 사용하면, 병렬프로세스가 dop 의 2배 만큼 필요하다.



PQ_DISTRIBUTE
- 병렬 Join 시에 데이터 분배 방식을 직접 결정
- PQ_DISTRIBUTE(inner_table, outer_distribution, inner_distribution)

pq_distribute(inner_table, hash, hash)
- 병렬조인을 위해 해시함수를 사용하도록 지정
- 실행계획에 PX SEND HASH 가 나타난다.

pq_distribute(inner_table, broadcast, none)
- outer table 의 모든 데이터를 병렬서버에 전송
- outer table 의 데이터 크기가 작을때 사용
- 실행계획에 PX SEND BROADCAST 가 나타난다.

pq_distribute(inner_table, none, broadcast)
- inner table 의 모든 데이터를 병렬서버에 전송
- inner table 의 데이터 크기가 작을때 사용
- 실행계획에 PX SEND BROADCAST 가 나타난다.

pq_distribute(inner_table, partition, none)
- Partial Partition-wise Join
- inner table 이 조인 키 컬럼에 대해 파티셔닝 돼 있을 때 사용
- 실행계획에 PX SEND PARTITION 이 나타난다.

pq_distribute(inner_table, none, partition)
- Partial Partition-wise Join
- outer table 이 조인 키 컬럼에 대해 파티셔닝 돼 있을 때 사용
- 실행계획에 PX SEND PARTITION 이 나타난다.

pq_distribute(inner_table, none, none)
- Full Partition-wise Join
- join 되는 두 테이블 모두 Join Key 컬럼으로 파티션되어 있는 경우에 사용
- 실행계획에 PX PARTITION RANGE ALL 이 나타난다.
- 하나의 서버 집합만 필요.



ROWNUM
- 병렬처리 쿼리에 ROWNUM 을 사용하면, 병렬프로세스에 병목이 발생한다.

rownum 을 row_number() 바꿔서 사용해야 한다.

select rownum() as 일련번호, 주문일자, 순번 from ( select /*+ parallel(t_order 4) 주문일자, 순번 from t_order order by 주문일자, 순번 ) => select /*+ parallel(t_order 4) row_number() over(order by 주문일자, 주문순번) as 일련번호, 주문일자, 순번 from t_order

데이터 복제 기법


데이터 복제를 이용한 소계 구하기
- 카티션 곱(Cartesian Product) 을 이용

select rownum as no from dual connect by level <= 2;

1 2

select deptno 부서번호 , decode(no, 1, to_char(empno), 2, '부서계') 사원번호 , sum(sal) 급여합 , round(avg(sal)) 급여평균 from emp e, (select rownum as no from dual connect by level <= 2) group by deptno, no, decode(no, 1, to_char(empno), 2, '부서계') order by 1, 2;

unpivot (열데이터를 행데이터로 변환)

SELECT * FROM 테이블 UNPIVOT (      컬럼별칭(값) FOR 컬럼별칭(열) IN (피벗열명 AS '별칭', ... )

select 고객번호, 고객명, 연락처구분, 연락처번호 from 고객 unpivot (    연락처번호 for 연락처구분 in (        집전화번호 as '집전화번호'        ,  사무실전화번호 as '사무실전화번호'        , 휴대폰번호 as '휴대폰번호'    ) ) where 고객구분코드 = 'VIP'

pivot (행데이터를 열데이터로 변환)

SELECT * FROM 테이블 PIVOT ( 집계함수 FOR 대상필드 IN (필드값 목록) )

select C.고객번호, C.고객명, D.집, D.사무실, D.휴대폰 from 고객 C     , 고객연락처 pivot (         min(연락처번호) for 연락처구분코드 in ( 'HOM' as '집', 'OFC' as '사무실', 'MBL' as '휴대폰' )     ) D where C.고객번호 = D.고객번호 and C.고객구분코드 = 'VIP'

LISTAGG

행을 문자열로 합치는 기능 (pivot)

LISTAGG([합칠 컬럼명], [구분자]) WITHIN GROUP(ORDER BY [정렬 컬럼명])
(하단에 group by 사용시)

LISTAGG([합칠 컬럼명], [구분자]) WITHIN GROUP(ORDER BY [정렬 컬럼명]) OVER(PARTITION BY [그루핑 컬럼명])
(하단에 group by 미사용시)





분석함수 : 누적값

sum(매출금액) over(partition by 지점코드 order by 판매월
    range between unbounded preceding and current row)
) 누적매출

부등호조인 : 누적값

select A.지점코드, A.판매월, min(A.매출금액) 매출금액
   , sum(B.매출금액) 누적매출
from 월별지점매출 A, 월별지점매출 B
where A.지점코드 = B.지점코드
   and A.판매월 >= B.지점코드
group by A.지점코드, A.판매월
order by A.지점코드, A.판매월



with 구문 (CTE)

materialize 방식
- 임시 테이블을 사용하여 반복 재사용, 실행계획에 TEMP TABLE TRANSFORMATION 이 나타남.
- 사이즈가 큰 데이터를 읽어 GROUP BY, 조인 등으로 집합크기를 줄일 수 있을때 사용.
- /*+ materialize */

inline 방식
- 참조 횟수만큼 런타임에서 반복 실행됨
- /*+ inline */