따봉 구걸남's 블로그

앞선 포스팅에서 소프트 파싱과 하드파싱을 정리한 글에서 

바인드 피킹 기능 한계 및 보완으로 ACS 가 새로 도입이 되었다고 했는데 더 자세히 알아보자

1. ACS 등장 배경 

 1) 리터럴 SQL 사용
  - 동일 구조 쿼리에서 입력값이 동일하지 않은 경우 하드파싱 부하 
 
 2) bind 변수 사용 SQL 
 - 바인드변수 사용으로 동일 SQL ID 를 사용, 하드파싱 감소
 - 데이터 분포를 고려하지 않은 실행계획이 생성, 실행됨

 3) bind peeking 기능 도입
 - 최초 실행시 데이터입력 값의 분포를 고려한 실행계획이 생성됨
 - 분포도가 다른 입력값이 들어오면 비효율 발생

 4) adaptive cursor share
 - 데이터 분포에 따라 다른 실행계획 생성
 - 오버헤드는 발생 

2. ACS 사용하기

 ACS 를 사용하려면 컬럼의 히스토그램이 생성되어 있어야 한다. 

통계정보 수집 할때 method_opt 옵션에서 지정할수 있고 문법과 예시는 아래와 같다. 

 - METHOD_OPT 옵션 문법

FOR COLUMNS [column_clause] [size_clause]
size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
column_clause := column_name | extension name | extension

 - 예시

EXEC DBMS_STATS.GATHER_TABLE_STATS('SYSTEM','TB_DPS_TRSC_BASE', ESTIMATE_PERCENT=>100, METHOD_OPT=>'FOR COLUMNS ACCT_NO SIZE 2 ');

* SIZE 뒤의 숫자만큼 버킷을 지정되어 수집되고 1부터 2048 까지 지정 할수 있다.

0 은 지정할수 없고, 1 은 히스토그램을 수집하지 않겠다는 것을 의미.

NDV 를 고려해서 버킷사이즈를 적절하게 선택 

- 관련 파라미터 확인 

alter [ system | session ] set "_optimizer_adaptive_cursor_sharing" = true;  -- default 

 - V$SQL 의 관련 컬럼

IS_BIND_SENSITIVE 
 => Y : 옵티마이저가 히스토그램을 분석, 바인드 값 변경 시 실행계획이 달라질 수 있음을 감지 
 => N : 통계가 없거나, 균일한 분포(임계를 넘지 않은) 

IS_BIND_AWARE
 => Y : (SENSITIVE already marked 상태) extended cursor sharing 사용 
 => N : 1) SENSITIVE 가 N 이거나
            2) SENSITIVE 가 Y 이지만 아직 extended cursor sharing 사용되지 않은 상태 
   

- 실행계획 분화 프로세스

  1. 통계, 히스토그램 수집

  2. 옵티마이저가 바인드변수에 따라 실행계획이 변경될수 있다고 판단되면 => IS_BIND_SENSITIVE = Y 

  3. 유저 입력값의 변경에 따라 이전에 비해 많은 일량 처리 됨 => IS_BIND_AWARE = Y

 4. 3번의 바인드 값 다시 들어오면 기존 커서 중지, 새로운 커서 생성

3. ACS 한계

 히스토그램을 반영하는 실행계획을 생성해주는 장점이 있지만 추가적인 오버헤드 발생하는 단점이 있다. 

 아래와 같은 추가적인 비용이 발생하게 된다.  

 - 실행계획 평가 비용 증가
 각 실행 시점마다 옵티마이저가 바인드 값과 기존 실행계획의 효율성을 지속적으로 평가
 
 - 커서 관리 부하
새로운 Child Cursor 생성 시 추가적 관리 부하 발생 

 - 메모리 할당(library cache) 및 통계 정보 수집(v$sql_cs_histogram) 발생
최대 20개까지의 Child Cursor 유지로 인한 메모리 증가

 - 파싱 오버헤드
Bind-Aware 상태 전환 시 추가적인 Soft Parsing 발생 -> 라이브러리 캐시 래치 경합 가능성 증가

즉, IS_BIND_AWARE = Y 이면 이후 해당 SQL 은

매번 바인드 값을 확인하고, 
Child Cursor 선택/생성 로직을 거치며,
통계 정보와 히스토그램을 참조하는 추가적인 내부 연산이 발생한다. 

Child Cursor가 많아질수록 커서 관리 비용, 라이브러리 캐시 경합, CPU 소모가 증가하게 된다. 

그러므로, 과도한 child cursor 로 인한 부작용 발생시, 아래와 같이 적절히 대응해야 할수도 있다. 

- ACS 기능을 비활성화하거나(_optimizer_adaptive_cursor_sharing=false)
- 바인드 변수 대신 리터럴 사용
- 히스토그램 수집 범위 조정 (skew 없으면 히스토그램을 수집X)
- SQL 구조 변경(조건 분기, 힌트 사용 등)

즉, 남용하지 말고 DBA 가 적절히 개입할 필요가 있다. 

1) 오라클에서 SQL 의 처리과정 

사용자가 SQL 을 실행하면 아래 1~ 3 까지의 파싱 단계를 거치게 된다. 

 1. syntax check : 문법적 오류 체크 (ex. select * form tab_a )

 2. semantic check : 존재하지 않는 테이블 조회 등

 3. shared pool check : 동일한 sql 이 shared pool 에 캐싱되어 있는지 확인 

이 과정을 거쳐 SQL 이 캐싱되어 있지 않는 것으로 확인되면 (library cache miss) 

4. 실행계획 생성 단계를 거쳐 

5. 최종적으로 실행을 하게 된다. 

이때 실행계획 생성과정에서 library cache, data dictionary cache 에 반복적으로 접근하고 이 과정에서 

latch 라는 lock 보다는 가벼운 잠금장치를 수없이 획득하는 과정을 거친다. 

2) 소프트 파싱 vs 하드 파싱

 - 소프트 파싱 (Soft Parsing) 

이미 만들어진 실행계획을 캐시에서 찾아 재사용하는 과정이다. 

Shared Pool의 Library Cache에 저장된 SQL 실행 계획을 공유하여 최적화 과정을 생략한다. ( 위에서 1,2,3 은 실행, 4,5 가 생략)

실행계획 생성과정이 cpu 를 많이 소모하는 과정이기 때문에 하드 파싱에 비해 더 빠르고 리소스를 절약할 수 있다.

 - 하드 파싱 (Hard Parsing)

 캐시에 SQL 이 존재하지 않아 (library cache miss) 새로운 실행 계획을 생성해야 할 때 발생. 

최적화 및 로우 소스 생성 단계까지 모두 거친 후에 SQL 이 실행된다. 

3) 소프트 파싱의 이점과 한계

소프트 파싱은 실행 계획을 반복적으로 생성하는 오버헤드를 줄여 성능을 향상 시켜주어 이롭다.

특히 OLTP 환경에서 동시 접속자가 많을 때 효과적. 그러나 항상 최선의 선택은 아니다.

사용자 입력값만 달라지는 SQL 은 다른 SQL ID 이 부여되어 하드파싱 비율이 올라가는데 이때 입력값을 

바인드 변수 처리하면 같은 SQL ID 로 처리되어 소프트 파싱으로 처리되는 비율이 높아지는 장점이 있다. 

하지만 이는 데이터의 분포와 상관없이 동일한 플랜을 적용하겠다는 의미이기 때문에,

skewed 데이터 분포를 보이는 컬럼에 대해서는 문제가 될수 있다. 

4) 바인드 Peeking의 역효과 예시

이에 대한 보완으로 오라클 9i 부터 바인드 피킹이란 것이 처음 등장했다.

바인드 변수를 사용하는 SQL 문장이 처음 실행될 때, 변수에 전달된 값을 옵티마이저가 '엿보고(peek)'

그 값을 기반으로 실행계획을 수립하는 기능.

그러나 이것은 다른 데이터 분포의 바인드변수가 들어오면 성능상 문제가 발생할수 있다. 

ex)  다음과 같은 테이블과 쿼리가 있다고 가정해보자 

SELECT * FROM 직원 WHERE 부서 = :dept

이 테이블에서 '영업부'는 전체 직원의 50%를 차지하고, 나머지 부서들은 각각 5% 미만의 직원을 가지고 있다.
 - 첫 실행 시 :dept에 '인사부'가 바인딩되었다면 => 옵티마이저는 인덱스 스캔을 선택합니다. 
 - 이 실행 계획이 캐시에 저장
 - 이후 '영업부'로 쿼리를 실행 => 캐시된 실행 계획(인덱스 스캔)을 사용
 - 하지만 영업부는 데이터의 50%를 차지하므로, 풀 테이블 스캔이 더 효율적 
이로 인해 '영업부' 쿼리의 성능이 크게 저하될 수 있습니다.

이런 부작용으로 보통은 "_OPTIM_PEEK_USER_BINDS" 파라미터가 false 로 사용

5) 소프트 파싱 한계에 대한 대안

1. 힌트 사용:
특정 쿼리에 대해 옵티마이저 힌트를 사용하여 원하는 실행 계획을 강제
SELECT /*+ FULL(직원) */ * FROM 직원 WHERE 부서 = :dept

2. 아웃라인 사용:
특정 쿼리에 대한 실행 계획을 고정
CREATE OUTLINE 직원_부서_조회 FOR
SELECT * FROM 직원 WHERE 부서 = :dept;

3. 적응적 커서 공유 활용:
Oracle 11g 이상에서는 적응적 커서 공유를 통해 바인드 변수 값에 따라 다른 실행 계획을 사용할 수 있다

아래에 따로 포스팅 해놓았다. 

적응적 커서 공유

4. 특정 SQL의 소프트 파싱 방지
SELECT /*+ NO_SHARED_CURSOR */ * FROM 직원 WHERE 부서 = :dept

5. CURSOR_SHARING (자동바인드 여부 제어) 파라미터 조정

세션 레벨에서 CURSOR_SHARING을 조정  
ALTER SESSION SET CURSOR_SHARING = EXACT;   -- 동일한 쿼리에 대해서 소프트파싱(default)
ALTER SESSION SET CURSOR_SHARING = FORCE;   -- 리터럴을 바인드로 바꿈. 

MYSQL 에서는 EXPLAIN [ANALYZE] 명령어로 실행계획 확인이 가능함.
쿼리 처리에 사용되는 각 반복자에 대해 TREE 형식의 SELECT 문 실행에 대한 확장 정보를 제공 한다.

1) 결과 컬럼들의 의미 

 - id : SELECT 쿼리별로 부여되는 식별자
 - select_type : 각 단위 SELECT 쿼리의 타입. ex) SIMPLE(단순 SELECT), PRIMARY(가장 바깥쪽 쿼리), UNION
 - table : 해당 행이 어떤 테이블에 대한 것인지를 나타냅니다[1].
 - type : 테이블에서 레코드를 읽은 방식. ex) system, const, eq_ref, ref, range, index, ALL 등(성능순)
 - possible_keys : 옵티마이저가 고려한 인덱스 목록. 실제 사용과는 무관.
 - key : 실제로 사용된 인덱스. 
 - key_len : 사용된 인덱스의 길이. 복합 인덱스에서 몇 개의 컬럼이 사용되었는지 알 수 있다
 - ref : 인덱스에서 값을 찾기 위해 사용된 컬럼이나 상수
 - rows : 쿼리를 처리하기 위해 검색하거나 조회한 레코드 수의 예측값
 - filtered : WHERE 조건으로 필터링되고 남은 레코드의 비율
 - Extra : 옵티마이저의 동작에 대한 추가적인 정보

* 추가 키워드
explain [extended] , [partitions] => 는 8.0 에서 사라짐. 
explain format=json -> json 형태로 출력
explain format=tree -> tree 형태로 출력
 
  


2. 실행계획 확인을 위한 테스트 스크립트  


-- 테이블 생성
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

-- 인덱스 생성
CREATE INDEX idx_customer_order ON orders (customer_id, order_date);

-- 고객 데이터 생성
INSERT INTO customers (customer_id, name, email)
SELECT 
    n,
    CONCAT('Customer ', n),
    CONCAT('customer', n, '@example.com')
FROM (
    SELECT @row := @row + 1 AS n
    FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t1,
         (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t2,
         (SELECT @row:=0) r
) numbers
WHERE n <= 1000;

-- 주문 데이터 생성
INSERT INTO orders (order_id, customer_id, order_date, total_amount)
SELECT 
    n,
    1 + FLOOR(RAND() * 1000),
    DATE_ADD('2025-01-01', INTERVAL FLOOR(RAND() * 365) DAY),
    ROUND(50 + RAND() * 950, 2)
FROM (
    SELECT @row := @row + 1 AS n
    FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t1,
         (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t2,
         (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t3,
         (SELECT @row:=0) r
) numbers
WHERE n <= 10000;


EXPLAIN ANALYZE
SELECT c.name, o.order_id, o.order_date, o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id = 1;

 

-> Nested loop inner join  (cost=0.70 rows=2) (actual time=0.123..0.128 rows=2 loops=1)
    -> Index lookup on c using PRIMARY (customer_id=1)  (cost=0.35 rows=1) (actual time=0.089..0.091 rows=1 loops=1)
    -> Index lookup on o using idx_customer_order (customer_id=c.customer_id)  (cost=0.35 rows=2) (actual time=0.031..0.033 rows=2 loops=1)


 * 실행계획 결과의 주요 부분 
 - cost: 옵티마이저가 예상한 비용. ex) cost=451.00은 전체 쿼리의 예상 비용
 - rows: 옵티마이저가 예상한 결과 행 수 ex) rows=1000은 1000개의 행이 반환될 것으로 예상됨.
 - actual time: 실제 실행 시간. ex) actual time=0.308..4.170은 첫 행을 가져오는 데 0.308 ms, 모든 행을 가져오는 데 4.170 ms
 - rows: 실제로 반환된 행 수 ex) rows=1000 은 실제로 1000개의 행이 반환
 - loops: 해당 작업이 반복된 횟수 ex) loops=1은 이 작업이 한 번 실행되었음을 의미
 
 * 실행 계획 추가 주목할 점
 - idx_customer_order 인덱스가 사용됨. (customer_id, order_date) 복합 인덱스
 - 인덱스의 선두 컬럼인 customer_id만 조건으로 사용. order_date는 사용되지 않음 
 - Index lookup on o using idx_customer_order (customer_id=c.customer_id)는 
    이 인덱스를 사용해 조인 조건을 만족하는 행을 찾는다는 것을 나타냄.
 - actual time 과 rows 를 보면, 인덱스를 효과적으로 사용하였음

테이블 통계정보가 부정확 할 경우 실행계획이 잘못 생성되어 성능에 이슈가 발생할 수 있다. 

그래서 보통 dbms_stats 유틸로 gather_table_stat 패키지를 실행해서 통계정보를 수집하는데 

바뀐 통계정보가 바로 반영되어 새로운 실행계획이 바로 적용되지 않을 수 있는데 

이 경우 no_invalidate=> false 옵션을 사용하면 즉시 cursor 가 무효화 되어 새로운 실행계획을 바로 적용할수 있다. 

* no_invalidate=> true 는 커서를 무효화 하지 않겠단 뜻으로 cursor 가 shared pool 에서 flush 된 후 reload 될때까지 변경되지 않는다. 

다만 rac 환경에서는 롤링이 되지않아 특정 노드에서만 쿼리가 느린 현상이 발견 됨.

"_optimizer_invalidation_period" 에 적용된 시간이 지나서야 모든 노드의 cursor 가 무효화가 된다. 

-- 현재 AWR 설정 확인 (주기, TOPn )

SELECT *
  FROM DBA_HIST_WR_CONTROL
;

-- 각 interval 당 snapshot 확인 
SELECT *
  FROM DBA_HIST_ASH_SNAPSHOT
ORDER BY SNAP_ID DESC
;

-- snapshot 의 sql_id 별 stat 확인 
SELECT *
  FROM DBA_HIST_ACTIVE_SESS_HISTTORY
;
 

 ## 스냅샷 생성 및 삭제 

1. 스냅샷 생성

BEGIN DBNS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT
( [FLUSH_LEVEL => 'FLUSH_LEVEL']);
END;
/


2. 스냅샷 삭제 

BEGIN DBNS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE
( LOW_SNAP_ID => snap_id, HIGH_SNAP_ID => snap_id [DBID => dbid]);
END;
/

3. 스냅샷 설정 변경 

BEGIN DBNS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
( [RETENTION => retention_time][, INTERVAL => interval_time]
 [, TOPNSQL => topn_sql_number]);
END;
/


4. 베이스라인 설정 

BEGIN DBNS_WORKLOAD_REPOSITORY.CREATE_BASELINE
( START_SNAP_ID => snap_id, END_SNAPID => snap_id,
BASELINE_NAME => 'baseline_name' [,DBID => dbid]
);
END;
/

 
5. 베이스라인 삭제  

BEGIN DBNS_WORKLOAD_REPOSITORY.DROP_BASELINE
( BASELINE_NAME => 'baseline_name' [,CASCADE => true|false][,DBID => dbid]
);
END;
/

개발자들은 SQL에 업무를 그대로 풀어서 작성하는 경향이 있다. 

그러다보면 where 절에 or 를 자주 사용하게 되는데..

그러나 or 는 DB 가 일 할 범위를 줄여주지 못하고 늘려주기만 할 뿐이라 주의해야 한다. 

or 는 in 으로 대체가 가능한 경우가 있는데 옵티마이저가 이를 알아서 바꿔주기도 하지만 

많은 경우에 그러지 못하다. 

이 경우 or 는 filter 처리되어 인덱스를 사용하지 못하고 나중에 처리되며 어떤 경우에는 

모든 조인처리가 끝난 후에 처리 되기도 한다. 

 select 

   from xxx

 where a = '1'

   and ( b = '01'  or b = '02' or b = '03' )

 위처럼 작성된 쿼리는 아래로 바꾸는 것이 좋다. 

 select 

   from xxx

 where a = '1'

   and b in ( '01', '02', '03' )

not in 서브쿼리를 사용해서 결과 rows 를 필터하고 싶은 경우가 있다.

그때 제외하고 싶은 조건이 추가 되거나 할때 단순하게 아래와 같이 쓰는 경우가 있다. 

SELECT *

  FROM A

WHERE ...

   AND NOT (     (A.COL1, A.COL2) IN ( SELECT 

                                                     FROM B

                                                     WHERE ... )

                   OR  (A.COL1, A.COL3) IN ( SELECT 

                                                       FROM B

                                                       WHERE ... )

                  )

컬럼의 갯수는 동일하지만 대상 컬럼은 바뀌었다. 

위 쿼리는 업무를 그대로 풀어서 가독성은 있지만 성능상의 불리함이 있다. 

NOT IN 절을 바꿔보자

AND NOT ((A.COL1, A.COL2, A.COL3) IN ( SELECT B.COL1

                                                      ,DECODE(RN,1,B.COL2, A.COL2)

                                                      ,DECODE(RN,1,A.COL3, B.COL3)

                                                FROM B,

                                                       ( SELECT ROWNUM RN

                                                            FROM ALL_OBJECTS

                                                          WHERE ROWNUM <=2

                                                         )BB

                                             WHERE ... 

                         )

                  )

조건에 따라서 COL1, COL2 또는 COL1,COL3 와 가공의 컬럼 RN 을 만들고 카티션 조인을 한다.

RN=1 인 경우 원본의 첫번째 NOT IN 이 되고 

RN=2 인 경우 원본의 두번째 NOT IN 이 된다. 

EX) RN=1    AND NOT ((A.COL1, A.COL2, A.COL3) IN ( B.COL1, B.COL2, A.COL3 )

    RN=2    AND NOT ((A.COL1, A.COL2, A.COL3) IN ( B.COL1, A.COL2, B.COL3 )

동일 컬럼의 비교는 항상 참이 되어 결국 결과는 아래와 같다. 

RN=1    AND NOT ((A.COL1, A.COL2) IN ( B.COL1, B.COL2 )

RN=2    AND NOT ((A.COL1, A.COL3) IN ( B.COL1, B.COL3 )

튜닝사례까지는 아니지만.. 

db segment 증가량은 어느 사이트나 관심의 대상이다. 

만약 매월 segment 사용량 데이터를 수집하고 있다면 이 raw data 로

전월대비 리포트를 뽑을수 있다. 

예를 들어

시점, owner, segment 를 매월 수집했다면..

2019년 10월 사용량과 9월 대비 증가량을 뽑고자 한다면..

select 수집일, owner, round(sum(bytes)) seg 

 from 수집한데이터테이블

where 수집일 in ('201909','201910')

group by 수집일, owner

수집일, owner 별 사용량 합이 구해진다. 

owner 가 OWN1, OWN2 이 있다고 하자.

select sum(decode(rn,1,seg)) total

        ,sum(rn * seg) tot_dif

        ,sum(decode(rn,1,decode(owner,'OWN1',seg))) tot_own1

        ,sum(decode(owner,'OWN1',rn * seg) ) tot_own1_dif

        ,sum(decode(rn,1,decode(owner,'OWN2',seg))) tot_own2

        ,sum(decode(owner,'OWN2',rn * seg) ) tot_own2_dif

  from (

select 수집일, owner, round(sum(bytes)) seg, decode(수집일,'201910',1,'201909',-1) rn

       from 수집한데이터테이블

where 수집일 in ('201909','201910')

group by 수집일, owner

        )

rn 이라는 가공의 컬럼을 만들어서 현재월이면 1, 지난 월이면 -1 이 나오게 해서

sum 실행시 전월이면 마이너스가 된다.