몇주전에 필자는  DBA 로부터 _optimizer_push_pred_cost_based 값을 True 에서 False 로 바꾸겠다는 보고를 받았다.
즉 Push Predicate 에 대해서는 CBQT(Cost Based Query Transformation) 을 사용하지 않고 Heuristic 변환 이나 Rule 변환 (Costing 을 하지않고 무조건 조건절이 push predicate 됨)방법을 사용하겠다는 뜻이다.
이유는 ora-600 에러와 Update, Delete 시에 where 절에 서브쿼리를 사용할때 서브쿼리내에 Union all 을 사용할경우 Wrong Result 가 나온다는 것이었다.
섬뜩한 결정이다.
일단 필자는 DBA 들에게 기다려보라고 이야기 하였다.
시스템이 OLTP 이니 Heuristic 변환 이 더맞을 수 있을거 같지만 필자가 프로젝트를 진행하는 곳은 False 로 바꿀경우에 더욱 무서운 일이 기다리고 있었다.
아래의 스크립트를 보자.

현상 :파라미터를 FALSE로 바꾸고 조인을 사용하는 뷰를 메인쿼리에서 아우터조인 하는경우 Push Predicate 적용안됨.
필자가 있는프로젝트의 경우 대부분의 쿼리가 경우 공통코드뷰를 사용하며 아우터조인 하는 경우도 30% 나 되었다.(갯수로 따지면 1500 개 정도의 쿼리임)
따라서  FALSE 로 바꿀경우 VW_공통코드를 아우터조인하는 쿼리를 전부 Push Predicate 되게 쿼리를 고쳐야 하는 상황 이었다.
오라클 버젼 10.2.0.3 이며 FIRST_ROWS_1 환경에서 테스트 되었다.

1.문제가 되는 쿼리 패턴

SELECT  A.고객번호   
        A.주민번호                                           
        A.고객명                                 
        A.고객분류코드                               
        B.고객분류코드명                               
FROM   TB_고객기본  A,
            VW_공통코드  B           
WHERE  A.주민번호        = :V_주민번호          
AND    B.공통대분류코드   (+)  = '고객분류코드'          
AND    B.공통소분류코드   (+)  = A.고객분류코드;



여기서 VW_공통코드는 두개의 테이블로 되어 있으며
공통대분류코드와 공통소분류코드가 조인이 되어있는 상태이다.

2. VW_공통코드 의 스크립트
--대부분의 사이트에서 사용하는 전형적인 공통코드 뷰이다.

CREATE VIEW VW_공통코드 AS
SELECT A.공통대분류코드,
             A.공통대분류코드사용여부,
             B.공통소분류코드,
             B.공통소분류코드명
    FROM TB_공통대분류코드 A,
              TB_공통소분류코드 B
  WHERE A.공통대분류코드 = B.공통대분류코드;  



3.인덱스 현황
TB_고객기본  : (주민번호) --> UNIQUE 인덱스
TB_공통대분류코드 : (공통대분류코드) --> PK 인덱스
TB_공통소분류코드 : (공통대분류코드, 공통소분류코드) --> PK 인덱스

4.PLAN

--  _optimizer_push_pred_cost_based  = true 일경우의 plan  

Execution Plan
--------------------------------------------------------------------------------
SELECT STATEMENT Optimizer=FIRST_ROWS_1 (Cost=3 Card=1 Bytes=119)
  NESTED LOOPS (OUTER) (Cost=3 Card=1 Bytes=119)
    TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TB_고객기본' (TABLE) (Cost=1 Card=1 Bytes=38)
      INDEX (UNIQUE SCAN) OF 'IX_고객기본_주민번호' (INDEX (UNIQUE)) (Cost=1 Card=1)
    VIEW PUSHED PREDICATE OF 'VW_공통코드' (VIEW) (Cost=2 Card=1 Bytes=81) --> PUSHED PREDICATE 작동함.
      NESTED LOOPS (Cost=2 Card=1 Bytes=54)
        TABLE ACCESS (BY INDEX ROWID) OF 'TB_공통소분류코드' (TABLE) (Cost=1 Card=1 Bytes=39)
          INDEX (UNIQUE SCAN) OF 'PK_공통소분류코드' (INDEX (UNIQUE)) (Cost=1 Card=1)  ---> unique scan
        INDEX (UNIQUE SCAN) OF 'PK_공통대분코드' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=15)



-- --  _optimizer_push_pred_cost_based  =  false 일경우의 plan

Execution Plan
--------------------------------------------------------------------------------
SELECT STATEMENT Optimizer=FIRST_ROWS_1 (Cost=13 Card=1 Bytes=77)
  NESTED LOOPS (OUTER) (Cost=13 Card=1 Bytes=77)
    TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TB_고객기본' (TABLE) (Cost=1 Card=1 Bytes=35)
      INDEX (UNIQUE SCAN) OF 'IX_고객기본_주민번호' (INDEX (UNIQUE)) (Cost=1 Card=1)
    VIEW OF 'VW_공통코드' (VIEW) (Cost=12 Card=1 Bytes=42)         --> PUSHED PREDICATE 작동안함.   
      NESTED LOOPS (Cost=2 Card=20 Bytes=980)
        INDEX (UNIQUE SCAN) OF 'PK_공통대분코드' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=15)
        TABLE ACCESS (BY INDEX ROWID) OF 'TB_공통소분류코드' (TABLE) (Cost=1 Card=20 Bytes=680)
          INDEX (RANGE SCAN) OF 'PK_공통소분류코드' (INDEX (UNIQUE)) (Cost=1 Card=20) ---> range scan



FALSE 일경우 이런스타일의 쿼리에서는 PUSH_PRED 힌트도 통하지 않는다.
NESTED LOOP 방식에서 LOOP 내에서 건건이 해당하는 대분류코드 전체를 RANGE 스캔하므로 전체적인 시스템관점에서 심각한 성능저하 현상을 발생시킬수 있다는 것이다.
이런스타일의 쿼리에는 Heuristic 이나 Rule 변환의 사각지대인거 같다.
이러한 패턴의 SQL 문이 1500 개 이상이 되는 상황에서 디폴트 값인 TRUE 를 FALSE 로 바꿀수 없었으며
TRUE 로 놓고 문제가 되는 쿼리를 역으로 잡기로 하였다.
즉 문제가 되는 쿼리만 SESSION 단위로 FALSE 로 하기로 하였다.
다행인것은 ALERT LOG 를 분석한결과 ORA-600 에러는 발생하지 않았으며
Update, Delete 시에 where 절에 서브쿼리를 사용할때 서브쿼리내에 Union all 을 사용할경우 Wrong Result 가 나오는 버그를 유발하는 쿼리는 없었으며 이또한 만약을 위하여 Patch를 Request 한 상태이다.

결론 :
옵티마이져의 특성과 관련된 히든 파라미터의 수정은 매우 신중해야 하며 SIDE EFFECT 에 의한 피해를 최소화 하기 위하여 노력해야한다.
버그를 피할 목적으로 무조건 DEFAULT 값을 무시하고 버그없는 세팅으로 바꾸는 경우가 있다.
이런경우 거의 예외없이 재앙이 따른다.
필자의 권고안은 제일 좋은 것은 패치미며 그것이 안될때는 DEFAULT 값으로 놓고 문제가 있는 SESSION 단위로 파라미터를 변경하는것을 권장한다.

Posted by extremedb
,