2) Cache Recovery (= Instance Recovery) 버퍼 캐시에만 적용한 변경사항을 데이터 파일에 기록하지 않은 상태에서 인스턴스가 비정상 종료되면, 작업내용을 모두 잃게되는데, 이러한 트랜잭션 데이터 유실에 대비하기 위해 Redo 로그를 남긴다.

3) Fast Commit 로그는 Append 방식으로 기록 -> 버퍼 캐시 블록과 데이터 파일 블록 간 동기화는 DBWR, CheckPoint를 이용해 나중에 일괄(Batch) 수행

7. Redo 메커니즘 1) Log Force at commit: 최소한 커밋 시점에는 로그를 데이터파일에 안전하게 기록 2) Fast Commit: Redo 로그를 믿고 빠르게 커밋 완료 3) Write Ahead Logging: 버퍼캐시 블록 갱신 전에 먼저 Redo 엔트리를 로그 버퍼에 기록

Merge 힌트 없이 옵티마이저가 View Merging 실행

select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));


select /*+ gather_plan_statistiscs */ d.dname, avg_sal_dept
from dept d
        ,(select deptno, avg(sal) avg_sal_dept
          from emp
          group by deptno) e
where d.deptno = e.deptno
and   d.loc = 'CHICAGO'

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | E-Rows |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |        |       |       |          |
|   1 |  HASH GROUP BY                |                |      3 |   983K|   983K|  521K (0)|
|   2 |   NESTED LOOPS                |                |      5 |       |       |          |
|   3 |    NESTED LOOPS               |                |      5 |       |       |          |
|*  4 |     TABLE ACCESS FULL         | DEPT           |      1 |       |       |          |
|*  5 |     INDEX RANGE SCAN          | EMP_DEPTNO_IDX |      5 |       |       |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| EMP            |      5 |       |       |          |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("D"."LOC"='CHICAGO')
5 - access("D"."DEPTNO"="DEPTNO")

no_merge 힌트로 View Merging 방지했을 때 조건절 Pushing 시도한 옵티마이저의 트레이스 결과

select /*+ gather_plan_statistiscs */ d.dname, avg_sal_dept
from dept d
        ,(select /*+ no_merge */ deptno, avg(sal) avg_sal_dept
          from emp
          group by deptno) e
where d.deptno = e.deptno
and   d.loc = 'CHICAGO'

-------------------------------------------------------------------
| Id  | Operation                       | Name           | E-Rows |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                |        |
|   1 |  NESTED LOOPS                   |                |      1 |
|*  2 |   TABLE ACCESS FULL             | DEPT           |      1 |
|   3 |   VIEW PUSHED PREDICATE         |                |      1 |
|*  4 |    FILTER                       |                |        |
|   5 |     SORT AGGREGATE              |                |      1 |
|   6 |      TABLE ACCESS BY INDEX ROWID| EMP            |      5 |
|*  7 |       INDEX RANGE SCAN          | EMP_DEPTNO_IDX |      5 |
-------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("D"."LOC"='CHICAGO')
4 - filter(COUNT(*)>0)
7 - access("DEPTNO"="D"."DEPTNO")

DB는 데이터를 메모리 (버퍼 캐시)에서 바꾸고, 나중에 디스크 (데이터 파일)에 반영한다. 그런데 디스크 반영 전에 장애가 발생하면 메모리 변경분이 날아간다. 이걸 복구하려고, 변경 내용을 Redo 로그에 먼저 기록해둔다.

1. Write ahead Logging (WAL, 선행 로그 기록)

데이터 블록을 디스크에 쓰기 전에, 그 변경에 대한 REDO로 먼저 디스크에 기록하는 원칙

WAL 순서: REDO 로그 먼저 기록 -> 데이터 블록 -> 장애 나도 REDO로 복구

즉, WAL 덕분에 오라클은 REDO만 안전하게 기록됐으면, 데이터 블록은 나중에 천천히 써도 된다!

2. Log Force at Commit (커밋 시 로그 강제 기록)

트랜잭션이 커밋될 때, 그 트랜잭션의 REDO 로그를 반드시 디스크(REDO 로그 파일)에 기록 완료한 뒤에야 커밋 성공을 알려주는 원칙

사용자 커밋 요청 -> LGWR가 해당 REDO를 로그 파일에 기록 (디스크 동기화) -> 기록 확인 완료 -> 커밋 성공 반환

3. Fast Commit

커밋 시 변경된 데이터 블록 전체를 디스크에 쓰지 않고, 가벼운 REDO 로그만 기록해서 커밋을 빠르게 끝내는 메커니즘

변경된 데이터 블록(dirty block)은 버퍼 캐시에 그대로 두고, 나중에 DBWR가 모아서 디스크에 쓴다.

커밋은 REDO 기록만으로 즉시 끝나니 빠름

4. Delayed Block Cleanout

커밋 시 변경된 모든 블록의 상태 정보(트랜잭션 락 등)를 즉시 정리하지 않고, 나중에 그 블록을 읽을 때 정리하는 메커니즘

트랜잭션이 데이터를 바꾸면, 각 블록에 "이 행은 트랜잭션 T가 락 걸었음" 같은 정보(ITL, 트랜잭션 슬롯)가 기록된다. 커밋하면 원래 이 정보를 "커밋됨"으로 정리 (cleanout) 해야 하는데 많은 블록을 변경했다면! 많은 I/O 발생

-> 나중에 해당 블록을 읽을 때 정리!

• 파티션 부분
• 병렬처리
• 데이터베이스 아키텍처 (redo, undo 등)

문제풀 때 생각할 것들!

• 집합적 사고!
• 불필요한 조인 제거!
• 조인 순서 변경! (큰 테이블 먼저 조인하지 말고 필터된 애들 작은 테이블 고려!)
• 함수적 종속관계!

Index Skip Scan이 작동하기 위한 조건

• 최선두 컬럼에 대한 조건절이 누락된 경우
• Distinct Value가 적은 두 개의 선두컬럼이 모두 누락된 경우
• 최선두 컬럼은 입력하고 중간 컬럼에 대한 조건절이 누락된 경우
• 선두 컬럼이 범위검색 조건인 경우

IN 조건은 '=' 인가?

IN은 IN-List Iterator 방식으로 풀려야 '=' 조건

인덱스를 사용(Range Scan) 할 수 있으려면

• 인덱스 선두 컬럼이 조건절에!
• 인덱스 선두 컬럼에 대한 가공, 중간값 검색, 부정형 비교 등 X

주요 I/O 발생 지점이 테이블 액세스 단계이면

• 인덱스 컬럼 추가
• Batch I/O 활용: batch_table_access_by_rowid 힌트 활용
• 클러스터링 팩터 개선: 배치 프로그램 정렬 insert, cluster_by_rowid 힌트 활용
• 클러스터링 전략: IOT, Cluster
• Full Scan: 파티션, 병렬 처리 활용

주요 I/O 발생 지점이 인덱스 스캔 단계이면

• 인덱스 컬럼 순서 조정
• Skip Scan 활용
• 선분조건을 IN-List로 변환
• 선분조건을 IN 서브쿼리 또는 조인으로 변환
• 인덱스 컬럼 가공, 중간값 검색, 부정형 비교, OR조건/IN-List, 옵션조건에 대한 컴토 및 튜닝

소트 머지 조인의 특징

• 실시간 인덱스 생성
  • 양쪽 집합을 정렬한 다음에는 NL 조인과 같은 오퍼레이션
• 인덱스 유무에 영향을 받지 않음
• 양쪽 집합을 개별적으로 읽고 나서 조인
• 스캔 위주의 액세스 방식
  • 양쪽 소스 집합에서 정렬 대상 레코드를 찾는 작업은 인덱스를 이용해 Random 액세스 방식으로 처리될 수 있음

First 테이블에 인덱스가 있으면 소트 생략 가능

해시 조인 사용기준

• 해시 조인 성능을 좌우하는 두 가지 키 포인트
  • 한 쪽 테이블이 Hash Area에 담길 정도로 충분히 작아야
  • Build Input 해시 키 컬럼에 중복 값이 거의 없어야

해시 조인은 아래와 같은 상황에서 사용!

1. 수행빈도가 낮고
2. 쿼리 수행 시간이 오래 걸리는
3. 대용량 테이블을 조인할 때 (배치, DW, OLAP 성 쿼리)

조인 메소드 비교

• NL 조인에서 Join 과정은 신경써야할 Cost
• 소트 머지, 해시 조인의 PGA 내 조인은 무시할 만한 Cost

서브쿼리와 조인

서브쿼리를 그대로 둔 상태로 최적화하려면 -> 필터(Filter) 오퍼레이션: NL 조인 방식 + 조인 순서 고정 *FILTER = NL 조인 *

Join Predicate Pushdown

• 메인 쿼리를 수행하면서 조인 조건절 값을 건건이 뷰 안으로 Pushing

/+ no_merge push_pred */

서브쿼리 Pushing

• Unnesting 되지 않은 서브쿼리는 항상 필터 방식! (메인 쿼리 기준 건건이 필터 (NL 방식)
• *- 서브쿼리 필터링을 먼저 처리해서 다음 단계로 넘어가는 로우 수를 클게 줄이면 성능 상 유리
• - /*+ NO_UNNEST PUSH_SUBQ ***/

조건절 이행

테이블간 조인 릴레이션을 기반으로, 한 테이블에 사용된 필터 조건을 반대편 테이블에 대한 필터 조건으로도 사용하는 경우

개념

NDV = 컬럼 값 종류 개수 (Number of Distinct Values)

선택도 = 1/NDV

카디널리티 = 총 로우수 X 선택도 = 총 로우수 / NDV

"선택도가 낮다" = "카디널리티가 낮다" = "변별력이 좋다"

SQLD 파트

트랜잭션 ACID

• 원자성: 트랜잭션의 작업은 모두 수행되거나 모두 수행되지 않아야 함
• 일관성: 트랜잭션이 완료되면 데이터 무결성이 일관되게 보장되어야 함
• 고립성: 트랜잭션이 다른 트랜잭션으로부터 고립된 상태로 수행되어야 함
• 지속성: 트랜잭션이 완료되면 장애가 발생하더라도 변경 내용이 지속되어야 함

PK 제약 조건 VS UNIQUE 제약 조건

PK 제약 조건은 NULL을 허용하지 않고, UNIQUE 제약 조건은 NULL을 허용한다. (단, DBMS마다 다름)

CHAR 타입 vs VARCHAR2 타입

CHAR 타입은 값의 크기가 데이터 타입의 크기보다 작으면 뒤쪽에 공백을 채워서 값을 저장하는 반면, VARCHAR2 타입은 입력한 값을 그대로 저장한다.

대기 이벤트

db file sequential read: 한 번에 한 블록씩 디스크에서 읽을 때 기다리는 대기 이벤트 (주로 인덱스를 통한 테이블 액세스)

db file scattered read: 여러 블록씩 (주로 테이블 풀스캔)

< 쿼리1 > (머징 전 - 인라인 뷰)
select *
from   (select * from emp  where job = 'SALESMAN') a,
       (select * from dept where loc = 'CHICAGO') b
where  a.deptno = b.deptno


< 쿼리2 > (머징 후 - 펼쳐진 형태)
select *
from   emp a, dept b
where  a.deptno = b.deptno
and    a.job = 'SALESMAN'
and    b.loc = 'CHICAGO'

쿼리1의 뷰 쿼리 블록은 액세스 쿼리 블록과의 머지 과정을 거쳐 쿼리2와 같은 형태로 변환되는데, 이를 '뷰 Merging'이라고 한다.

(힌트: merge, no_merge)

(2) 단순 뷰 Merging

조건절과 조인문만을 포함하는 단순 뷰는 no_merge 힌트를 사용하지 않는 한 언제든 Merging이 일어난다.

반면 group by 절이나 distinct 연산을 포함하는 복합 뷰는 파라미터 설정 또는 힌트 사용에 의해서만 뷰 Merging이 가능하다.

또한, 집합 연산자, connect by, rownum 등을 포함하는복합 뷰는 아예 뷰 Merging이 불가능하다.

(3) 복합 뷰 Merging

_complex_view_merging 파라미터를 true로 설정하더라도 아래 항목들을 포함하는 복합뷰는 Merging 될 수 없다.

• 집합(set) 연산자 (union, union all, intersect, minus)
• connect by절
• ROWNUM pseudo 컬럼
• select-list에 집계 함수 (avg, count, max, min, sum) 사용 : group by 없이 전체를 집계하는 경우를 말함
• 분석 함수

select d.dname, avg_sal_dept
from   dept d,
       (select deptno, avg(sal) avg_sal_dept
        from   emp
        group by deptno) e
where  d.deptno = e.deptno
and    d.loc = 'CHICAGO'


-- 복합 뷰 머징 후 변환된 쿼리
select d.dname, avg(sal)
from   dept d, emp e
where  d.deptno = e.deptno
and    d.loc = 'CHICAGO'
group by d.rowid, d.dname

-- 뷰 Merging이 일어난다면 두 쿼리는 똑같이 아래 실행계획을 사용한다.

Execution Plan
--------------------------------------------------------------------------
0        SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1 Bytes=28)
1    0    HASH (GROUP BY) (Cost=5 Card=1 Bytes=28)
2    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)
3    2      NESTED LOOPS (Cost=4 Card=5 Bytes=140)
4    3       TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=1 Card=5 Bytes=25)
5    3       INDEX (RANGE SCAN) OF 'EMP_IDX' (INDEX) (Cost=0 Card=5)

위 쿼리가 뷰 Merging을 통해 얻을 수 있는 이점은, dept.loc = 'CHICAGO'인 데이터만 선택해서 조인하고, 조인에 성공한 집합만 group by 한다는 데에 있다.

(5) Merging 되지 않는 뷰의 처리방식

뷰 Merging을 시행했을 때 오히려 비용이 더 증가한다고 판단되거나 부정확한 결과집합이 만들어질 가능성이 있을 때 옵티마이저는 뷰 Merging을 포기한다.

뷰 Merging이 이루어지지 않았을 땐 2차적으로 조건절 Pushing을 시도한다.

하지만 이마저도 실패한다면 뷰 쿼리 블록을 개별적으로 최적화하고, 거기서 생성된 서브플랜을 전체 실행계획을 생성하는 데 사용한다.

NO_MERGE 시 실행계획에 VIEW 오퍼레이션이 나타난다!

-- order by 절을 추가하고 다시 수행해보자
select /*+ leading(d) use_nl(e) */ *
from   dept d,
       (select /*+ NO_MERGE */ * from emp ORDER BY ENAME) e
where  e.deptno = d.deptno


Call      Count  CPU Time  Elapsed Time    Disk    Query   Current   Rows
-------   -----  --------  ------------    ----    -----   -------   ----
Parse         1     0.000         0.002       0        0         0      0
Execute       1     0.000         0.000       0        0         0      0
Fetch         2     0.000         0.000       0        7         0     14
-------   -----  --------  ------------    ----    -----   -------   ----
Total         4     0.000         0.003       0        7         0     14


Rows    Row Source Operation
-----   ---------------------------------------------------------------
   14   NESTED LOOPS (cr=7 pr=0 pw=0 time=169 us)
    4    TABLE ACCESS FULL DEPT (cr=4 pr=0 pw=0 time=87 us)
   14    VIEW (cr=3 pr=0 pw=0 time=208 us)
   56     SORT ORDER BY (cr=3 pr=0 pw=0 time=255 us)
   14      TABLE ACCESS FULL EMP (cr=3 pr=0 pw=0 time=61 us)

서브쿼리는 하나의 SQL 문장 내에서 괄호로 묶인 별도의 쿼리 블록을 말한다.

1. 인라인 뷰: from 절에 나타나는 서브쿼리
2. 중첩된 서브쿼리: 결과집합을 한정하기 위해 where 절에 사용된 서브쿼리
3. 스칼라 서브쿼리: 한 레코드당 정확히 하나의 컬럼 값만을 리턴 (주로 select 절)

옵티마이저는 쿼리 블록 단위로 최적화를 수행하는데, 쿼리 전체의 최적화를 위해 먼저 서브쿼리를 풀어내야 한다.

서브쿼리를 풀어내는 두 가지 쿼리 변환 중 '서브쿼리 unnesting'은 중첩된 서브쿼리와 관련 있고, '뷰 Merging'은 인라인 뷰와 관련 있다.

(2) 서브쿼리 Unnesting의 의미

'nest' : '상자 등을 차곡차곡 포개넣다' = '중첩'

따라서 서브쿼리 Unnesting의 의미는 중첩된 서브쿼리를 풀어내는 것을 말한다.

1. 동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화 한다. 이를 '서브쿼리 Unnesting' 이라고 한다.

2. 서브쿼리를 Unnesting하지 않고 원래대로 둔 상태에서 최적화한다. 메인쿼리와 서브쿼리를 별도의 서브플랜으로 구분해 각각 최적화를 수행하며, 이때 서브쿼리에 필터 오퍼레이션이 나타난다.

(3) 서브쿼리 Unnesting의 이점

서브쿼리를 메인쿼리와 같은 레벨로 풀어낸다면 다양한 액세스 경로와 조인 메소드를 평가할 수 있다. -> 더 나은 실행계획을 찾을 가능성이 높아진다.

관련 힌트

• unnest: 서브쿼리를 Unnesting 함으로써 조인방식으로 최적화하도록 유도한다.
• no_unnest: 서브쿼리를 그대로 둔 상태에서 필터 방식으로 최적화하도록 유도한다.

(4) 서브쿼리 Unnesting 기본 예시

select * from emp
where deptno in (select /*+ no_unnest */ deptno from dept)


SELECT STATEMENT
    FILTER
        TABLE ACCESS FULL    EMP
        INDEX UNIQUE SCAN   DEPT_PK

Unnesting 하지 않은 서브쿼리를 수행할 때는 메인 쿼리에서 읽히는 레코드마다 값을 넘기면서 서브쿼리를 반복 수행한다.

(5) Unnesting된 쿼리의 조인 순서 조정

Unnesting에 의해 일반 조인문으로 변환된 후에는 emp, dept 어느 쪽이든 드라이빙 집합으로 선택될 수 있다. (옵티마이저의 판단)

*메인 쿼리 집합 먼저 드라이빙 *

select /*+ leading(emp) */ * from emp
where deptno in (select /*+ unnest */ deptno from dept)

*서브쿼리 쪽 먼저 드라이빙 *

select /*+ ordered */ * from emp
where deptno in (select /*+ unnest */ deptno from dept)

10g부터는 쿼리 블록마다 이름을 지정할 수 있는 qb_name 힌트가 제공된다.

select /*+ leading(dept@qb1) */ * from emp
where deptno in (select /*+ unnest qb_name(qb1) */ deptno from dept)

(6) 서브쿼리가 M쪽 집합이거나 Nonunique 인덱스일 때

만약 서브쿼리 쪽 테이블이 조인 컬럼에 PK/Unique 제약 또는 Unique 인덱스가 없다면, 일반 조인문처럼 처리했을 때 어떻게 될까?

select * from dept
where deptno in (select deptno from emp)

해당 서브쿼리의 emp 테이블의 deptno 컬럼에는 Unique 인덱스가 없다. 

-> 

select *
from (select deptno from emp) a, dept b
where b.deptno = a.deptno 

위 쿼리는 M쪽 집합인 emp 테이블 단위의 결과집합이 만들어지므로 결과에 오류가 생긴다. 

select * from emp
where deptno in (select deptno from dept)

위 쿼리는 M쪽 집합을 드라이빙해 1쪽 집합을 필터링 하도록 작성되었으므로 조인문으로 바꾸더라도 결과에 오류가 생기지 않는다. 

but 제약조건이나 Unique 인덱스가 없으면 옵티마이저가 테이블 간의 관계를 모르기 때문에 일반 조인문으로 쿼리 변환을 하지 않는다.

이럴 때 옵티마이저는 두 가지 방식 중 하나를 선택하는데, Unnesting 후 어느 쪽 집합이 먼저 드라이빙 되느냐에 따라 달라진다.

• 1쪽 집합임을 확신할 수 없는 서브쿼리 쪽 테이블이 먼저 드라이빙 된다면, 먼저 sort unique 오퍼레이션을 수행함으로써 1쪽 집합으로 만든 다음에 조인한다.
• 메인 쿼리 쪽 테이블이 드라이빙 된다면 세미 조인 방식으로 조인한다.

Sort Unique 오퍼레이션 수행

select * from emp
where deptno in (select deptno from dept) ;

-> dept에 제약 조건 X인 상태 

SQL 트레이스에 SORT UNIQUE 발생 

select b.*
from (select /*+ no_merge */ distinct deptno from dept order by deptno) a, emp b
whrere b.deptno = a.deptno

--> 힌트 사용 시 
(9i)
select /*+ ordered use_nl(emp) */ * from emp
where deptno in (select /*+ unnest */ deptno from dept)

(10g)
select /*+ leading(dept@qb1) use_nl(emp) */ * from emp
where deptno in (select /*+ unnest qb_name(qb1) */ deptno from dept)

세미 조인 방식으로 수행

select * from emp
where deptno in (select deptno from dept)

SELECT ~
    NESTED LOOPS SEMI
        TABLE ACCES FULL (EMP)
        INDEX RANGE SCAN (DEPT_IDX)

NL 세미 조인으로 수행할 때는 sort unique 오퍼레이션을 수행하지 않고도 결과집합이 M쪽 집합으로 확장되는 것을 방지하는 알고리즘을 사용한다.

Outer 테이블의 한 로우가 Inner 테이블의 한 로우와 조인에 성공하는 순간 진행을 멈추고 Outer 테이블의 다음 로우를 계속 처리하는 방식

힌트 사용 Unnesting한 다음에 메인 쿼리 쪽 테이블이 드라이빙 집합으로 선택되도록 한다.

select /*+ leading(emp) */ * from emp
where deptno in (select /*+ unnest nl_sj */ deptno from dept)

(7) 필터 오퍼레이션과 세미조인의 캐싱 효과

옵티마이저가 쿼리 변환을 수행하는 이유는, 전체적인 시각에서 더 나은 실행계획을 수립할 가능성을 높이는 데에 있다.

*서브쿼리를 Unnesting해 조인문으로 바꾸고 나면 조인 방식, 순서도 자유롭게 선택할 수 있다. *

메인 쿼리를 수행하면서 건건이 서브쿼리를 반복 수행하는 단순한 필터 오퍼레이션을 사용할 수 밖에 없다. 그래도 오라클은 필터 최적화 기법을 갖고 있는데, 서브쿼리 수행 결과를 버리지 않고 내부 캐시에 저장하고 있다가 같은 값이 입력되면 저장된 값을 출력하는 방식이다.

select count(*) from t_emp t
where exists (select /*+ no_unnest */ 'x' from dept
              where deptno = t.deptno and loc is not null)


Call      Count  CPU Time  Elapsed Time    Disk    Query   Current    Rows
-------   -----  --------  ------------    ----    -----   -------    ----
Parse         1     0.000         0.000       0        0         0       0
Execute       1     0.000         0.000       0        0         0       0
Fetch         2     0.000         0.003       0       18         0       1
-------   -----  --------  ------------    ----    -----   -------    ----
Total         4     0.000         0.003       0       18         0       1


Rows    Row Source Operation
-----   ---------------------------------------------------------------------
    1   SORT AGGREGATE (cr=18 pr=0 pw=0 time=2854 us)
 1400    FILTER (cr=18 pr=0 pw=0 time=25325 us)
 1400     TABLE ACCESS FULL T_EMP (cr=12 pr=0 pw=0 time=7049 us)
    3     TABLE ACCESS BY INDEX ROWID DEPT (cr=6 pr=0 pw=0 time=122 us)
    3      INDEX UNIQUE SCAN DEPT_PK (cr=3 pr=0 pw=0 time=55 us) (Object ID 57571)

NL 세미 조인의 캐싱 효과

select count(*) from t_emp t
where exists (select /*+ unnest nl_sj */ 'x' from dept
              where deptno = t.deptno and loc is not null)


Call      Count  CPU Time  Elapsed Time    Disk    Query   Current    Rows
-------   -----  --------  ------------    ----    -----   -------    ----
Parse         1     0.000         0.000       0        0         0       0
Execute       1     0.000         0.000       0       17         0       1
Fetch         2     0.000         0.001       0        0         0       0
-------   -----  --------  ------------    ----    -----   -------    ----
Total         4     0.000         0.001       0       17         0       1


Rows    Row Source Operation
-----   ---------------------------------------------------------------------
    1   SORT AGGREGATE (cr=17 pr=0 pw=0 time=15464 us)
 1400    NESTED LOOPS SEMI (cr=17 pr=0 pw=0 time=4220 us)
 1400     TABLE ACCESS FULL T_EMP (cr=12 pr=0 pw=0 time=73 us)
    3     TABLE ACCESS BY INDEX ROWID DEPT (cr=5 pr=0 pw=0 time=31 us) (Object ID 57571)
    3      INDEX UNIQUE SCAN DEPT_PK (cr=2 pr=0 pw=0 time=...)

(8) Anti 조인

not exists, not in 서브쿼리도 Unnesting 하지 않으면 아래와 같이 필터 방식으로 처리된다.

select * from dept d
where not exists
  (select /*+ no_unnest */ 'x' from emp where deptno = d.deptno)


| Id | Operation           | Name          | Rows | Bytes | Cost | (%CPU) |
|----|---------------------|---------------|------|-------|------|--------|
|  0 | SELECT STATEMENT    |               |    3 |    60 |    5 |   (0)  |
|* 1 |  FILTER             |               |      |       |      |        |
|  2 |   TABLE ACCESS FULL | DEPT          |    4 |    80 |    3 |   (0)  |
|* 3 |   INDEX RANGE SCAN  | EMP_DEPTNO_IDX|    2 |     6 |    1 |   (0)  |

기본 처리루틴은 exists 필터와 동일하며, 조인에 성공하는 레코드가 하나도 없을 때만 결과집합에 포함시킨다는 점이 다르다.

• exists 필터: 조인에 성공하는 (서브) 레코드를 만나는 순간 결과집합에 담고 다른 (메인) 레코드로 이동
• not exists 필터: 조인에 성공하는(서브) 레코드를 만나는 순간 버리고 다음 (메인) 레코그로 이동한다. 조인에 성공하는 (서브) 레코드가 하나도 없을 때만 결과집합에 담는다.

(9) 집계 서브쿼리 제거

(10) Pushing 서브쿼리

앞에서 설명한 것처럼 Unnesting 되지 않은 서브쿼리는 항상 필터 방식으로 처리되며, 대개 실행계획 상에서 맨 마지막 단계에 처리된다.

Pushing 서브쿼리는 실행계획 상 가능한 앞 단계에서 서브쿼리 필터링이 처리되도록 강제하는 것을 말한다. (push_subq 힌트)

Pushing 서브쿼리는 Unnesting 되지 않은 서브쿼리에만 작동한다. -> push_subq 힌트는 항상 no_unnest 힌트와 같이 기술하는 것이 올바른 방법

-- 오라클 10g에서 push_subq 힌트 사용하기
select /*+ leading(e1) use_nl(e2) */ sum(e1.sal), sum(e2.sal)
from   emp1 e1, emp2 e2
where  e1.no = e2.no
and    e1.empno = e2.empno
and    exists (select /*+ no_unnest push_subq */ 'x' from dept
               where deptno = e1.deptno
               and   loc = 'NEW YORK')

서브 엔진으로서 Query Transformer가 해당 역할을 담당한다.

즉, 쿼리 변환은 쿼리 옵티마이저가 SQL을 분석해 의미적으로 동일하면서도 더 나은 성능이 기대되는 형태로 재작성하는 것을 말한다.

쿼리 변환의 종류

1. 서브쿼리 Unnesting
2. 뷰 Merging
3. 조건절 Pushing
4. 조건절 이행
5. 공통 표현식 제거
6. Outer 조인을 Inner 조인으로 변환
7. 실체화 뷰 쿼리로 재작성
8. Star 변환
9. Outer 조인 뷰에 대한 조인 조건 Pushdown
10. OR-expansion

옵티마이저가 SQL을 최적화할 때 많은 일을 수행한다.

• 테이블, 컬럼, 인덱스 구성에 관한 기본 정보
• 오브젝트 통계: 테이블, 인덱스, 컬럼 통계
• 시스템 통계: CPU 속도, Single, Multi Block I/O 속도 등
• 옵티마이저 관련 파라미터

하나의 쿼리를 수행하는 데 있어 후보군이 될만한 무수히 많은 실행경로를 도출하고, 딕셔너리와 통계정보를 읽어 각각에 대한 효율성을 판단해야 하므로 하드 파싱 과정에 많은 CPU 자원을 사용한다.

24. Static vs Dynamic SQL

Java 언어는 Static SQL을 지원하지 않는다.

쿼리 툴에서 수행하는 SQL은 모두 Dynamic SQL이다.

Sort Area 크기 조정을 통한 튜닝의 핵심은, 디스크 소트가 발생하지 않도록 하는 것을 1차 목표로 삼고 불가피할 때는 Onepass 소트로 처리되도록 하는 데에 있다.

(1) PGA 메모리 관리 방식의 선택

데이터 정렬, 해시 조인, 비트맵 머지, 비트맵 생성 등을 위해 사용하는 메모리 공간을 'Work Area'라고 부르며 파라미터를 통해 조정한다.

기존에 수동으로 관리했지만 9i 부터는 '자동 PGA 메모리 관리' 기능으로 자동 관리된다.

DB 관리자는 pga_aggregate_target 파라미터를 통해 인스턴스 전체적으로 이용 가능한 PGA 메모리 총량을 지정하기만 하면 된다.

시스템 또는 세션 레벨에서 '수동 PGA 메모리 관리' 방식으로 전환할 수 있다.

특히, 트랜잭션이 거의 없는 야간에 대량의 배치 Job을 수행할 때는 수동 방식으로 변경하고 직접 크기를 조정하는 것이 효과적일 수 있다.

(2) 자동 PGA 메모리 관리 방식 하에서 크기 결정 공식

PGA는 자동 PGA 메모리 관리 기능을 사용한다고 해서 pga_aggregate_target 크기만큼의 메모리를 미리 할당해 두지는 않는다. 이 파라미터는 workarea_size_policy를 auto로 설정한 모든 프로세스들이 할당받을 수 있는 Work Area의 총량을 제한하는 용도로 사용된다.

(4) 적정 크기

pga_aggregate_target 파라미터 설정의 적정 크기?

오라클의 권고하는 값

• OLTP 시스템: (Total Physical Memory X 80%) X 20%
• DSS 시스템: (Total Physical Memory X 80%) X 50%

(5) Sort Area 할당 및 해제

자동 PGA 메모리 관리 방식이 도입되면서부터는 프로세스가 더이상 사용하지 않는 공간을 즉각 반환함으로써 다른 프로세스가 사용할 수 있도록 한다.

(1) 소트를 완료하고 나서 데이터 가공하기

select lpad(상품번호, 30) || lpad(상품명, 30)
from   주문상품
where ~
order by 상품번호

vs

select lpad(상품번호, 30) || lpad(상품명, 30)
from (
    select 상품번호, 상품명
    from 주문상품
    where ~ 
    order by 상품번호
)

1번 SQL은 레코드당 60(30+30) 바이트로 가공된 결과치를 Sort Area에 담는다. 반면 2번 SQL은 가공되지 않은 상태로 정렬을 완료하고 나서 최종 출력할 때 가공하므로 1번 SQL에 비해 Sort Area를 훨씬 적게 사용한다.

(2) Top-N 쿼리

Top-N 쿼리 형태로 작성하면 소트 연산(= 값 비교) 횟수를 최소화함은 물론 Sort Area 사용량을 줄일 수 있다.

select * from (
    select 거래일시, 체결건수...
    from   시간대별종목거래
    where  종목코드 = 'A'
    and    거래일시 >= '20260717'
    order by 거래일시
)
where rownum <= 10

SELECT ~
    COUNT (STOPKEY)
        VIEW
            TABLE ACCESS (BY INDEX ROWID) ~
                INDEX (RANGE SCAN) OF ~

[종목코드 + 거래일시] 순으로 구성된 인덱스가 존재한다면 해당 인덱스를 통해 order by 연산을 대체할 수 있다.

rownum 조건을 사용해 N건에서만 멈추도록 실행

Top-N 쿼리의 소트 부하 경감 원리

rownum <= 10이면 우선 10개 레코드를 담을 배열을 할당하고, 처음 읽은 10개 레코드를 정렬된 상태로 담는다.

이후 읽는 레코드에 대해서는 맨 우측에 있는 값(가장 큰 값)과 비교해서 그보다 작은 값이 나타날 때만 배열 내에서 다시 정렬을 시도한다. 이 방식으로 처리하면 전체 레코드를 정렬하지 않고도 10개 레코드를 정확히 찾아낼 수 있다.

(3) 분석함수에서의 Top-N 쿼리

window sort 시에도 rank()나 row_number()를 쓰면 Top-N 쿼리 알고리즘이 작동해 max() 등 함수를 쓸 때보다 소트 부하를 경감시켜 준다.

(1) Sort Order By 대체

select custid, name, resno, status, tell
from customer
where region = 'A'
order by custid

[region + custid] 순으로 구성된 인덱스를 사용한다면 sort order by 연산을 대체할 수 있다.

(2) Sort Group By 대체

select region, avg(age), count(*)
from customer
group by region

-> SORT GROUP BY NOSORT

region이 선두 컬럼인 결합 인덱스나 단일 컬럼 인덱스를 사용한다면 sort group by 연산을 대체할 수 있다.

(3) 인덱스가 소트 연산을 대체하지 못하는 경우

select * from emp order by sal;

sal 컬럼을 선두로 갖는 인덱스가 있는데도 정렬을 수행하는 경우는

옵티마이저의 판단으로 인해 Index Scan을 하지 않고 Table Full Scan을 하는 경우!

또 다른 경우는 nulls first 구문!

select ~
from emp e
where deptno = 30
order by ename NULLS FIRST ;

-> SORT (ORDER BY)

단일 컬럼 인덱스일 때는 null 값을 저장하지 않지만 결합 인덱스일 때는 null 값을 가진 레코드를 맨 뒤쪽에 저장한다.

따라서 null 값부터 출력하려고 할 때는 인덱스를 이용하더라도 소트가 불가피하다.

PK 컬럼인 empno를 select-list에 포함하므로 두 집합간에는 중복 가능성이 전혀 없다. 따라서 UNION ALL을 사용해야 한다!

distinct를 사용하는 경우도 대부분 exists 서브쿼리로 대체함으로써 소트 연산을 없앨 수 있다.

select distinct 과금연월
from  과금
where 과금연월 <= :yyyymm
and   지역 like :reg || '%'

입력한 과금연월 이전에 발생한 과금 데이터를 모두 스캔하고 그 중 중복 값을 제거해서 결과를 출력하게 된다.

이는 각 월별로 과금이 발생한 적이 있는지 여부만 확인하면 되므로 쿼리를 아래처럼 바꿀 수 있다.

소량의 데이터만을 갖는 연월테이블을 먼저 드라이빙해 과금 테이블을 exists 서브쿼리로 필터링하는 방식이다.

select 연월
from  연월테이블 a
where 연월 <= :yyyymm
and exists (
    select 'x'
    from  과금
    where 과금연월 = a.연월
    and   지역 like :reg || '%'

)

exists 서브쿼리의 가장 큰 특징은, 메인 쿼리로부터 건건이 입력 받은 값에 대한 조건을 만족하는 첫 번째 레코드를 만나는 순간 ture를 반환하고 서브쿼리 수행을 마친다는 점이다.

Sort Aggregate는 전체 로우를 대상으로 집계를 수행할 때 나타나는데, 실제 소트가 발생하지는 않는다.

select sum(sal), max(sal), min(sal) from emp;

-> SORT AGGREGATE

0 sorts (memory)
0 sorts (disk)

(2) Sort Order By

데이터 정렬을 위해 order by 오퍼레이션을 수행할 때 나타난다.

select * from emp order by sal desc;

-> SORT ORDER BY

1 sorts (memory)
0 sorts (disk)

(3) Sort Group By

sorting 알고리즘을 사용해 그룹별 집계를 수행할 때 나타난다.

select deptno, job, sum(sal), max(sal), min(sal)
from emp
group by deptno, job
order by deptno, job ;

-> SORT GROUP BY

1 sorts (memory)
0 sorts (disk)

Hash Group By와 비교

order by절을 함께 명시하지 않으면 대부분 hash group by 방식으로 처리된다.

select deptno, job, sum(sal), max(sal), min(sal)
from emp
group by deptno, job ;

-> HASH GROUP BY

0 sorts (memory)
0 sorts (disk)

hash group by는 정렬을 수행하지 않고 해싱 알고리즘을 사용해 데이터를 그룹핑한다. 읽는 로우마다 group by 컬럼의 해시 값으로 해시 버킷을 찾아 그룹별로 집계항목을 갱신하는 방식이다.

*정렬된 group by 결과를 얻고자 한다면, 실행계획에 설령 'sort group by'라고 표시되더라도 반드시 order by를 명시해야 한다. *

(4) Sort Unique

Unnesting된 서브쿼리가 M쪽 집합이거나 Unique 인덱스가 없다면, 그리고 세미 조인으로 수행되지도 않는다면 메인 쿼리와 조인되기 전에 sort unique 오퍼레이션이 먼저 수행된다.

(5) Sort Join

소트 머지 조인을 수행할 때 나타난다.

select /*+ ordered use_merge(e) * / *
from dept d, emp e
where d.deptno = e.deptno ;

-> SORT JOIN 
   SORT JOIN

2 sorts (memory)
0 sorts (disk)

(6) Window Sort

분석함수를 수행할 때 나타난다.

select empno, ename, job, mgr, sal
    , avg(sal) over (partition by deptno)
from emp ;

-> WINDOW SORT

1 sorts (memory)
0 sorts (disk) 

01 소트 수행 원리

(1) 소트 수행 과정

SQL 수행 도중 데이터 정렬이 필요할 때면 오라클은 PGA 메모리에 Sort Area를 할당하는데, 그 안에서 처리를 완료할 수 있는지 여부에 따라 소트를 두 가지 유형으로 나눈다.

• 메모리 소트: 전체 데이터의 정렬 작업을 메모리 내에서 완료 (Internal Sort)
• 디스크 소트: 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우 (External Sort)

SGA -> PGA -> Temp Tablespace -> PGA

데이터 양이 많을 때는 정렬된 중간 결과집합을 Temp 테이블스페이스의 Temp 세그먼트에 임시 저장한다. Sort Area가 찰 때마다 Temp 영역에 저장해 둔 중간 단계의 집합을 'Sort Run' 이라고 부른다. Sort Run 생성을 마치면, 이를 다시 Merge해야 정렬된 최종 결과집합을 얻게 된다.

(3) Sort Area

Sort Area는 소트 오퍼레이션이 진행되는 동안 공간이 부족해질 때마다 청크(Chunk) 단위로 조금씩 할당한다.

Sort Area는 어떤 메모리 영역에 할당될까?

PGA -> 각 오라클 서버 프로세스는 자신만의 PGA 메모리 영역을 할당받고, 이를 프로세스에 종속적인 고유 데이터를 저장하는 용도로 사용한다.

UGA -> 세션이 프로세스 개수보다 많아질 수 있는 구조로서, 하나의 프로세스가 여러 개 세션을 위해 일한다. 각 세션을 위한 독립적인 메모리 공간이 필요해지는데, 이를 UGA (User Global Area)라고 한다.

CGA -> Call이 진행되는 동안에만 필요한 데이터는 CGA에 담는다.

Sort Area 할당 위치

Sort Area가 할당되는 위치는 SQL문 종류와 소트 수행 단계에 따라 다르다.

DML 문장은 하나의 Execute Call 내에서 모든 데이터 처리를 완료하며, Execute Call이 끝나는 순간 자동으로 커서가 닫힌다. 따라서 DML 수행 도중 정렬한 데이터를 Call을 넘어서까지 참조할 필요가 없으므로 Sort Area를 CGA에 할당한다.

Select문에서의 데이터 정렬은 상황에 따라 다르다. Fetch Call에서 사용되므로 마지막 소트를 위한 Sort Area는 UGA에 할당한다. 반면 마지막보다 앞선 단계에서 정렬된 데이터는 첫 번째 Fetch Call 내에서만 사용되므로 Sort Area를 CGA에 할당한다.

(4) 소트 튜닝 요약

소트 오퍼레이션은 메모리 집약적일뿐만 아니라 CPU 집약적이기도 하며, 데이터량이 많을 때는 디스크 I/O까지 발생시키므로 쿼리 성능을 좌우하는 가장 중요한 요소다.

따라서 될 수 있으면 소트가 발생하지 않도록 SQL을 작성해야 하고, 소트가 불가피하다면 메모리 내에서 수행을 완료할 수 있도록 해야 한다.

• 데이터 모델 측면에서의 검토
• 소트가 발생하지 않도록 SQL 작성
• 인덱스를 이용한 소트 연산 대체
• Sort Area를 적게 사용하도록 SQL 작성
• Sort Area 크기 조정

PARTITION BY RANGE(주문일시)
( PARTITION P1 VALUES LESS THAN (TO_DATE('20200701', 'YYYYMMDD') )
, PARTITION P2 VALUES LESS THAN (TO_DATE('20210101', 'YYYYMMDD') )
, ...
, PARTITION PX VALUES LESS THAN ( MAXVALUE ) )

14. 다시 풀어보자!

15. Unique 인덱스를 파티셔닝하려면, 파티션 키가 모두 인덱스 구성 컬럼이어야 한다!

22. 다시 풀어보자!

24, 25 -> 추후

파티션 Pruning은 하드파싱이나 실행 시점에 SQL 조건절을 분석하여 읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외시키는 기능이다.

파티션 테이블에 대한 쿼리나 DML을 수행할 때 극적인 성능 개선을 가져다 주는 핵심 원리가 파티션 Pruing에 있다!

(1) 기본 파티션 Pruning

• 정적 파티션 Pruning: 파티션 키 컬럼을 상수 조건으로 조회하는 경우에 작동하며, 액세스할 파티션이 쿼리 최적화 시점에 미리 결정되는 것이 특징이다.

실행계획의 Pstart, Pstop 컬럼에는 액세스할 파티션 번호가 출력된다.

• 동적 파티션 Pruning: 파티션 키 컬럼을 바인드 변수로 조회하면 쿼리 최적화 시점에는 액세스할 파티션을 미리 결정할 수 없다. 실행 시점이 돼서야 사용자가 입력한 값에 따라 결정되며, 'KEY'로 표시된다.

  NL 조인할 때도 Inner 테이블이 조인 컬럼 기준으로 파티셔닝 돼 있다면 동적 Pruning이 작동한다.

  파티션 컬럼에 IN-LIST 조건을 사용하면 KEY(I)라고 표시된다.

CREATE TABLE t ( key, no, data )
PARTITION BY RANGE (no) SUBPARTITION BY HASH (key) SUBPARTITIONS 16 (
    PARTITION p01 VALUES LESS THAN (11),
    PARTITION p02 VALUES LESS THAN (21),
    PARTITION p03 VALUES LESS THAN (31),
    PARTITION p04 VALUES LESS THAN (41),
    PARTITION p05 VALUES LESS THAN (51),
    PARTITION p06 VALUES LESS THAN (61),
    PARTITION p07 VALUES LESS THAN (71),
    PARTITION p08 VALUES LESS THAN (81),
    PARTITION p09 VALUES LESS THAN (91),
    PARTITION p10 VALUES LESS THAN (MAXVALUE)
)
AS
SELECT 
    LPAD(ROWNUM, 6, '0') AS key, 
    MOD(ROWNUM, 50) + 1 AS no, 
    LPAD(ROWNUM, 10, '0') AS data
FROM dual
CONNECT BY LEVEL <= 999999;

SELECT COUNT(*) FROM t WHERE no BETWEEN 30 AND 50;

-------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |    13 |       |       |
|   1 |  SORT AGGREGATE            |      |     1 |    13 |       |       |
|   2 |   PARTITION RANGE ITERATOR |      |  576K | 7323K |     3 |     5 |
|   3 |    PARTITION HASH ALL      |      |  576K | 7323K |     1 |    16 |
|   4 |     TABLE ACCESS FULL      | T    |  576K | 7323K |    33 |    80 |
-------------------------------------------------------------------------------------------------

-> 서브파티션 키에 대한 조건이 없어서 1~16 모두 스캔!

(2), (3) Skip

(4) SQL 조건절 작성 시 주의사항

like 보단 between!

파티셔닝은 테이블과 인덱스 데이터를 파티션 단위로 나누어 저장하는 것을 말한다

테이블을 파티셔닝하면 하나의 테이블일지라도 파티션 키에 따라 물리적으로는 별도의 세그먼트에 데이터가 저장되며, 인덱스도 마찬가지다.

파티셔닝이 왜 필요한가?

• 관리적 측면: 파티션 단위 백업, 추가, 삭제, 변경
• 성능적 측면: 파티션 단위 조회 및 DML 수행

테이블을 적당한 단위로 나누어 저장한다면 Full Scan 하더라도 일부 파티션 세그먼트만 읽고 멈출출 수 있다. 또한 파티셔닝과 병렬 처리가 만났을 때 그 효과가 배가된다.

파티셔닝도 클러스터, IOT와 마찬가지로 관련 있는 데이터가 흩어지지 않고 물리적으로 인접하도록 저장하는 클러스터링 기술에 속한다.

클러스터와 다른 점은 세그먼트 단위로 모아서 저장한다.

(1) 파티션 기본 구조

파티션 테이블

create table partition_table
partition by range(deptno) (
    partition p1 values less than (20)
  , partition p2 values less than (30)
  , partition p3 values less than (40)
)
as 
select * from emp ;

create index ptable_empno_idx on partition_table(empno) LOCAL;

파티셔닝은, 내부에 몇 개의 세그먼트를 생성하고 그것들이 논리적으로 하나의 오브젝트임을 메타 정보로 딕셔너리에 저장해 두는 것에 지나지 않는다.

파티션 테이블은 테이블과 세그먼트 관계가 1:M 관계다.

(2) Range 파티셔닝

create table 주문 (주문번호 number, 주문일자 varchar(2), 고객id ... ) 
partition by range (주문일자) (
    partition p2009_q1 values less than ('20090401')
  , partition p2009_q2 values less than ('20090701')
  , partition p2009_q3 values less than ('20091001')
  , partition p2009_q4 values less than ('20100101')
  .....
  , partition p9999_mx values less than (MAXVALUE)
);

파티셔닝 테이블에 값을 입력하면 각 레코드를 파티션 키 컬럼 값에 따라 분할 저장하고, 읽을 때도 검색 조건을 만족하는 파티션만 읽을 수 있어 이력성 데이터 조회 시 성능을 크게 향상 시켜준다.

(3) Hash 파티셔닝

파티션 키에 해시 함수를 적용한 결과 값이 같은 레코드를 같은 세그먼트에 저장해 두는 방식이며, 주로 고객ID 처럼 변별력이 좋고 데이터 분포가 고른 컬럼을 파티션 기준 컬럼으로 선정해야 효과적이다.

해시 알고리즘 특성상 등치(=) 조건 또는 IN-List 조건으로 검색할 때만 파티션 Pruning이 작동한다.

create table 고객 (고객id varchar2(5), 고객명 varchar2(10), ...)
partition by hash(고객id) partitions 4;

데이터가 모든 파티션에 고르게 분산돼 있다면, 각 파티션이 서로 다른 디바이스에 저장돼 있다면 병렬 I/O 성능을 극대화 할 수 있다.

동시 입력이 많은 대용량 테이블이나 인덱스에 발생하는 경합을 줄일 목적으로도 해시 파티셔닝을 사용한다. 대용량 거래 테이블일수록 DML 발생량이 많이 경합 발생 가능성도 그만큼 크다. 이럴 때 테이블을 해시 파티셔닝하면 세그먼트 헤더 블록에 대한 경합을 줄일 수 있다.

• Right Growing 인덱스도 동일

(4) 리스트 파티셔닝

사용자에 의해 미리 정해진 그룹핑 기준에 따라 데이터를 분할 저장하는 방식이다.

create table 인터넷매물 (물건코드 varchar2(5), 지역분류 varchar2(4), ...)
partition by list(지역분류) (
    partition p_지역1 values ('서울')
  , partition p_지역2 values ('경기', '인천')
  , ...
  , partition p_기타 values (DEFAULT) -> 기타 지역 
);  

리스트 파티셔닝은 단일 컬럼으로만 파티션 키를 지정할 수 있다.

(5) 결합 파티셔닝

결합 파티셔닝을 구성하면 서브 파티션마다 세그먼트를 하나씩 할당하고, 서브 파티션 단위로 데이터를 저장한다.

즉, 주 파티션 키에 따라 1차적으로 데이터를 분배하고, 서브 파티션 키에 따라 최종적으로 저장할 위치 (세그먼트)를 결정한다.

**[Range + 해시] 결합 파티셔닝 **

create table 주문 (주문번호 number, 주문일자 varchar2(8), 고객id varchar2(5), ...)
partition by range (주문일자)
subpartition by hash(고객id) subpartitions 8
( partition p2009_q1 values less than ('20090401')
, partition p2009_q2 values less than ('20090701')
...
, partition p9999_mx values less than (MAXVALUE) );

**[Range + 리스트] 결합 파티셔닝 **

CREATE TABLE 판매 (
    판매점    VARCHAR2(10),
    판매일자  VARCHAR2(8),
    ... 
)
PARTITION BY RANGE (판매일자)
SUBPARTITION BY LIST (판매점)
SUBPARTITION TEMPLATE
(
    SUBPARTITION lst_01 VALUES ('강남지점', '강북지점', '강서지점', '강동지점'),
    SUBPARTITION lst_02 VALUES ('부산지점', '대전지점'),
    SUBPARTITION lst_03 VALUES ('인천지점', '제주지점', '의정부지점'),
    SUBPARTITION lst_99 VALUES (DEFAULT)
)
(
    PARTITION p2009_q1 VALUES LESS THAN ('20090401'),
    PARTITION p2009_q2 VALUES LESS THAN ('20090701'),
    PARTITION p2009_q3 VALUES LESS THAN ('20091001'),
    PARTITION p2009_q4 VALUES LESS THAN ('20100101')
);

• 소량의 데이터 조회 시
• 대용량 데이터를 조회할 때는 부분범위처리가 가능한 상황에서 제한적으로 사용
• 조인 또는 스칼라 서브쿼리 형태로 변환
• 어쩔 수 없을 경우 함수를 쓰되 호출 횟수를 최소화

함수 호출 부하 해소 방안

• 페이지 처리 또는 부분범위처리 활용
• Decode 함수 또는 Case문으로 변환
• 뷰 머지 방지를 통한 함수 호출 최소화
• 스칼라 서브쿼리 캐싱 효과를 이용한 함수 호출 최소화
• Deterministic 함수의 캐싱 효과 활용
• 복잡한 함수 로직을 풀어 SQL로 구현

(1) 페이지 처리 또는 부분범위처리 활용

함수를 인라인 뷰 안쪽에 두면 조건에 맞는 전체 레코드 수만큼 호출되고 정렬까지 발생한다.

해법은 맨 바깥 select-list로 끌어올린다. 그러면 order by + rownum 페이지 필터를 통과한 최종 10건에만 함수가 호출된다!

--  함수를 맨 바깥으로 → 페이지된 최종 10건에만 호출
select memb_nm(매도회원번호) 매도회원명, ...   -- 여기서만!
from (
   select rownum no, a.* from (
      select 매도회원번호, ...   -- 안쪽은 원본 컬럼만, 함수 없음
      from 체결 where ... order by 체결시각 desc
   ) a where rownum <= 30
) where no between 21 and 30;

(2) Decode 함수 또는 Case문으로 변환

함수 로직을 풀어서 decode, case문으로 전환하거나 조인문으로 구현할 수 있는지 확인해보자!

(3) 뷰 머지(View Merge) 방지를 통한 함수 호출 최소화

단계 A: 함수를 그냥 3번 호출

select sum(decode(SF_상품분류(시장코드, 증권그룹코드), '1. 주식 현물', 체결수량)) 주식현물수량
     , sum(decode(SF_상품분류(시장코드, 증권그룹코드), '2. 주식외 현물', 체결수량)) 주식외수량
     , sum(decode(SF_상품분류(시장코드, 증권그룹코드), '3. 파생', 체결수량)) 파생수량
from   체결
where  체결일자 = '20090315'

단계 B: 인라인 뷰로 묶기 (1번만 호출) -> 옵티마이저에 의해 A와 동일

select sum(decode(상품분류, '1. 주식 현물', 체결수량)) 주식현물수량
     , sum(decode(상품분류, '2. 주식외 현물', 체결수량)) 주식외수량
     , sum(decode(상품분류, '3. 파생', 체결수량)) 파생수량
from ( select SF_상품분류(시장코드, 증권그룹코드) 상품분류   -- 여기서 1번만 계산하려는 의도
            , 체결수량
       from   체결
       where  체결일자 = '20090315' )

단계 C: no_merge 힌트 사용

select sum(decode(상품분류, '1. 주식 현물', 체결수량)) 주식현물수량
     , sum(decode(상품분류, '2. 주식외 현물', 체결수량)) 주식외수량
     , sum(decode(상품분류, '3. 파생', 체결수량)) 파생수량
from ( select /*+ NO_MERGE */ SF_상품분류(시장코드, 증권그룹코드) 상품분류
            , 체결수량
       from   체결
       where  체결일자 = '20090315' )

• rownum : 옵티마이저가 rownum을 확인하면 해당 뷰를 머지하지 않는다!

(4) 스칼라 서브쿼리의 캐싱효과를 이용한 함수 호출 최소화

select ( select d.dname        -- 출력값: d.dname
         from   dept d
         where  d.deptno = e.deptno )   -- 입력값: e.deptno
from   emp e

서브쿼리가 수행될 때마다 입력 값을 캐시에서 찾아보고 거기 있으면 저장된 출력 값을 리턴하고, 없으면 쿼리를 수행한 후 입력 값과 출력 값을 캐시에 저장한다.

함수를 Dual 테이블을 이용해 스칼라 서브쿼리로 한번 감싸준다! 특히, 함수 입력 값의 종류가 적을 때 이 기법을 활용하면 함수 호출 횟수를 획기적으로 줄일 수 있다!

select ...
from ( select /*+ NO_MERGE */
              (select SF_상품분류(시장코드, 증권그룹코드) from dual) 상품분류  -- 감쌈
            , 체결수량
       from 체결 where 체결일자 = '20090315' )

입력값의 종류가 많으면 해시 충돌 때문에 함수가 그대로 호출된다. (충돌이 나면 오라클은 기존 캐시 엔트리를 그대로 둔 채 그냥 스칼라 서브쿼리를 한 번 더 실행한다)

(5) Deterministic 함수의 캐싱 효과 활용

10gR2에서 함수를 선언할 때 Deterministic 키워드를 넣어 주면 스칼라 서브쿼리를 덧입히지 않아도 캐싱 효과가 나타난다.

함수의 입력 값과 출력 값은 CGA(Call Global Area)에 캐싱된다.

CGA에 할당된 값은 데이터베이스 Call 내에서만 유효하므로 Fetch Call이 완료되면 그 값들은 모두 해제된다. 따라서 Deterministic 함수의 캐싱 효과는 데이터베이스 Call 내에서만 유효하다.

무엇보다 Deterministic은 오라클의 보장이 아니라 개발자의 선언일 뿐이어서 내부에 SELECT가 있는 함수에 캐싱 목적으로 붙이면 일관성 없는 결과를 초래하므로 순수 연산 함수에만 사용해야 한다.

(6) 복잡한 함수 로직을 풀어 SQL로 구현

PL/SQL은 인터프리터 언어이므로 그것으로 작성한 함수 실행 시 매번 SQL 실행 엔진과 PL/SQL 가상머신 사이에 컨텍스트 스위칭이 일어난다.

SQL에서 함수를 호출할 때마다 SQL 실행엔진이 사용하던 레지스터 정보들을 백업했다가 PL/SQL 엔진이 실행을 마치면 다시 복원하는 작업을 반복하게 되므로 느려질 수 밖에 없다.

(2) Recursive Call을 포함하지 않는 함수의 성능 부하

오라클 내장 함수 to_char와 사용자 정의 함수를 사용할 때의 수행시간을 비교해 보면 2.45초, 15.57초 소요된 것을 알 수 있다.

Recursive Call 없이 컨텍스트 스위칭 효과만으로 보통 5~10배 정도 느려진다.

(3) Recursive Call을 포함하는 함수의 성능 부하

보통 사용자 정의 함수에는 Recursive Call이 포함된다.

Recursive Call은 매번 Execute Call과 Fetch Call을 발생시키기 때문에 대량의 데이터를 조회하면서 레코드 단위로 함수를 호출하도록 쿼리 작성시 성능이 극도로 나빠진다.

I/O가 전혀 발생하지 않는 가벼운 쿼리를 삽입했음에도 1분 5초 가량 소요

Recursive Call 없는 함수와 비교하면 4배 가량 더 느려졌다. => 원인: 데이터베이스 Call + I/O 발생

함수에서 발생하는 Recursive Call은 대부분 I/O를 수반하므로 실제 훨씬 큰 성능저하를 일으킨다.

(4) 함수를 필터 조건으로 사용할 때 주의 사항

함수 조건이 인덱스 access면 1번, filter면 액세스 레코드 수만큼 호출된다.

-> 이는 인덱스 access, filter 과정 생각하면 됨

(5) 함수의 읽기 일관성

함수 안에 Select 문이 들어 있으면, 그 함수를 대량 행에 걸어 조회하는 도중에 다른 세션이 데이터를 바꾸고 커밋할 경우 같은 입력값인데 결과가 중간부터 달라지는 형상이 생긴다.

원인은 함수 내부 쿼리가 메인 쿼리의 시작 시점이 아니라 자기가 실행되는 시점을 기준으로 블록을 읽기 때문이다!

이런 경우 일반 조인문이나 스칼라 서브쿼리를 사용할 때만 완벽한 문장수준 읽기 일관성이 보장된다.

데이터베이스 Call을 커서의 활동상태에 따라 Parse, Execute, Fetch로 나눔

Call이 어디서 발생하느냐에 따라 User call과 Recursive Call로 나눔

1. Call 통계
2. User Call vs Recursive Call
3. 데이터베이스 Call이 성능에 미치는 영향
4. Array Processing 활용
5. Fetch Call 최소화
6. 페이지 처리의 중요성
7. PL/SQL 함수의 특징과 성능 부하
8. PL/SQL 함수 호출 부하 해소 방안

01 Call 통계

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.03         12         15          0          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.04         12         15          0          14

커서의 진행 상태에 따라 Parse, Execute, Fetch 세 개의 Call로 나누어 각각에 대한 통계정보를 보여준다.

• Parse: SQL을 파싱하고 실행계획을 생성하는 단계
• Execute: SQL 커서를 실행하는 단계
• Fetch: 레코드를 실제로 Fetch 하는 단계 (select 문에서 실제 레코드를 읽어 사용자가 요구한 결과집합을 반환)

Insert, update, delete, merge 등 DML문은 Execute call 시점에 모든 처리과정을 서버 내에서 완료하고 처리결과만 리턴하므로 Fetch Call이 전혀 발생하지 않는다. insert ... select 문도 마찬가지다.

select문일 때 Execute Call 단계에서는 커서만 오픈하고, 실제 데이터를 처리하는 과정은 모두 Fetch 단계에서 일어난다.

SELECT REGION, COUNT(*)
FROM CUST
GROUP BY REGION

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.03       0.04        180        842          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.04        180        842          0           5

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87

Rows     Row Source Operation
-------  ---------------------------------------------------
      5  HASH GROUP BY (cr=842 pr=180 pw=0 time=41000 us)
 100000   TABLE ACCESS FULL CUST (cr=842 pr=180 pw=0 time=...)

for update 구문을 사용하면 Execute Call 단계에서 모든 레코드를 읽어 Lock을 설정한다.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.20       0.24          0         74      10178           0
Fetch       11      0.00       0.00          0         14          0         101
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       13      0.20       0.24          0         88      10178         101

02 User Call vs Recursive Call

Call이 어디서 발생하느냐에 따라 User Call과 Recursive Call로 나눌 수 있다.

User Call은 OCI(Oracle Call Interface)를 통해 오라클 외부로부터 들어오는 Call을 말한다.

DBMS 성능과 확장성을 높이려면 User Call을 최소화하려는 노력이 무엇보다 중요하며, 이를 위해 아래와 같은 기능과 기술을 적극적으로 활용하자

• Loop 쿼리를 해소하고 집합적 사고를 통해 One-SQL로 구현
• Array Processing: Array 단위 Fetch, Bulk Insert/Update/Delete
• 부분범위처리 원리 활용
• 효과적인 화면 페이지 처리
• 사용자 정의 함수/프로시저/트리거의 적절한 활용

Recursive Call은 오라클 내부에서 발생하는 Call을 말한다.

SQL 파싱과 최적화 과정에서 발생하는 Data Dictionary 조회, PL/SQL로 작성된 사용자 정의 함수/프로시저/트리거 내에서의 SQL 수행 등

Recursive Call을 최소화하려면, 바인드 변수를 적극적으로 사용해 하드파싱 발생횟수를 줄여야 한다.

PL/SQL은 가상머신 상에서 수행되는 인터프리터 언어이므로 빈번한 호출 시 컨텍스트 스위칭 때문에 성능이 매우 나빠진다

BEGIN
  FOR r IN (SELECT id FROM big_table) LOOP   -- 100만 건이라 치면
    UPDATE target SET flag = 'Y' WHERE id = r.id;  -- 매 행마다 SQL 엔진 호출
  END LOOP;
END;

이 루프는 100만번 도는데, 매 반복마다 UPDATE 한 번을 위해 PL/SQL -> SQL -> PL/SQL로 컨텍스트가 오간다.

이는 반대 방향도 성립

SELECT id, my_plsql_func(salary) FROM emp;  -- 행마다 SQL → PL/SQL 전환

BULK COLLECT 는 한 행씩 fetch 하는 대신 여러 행을 컬렉션으로 한 번에 가져오고, FORALL은 컬렉션 전체에 대한 DML을 한 번의 전환으로 SQL 엔진에 넘긴다.

위 루프를 BULK COLLECT + FOR ALL로 바꾸면 행마다 일어나던 전환이 배치 당 한번으로 줄어, 같은 작업이 극적으로 빨라진다.

03. 데이터베이스 Call이 성능에 미치는 영향

04. Array Processing 활용

Array Processing 기능을 활용하면 한 번의 SQL 수행으로 다량의 로우를 동시에 Insert/Update/Delete 할 수 있다.

이는 네트워크를 통한 데이터베이스 Call을 감소시켜주고, 궁극적으로 SQL 수행시간과 CPU 사용량을 획기적으로 줄여준다.

ㅇ ㅇ ㅇ ㅇ ㅇ ㅇ ㅇ ㅇ ㅇ

ㅇ