태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

The Power of Predicates in dbms_xplan

오라클 2008.01.15 22:39
9i에서 dbms_xplan 패키지가 소개되면서 실행 계획을 사용하는 방식에 일대 혁명이 일어났다. 단순히 실행 계획을 추측하기 위해 사용하던 것에서 벗어나 이제는 SQL Trace를 대신하거나 혹은 SQL Trace의 부족한 부분을 보완할 정도의 툴로 발전했다.

dbms_xplan 패키지가 제공하는 정보들은 하나 하나가 대단히 중요한 것들이다. 오늘은 그 중 Predicate 정보에 대해 재밌는 예를 살펴보고자 한다.

dbms_xplan 패키지를 사용해 본 사람이라면 아래와 같은 정보에 익숙할 것이다.

explain plan for select count(*) from t_const1 where name = '0987654321';
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
Plan hash value: 4135594318

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     7 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| T_CONST1 |     1 |     7 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("NAME"='0987654321')

위의 정보에서 Predicate Information를 해석해 보면 2번째 Operation, 즉 TABLE ACCESS FULL Operation에서 NAME = '0987654321' 조건이 Filter로 사용되었다는 의미이다. 이것만으로도 Plan을 해석하는데 있어서 매우 유용한 정보가 된다.

Predicate의 타입은 Filter나 Access로 나뉘는데 Access는 Optimizer가 Access Path를 결정하는데 사용했다는 의미이고, Filter는 Filtering 조건으로만 사용했다는 의미이다.

Predicate 정보가 매우 중요한 역할을 제공하는 예를 한가지 만들어 보자. 우선 아래의 SQL Trace 결과를 보자.

alter session set sql_trace = true;                                            
select /*+ index(t_const1) */ count(*) from t_const1 where name = '0987654321';                       
alter session set sql_trace = false;                                           
                                                                               
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.96       0.94          0       8633          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.96       0.94          0       8633          0           1

SQL Trace 결과를 많이 본 사람이면 "으잉?"하고 의문을 품을 것이다. 8633블록을 Logical Reads로 읽는데 무려 1초에 가까운 시간이 걸린 것이다. 대개의 경우 이 정도의 일량이면 0.1 초 내에 끝나는 것이 일반적이다. 왜 이런 현상이 발생하는 것일까? 보통 이런 경우 Function의 과도한 사용같은 것을 의심해 보지만, 위의 Query와 SQL Trace 결과를 보면 전혀 무관하다는 것을 알 수 있다.

안타깝게도 SQL Trace 만으로는 위의 결과를 해석할 수 없다. 위의 쿼리를 dbms_xplan 패키지를 사용해서 다시 분석해 보자.

select /*+ gather_plan_statistics index(t_const1) */  count(*) from t_const1
where name = '0987654321';

select * from table(dbms_xplan.display(null, null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
SQL_ID  8pjh0vyqxqzny, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(t_const1) */  count(*) from t_const1 where
name = '0987654321'

Plan hash value: 225932663

--------------------------------------------------------------------------------------------
| Id  | Operation         | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE   |              |      1 |      1 |      1 |00:00:01.25 |    4961 |
|*  2 |   INDEX RANGE SCAN| T_CONST1_IDX |      1 |   9938 |   1000K|00:00:00.01 |    4961 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NAME"='0987654321')
       filter(TRIM("NAME")='0987654321')

(gather_plan_statistics 힌트에 대해서는 여기서 상세히 논의한 바 있다)

아하!! 위의 결과를 보면 전혀 원하지 않았던 filter 조건이 추가된 것을 확인할 수 있다. 이런 원하지 않은 Filtering 조건에 의해 TRIM("NAME")='0987654321' 계산이 반복적으로 발생하고 이로 인해 원하지 않는 성능 저하가 발생한 것이다. 이런 원하지 않는 Filtering이 발생하는 주된 이유 중 하나가 CHECK Constraint 조건이다. 실제로 dba_constraints 뷰를 조회해보면 다음과 같은 결과를 얻을 수 있다.

select constraint_name, constraint_type, search_condition
from dba_constraints where table_name = 'T_CONST1';

CONSTRAINT_NAME    CONSTRAINT_TYPE    SEARCH_CONDITION
C1    C    name = trim(name)

즉, 테이블을 생성할 때 Blank 문자를 없애기 위해 다음과 같은 Constraint가 추가된 것이다.

alter table t_const1 add constraint c1 check (name = trim(name));

Filtering 조건에 Constraint가 있는 경우, Oracle의 Optimizer는 이를 염두에 둔 실행 계획을 생성하며, 실행 과정에서 데이터의 범위를 줄이기 위해 필요하다면 Constraint 조건을 Filtering Predicate로 삽입하는 일을 수행한다. 이것은 많은 경우 성능에 유리하지만 위의 샘플에서는 오히려 불필요한 Filtering을 유발해 성능 저하를 가져오는 것이다.

이제 Constraint를 제거하면 어떤 변화가 생기는지 관찰해 보자.

alter table t_const1 drop constraint c1;

select /*+ gather_plan_statistics index(t_const1) */  count(*) from t_const1
where name = '0987654321';

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
SQL_ID  8pjh0vyqxqzny, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(t_const1) */  count(*) from t_const1 where name =
'0987654321'

Plan hash value: 225932663

-----------------------------------------------------------------------------------------------------
| Id  | Operation         | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE   |              |      1 |      1 |      1 |00:00:00.22 |    4961 |     20 |
|*  2 |   INDEX RANGE SCAN| T_CONST1_IDX |      1 |    993K|   1000K|00:00:00.01 |    4961 |     20 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NAME"='0987654321')

Constraint를 제거한 후 불필요한 Filtering 조건이 제거된 것을 확인할 수 있다. 그만큼 성능은 개선된다.  또 하나의 중요한 변화는 E-Rows(Estimated Rows, Cardinality)의 변화이다. Constraint에 의해 Filtering 조건이 추가된 경우에는 E-Rows 값이 9938 인데 반해, Constraint가 없는 경우에는 E-Rows의 값이 993K로 정확하게 100배의 값이다. Oracle은 Constraint 조건에 의해 Filtering이 추가되는 경우 여기서 99%가 걸러진다(Filter)고 가정하고 실제 예상 로우수의 1%만을 Cardinality로 사용한다. 이런 Cardinality의 변화는 실행 계획의 변화를 유발할 수도 있다.

하지만, 기껏 데이터 정합성을 위해 추가한 Constraint를 이런 이유로 제거하는 것은 용납하기 힘들 것이다. 다행히 10195 Event를 이용하면 Constraint를 제거하지 않고도 동일한 효과를 얻을 수 있다.


alter session set events '10195 trace name context forever, level 1';

위의 간단한 예제는 dbms_xplan 패키지가 제공하는 Predicate 정보가 특정 문제를 분석하는 데 있어서 없어서는 안되는 중요한 정보를 제공한다는 것을 잘 보여준다. dbms_xplan 패키지가 제공하는 정보는 매우 다양하며, 잘 활용하면 특정 상황에서는 다른 툴로는 도저히 해석할 수 없는 몇몇 현상들에 대한 분석을 가능하게 해주기도 한다. 앞으로 기회가 닿는 대로 dbms_xplan 패키지의 중요한 기능들에 대해서 논의하는 시간을 가졌으면 하는 바램이다.


신고
Trackback 1 : Comment 0

Write a comment

티스토리 툴바