태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'gather_plan_statistics'에 해당되는 글 3건

  1. 2008.10.20 11g is better - 10046 Trace가 좋아졌다!!!
  2. 2008.04.10 gather_plan_statistics 힌트와 Parallel Query
  3. 2008.01.09 How to trace sql exectuion - Oracle 11g의 Real-Time SQL Monitoring (4)

11g is better - 10046 Trace가 좋아졌다!!!

오라클 2008.10.20 16:56
Query를 튜닝하다면 보면
Oracle의 예측과 실제 수행  결과를 한눈에 비교해보고 싶을 때가 있다.

하지만 10046 Trace의 결과로는 그것을 알 수가 없다.
다음과 같이 실제 수행 결과만 출력되기 때문이다.


select count(*)
from t1
where c1 between 1 and 100

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        2      0.00       0.00          0          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          7          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=7 pr=0 pw=0 time=737 us)
    100   TABLE ACCESS FULL T1 (cr=7 pr=0 pw=0 time=429 us)


Oracle 10g부터는 GATHER_PLAN_STATISTICS라는 아름다운 Hint를 사용하면 예측과 실제를 한 눈에 비교할 수 있다.

select /*+ gather_plan_statistics */
  count(*)
from t1
where c1 between 1 and 100
;

--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |            |      1 |00:00:00.01 |       7 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |    100 |     4   (0)|    100 |00:00:00.01 |       7 |
--------------------------------------------------------------------------------------------------

Oracle 11g부터는 10046 Trace에 예측 정보가 추가로 출력된다.
아래 결과를 보자.

select count(*)
from t1
where c1 between 1 and 100

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0         10         18           0
Fetch        2      0.00       0.00          0          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.01          0         18         18           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 88 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=7 pr=0 pw=0 time=0 us)
    100   TABLE ACCESS FULL T1 (cr=7 pr=0 pw=0 time=2 us cost=3 size=300 card=100)

Row Source Operation에 Cost, Cardinality 등의 정보가 추가적으로 출력되는 것을 알 수 있다.

기쁘지 아니한가?


신고
Trackback 0 : Comment 0

Write a comment


gather_plan_statistics 힌트와 Parallel Query

오라클 2008.04.10 15:37
오라클 10g부터 gather_plan_statistics 힌트를 이용하면 SQL Trace를 수행하지 않고도 Query의 Plan 단계별 일량(Actual+Estimated Rows을 포함한)을 알 수 있다는 것은 여러 차례 논의한 바 있다.

하지만 아래에 아주 재미있는 예가 있다.

alter table t_abnormal parallel 4;

select /*+ gather_plan_statistics */ count(*)
from t_abnormal;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

----------------------------------------------------------------------------------
| Id  | Operation              | Name       | Starts | E-Rows | A-Rows | Buffers | ----------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE        |            |      1 |      1 |      1 |       3 |

|   2 |   PX COORDINATOR       |            |      1 |        |      4 |       3 |
|   3 |    PX SEND QC (RANDOM) | :TQ10000   |      0 |      1 |      0 |       0 |
|   4 |     SORT AGGREGATE     |            |      0 |      1 |      0 |       0 |
|   5 |      PX BLOCK ITERATOR |            |      0 |  50000 |      0 |       0 |
|*  6 |       TABLE ACCESS FULL| T_ABNORMAL |      0 |  50000 |      0 |       0 |
----------------------------------------------------------------------------------


위의 결과를 보면 Slave가 수행한 일량(A-Rows, Buffers) 정보는 기록되지 않고, Coordinator가 수행한 일량 정보만이 기록되는 것을 확인할 수 있다.

왜 이런 현상이 발생하는가?
기억할 것은 이런 현상은 SQL Trace에서도 동일하게 발생한다는 것이다. 아래 Report는 동일한 쿼리를 Trace하고 tkprof에 의해 출력한 결과이다.

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=3 pr=0 pw=0 time=641714 us)
      4   PX COORDINATOR  (cr=3 pr=0 pw=0 time=641660 us)
      0    PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
      0     SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
      0      PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
      0       TABLE ACCESS FULL T_ABNORMAL (cr=0 pr=0 pw=0 time=0 us)

gather_plan_statistics 힌트를 사용했을 때와 동일하게 Slave가 수행한 일량은 기록되지 않는다. 비슷한 경험을 해본 사람들이라면 Slave가 수행한 일량은 Coordinator의 trace file이 아닌 Slave의 trace file에 기록된다는 것을 잘 알고 있을 것이다.

bdump directory(PQ Slave는 background session으로 인식되므로)에 Slave들의 trace file이 기록되고, tkprof 결과는 다음과 같다. 즉, 별도의 trace file을 확인해야 Slave들의 일량을 알 수 있다.

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
      0   PX COORDINATOR  (cr=0 pr=0 pw=0 time=0 us)
      0    PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
      1     SORT AGGREGATE (cr=87 pr=0 pw=0 time=522343 us)
  15855      PX BLOCK ITERATOR (cr=87 pr=0 pw=0 time=431178 us)
  15855       TABLE ACCESS FULL T_ABNORMAL (cr=87 pr=0 pw=0 time=127547 us)


이것은 Oracle의 버그가 아니라 Oracle PQ 아키텍처에서 오는 기본적인 한계점이라고 할 수 있다. 이 한계점이 gather_plan_statistics 힌트에 그대로 나타난 것이라고 할 수 있다.

이것이 중요한 결함인가가 더 중요한 사실일텐데... 나의 생각은 그렇지 않다는 것이다. 개별 Slave들의 일량은 사실 중요하지 않다. Query 전체 레벨의 일량 정보만 있으면 정확한 분석이 가능하기 때문이다. 어차피 Slave들이 하는 일은 전체 일량을 나누어 가지고 수행하는 것에 불과하기 때문이다.

따라서 다음과 같이 no_parallel 힌트를 사용해서 Serial하게 수행함으로써 gather_plan_statistics 힌트의 목적을 그대로 살릴 수 있다.

select /*+ gather_plan_statistics no_parallel(t_abnormal) */ count(*)
from t_abnormal;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

-------------------------------------------------------------------------------
| Id  | Operation          | Name       | Starts | E-Rows | A-Rows | Buffers |
-------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |            |      1 |      1 |      1 |     122 |
|   2 |   TABLE ACCESS FULL| T_ABNORMAL |      1 |  50000 |  50000 |     122 |
-------------------------------------------------------------------------------

신고
Trackbacks 26 : Comment 0

Write a comment


How to trace sql exectuion - Oracle 11g의 Real-Time SQL Monitoring

오라클 2008.01.09 11:41
우리가 실행한 SQL 문장이 "실제로" 어떻게 수행되었는지 손쉽게 추적하고 모니터링하는 방법은? 여기에 몇 개의 답변을 할 수 있을까?

1) ~ Oracle 9i
Autotrace
SQL Trace

Oracle 9i까지는 Autotrace와 SQL Trace를 이용하면 SQL이 실제로 수행하면서 얼마나 많은 일을 했는지 알 수 있다. 하지만 Autotrace는 실행 단계별 일량(Row source operation 정보라고 흔히 부름)을 남기지 않기 때문에 실제로 SQL을 추적하는데는 한계가 많다.

이런 이유 때문에 대부분의 경우 SQL Trace와 Tkprof를 이용해 SQL을 추적한다. 물론 이것이 얼마나 귀찮은 작업인지는 해본 사람은 다 공감할 것이다.

2) Oracle 10g ~
GATHER_PLAN_STATISTICS 힌트

Oracle 10g에 추가된 이 놀라운 힌트는 그 동안 우리가 그토록 목말라했던 기능을 제공한다. 쿼리를 수행하면서 바로 SQL Trace와 같은 실행 정보 추적을 할 수 없을까? 다음과 같이 가능하다.

select /*+ gather_plan_statistics */ * from t_rtm where id between 1 and 100;

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

PLAN_TABLE_OUTPUT
SQL_ID  bnxtw0wqkqmx5, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t_rtm where id between 1
and 100

Plan hash value: 2986615217

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| T_RTM     |      1 |     94 |     50 |00:00:00.01 |       5 |
|*  2 |   INDEX RANGE SCAN          | T_RTM_IDX |      1 |     94 |     50 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID">=1 AND "ID"<=100)

gather_plan_statistics라는 힌트 하나만으로 SQL Trace+Tkprof를 수행한 것과 거의 동일한 효과를 얻을 수 있다. 만세~
이 기능은 한가지 큰 장점은 Estimated 값과 Actual 값을 한 눈에 비교해 볼 수 있다는 것이다. 비용 계산이 합리적인지 가늠할 수 있는 기준이 된다.

gather_plan_statistics 힌트은 아래 블로그에서 아주 상세하게 다룬바 있다.
http://ukja.tistory.com/entry/%EC%98%A4%EB%9D%BC%ED%81%B4-%ED%8C%81-GATHERPLANSTATISTICS-%ED%9E%8C%ED%8A%B8

3) Oracle 11g ~
Real-Time SQL Monitoring (v$sql_monitor, v$sql_plan_monitor)

비록 gather_plan_statistics 힌트가 획기적인 기능을 제공하지만, 임시 방편에 불과하다.
가령 실행 시간이 5초 이상되는 느린 쿼리들의 실행 이력(plan과 row source operation까지 포함한)을 볼 수 있는 방법은 없을까?

Oracle 11g에서 추가된 Real-Time Monitoring기능이 그 해답을 제공한다. Oracle은 실행 시간이 5초 이상(_sqlmon_threshold 히든 파라미터로 조정)인 모든 쿼리의 실행 이력 정보를 v$sql_monitor와 v$sql_plan_monitor 뷰에 남긴다. 그리고 dbms_sqltune.report_sql_monitor 함수를 이용해서 정보를 조회할 수 있도록 해준다. 또는 MONITOR 힌트를 사용해서 강제로 추적하게 할 수도 있다. 아래 예를 보자. (정릴이 깨지는 현상 이해 바람)

select /*+ monitor */ * from t_rtm where id between 1 and 100;

select sql_id from v$sql where sql_text like 'select /*+ monitor */ * from t_rtm%';

sql_id = bpfu171khfbg6

select dbms_sqltune.report_sql_monitor(sql_id=>'bpfu171khfbg6') from dual

SQL Monitoring Report

SQL Text
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select /*+ monitor */ * from t_rtm where id between 1 and 100
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Global Information
 Status              :  DONE (FIRST N ROWS)
 Instance ID         :  1                  
 Session ID          :  147                
 SQL ID              :  bpfu171khfbg6      
 SQL Execution ID    :  16777218           
 Plan Hash Value     :  2986615217         
 Execution Started   :  01/09/2008 11:05:18
 First Refresh Time  :  01/09/2008 11:05:18
 Last Refresh Time   :  01/09/2008 11:05:19

---------------------------------------
| Elapsed |  Other   | Fetch | Buffer |
| Time(s) | Waits(s) | Calls |  Gets  |
---------------------------------------
|    0.00 |     0.00 |     2 |      5 |
---------------------------------------

SQL Plan Monitoring Details
==========================================================================================================================================
| Id |           Operation           |   Name    |  Rows   | Cost |   Time    | Start  | Starts |   Rows   | Activity  | Activity Detail |
|    |                               |           | (Estim) |      | Active(s) | Active |        | (Actual) | (percent) |   (sample #)    |
==========================================================================================================================================
|  0 | SELECT STATEMENT              |           |         |    3 |           |        |      1 |          |           |                 |
|  1 |   TABLE ACCESS BY INDEX ROWID | T_RTM     |      94 |    3 |           |        |      1 |          |           |                 |
|  2 |    INDEX RANGE SCAN           | T_RTM_IDX |      94 |    2 |         1 |     +0 |      1 |       50 |           |                 |
==========================================================================================================================================

기본적으로 gather_plan_statistics와 거의 비슷한 데이터를 제공하는 것을 확인할 수 있다. 이 데이터들은 v$sql_monitor와 v$sql_plan_monitor 뷰에 상주하기 때문에 필요하면 언제든지 직접 조회할 수도 있고, dbms_sqltune.report_sql_monitor 함수를 이용해 간편하게 조회할 수도 있다.

Real-Time SQL Monitoring기능은 DBA나 SQL Tuner들이 쿼리를 튜닝하는 방법을 획기적으로 개선시킬 수 있을 것으로 기대된다. 특별한 이유가 없는 한 더 이상 SQL Trace와 Tkprof를 번갈아가며 사용할 필요가 없을 것이다.


PS) 비록 SQL 실행을 추적할 수 있는 다양한 기법들이 추가되고 있지만, 여전히 SQL Trace는 가장 강력한 SQL 추적 툴이다.

                      SQL Trace여 영원하라~
신고
Trackback 0 : Comments 4
  1. 박광서 2010.04.29 15:15 신고 Modify/Delete Reply

    언제나 유익한 정보 감사히 얻고 갑니다.
    늘 건강하세요^^

  2. 이종욱 2010.06.29 10:53 신고 Modify/Delete Reply

    select dbms_sqltune.report_sql_monitor(sql_id=>'20bk5bj29ggpj') from dual;
    실행하면..

    SQL Monitoring Report 메세지와
    SQL Text 까지만 나옵니다~

    infomation 및 plan을 보려면.. 다른 세팅이 필요한건가요??

  3. 카미유 2011.05.05 10:20 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment

티스토리 툴바