태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Oracle 10g의 Cost Based Push Predicate에 의한 성능 저하...

오라클 2008.02.18 20:50
Oracle 10g에 소개된 혁신적인 기능 중 하나가 Cost Based Query Transformation(CBQT)이다. Oracle은 Query의 성능을 최적화하기 위해 Optimization 단계 전에 다양한 Query Transformation을 시도한다. 그 중 대표적인 것들이 Simple View Merging, Complex View Merging, Subquery Unnesting, Push Predicate 등이다.

Oracle 9i까지는 이러한 Query Transformation은 Rule Base로 작동한다. 즉, 특정한 룰을 가지고 룰을 만족하면 항상 Transformation을 시도한다. 비록 CBO로 Optimization을 수행하더라도 Transformation은 여전히 Rule Base로 동작하는 셈이다.

Oracle 10g에서는 이러한 개념이 완전히 바뀌어서, 이제는 Query Transformation 자체도 Cost Base로 동작한다. 즉, Transformation을 함으로써 쿼리의 비용(Cost)이 낮아지는 경우에만 Transformation을 수행하게 된다. 대부분의 경우 이것은 성능 개선에 도움이 된다. 불필요한 Transformation에 의해 쿼리의 성능이 오히려 느려질 수도 있기 때문이다. 하지만 드물게 특정 상황에서는 CBQT에 의해 Transformation이 이루어지지 않음으로써 Query 수행히 늦어지는 경우가 발생한다.

이런 상황은 Oracle 9i에서 Oracle 10g로 업그레이드를 수행했을 때 특히 문제가 된다. 9i에서는 Nested Loop로 잘 돌아가던 Query가 10g에서는 갑자기 Hash Join으로 풀리면서 성능이 느려지는 현상이 발생할 수 있다.

간단한 테스트를 통해 이 현상을 재연해보자.
아래 테스트는 Push Predicate가 Cost Based로 이루어지는 경우 어떤 문제가 발생할 수 있는지를 잘 보여준다.

-- 버전 확인
OWI@joss> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

-- 우선 필요한 Object들을 생성한다.
OWI@joss> create table t1
  2  as select rownum as id, mod(rownum, 5)+1 as n, object_name as name
  3  from all_objects where rownum <= 100;

테이블이 생성되었습니다.

OWI@joss> create table t2
  2  as select rownum as id, rownum as id2, object_name as name
  3  from all_objects where rownum <= 20000;

테이블이 생성되었습니다.

OWI@joss> create table t3
  2  as select * from t2;

테이블이 생성되었습니다.

OWI@joss> create index t2_idx on t2(id, id2);

인덱스가 생성되었습니다.

OWI@joss> create index t3_idx on t3(id, id2);

인덱스가 생성되었습니다.

-- 통계 정보를 생성한다. 상황을 간단하게 하기 위해 histogram을 생성하지 않는다.
OWI@joss> begin
  2           dbms_stats.gather_table_stats(user,'t1',
  3               method_opt=>'for all columns size 1', cascade=>true);
  4
  5           dbms_stats.gather_table_stats(user,'t2',
  6               method_opt=>'for all columns size 1', cascade=>true);
  7
  8           dbms_stats.gather_table_stats(user,'t3',
  9               method_opt=>'for all columns size 1', cascade=>true);
 10  end;
 11  /

PL/SQL 처리가 정상적으로 완료되었습니다.

-- View를 생성한다. t2 테이블과 t3 테이블을 조인하는 간단한 뷰이다.
OWI@joss> create or replace view v1
  2  as
  3  select  t2.id id2_1,
  4           t2.id2 id2_2,
  5           t3.id id3_1,
  6           t3.id2 id3_2,
  7           t2.name name2,
  8           t3.name name3
  9  from t2, t3
 10  where
 11           t2.id = t3.id
 12           and t2.id2 = t3.id2;

뷰가 생성되었습니다.

-- Cost based predicate push를 Enable한다.(기본값이 True이다)
OWI@joss> alter session set "_optimizer_push_pred_cost_based" = true;

세션이 변경되었습니다.

-- View Merging을 방지하기 위해 Outer Join을 사용하는 것에 유의하자.
-- View Merging이 발생하면 Push Predicate이 발생하지 않기 때문이다.
OWI@joss> select /*+ gather_plan_statistics cost_based */
  2           t1.name, v1.name2, v1.name2
  3  from t1, v1
  4  where
  5           t1.n = 1
  6           and t1.id = v1.id2_1(+);
-- 출력 결과는 생략

--
OWI@joss> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

------------------------------------------------------------------------------
| Id  | Operation               | Name   | Starts | E-Rows | A-Rows |Buffers |
------------------------------------------------------------------------------
|*  1 |  HASH JOIN OUTER        |        |      1 |     20 |     20 |    168 |
|*  2 |   TABLE ACCESS FULL     | T1     |      1 |     20 |     20 |      3 |
|   3 |   VIEW                  | V1     |      1 |  20000 |  20000 |    165 |
|*  4 |    HASH JOIN            |        |      1 |  20000 |  20000 |    165 |
|   5 |     INDEX FAST FULL SCAN| T3_IDX |      1 |  20000 |  20000 |     59 |
|   6 |     TABLE ACCESS FULL   | T2     |      1 |  20000 |  20000 |    106 |
------------------------------------------------------------------------------
                                                                                                       
Predicate Information (identified by operation id):
---------------------------------------------------                                                                                                 
   1 -  access("T1"."ID"="V1"."ID2_1")
   2 - filter("T1"."N"=1)
   4 - access("T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2")
                                                                                     
                                                              

25 개의 행이 선택되었습니다.

위의 예를 보면 실행 계획의 비효율성이 존재하는 것을 확인할 수 있다.

1. 2번째 단계에서 t1 테이블에서 단 20건의 데이터만이 추출된다.
2. 이 20건의 데이터를 이용해서 v1에 대해 Nested Loop 조인을 수행하는 것이 가장 바람직할 것이다.
3. 하지만, v1 뷰에는 t1.n = 1 에 해당하는 조건을 인식시킬 수 없으므로
4. Oracle은 t1과 v1을 Hash Join으로 수행한다.

만일 Predicate Push가 이루어져서 t1.n = 1 에 해당하는 t1.id 값이 v1 뷰안에 Push되면 매우 효율적인 실행 계획을 만들 수 있다. 아래 테스트 결과를 보자.

-- Cost Based Push Predicate를 Disable한다.
OWI@joss> -- disable cost based predicate pushing
OWI@joss> alter session set "_optimizer_push_pred_cost_based" = false;

세션이 변경되었습니다.

OWI@joss>
OWI@joss> select /*+ gather_plan_statistics no_cost_based */
  2           t1.name, v1.name2, v1.name2
  3  from t1, v1
  4  where
  5           t1.n = 1
  6           and t1.id = v1.id2_1(+);

출력 결과는 생략

OWI@joss> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Starts | E-Rows | A-Rows |Buffers|
------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS OUTER            |        |      1 |     20 |     20 |     75|
|*  2 |   TABLE ACCESS FULL            | T1     |      1 |     20 |     20 |      5|
|   3 |   VIEW PUSHED PREDICATE        | V1     |     20 |      1 |     20 |     70|
|   4 |    NESTED LOOPS                |        |     20 |      1 |     20 |     70|
|   5 |     TABLE ACCESS BY INDEX ROWID| T2     |     20 |      1 |     20 |     45|
|*  6 |      INDEX RANGE SCAN          | T2_IDX |     20 |      1 |     20 |     25|
|*  7 |     INDEX RANGE SCAN           | T3_IDX |     20 |      1 |     20 |     25|
------------------------------------------------------------------------------------
 
                                                                                    

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."N"=1)
   6 - access("T2"."ID"="T1"."ID")
   7 - access("T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2")

                   

                                                                                        
위의 결과를 보면

1. 3번 단계에서 VIEW PUSHED PREDICATE 가 발생하는 것을 확인할 수 있다. Starts 값이 20이라는 것에서 t1.n = 1 조건에 해당하는 20건에 대해 Nested Loop로 Push가 이루어지는 것을 알 수 있다.
2. 무엇보다 큰 차이는 6번 단계의 Predicate Information에서 t2.id = t1.id 조건이 Access Predicate로 추가된 것을 알 수 있다.
3. 즉, Predicate Push에 의해 v1 뷰안에 t1.id 조건이 Push된 것이다.
4. 이로 인해 효율적인 Nested Loop 조인이 이루어지고 일량도 168 블록에서 75 블록으로 크게 줄었다.

위의 예는 Push Predicate가 발생해야 함에도 불구하고, Cost Based Push Predicate에 의해 발생하지 않고 그로 인해 성능이 저하되는 전형적인 사례이다. 이런 사례들 때문에 Oracle 9i에서 10g로 업그레이드후 예상치 않게 성능이 저하되는 현상이 발생하는 것으로 보고되는 것이다. Cost Based Query Transformation과 관련해서 가장 이슈가 되는 두 파라미터가 _optimizer_cost_based_transformation(View merging/subquery unnesting 등을 제어)가 _optimizer_push_pred_cost_based(Predicate Push를 제어)이다.

위의 간단한 테스트 결과가 Query 성능을 분석할 때 실행 계획과 Predicate 정보가 얼마나 중요한지 또 하나의 예로 활용되었으면 한다.

더 자세한 정보는 아래 URL을 참조한다.

http://wiki.ex-em.com/index.php/OPTIMIZER_PUSH_PRED_COST_BASED
http://wiki.ex-em.com/index.php/OPTIMIZER_COST_BASED_TRANSFORMATION



신고
Trackback 0 : Comment 1
  1. 허거덩왕자 2009.11.26 00:05 신고 Modify/Delete Reply

    좋은정보감사합니다~

Write a comment

티스토리 툴바