따봉 구걸남's 블로그

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

그래서 보통 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' )

A 라는 집합에서 B 테이블의 b1 조합 제외, b2 조합 제외

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 ... )

                  )

위 구조의 문제점

 개발자들은 SQL 에 업무를 그냥 서술하는 경향이 있다. 

 위의 쿼리는 NOT IN 절에 조건을 계속 추가하는 식으로 작성이 되어있어서

A 의 조건을 만족하는 모든 ROW 에 대해서 NOT IN 절을 확인하게 되었있다. 

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 )

 가 된다. 

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

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 실행시 전월이면 마이너스가 된다. 

아래와 같은 쿼리가 있다. 

SELECT *

 FROM A,

           ( 

           SELECT ...

              FROM (

                         SELECT

                          FROM ...

                        WHERE  A.COL1 = B.COL1

                           AND A.COL2 = B.COL2

                        GROUP BY ...

                     )

) INLINEVIEW B

WHERE  A.COL1='AA' AND   A.COL3 > SYSDATE -3

    AND A.COL1 = B.COL1(+)

    AND A.COL2 = B.COL2(+)

위 구조에서

인라인뷰 B 가 GROUP BY 등으로 가공된 집합이라면

A 의 조건들이 인라인뷰로 파고들어가지 못한다. 

인라인 뷰는 기본적으로 일반테이블과 조인시 동등한 관계이기 때문

만약,  테이블 A 와 인라인 뷰가 1:1 이나 M:1 조인이라면 (조인으로 인해 결과 ROWS 가 바뀌지 않는다면)

인라인뷰를 스칼라서브쿼리로 바꿔줄수 있다. 

SELECT XXX,

          ( 

           SELECT ...

              FROM (

                         SELECT

                          FROM ...

                        WHERE  A.COL1 = B.COL1

                           AND A.COL2 = B.COL2

                        GROUP BY ...

                     )

)

 FROM A

WHERE  A.COL1='AA' AND   A.COL3 > SYSDATE -3

스칼라서브쿼리는 메인테이블에 종속적 관계이기 때문에 메인테이블을 참조해서 쓸수있다. 

그러나, 1:1 이나 M:1 의 관계가 아니라면 쓸수 없다.

그렇다면 WITH 절을 활용해서 메인테이블을 참조할수 있게 해보자.

WITH MAIN AS (  

   SELECT ..

    FROM A

   WHERE

),

INLINEVIEW AS (

  SELECT ...

              FROM (

                         SELECT

                          FROM ...

                        WHERE  MAIN.COL1 = B.COL1

                           AND MAIN.COL2 = B.COL2

                        GROUP BY ...

                       )

)

SELECT *

 FROM MAIN  A

         , INLINEVIEW  B

WHERE  A.COL1 = B.COL1(+)

    AND A.COL2 = B.COL2(+)

스칼라서브쿼리로 변경한 것보다 비효율은 존재하지만 

인라인뷰가 1의 집합이 아닌경우에도 쓸수있다. 

## 쿼리를 잘모르는 개발자들은 업무에 대한 쿼리를 업무요건에 따라 그때 그때 만들기보다

모듈 개념으로 여기서 쓰던 쿼리를 그대로 따오는 경향이 있다. 

그런 케이스에서 위와 같은 비효율이 존재하는 쿼리가 만들어진다.