튜닝이론정리

조건절 PUSHING

옵티마이저는 VIEW를 만나면 일차적으로 VIEW MERGING을 수행하지만 상황에 따라 조건절 PUSING을 시도합니다.

조건절 PUSH DOWN

메인쿼리의 조건절을 인라인 뷰 안으로 밀어넣어 서브쿼리의 처리량을 대폭 줄일 수 있습니다.

SELECT /*+PUSH_PRED*/
DEPTNO, AVG_SAL
FROM (
    SELECT DEPTNO, AVG(SAL) AVG_SAL
    FROM EMP
    GROUP BY DEPTNO
)
WHERE DEPTNO = 30;

조건절 이행 후 PUSH PRED

인라인뷰의 EMP 테이블에도 DEPTNO 칼럼이 존재하면 DEPTNO가 포함된 인덱스 스캔이 발생합니다.

SELECT E.DEPTNO, D.DNAME, E.AVG_SALS
FROM
(
  SELECT /*+NO_MERGE*/ 
  DEPTNO, AVG(SAL) ALV_SAL
  FROM EMP
  GROUP BY DEPTNO
) E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND D.DEPTNO = 30;

조건절 PULLUP

하나의 서브쿼리의 조건절이 pullup 해 다른 서브쿼리로 이행되어 push down 돼 인덱스 스캔을 유발합니다.

SELECT DEPTNO, AVG_SAL
FROM (
  SELECT DEPTNO, AVG(SAL) AVG_SAL 
  FROM EMP 
  WHERE DEPTNO = 30 
  GROUP BY DEPTNO
) E1, 
(
  SELECT 
  DEPTNO, MIN(SAL)AVG_SAL 
  FROM EMP 
  GROP BY DEPTNO
) E2
WHERE E1.DEPTNO = E2.DEPTNO;

조인조건 PUSH DOWN

조인조건을 QUERY BLOCK으로 밀어넣습니다. 부분범위 처리에 유용합니다.

SELECT
FROM DEPT D, EMP E
WHERE E.JOB = 'CLERK'
AND D.DEPTNO = 30
AND D.DEPTNO = E.DEPTNO

조인조건 push down이 적용되면 쿼리는 다음과 같이 동작합니다

  SELECT *
  FROM DEPT D, EMP E
  WHERE E.JOB = 'CLERK'
  AND E.DEPTNO = 30
  AND D.DEPTNO = 30

불필요한 조인 제거

특정상황에서 조인한 결과와 M쪽 테이블의 조인 대상레코드가 동일한 경우 조인을 하지않는것이 좋습니다.

예를들어 1쪽 테이블에 PK 조건이 있고 M쪽 테이블의 조인기준칼럼에 NOT NULL 제약이 존재하는 상황에서 M쪽 테이블의 칼럼만 존재하면 조인이 불필요합니다.

-- 조인시 조인이 실패한 데이터는 안나오기 때문에 조인을 안하는 것과는 다름
select e.empno, e.ename. e,deptno
from dept d, emp e
where d.deptno = e.deptno;

OR EXPAND

OR 연산은 인덱스 스캔범위를 넓히기때문에 OR EXPAND를 통해 UNION ALL 연산으로 변환할 수 있습니다. 이때 인덱스 수직적 탐색비용은 증가합니다. 관련힌트는 USE_CONCAT / NO_EXPAND 입니다.

SELECT *
FROM EMP
WHERE SAL = 3000
OR DEPTNO = 200;


-- UNION ALL

SELECT * FROM EMP
WHERE SAL = 3000
UNION ALL
SELECT * FROM EMP
WHERE DEPTNO = 200
AND LNNVL(SAL = 3000)

기타쿼리변환

COUNT 함수 사용시 값이 null 인 레코드는 포함되지 않기때문에 옵티마이저는 조건절에 Is not null 조건을 추가하여 결과집합에서 제외후 COUNT 합니다.

파티션

파티셔닝은 특정기준별로 테이블 데이터를 모아 분할저장하는 기법입니다. 가장 일반적인 파티셔닝 기준은 날짜입니다. 파티션된 테이블은 파티션 별로 TRUNCATE가 가능합니다. DELETE는 DML인 반면 TRUCATE는 DDL이기 때문에 처리속도가 빠릅니다

파티션 별로 인덱스를 생성할 수 있습니다. 이를 LOCAL PARTITION INDEX 라고 합니다. 이는 인덱스의 루트블록이 테이블 파이션의 수만큼 존재함을 의미합니다. 파티션의 기준키가 인덱스에 포함되면 파티션의 수직적 탐색 횟수를 줄일 수 있습니다. 이를 PARTITION PLUNNING이라 합니다.

파티션은 물리적으로는 떨어져있지만 논리적으로는 하나의 세그먼트에 존재합니다.

RANGE PARTITIONING

파티션 키 값을 범위로 분할합니다. 주로 날짜칼럼을 기준으로 합니다.

HASH PARTITIONING

파티션 키 값에 해시함수를 적용하고 출력값을 기준으로 파티셔닝합니다.

파티션 구성방법

PARTITION BY RANGE(매출일자)
(
  PARTITION P1 VALUES LESS THEN('20190401')
  -- 모든 파티션 키의 기준이 지나서 알맞는 파티션을 찾지 못하면 여기에 적제
  PARTITION P_MAX VALUE LESS THEN (MAXVALUE)
)

PARTITON PLUNNNG

옵티마이저가 SQL의 대상 테이블과 조건절을 분석한 후 불필요한 파티션을 액세스 대상에서 제외하는 기능입니다.

정적파티셔닝은 엑세스할 파티션을 컴파일 시점에 미리 결정하며 파티션 키 칼럼을 상수조건으로 조회합니다. 동적 파티셔닝은 엑세스할 파티션을 실행 시점에 결정 바인드 변수로 조회합니다.

파티션시 주의점

파티션 키 칼럼에 대한 가공이 발생해선 안됩니다. 묵시적 형 변환이라도 정상적인 파티션 PLUNNNG이 미작동합니다.

인덱스 파티셔닝

LOCAL 파티션 vs GLOBAL 파티션

LOCAL은 테이블 파티션과 1:1로 대응되도록 파티셔닝한 인덱스입니다. GLOBAL은 파티션 테이블과 독립적인 구성을 갖도록 파티셔닝한 인덱스를 말합니다.

PREFIXED VS. NON-PREFIEXD 파티션

PREFIXED 은 파티션 인덱스를 생성할때 파티션 키 칼럼이 인덱스 선두에 위치한 경우를 말하고 NON-PREFIXED 파티션 인덱스는 인덱스 선두칼럼이 아닌 곳에 위치하는 경우를 말합니다.

파티션 키 칼럼은 주로 일자관련 칼럼을 사용하기때무에 주로 BETWEEN 조건을 이용합니다. PREFIXED로 구성시 BETWEEN 범위조건으로인해 인덱스 스캔 효율율이 감소할 수 있습니다.

배치프로그램 튜닝

병렬처리 활용

병렬처리는 테이블을 FULL SCAN 해야합니다.

SELECT /*+FULL(A) PARALLEL(A 2)*/
FROM EMP A

-- SELECT STATEMENT
--     PX COORDINATOR
--        PX SEND QC
--            PX BLOCK ITERATOR
--                TABLE ACCESS FULL OF EMP

PARALLEL SERVER PROCESS가 선언한 병렬처리 수 만큼 생성됩니다. 프로세스는 쿼리를 처리하고 QC(SINGLE SERVER PROCESS)에 전달합니다.

ORDER BY, GROUP BY가 포함된 쿼리를 병렬처리하면 레코드 분배를 위해 P-P 방식의 오퍼레이션이 발생합니다.

SELECT /*+FULL(A) PARALLEL(A 2)*/
FROM EMP A
ORDER BY SAL

-- SELECT STATEMENT
--    PX COORDINATOR
--        PX SEND QC(ORDER) -- P-S
--            SORT ORDER BY
--                PX SEND RECEIVE
--                    PX SEND RANGE -- P-P RANGE 방식
--                        PX BLOCK ITEROATOR
--                            TABLE ACCESS FULL OF EMP

분배

서버 프로세스간 데이터를 전달하는 것을 분배라고 합니다. 분배의 종류는 다음과 같습니다.

- SINGLE PROCESS TO PARALLER PROCESS
- PARALLER PROCESS TO PARALLER PROCESS
- PARALLEL PROCESS TO SINGLE PROCESS

분배의 방식(PQ_DISTRIBUTE)

- RANGE
- BRAODCAST
- KEY
- HASH
- ROUND-ROBIN(INSERT-SELECT)

병렬 조인

FULL PARTITION WISE JOIN

두 테이블 모두 JOIN 칼럼을 기준으로 PARTITIONING 되있는경우 입니다. 동일한 범위의 파티션끼리만 하나의 PARALLEL 서버가 조인합니다. PQ_DISTRIBUTE 중 PARTITION RAGNE 방식을 사용하고 P-P 분배가 발생하지 않습니다. 키의 범위가 다른 파티션끼리는 절대 조인하지 않습니다.

-- PARTITION KEY COLUMN : 일자
SELECT *
FROM T1, T2
WHERE T1.일자 = T2.일자

PARTIAL PARTITION WISE JOIN

파티션 테이블과 비파티션 테이블을 조인할 때 파티션 테이블의 키를 기준으로 비파티션 테이블을 파티션하고 조인합니다. PQ_DISTRIBUTE 방식 중 PARTITION KEY 방식을 사용합니다.

SELECT /*+
    LEADING(D) FULL(E) FULL(D) USE_HASH(E)
    PARALLEL (E 2) PARALLE(D 2)
    PQ_DISTRIBUTE(E PARTITION NONE)
*/
*
FROM DEPT P, EMP E
WHERE E.DEPTNO = D.DEPTNO

PQ_DISTRIBUTE 힌트

PQ_DISTRIBUTE (LOOKUP TABLE, DRIVING TABLE PARTITION/NONE, LOOK UP TABLE PARTITION/NONE)

DYNAMIC PARTITON WISE JOIN(HASH)

두 테이블 모두 파티션이 안된 경우 해시함수를 통해 파티셔닝 기준을 만들어 파티셔닝후 병렬처리합니다. 두 테이블 모두 HASH PARTITIONING 하고 HASH 방식으로 분배합니다.

SELECT
/*+
LEADING(D) FULL(E) FULL(D) PARALLEL(E 2) PARALLEL(D 2) PQ_DISTRIBUTE(E HASH HASH)
*/
*
FROM DEPT D, EMP E
WHERE E.DEPTNO = D.DEPTNO

-- EXECUTION PLAN

-- SELECT STATEMENT
-- PX COORDINATOR
--     PX SEND QC(RANDOM) P - S
--         HASH JOIN BUFFERED
--             PX RECEIVE 
--                 PX SEND HASH P - P
--                     PX SEND ITERATOR 
--                        TABLE ACCESS FULL OF DEPT
--             PX RECEIVE 
--                 PX SEND HASH P - P
--                     PX SEND ITERATOR 
--                        TABLE ACCESS FULL OF EMP

DYNAMIC PARTITION WISE JOIN (BROADCAST)

한 테이블은 레코드가 적고 한 테이블은 많으면 많은 쪽 테이블의 파티션에 작은 테이블 레코드를 분배합니다. PQ_DISTRIBUTE의 BROADCAST 분배방식을 사용합니다.

SELECT /*+
  LEADING(D) FULL(E) FULL(D) PARALLEL(E 2) PARALLEL(D 2)
  PQ_DISTRIBUTE(E BROADCASE NONE)
*/
*
FROM DEPT D, EMP E
WHERE E.DEPTNO = D.DEPTNO

-- SELECT STATEMENT
-- PX COORDINATOR
--  PX SEND QC(RANDOM) P - S
--      HASH JOIN BUFFERED
--          PX RECEIVE 
--              PX SEND HASH P - P
--                  PX BLOCK ITERATOR 
--                      TABLE ACCESS FULL OF DEPT
--          PX BLOCK ITERATOR 
--              TABLE ACCESS FULL OF EMP

NO_PARALLEL

병렬처리가 안된 테이블은 QC가 처리합니다. S-P 관련 오퍼레이션만 발생합니다. 특정 배치프로세스 일때 SERVER 프로세스가 몇개 발생하는지 파악해야 합니다.

SELECT /*+LEADING(D) FULL(E) FULL(D)
PARALLEL (E 2) NO_PARALLEL(D)
PQ_DISTRIBUTE(E BROADCAST NONE)
*/ *
FROM DEPT D, EMP E
WHERE E.DEPTNO = D.DEPTNO

-- SELECT STATEMENT
-- PX COORDINATOR
--  PX SEND QC(RANDOM) P - S
--      HASH JOIN 
--          BUFFER SOT 
--              PX SEND BROADCAST S - P BROADCAST
--                      TABLE ACCESS FULL OF DEPT
--          PX BLOCK ITERATOR 
--              TABLE ACCESS FULL OF EMP

SELECT /*+LEADING(D) FULL(E) FULL(D)
PARALLEL (E 2) NO_PARALLEL(D)
PQ_DISTRIBUTE(E HASH HASH)
*/ *
FROM DEPT D, EMP E
WHERE E.DEPTNO = D.DEPTNO


SELECT STATEMENT
  PX COORDINATOR
    PX SEND QC(RANDOM) -- P - S
      HASH JOIN BUFFERED
        BUFFER SORT
          PX RECIEVE
            PX SEND HASH -- S - P  HASH
              TABLE FULL ACCESS OF DEPT
          PX RECEIVE
            PX SEND HASH -- P-P
              PX BLOCK ITERATOR
                TABLE CEESS FULL OF EMP

SQL이 수행할 작업 범위를 여러 개의 작은 단위로 나눠 동시에 처리하기 때문에 수행속도가 절감됩니다. PARALLEL_INDEX 힌트를 사용할 때는 반드시 iNDEX_FFS 힌트도 함께 사용해야 합니다.

QC

병렬프로세스를 생성하는 역할을 합니다. 병렬도와 오퍼레이션 종류에 따라 하나 또는 2개의 병렬 서버집합을 할당합니다. 필요한 만큼의 서버플세스를 확보하고 부족분에 대해 신규 생성합니다. 각 병렬서버로부터 산출물을 통합하여 사용자에게 전송하는 역할을 합니다.

SQL 튜닝

BIND 변수 활용

바인드 변수를 이용해 항상 동일한 SQL을 사용하도록하면 라이브러리 캐시를 공유할 수 있습니다. BIND 변수를 사용하지 않고 상수변수를 사용하면 매번 쿼리가 달라져 하드파싱이 발생합니다.

DBMS CALL 최소화

CURSOR OPEN 과 CURSOR CLOSE를 최소화해서 LIBRARY CACHE 참조를 최소화합니다. LIBRARY CAHCE는 LATCH가 발생하므로 성능에 부담이 됩니다.

ONE SQL

ONE SQL은 한 번만 수행되므로 1번의 파싱, 1번의 EXECUTE, 결과레코드의 수 / 1회 FETCH로 읽어올 수 있는 레코드의 수 만큼의 FETCH가 발생합니다.

ARRAY PROCESSING

FETCH 한번에 최대한 많은 레코드를 가져오기위한 기법입니다.

I/O 성능 효율화

DRIVING 조건 최대화를 통해 인덱스 매칭도를 향상하여 수평적 스캔 선택도를 높일 수 있습니다.

결합인덱스 우선순위

- 빈번하게 사용되는 칼럼
- 등차비교 칼럼
- 카디널리티가 높은 칼럼
- 소트연산을 대체할 수 있는 칼럼

TABLE RANDOM ACCESS 최소화

조건절에 사용된 칼럼이 최대한 인덱스에 존재하도록 합니다.

BATCH 프로그램 튜닝

- PARALLEL READ
- PARALLEL WRITE
- TEMPORARY TABLESPACE READ/WRITE
- DIRECT PATH READ
- DIRECT PATH WIRTE
- LOB TYPE READ NO CHACHE OPTION

SHARED POOL

- LIBRARY CACHE
- DICTIONARY CACHE
- DATA BUFFER CACHE
- READ LOG BUFFER

절차형 프로그래밍 부하

- APPLICATION CURSOR 를 열고 루프내에서 다른 SQL을 반복처리하는경우
- 반복적인 DBMS CALL이 발생하는 경우
- RANDOM ACCESS 위주의 쿼리인경우
- 동일 엑세스가 중복 발생하는 경우

절차형 프로그래밍 튜닝방안

병목을 일으키는 SQL을 찾아 인덱스를 재구성하고 엑세스 경로 최적화합니다. 병럴프로세스를 이용해 처리시간을 최소화합니다. ARRAY PROCESSING을 활용하고 ONE SQL을 최대한 사용합니다.

PUSH_PRED

서브쿼리를 가능한 선순위로 처리하게 합니다. 이 기법을 모르면 서브쿼리를 직접 풀어서 실행해야 합니다.

아키텍쳐 기반 튜닝 원리

오라클 서버는 크게 DATABASE와 INSTANCE로 구분됩니다. DATABASE는 다시 DATAFILE, CONTROL FILE, REDO LOG FILE로 나뉩니다. INSTANCE는 SGA와 BACKGROUND PROCESS로 구분돼있습니다.

SGA는 다시 SHARED POOL과 DATA BUFFER CACHE, REDO LOG BUFFER 로 구분됩니다. SHARED POOL은 LIBRARY CACHE와 DICTIONARY CACHE로 구분됩니다.BACKGROUND PROCESS는 PMON, SOMN, DBWR, LGWR, CKPT로 구분됩니다.

BACKGROUND PROCESS 는 INSTANCE RECOVERY를 담당하는데 ROLL FORWARD, ROLL BACK, CHECK POINT를 활용합니다. ROLL BACK은 SMON이 담당하고 PGA RELEASE는 PMON, DATA BUFFER CACHE 에서 DATA FILE로 옮겨쓰는 작업은 DBWR, REDOLOG BUFFER CACHE 에서 REDO LOG FILE로 옮겨쓰는 작업은 LGWR , MEMORY 와 DISK를 동기화 하는 작업은 CKPT가 담당합니다.

SQL FULL TEXT와 SQL EXECUTION PLAN이 저장되는 곳은 LIBRARY이고 스키마 정보가 저장되는 곳은 DICTIONARY CACHE입니다.

서버 프로세스는 사용자 프로세스와 통신하며 각종 명령을 처리합니다. 서버 프로세스는 OPTIMIZER 모듈을 실행시켜 실행계획을 최적화 합니다. 익스텐트는 테이블 스페이스로부터 공간을 할당받는 단위입니다. 익스텐트 내 블록은 물리적으로 인접합니다. 익스텐트는 특정 세그먼트에 소속되 있고 익스텐트 하나당 8개의 블록이 있습니다.

세그먼트는 테이블, 인덱스를 담는 공간입니다. 물리적 저장공간인 데이터 파일을 논리적으로 구성했기 때문에 데이터파일과 세그먼트는 M:M 관계입니다

UNDO/REDO/로그

UNDO는 TRANSACTION ROLLBACK을 위해 사용하는 데이터입니다. REDO 는 TRANSACTION ROLLFORWARD를 위해 사용하는 데이터입니다.

- FAST COMMIT : COMMIT 시, DBWR가 DB 파일에 실제 변동사항을 저장하지 않았지만 REDO LOG를 믿고 빠르게 commit 하는 정책을 말합니다.

- WRITE AHEAD LOGGING : DB BUFFER CACHE 이전에 REDO LOG에 변동사항을 저장하는 정책입니다.

- LOG FORCE AT COMMIT : COMIIT을 만나면 데이터 정합성을위해 로그만 기록하는 정책입니다.

'DB > SQL튜닝' 카테고리의 다른 글

SQL자격검정실전문제 - 인덱스와 조인(기본원리/튜닝/조인기본원리/고급조인기법) (0)	2024.07.02
SQL자격검정실전문제 - 아키텍쳐 기반 튜닝 원리 ( 데이터베이스 아키텍쳐, SQL 파싱부하, 데이터베이스 call과 네트워크 부하, 데이터베이스 I/O원리) (0)	2024.06.26
튜닝이론 - 3 (0)	2024.06.04
튜닝이론 정리-2 (2)	2024.06.03
SQL 튜닝 이론 정리1 (1)	2024.05.30

튜닝이론정리 - 4