Window 7을 사용하는 사람들은 ReadyBoost 기능을 잘 알 것이다. 부팅 시에 필요한 정보를 성능이 우수한 Flash Disk(USB)에 넣음으로써 부팅속도를 향상시키는 것이다. 즉 부팅 시에 성능이 느린 Hard Disk를 사용하지 않으므로 성능이 향상된다. 이와 비슷한 기능이 오라클에도 있다.

 

Oracle 11.2부터 Database Smart Flash Cache라는 것이 추가되었다. 이 개념은 buffer cache Aging Out되어 Disk로 내려가는 단점을 보완한 것이다. buffer cache에서 Aging Out 되더라도 성능이 우수한 Flash Disk(SSD)로 내려가는 개념이다. 따라서 SSD Aging out된 블록을 다시 읽을 때 기존 Disk보다 빠른 성능을 낼 수 있다. Database Smart Flash Cache 기능이 추가되어 메모리 구조도가 약간 변경되었다.
 

그림의 출처: Database Administrator's Guide 11g Release 2


Flash Disk
2 cache로 사용하는 셈이다. 하지만 Flash Disk에서 조차 aging out될 때는 Disk로 내려가는 것은 막을 수 없다.

 

제약사항

반드시 솔라리스나 오라클 엔터프라이즈 리눅스를 사용해야 한다.

 

어떨 때 사용해야 되나?

아래의 세 가지 경우를 모두 만족하면 Database Smart Flash Cache를 고려해야 한다.


첫 번째, AWR이나 Statspack에서 Buffer Pool Advisory를 참조하여 buffer cache가 부족한 경우

두 번째, db file sequential read Top Wait Eevnt일 경우

세 번째, 여분의 CPU가 있는 경우

 

위의 세 가지 경우를 모두 만족해야 되는 이유는 기존의 Disk에도 약간의 Cache기능이 있으므로, 위의 조건과 같이 buffer cache가 부족하거나 aging out이 많이 발생하는 경우만 성능향상의 폭이 크기 때문이다.

 

All or Nothing

RAC인 경우 하나의 SSD Disk를 다른 노드에 공유할 수 없다. , 노드별로 Smart Flash Cache를 별도로 설정해야 한다. 또한 하나의 노드에 Database Smart Flash Cache를 설정하였으면 나머지 노드에도 모두 Smart Flash Cache를 설정해야 한다.

 

Database Smart Flash Cache Size 설정

Flash Cachebuffer cache 2~10배 정도를 권고한다. Flash Cache의 Sizebuffer cache 2배보다 작으면 효과를 볼 수 없다.

 

Parameters

db_flash_cache_file:

Flash Cache로 사용할 파일의 경로를 설정한다. 만약 파일이 없으면 오라클이 startup시에 생성한다. 반드시 SSD내의 경로를 사용해야 한다. 그렇지 않으면 성능이 떨어진다.
:/dev/fioa1


db_flash_cache_size:

Giga 단위로 기술해야 한다. 이 파라미터를 0으로 설정하면 Database Smart Flash Cache기능이 Disable된다. 이 파라미터는 scope = memory 옵션을 사용할 수 없다. 따라서 이 파라미터를 적용하려면 Shutdown Startup이 필요하다.

: 16G

 

Buffer Cache 튜닝

aging out이 발생하여 buffer cache에서 Flash Cache로 밀려날 때에도 블록의 메타정보는 그대로 남게 된다. 그 메타정보는 어림잡아 한 블럭당 100 byte 정도이다. 만약 RAC라면 한 블럭당 200 byte를 차지한다. 이 메타정보는 블록이 flash cache로 이동될 때 flash cache내의 address 정보를 가지고 있을 것으로 추측된다. 만약 Flash Cache를 사용할 것이라면 buffer cache block 100 byte 정도(RAC라면 200 byte) 줄어들므로 그만큼 더 잡아주기 바란다.

 

필자의 여건상 Database Smart Flash Cache를 테스트 해볼 수 없다. 누가(SSD를 보유한 사람이) 테스트를 하여 후기를 올려주었으면 한다. 테스트 시나리오는 아래와 같다. Flash Cache의 적용 전/후의 성능을 비교하는 것이다. 나라면 이렇게 테스트 할 것이다.

 

Database Smart Flash Cache 미적용 시나리오

1. buffer cache를 아주 작게 잡는다.

2. 큰 테이블 두 개에 각각 사이즈가 큰 인덱스를 하나씩 만들어 Nested Loop Join을 시키면 aging out이 발생될 것이다.

 

Database Smart Flash Cache 적용 시나리오

1. buffer cache를 아주 작게 잡는다.

2. Database Smart Flash Cache기능을 Setup 한다.

3. 큰 테이블 두 개에 각각 사이즈가 큰 인덱스를 하나씩 만들어 Nested Loop Join을 시키면 aging out이 발생될 것이다.

 

측정항목

AWRdb file sequential read의 부하가 얼마나 줄어드는지 관찰한다.

Nested Loop Join의 전체건 처리 속도는 얼마나 빨라지는지

 

이상으로 Oracle 11.2에 추가된 Database Smart Flash Cache 기능에 대해 살펴보았다.  한가지 이상한 점은 Database Smart Flash Cache 기능을 HP IBM 서버 등에는 사용할 수 없다는 것이다. 요즘 Oracle HP와 사이가 좋지 않다. 오라클의 가격정책도 이런 사실을 증명해준다. 돌아올 수 없는 강을 건넌 것인가? 아니면 Oracle 다음 버전(V 12)에서 HP IBM서버도 지원 할 것인가? 나는 전자라고 생각한다.

 

Reference:

Oracle Database 11g Release 1 (11.2.0.1) New Features- 1.8.1.1 Database Smart Flash Cache

Oracle Database Administrator's Guide 11g Release 2-Configuring Database Smart Flash

Posted by extremedb

댓글을 달아 주세요

  1. salvationism 2011.05.09 15:23  댓글주소  수정/삭제  댓글쓰기

    [신기능들에 대한 불신과 베타 테스터]
    신기능을 쓰면 항상 따라오는 베타 테스터 라는 느낌.
    어떻게 보면 느낌이 아니라 사실 입니다.

    신기능이 늘어날 수 록..
    한편에서는 장애 포인트만 많아지네.. 이런 생각이 간혹 듭니다.

    ID 1260804.1 를 읽어 보면 ExaData에서
    판치고 있는 Wrong Result의 심각성과 늘어난 장애 포인트를 느끼게 됩니다.
    Exa 지원하면서 많이 느끼기도 했구요.

    smart flasg cache는 Exa에 비해 단순해서 좀 안정적일거라는 짐작은 합니다.
    운영에서의 적용은 번거로운면이 많이서 극히 일부만 쓰지 않을까하는.. ㅎㅎ;;

  2. 라튜니 2011.05.31 15:00  댓글주소  수정/삭제  댓글쓰기

    다음글이 언제 포스팅 되는지 궁금합니다~ 요즘 포스팅이 너무 뜸하신거 같아요~

  3. NCDB 2011.05.31 19:20  댓글주소  수정/삭제  댓글쓰기

    안녕하세요.
    동규님 좋은글 항상 감사하며 보고 있습니다.
    다름이 아니라 동규님 추천으로 relational database index design and the optimizers 란 책을 샀는데요.
    여기 챕터 끝나면 연습문제가 있는데 혹시 솔루션을 어디서 구할수 있는가 해서여.
    FTP로 들어가서 보니 답이 안나와 있습니다.
    바쁘시겠지만 답변 부탁드리겠습니다.
    감사합니다.

  4. ExtraOdinary 2011.06.01 17:40  댓글주소  수정/삭제  댓글쓰기

    좋은 정보 잘 보았습니다. 보면서 한가지 의문이 드는데요. 기존 Buffer Cache에서 자주 밀려나는 Block 즉, FTS에 의해 Buffer Cache에서 자주 밀려나는 블록들이 Flash Cache에 캐싱이 될텐데요. 그 경우 기존의 FTS 보다 훨씬 속도 측면에서
    빨리질 것으로 생각이 됩니다.
    이런 경우 System statistics를 사용하는 환경에서 Multi Block I/O의 속도가 빨라져 실행계획이 FTS에 유리하도록 Cost 계산이 될 것 같은데, 이 때 실행계획이 바뀌는 현상은 없을까요?

  5. Favicon of http://www.battery-uk.co.uk BlogIcon batterypang 2011.10.21 15:05  댓글주소  수정/삭제  댓글쓰기

    좋은 정보 잘 보았습니다. 보면서 한가지 의문이 드는데요. 기존 Buffer Cache에서 자주 밀려나는 Block 즉, FTS에 의해 Buffer Cache에서 자주 밀려나는 블록들이 Flash Cache에 캐싱이 될텐데요. 그 경우 기존의 FTS 보다 훨씬 속도 측면에서
    빨리질 것으로 생각이 됩니다.
    이런 경우 System statistics를 사용하는 환경에서 Multi Block I/O의 속도가 빨라져 실행계획이 FTS에 유리하도록 Cost 계산이 될 것 같은데, 이 때 실행계획이 바뀌는 현상은 없을까요?

  6. DBA 2011.12.14 19:24  댓글주소  수정/삭제  댓글쓰기

    제 생각에는 차라리 Memory를 더 꽂아서 Buffer cache를 늘려주는게 어떨까 하는 것이구요.
    Storage 에도 캐쉬 메모리를 더 늘려주는 방안이 있을 수 있습니다. Read/write 캐쉬를 더 늘려주는 것이죠.

    Buffer cache내에서 Keep pool, recycle pool등을 활용하는 방안도 있을 수 있을 것이구요.

    물론 SSD를 2차 캐쉬처럼 쓰는 위의 방법도 나름 장점은 있을 것 같습니다. 이른바 하이브리드 구성인데요.
    SSD 가격이 비싸서 전체를 SSD로 하기에는 비용 문제가 너무 크다. 따라서 하이브리드 방식을 채택하다는 것인데,
    어느 정도 효과는 있을 것이 분명합니다.

    솔라리스하고 리눅스에서만 구성이 가능하다는 점은 범용적으로 쓰이기에는 한계가 있다는 것이 아쉽군요.

    또 하나 위에도 적었듯이 비용 문제인데요.
    SSD 살 돈으로 메모리 더 사고... 마그네틱 하드디스크 성능에 더 투자를 하는 방법대비 어느게 더 싸게 먹히느냐라는 문제가 있습니다.

  7. 유일환 2012.11.09 17:37  댓글주소  수정/삭제  댓글쓰기

    블로그 매우 잘보고 있습니다. 좋은글 감사드립니다!!! 꾸벅!!

DBMS_XPLAN 패키지의 장점은 포맷을 자유로이 설정한다는것
  작년 겨울에 About DBMS_XPLAN - 1.실행계획 이라는 글에서 실행계획의 세부항목을 소개한바 있다.
이 패키지의가장 뛰어난 특징은 사용자가 출력 포맷을 설정하여 원하는 정보만 얻을수 있다는 것이다.
따라서 이번에는 DBMS_XPLAN 패키지 사용시 포맷설정을 자유롭게 하기 위한 Format Controller를 소개하려 한다.

Format Controller는 아래와 같이 3가지 종류가 있다.

1) 기본 Format Controller : 반드시 적용되어야 하는 기본적인 Controller 이다.
                                   적용하지 않더라도 자동으로 기본값으로 적용된다.

2)세부 Format Controller: 기본 포맷정보에 의해서 표시되거나 생략되는 되는 세부적인 포맷을 Control 한다.
                                   이 Control은  + 표시로 추가하거나 - 표시로 생략이 가능하다.

3)실행통계 Format Controller: 이 Control을 적용하면 실행시의 PGA 통계를 출력한다.

이제 한가지씩 상세히 살펴보자.

1)  기본 Format Controller
1.basic
     : 가장 기본적인 포맷으로서 id, Operation, Object Name을 출력한다.
2.typical     : basic 옵션에서 한발더 나아가서 옵티마이져가 에상할수 있는 모든것들을 보여준다.
                  출력되는 정보로는 예상 row, 예상 bytes, 예상 temporary space 사용량, cost, 예상시간,
                  Predicate Information(Operation 별로 access 및 filter 정보) 이다.
3.serial      : typical 과 같으나 parallel 쿼리사용시 관련 정보가 나오지 않는다.
4.all           : plan 정보는 typical 과 같으나 plan 이외의 정보중에서 Outline Data 정보를 제외하고 전부 출력한다.
5.advanced : all 과 같지만 Peeked Binds, Outline Data, note 등을 더보여준다.

2)  세부 Format Controller

1.alias  :Operation 별로 쿼리블럭명과 object alias 를 control 한다.
             plan 의 하단에 위치하며 쿼리변형이 발생하거나 복잡한 쿼리 튜닝의 경우 유용하다.        
2.bytes : plan 상의 E-Bytes 정보를 control 한다.
3.cost  :plan 상의 Cost (%CPU)를 control 한다.
4.note  : 결과중 가장 마지막에 위치하며 여러가지 유용한 정보를 보여준다.
            예를 들면 dynamic sampling 이 사용되었는지의 혹은 plan_table 이 old 버젼이므로 새로만들어야
            한다는 등의 유용한 정보를 나타낸다.     
5.outline : Outline Data를 control 한다. USER 가 작성한 힌트와 옵티마이져가 추가한 내부적인 힌트들이
               포함된다. 쿼리변형이 발생하거나 복잡한 쿼리 튜닝의 경우 유용하다.   
6.parallel : PARALLEL 쿼리인경우 TQ, IN-OUT, and PQ Distrib 등의 정보를 control 한다.
7.partition :파티션 ACCESS 가 포함된경우 Pstart(시작 파티션) and Pstop(종료 파티션) 등의 정보를 control 한다.
8.peeked_binds : BIND 변수의 값을 control 한다. 단 _optim_peek_user_binds 파라미터의 값이 TRUE 로
                          되어있는 경우만 해당되며  파라미터는 세션단위로 수정이 가능하다. EXPLAIN PLAN 을
                          사용한 경우에는 나타나지 않는다.
9.predicate : Predicate Information을 control 한다. Operation 별로 access 및 filter 정보를 나타낸다.
                   일반적인 튜닝시 가장 눈여겨 보아야할 정보이다.
10.projection : projection information을 control 한다. Operation 별로 select 되는 컬럼정보를 나타낸다.
11.remote : DBLINK 를 사용힐때 REMOTE 쿼리의 수행정보를 control 한다.
12.rows : plan 상의 E-Rows수를 control 한다.

3)  실행통계 Format Controller

  이정보들은 DBMS_XPLAN.DISPLAY 함수에는 적용되지 않는다. 왜냐하면 explain plan 은 쿼리가 실제 수행되는것이 아니므로 실행통계정보가 없기 때문이다.
또한 DBMS_XPLAN.DISPLAY_CURSOR 나 DBMS_XPLAN.DISPLAY_AWR 등의 함수 수행시에도 GATHER_PLAN_STATISTICS 힌트를 주거나 아니면 파라미터 STATISTICS_LEVEL = ALL 로 되어 있어야 출력이 가능하다.

1.allstats : I/O 통계정보(Buffers, Reads, Writes)와 PGA 통계정보(OMem, 1Mem , Used-Mem, Used-Tmp,
                Max-Tmp 등)를 동시에 control 한다.
2.iostats : I/O 통계정보(Buffers, Reads, Writes)를 control 한다.
3.last : 실행통계 출력시 이 control을 명시하면 가장마지막에 수행된 실행통계를 출력한다.
          이 control을 명시하지 않으면 실행통계의 누적치를 출력하므로 주의가 필요하다.
4.memstats :PGA 통계정보(OMem, 1Mem , Used-Mem, Used-Tmp, Max-Tmp 등)를 control 한다.
5.runstats_last : iostats control 과 last control 을 합친것과 같다.
                       이 control은 Oracle 10g Release 1 에서만 사용할수 있다.
6.runstats_tot : iostats control과 동일하다.  이 control은 Oracle 10g Release 1 에서만 사용할수 있다.

주의사항 : runstats_last 와 runstats_tot 를 제외한 4가지의 control은 Oracle 10g Release 2 에서만 사용할수 있다.


그럼 이제 적용해볼까?
  위에서 설명한 Controller 를 이용하여 Format 을적용해보자.

SElECT  /*+ gather_plan_statistics */  *
FROM EMP E
WHERE E.DEPTNO = :B1
  AND ROWNUM <= 100
ORDER BY EMPNO;

이후로는 위의 SQL 은 동일하므로 생략된다.

SELECT *
   FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'advanced allstats last' ) );


아래의 plan 은 지면관계상 잘려서 2줄로 나타내었음을 이해해주기 바란다.

----------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | E-Rows |E-Bytes|E-Temp |
----------------------------------------------------------------------------------
|   1 |  SORT ORDER BY                |        |      1 |    100 |  2600 |   153M|
|*  2 |   COUNT STOPKEY               |        |      1 |        |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |      1 |   2002K|    49M|       |
|*  4 |     INDEX RANGE SCAN          | EMP_N1 |      1 |   2003K|       |       |
----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
 Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------
 19034   (1)| 00:03:49 |      9 |00:00:00.01 |       4 |  2048 |  2048 | 2048  (0)|
            |          |      9 |00:00:00.01 |       4 |       |       |          |
  4126   (1)| 00:00:50 |      9 |00:00:00.01 |       4 |       |       |          |
   989   (1)| 00:00:12 |      9 |00:00:00.01 |       3 |       |       |          |
-----------------------------------------------------------------------------------

 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / E@SEL$1
 
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_bloom_filter_enabled' 'false')
      OPT_PARAM('_optimizer_connect_by_cost_based' 'false')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(1)
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMP"."DEPTNO"))
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=100)
   4 - access("E"."DEPTNO"=:B1)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) "E"."EMPNO"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "E"."EMPNO_VARCHAR"[VARCHAR2,40], "E"."JOB"[VARCHAR2,2], "E"."HIREDATE"[DATE,7]
   2 - "E"."EMPNO"[NUMBER,22], "E"."EMPNO_VARCHAR"[VARCHAR2,40], "E"."JOB"[VARCHAR2,2], "E"."HIREDATE"[DATE,7], "E"."DEPTNO"[NUMBER,22]
   3 - "E"."EMPNO"[NUMBER,22], "E"."EMPNO_VARCHAR"[VARCHAR2,40], "E"."JOB"[VARCHAR2,2], "E"."HIREDATE"[DATE,7], "E"."DEPTNO"[NUMBER,22]
   4 - "E".ROWID[ROWID,10], "E"."DEPTNO"[NUMBER,22]
 

 'advanced allstats last' 포맷은 출력되는 정보가 너무많아
   'advanced allstats last' 포맷을 적용하였으므로 DBMS_XPLAN.DISPLAY_CURSOR 가 보여줄수 있는 모든
정보를 출력 하였다. 단 지면 관계상 가장 처음에 나오는 SQL TEXT 와 sql_id, child number, plan_hash_value 등은 생략하였다. 많은정보를 생략하였음에도 불구하고 일반적인 튜닝시 필요가 없는 정보가 모두 출력되고 말았다. 

 이제 위에서 정의된 각 Controller 를 이용하여 여러분만의 Format 을 만들어보자.
필자의 경우 가장 선호하는 포맷은 아래의 두가지 이다.
 
권장되는 포맷유형 2가지

1.쿼리변형이 없는 단순 쿼리 튜닝의 경우:

SELECT *
   FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'allstats last -rows +predicate'));

 포맷을 'allstats last -rows +predicate' 로 주었으므로 예측 row 수(E-row) 가 생략되고 실행통계와
Predicate Information 만을 출력한다.
 아래의 plan 또한 너무길어 지면관계상 2줄로 나타내었다.

-------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | A-Rows |   A-Time   |
-------------------------------------------------------------------------------
|   1 |  SORT ORDER BY                |        |      1 |      9 |00:00:00.01 |
|*  2 |   COUNT STOPKEY               |        |      1 |      9 |00:00:00.01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      9 |00:00:00.01 |
|*  4 |     INDEX RANGE SCAN          | EMP_N1 |      1 |      9 |00:00:00.01 |
-------------------------------------------------------------------------------

      -------------------------------------
       Buffers |  OMem |  1Mem | Used-Mem |
      -------------------------------------
             4 |  2048 |  2048 | 2048  (0)|
             4 |       |       |          |
             4 |       |       |          |
             3 |       |       |          |
      -------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=100)
   4 - access("E"."DEPTNO"=:B1)
 
 
깔끔하게 꼭필요한 정보만 출력 되었다.


2.쿼리변형이 발생하거나 복잡한 쿼리 튜닝의 경우.

SELECT * FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'allstats last -rows +alias +outline +predicate'));


 'allstats last -rows +alias +outline +predicate' 포맷을 사용하면 Query Block Name / Object Alias 정보와 Outline Data 정보가 추가로 출력된다.
아래의 plan 도 지면관계상 2줄로 나타내었다.

-------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | A-Rows |   A-Time   |
-------------------------------------------------------------------------------
|   1 |  SORT ORDER BY                |        |      1 |      9 |00:00:00.01 |
|*  2 |   COUNT STOPKEY               |        |      1 |      9 |00:00:00.01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      9 |00:00:00.01 |
|*  4 |     INDEX RANGE SCAN          | EMP_N1 |      1 |      9 |00:00:00.01 |
-------------------------------------------------------------------------------

      -------------------------------------
       Buffers |  OMem |  1Mem | Used-Mem |
      -------------------------------------
             4 |  2048 |  2048 | 2048  (0)|
             4 |       |       |          |
             4 |       |       |          |
             3 |       |       |          |
      -------------------------------------

 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / E@SEL$1
 
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_bloom_filter_enabled' 'false')
      OPT_PARAM('_optimizer_connect_by_cost_based' 'false')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(1)
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMP"."DEPTNO"))
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=100)
   4 - access("E"."DEPTNO"=:B1)
 
  쿼리변형이 발생한경우나 뷰(혹은 인라인뷰) 등을 튜닝할경우 아주 적합한 옵션이다. 하지만 특이한경우
Column Projection Information 이 필요한경우도 있다. 이경우는 +projection 포맷을 추가해주면 된다.

  자신만의 적절한 포맷이 필요해
 출력되는 정보의 양이 너무 많으면 소화 하기가 힘들고 너무 적으면 튜닝하기가 어려워진다. SQL이 아무리 복잡하고 다양한 경우가 있더라도 2~3 가지의 Format 조합으로도 충분하다. 여러분 각자의 입맛에 맞는 Format 을 개발해보길 바란다. 물론 그러기 위해서는 각각의 Controller 들과 친해질 필요가 있다.

Posted by extremedb

댓글을 달아 주세요

FPD(Filter Push Down)이란 뷰 바깥쪽의 조건을 뷰안으로 진입 시킨다는 의미이다.
FPD 는 자체로도 작동하지만 Subquery Flattening, View Merging, Join Predicate Pushdown 등이 진행될 때에도 부가적으로 실행되는 아주 중요한  Query Transformation 기법 이다.
FPD 와 관련하여 튜너및 DBA 들이 잘못알고 있는 사실이 있으므로 이것을 바로 잡고자 한다.
no_merge 힌트를 사용하면 뷰 바깥쪽의 조건들이 뷰 내로 파고들지 못한다는 미신이 있다.
과연 이것이 맞는 이야기인가?
먼저 테스트를 위하여 테이블을 하나 만들어야 한다.
환경 Oracle 11.1.0.6

CREATE TABLE scott.BIG_EMP AS    --> big_emp 생성
SELECT ROWNUM AS EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL + rownum as SAL, A.COMM, A.DEPTNO
  FROM scott.EMP A,
       (SELECT LEVEL AS NO FROM DUAL CONNECT BY LEVEL <= 2000) B;

ALTER TABLE scott.BIG_EMP --> PK 생성
   ADD ( CONSTRAINT PK_BIG_EMP PRIMARY KEY (EMPNO) USING INDEX );

CREATE INDEX scott.BIG_EMP_SAL_IX ON scott.BIG_EMP (SAL); --> SAL 항목에 인덱스 생성

dbms_stats.gather_table_stats('SCOTT', 'BIG_EMP', cascade => true);


 아래의 SQL 을 본다면 no_merge 힌트는 Filter Push Down(조건절을 뷰안으로 진입)을 방해하지 않음을 알수 있다.

select /*+ gather_plan_statistics */
         deptno, max_sal
  from (select /*+ no_merge index(big_emp big_emp_sal_ix) */
                   deptno, max(SAL) max_sal
          from big_emp
         group by deptno)
where  max_sal  >  32900
;

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  VIEW                          |                |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   FILTER                       |                |      1 |      1 |00:00:00.01 |       3 |
|   3 |    HASH GROUP BY               |                |      1 |      1 |00:00:00.01 |       3 |
|   4 |     TABLE ACCESS BY INDEX ROWID| BIG_EMP        |      1 |      7 |00:00:00.01 |       3 |
|*  5 |      INDEX RANGE SCAN          | BIG_EMP_SAL_IX |      1 |      7 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(MAX("SAL")>32900)
   5 - access("SAL">32900)    

재미있는 점은 뷰 바깥의 max_sal  >  32900 조건을 뷰내로 진입시키면서 아래처럼 where 절을 2단계로 추가 하였다.

1단계: having 절로 변환       --> having max(SAL) > 32900
2단계 :where 조건으로 변환  --> where sal > 32900

실제로 10053 Event 정보를 분석해보면 FPD(Filter Push Down) 가 2단계로 진행된다는 것을 알수 있다.
그러면 옵티마이져가 변경한 SQL 보자.

select /*+ gather_plan_statistics */
         deptno, max_sal
  from (select /*+ index(big_emp BIG_EMP_SAL_IX) */
                   deptno, max(sal) max_sal
          from big_emp
         where sal > 32900               --> 옵티마이져가 FPD 로 조건을 생성함.
         group by deptno
         having max(sal) >  32900    --> 옵티마이져가 FPD 로 조건을 생성함.
       )
;

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  VIEW                          |                |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   FILTER                       |                |      1 |      1 |00:00:00.01 |       3 |
|   3 |    HASH GROUP BY               |                |      1 |      1 |00:00:00.01 |       3 |
|   4 |     TABLE ACCESS BY INDEX ROWID| BIG_EMP        |      1 |      7 |00:00:00.01 |       3 |
|*  5 |      INDEX RANGE SCAN          | BIG_EMP_SAL_IX |      1 |      7 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(MAX("SAL")>32900)
   5 - access("SAL">32900)

Plan 및 Predicate Information 가 원본쿼리와 완전히 일치함을 알수 있다.

오히려 이런 경우는 원본쿼리에 merge 힌트를 사용하는것이 더 위험하다.
아래의 SQL 을 보자.

select /*+ gather_plan_statistics qb_name(outer) */
          deptno, max_sal
  from (select /*+ MERGE qb_name(inner) index(big_emp BIG_EMP_SAL_IX) */
                   deptno, max(sal) max_sal
          from big_emp
         group by deptno
         )
where  max_sal  >  32900
;


--------------------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------
|*  1 |  FILTER             |         |      1 |      1 |00:00:00.02 |     177 |
|   2 |   HASH GROUP BY     |         |      1 |      3 |00:00:00.02 |     177 |
|   3 |    TABLE ACCESS FULL| BIG_EMP |      1 |  28000 |00:00:00.03 |     177 |
--------------------------------------------------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(MAX("SAL")>32900)

PLAN 정보의 Buffers(read 한 블럭수) 를 보면 일량이 3에서 177 로 늘어난 것을 알수 있다.
Group By 된 뷰가 merge 힌트에 의해서 해체 되었다.
Merge 되는 경우는 having 절만 생성되며 where 조건은 추가적으로 생성되지 않음을 알수 있다.
따라서 다음과 같은 SQL 로 변환된것을 알수 있다.

select deptno,
       max(sal) max_sal
  from big_emp
 group by deptno
 having max(sal) >  32900 ;  --> 추가적인 where 절이 없음

위와 같이 변경된것을 Complex View Merging 이라고 한다.
Complex 가 추가적으로 붙은 이유는 group by 절이 있는 뷰를 Merge(해체) 했기 때문이다.

결론 :
집계함수를 사용하는 뷰의 바깥에서 집계함수의 결과를 filter 할경우 merge 힌트를 사용하면 뷰가 해체 되면서 Having 절로 변환이 되지만 where 조건은 생성되지 않으므로 인덱스가 있다고 해도 사용할수 없음을 알수 있다.
오히려 이런 경우는 no_merge 힌트를 사용하여 FPD 를 유도하는 것이 정답인 것이다.

Posted by extremedb

댓글을 달아 주세요

  1. feelie 2009.09.23 21:36  댓글주소  수정/삭제  댓글쓰기

    지난내용이데 궁금해서 질문합니다.
    Subquery Flattening - SEMI join,
    View Merging - 뷰쿼리가 액세스 쿼리로 변환
    Join Predicate Pushdown - 액세스쿼리가 뷰쿼리로 변환
    맞습니까?
    filter push down 도 조건절진입과 동일한 것인가요?
    모 책에서는 조건절진입, 뷰 병합 으로만 설명이 되어있어 위의 내용들에 대한 정확한 개념이 잡히질 않네요..

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.09.23 22:42 신고  댓글주소  수정/삭제

      말씀하신 책을 읽은지가 오래되서 가물가물 합니다.
      그래서 다른방법으로 설명드립니다.
      Subquery Flattening - (SEMI/ANTI join, Subquery Unnesting) -서브쿼리가 없어지고 조인으로 바뀜.
      View Merging - 뷰 혹은 인라인뷰가 해체 됨(테이블끼리 조인으로 바뀜)
      JPPD - 조인조건이 뷰 혹은 인라인뷰 내부로 파고 듬
      FILTER PUSH DOWN - 조인조건이 아닌 FILTER 조건이 뷰 혹은 인라인뷰 내부로 파고듬.
      이렇게 정리하시면 쉬울겁니다.

      외국의 유명한 영문학 교수가 한국에 와서 영어문법에 대한 강연을 하는데 한 학생이 질문을 하였습니다.
      학생 :'5형식중에서 가장 쉬운것을 하나 소개해주세요'
      교수의 답변이 쇼킹 합니다.
      교수 : '5형식이 뭔가요?'

      뷰쿼리, 엑세스쿼리 ....음 용어 정말 어렵네요.
      하지만 일반적으로 통용되는 용어라면 아시는게 좋을수도....

  2. feelie 2009.09.24 12:59  댓글주소  수정/삭제  댓글쓰기

    감사합니다.
    JPPD, FPD을 구별하기가 어려웠는데 조인조건이냐 아니냐 군요.
    감사합니다.

  3. Favicon of https://1ststreet.tistory.com BlogIcon SITD 2011.05.12 15:14 신고  댓글주소  수정/삭제  댓글쓰기

    옵티마이져 진짜 영리하네요.

    좋은 글 감사드립니다~

  4. eqon 2013.11.20 11:34  댓글주소  수정/삭제  댓글쓰기

    오라클 버전은 11.2.0.2.0

    view내부로 predicate파고 들어가지 않게 제어 하고 싶은데 계속 들어가버리네요.

    53 트레이스에서 확인해보니까 아래와 같이 FPD가 작동하네요.

    FPD: Considering simple filter push (pre rewrite) in query block QB2 (#0)

    FPD기능을 끌 수 있는 파라미터가 없을까요

    /*+ NO_MERGE no_push_pred
    OPT_PARAM('_push_join_predicate' 'false')
    OPT_PARAM('_pred_move_around' 'false')
    OPT_PARAM('_optimizer_push_pred_cost_based' 'false')
    OPT_PARAM('_simple_view_merging' 'false')
    OPT_PARAM('_optimizer_enhanced_filter_push' 'false' )
    OPT_PARAM('_optimizer_filter_pred_pullup' 'false') */

    이 정도 힌트 넣어봤는데 아무것도 안 먹네요.
    선생님 책 93페이지 filter push down절에서도 제어할 수 있는 파라미터는 안 나와 있네요. 할 수 없이 뷰 안에 의미없는
    rownum > 0 넣어주니까 기능이 작동하지 않는 걸 확인했는데요.
    힌트를 제어할 수 있는 방법을 알고 싶어요.

Hash Join Right (Semi/Anti/Outer) 의 용도 라는 글에서 Internal Hint Transformation 개념을 설명한적이 있다.
오늘은 예제 몇가지에 대하여 소개하려 한다.
먼저  Internal Hint Transformation 이 왜 일어나는지 설명하기 위하여 힌트의 종류를 나누어 보자.
오라클 Performace Tuning Guide 에 보면 힌트의 Type 에 대해서 아래와 같이 분류하고 있다.

1.Single Table Hints : 하나의 테이블이나 인덱스에 대하여 사용하는 힌트
                               index 관련 힌트나 use_nl, use_merge, use_hash 등이 여기 속한다.
2.Multi Table Hints : 여러 테이블이나 블럭에 대하여 사용하는 힌트.
                             leading 힌트나 index_join, index_combine 등이 여기에 해당된다.
3.Query Block Hints: 하나의 쿼리블럭에 사용하는 힌트
                             STAR_TRANSFORMATION, UNNEST, MERGE, PUSH_PRED,  USE_CONCAT, NO_EXPAND 등이 여기에 해당된다.
4.Statement Hints : 전체 SQL 단위로 사용하는 힌트
                           all_rows, first_rows_n 등이 여기에 해당된다.

가장 흔한 힌트변환은 use_nl(a, b) 를 leading(a b) use_nl(b) 로 바꾸는 것이다.
use_nl 은 Single Table Hints 이기 때문이다.
또한 use_nl_with_index 등을 사용하여도 각각 index, use_nl 힌트등으로 바꾸어 버린다.
이중에서 가장 극적인 Internal Hint Transformation 예제는 Index_Combine 이다.
아래 예제를 보자.

환경 :10.2.0.4

먼저 Bit map 인덱스를 2개 만든다.
create bitmap index IX_EMP_N2 on emp(mgr);
create bitmap index IX_EMP_N3 on emp(deptno);

dbms_stats.gather_table_stats(user, 'EMP', cascade => true);  

 select /*+ gather_plan_statistics index_combine(emp IX_EMP_N2 IX_EMP_IDX3) */
        empno, mgr, deptno
   from emp
  where NOT( mgr = 7698 )
    and  deptno = 20;

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

오라클은 위의 힌트를 어떻게 바꿀까?
dbms_xplan.display_cursor 의 결과중에 Outline Data 를 보면 아래와 같다.
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_bloom_filter_enabled' 'false')
      OPT_PARAM('_optimizer_connect_by_cost_based' 'false')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(1)
      OUTLINE_LEAF(@"SEL$1")
      BITMAP_TREE(@"SEL$1"
"EMP"@"SEL$1
" AND(("EMP"."DEPTNO") MINUS(("EMP"."MGR")) MINUS_NULL(("EMP"."MGR"))))
      END_OUTLINE_DATA
  */


이상하지 않은가?
Bitmap_tree 라는 힌트는 사용하지도 않았다.
오라클은 Index_Combine 힌트를 Bitmap_Tree 힌트로 바꾼것이다.
이 암호와도 같은 힌트를 간단히 바꾸면 아래와 같다.
BITMAP_TREE(emp and( (emp.deptno) minus((emp.mgr)) minus_null((emp.mgr))))
간단히 설명하면  deptno = 20 을 만족하는 집합에서  mgr = 7698  을 만족하는 집합을 뺴주는것(minus) 이다.
그렇다면 남은 minus_null 힌트는 무엇일까?
이힌트는 부정형으로 Bit Map 비교시에만 나타난다.
다시말하면 bit map 연산시 mgr 이 7698 이 아닌것을 나타내면 mgr is null 인 데이터가 포함이 되어 버린다.
따라서 mgr is null 인 집합도 빠져야 하기 때문에 옵티마이져는 minus_null(mgr) 힌트를 사용한 것이다.
이것은 Bit map Operation 의 특성에서 나온것이다.
참고로 where 절에 mgr is not null 이라고 명시하거나 혹은 not null Constraints 를 주게되면 minus_null 힌트는 사라진다.

아래의 Predicate Information 를 보면 상세히 알수 있다.

Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("DEPTNO"=20)            --> (("EMP"."DEPTNO")
   6 - access("MGR"=7698)              --> MINUS(("EMP"."MGR"))
   7 - access("MGR" IS NULL)         --> MINUS_NULL(("EMP"."MGR"))

결론 : Query Transfomation 에는 Internal Hint Transformation 도 포함 되어야 한다.
         Internal Hint Transformation 는 힌트의 용법을 정확히 지키지 않으면 거의 모든곳에서 나타날수 있다.
         또한 용법을 정확히 사용하여도 내부적으로 변환시키는 경우가 많이 있다.
         그러나 이런것이 나타난다고 해서 걱정할 필요는 없다.
         또한 Internal 힌트를 사용할 필요도 없다.
         단지 "옵티마이져가 내부적으로 이런일을 하고 있다." 라고 알고 있으면 당황하지 않을 것이다.


Posted by extremedb

댓글을 달아 주세요

최근의 많은수의 사람들이 DBMS_XPLAN 패키지를 사용하여 튜닝을 하고 있다.
필자는 DBMS_XPLAN 패키지에 대한 너무많은 질문공세 때문에 아예 블로그에 올릴 결심을 하였다.
오늘은 DBMS_XPLAN 패키지에 대한 첫번째 이야기로 가장중요한 실행계획에 대하여 조목조목 따져보려고 한다.

DBMS_XPLAN 패키지는 9i 부터 점점 발전하여 지금은 Trace + tkprof 보고서와 자웅을 겨룰 정도로 발전하고
있다.
DBMS_XPLAN 패키지내의 함수는 10g R2 기준으로 6개 이지만 가장 자주 사용하는 함수는 아래의 3가지 이다.

1.DISPLAY                 --> 예측 실행계획을 보여준다.
2.DISPLAY_CURSOR   --> 실제 실행된 실행계획을 보여준다.
3.DISPLAY_AWR         --> 실제 실행된 실행계획을 보여준다.

오늘의 주제는 실행계획상의 각항목에 대한 설명이므로 3개의 함수에 대한 자세한 설명은 다음에 계속하여
연재할 계획이다.

실행계획은 패키지 내의 3가지 함수(display, display_cursor, display_awr)를 통해 모두 조회가 가능하다.

아래의 스크립트는 display_awr 의 예제이며 sql_id 만 구하면 언제든지 실행될수 있다.

select * from table(dbms_xplan.display_awr(:v_sql_id,null,null,'advanced allstats last'));



사용자 삽입 이미지





위 PLAN 은 DBMS_XPLAN 패키지의 format 항목을 Advanced 로 했을 경우에 나타나는 Plan 의 모습이다.
아래는 위의 Plan 항목 하나하나에 대한 자세한 설명이다.
물론 위의 예제는 실행계획의 모든 항목이 나온것은 아니다.
예를 들면 파티션테이블을 사용하지 않았으므로 Partiton 관련 항목이 빠진것이다.

DBMS_XPLAN 패키지의 실행계획의 항목은 아래처럼 크게 7개로 나눌수 있다.

주의사항:

파랑색부분( 5), 6), 7)번 에해당됨)은 실행통계가 있을경우만 해당된다.

display_cursor, display_awr 의  경우 statistics_level 파라미터를 all 로 설정하거나 SQL 에 gather_plan_statistics 를 사용한경우 실행통계를 볼수 있다.

 

1)Basics 항목 (Always Available)

Id                : Operation ID .

                  * 가 달려있는 경우는 predicate 정보에 access filter 에 관한정보가 나옴을 표시한것임.

Operation  : 각각 실행되는 JOB 을 나타냄

                    row source operation.의 줄임말임.

Name          : Operation 이 엑세스하는 테이블 및 인덱스를 나타냄.

2)Query Optimizer Estimations(옵티마이져의 예상 row 수 및 bytes, cost , temp 사용량)

Rows (E-Rows)  : operation 이 끝났을 때 return되는 건수를 나타냄.

                              이것은 예측 건수 이므로 실제 건수와는 다름.

Bytes (E-Bytes) : operation return byte .
                             
예상치 이므로 실제 받은 byte와는 다름

TempSpc             : operation. temporary space 를 사용한 양(예상치임)

Cost (%CPU)      : operation Cost. (예상치 임)

                              괄호안의 내용은 CPU Cost 의 백분율임.

                               이값은 Child Operation Cost 를 합친 누적치임.

Time                     : 예측 수행시간

 

3)Partitioning (파티션을 엑세스 할경우만 나타남)

Pstart  : 파티션을 엑세스 하는경우 시작파티션을 나타냄

             상수로 들어올때는 파티션 번호로 나타나며 변수로 들어올때는 KEY 로 나타남

Pstop  : 마지막 파티션을 나타냄.

              따라서 patart, pstop 를 이용하면 access 한 파티션을 알수 있음. 


4)Parallel and Distributed Processing (Parallel Processsing
을 사용하거나 DB-LINK 를 사용하는경우)

Inst         :  DB-LINK (사용하는 경우만 나타남).

TQ            :  PARALLEL SQL 사용시 table queue 명을 나타냄
                   
TQ PARALLEL SLAVE 간의 통신을 담당함.

IN-OUT      :  Parallel processing 시에 각각의 Operation 이 Serial 로 실행되는지 parallel 로 진행되는지를
                     나타냄.

PQ Distrib :  Parallel processing 시에 producers 와 consumers 간의 데이터의 분배방식을 나타냄.


* 이부분의 자세한 내용은 아래를 참조하기 바란다.
1.http://scidb.tistory.com/entry/Parallel-Query-의-조인시-Row-Distribution
2.http://scidb.tistory.com/entry/Parallel-Query-의-조인시-또다른-튜닝방법pxjoinfilter 


5)Runtime Statistics (
실제 수행시간밑 실제수행건수)

Starts     : operation try 한 건수(예를 들어 nested loop join 이라면 인덱스를 여러 번 scan )

A-Rows  : operation return 한 건수

A-Time   : 실제 실행시간

0.1초까지 나타남 (HH:MM:SS.FF).

                 이값은 Child Operation Cost 를 합친 누적치임.             
 

6)I/O Statistics (I/O 관련하여 READ / WRITE 한 블록수)

Buffers  : Operation 이 메모리에서 읽은 block .

Reads   : Operation disk 에서 읽은 block .

Writes   : Operation disk write block .

 

7)Memory Utilization Statistics(hash 작업이나 sort 작업시 사용한 메모리 통계)

OMem         : optimal execution 에 필요한 메모리(예측치임).

1Mem          : one-pass execution. 에 필요한 메모리(예측치임)

O/1/M        : operation 이 실행한 optimal/one-pass/multipass 횟수가 순서대로 표시됨.

Used-Mem : 마지막 실행시의 사용한 메모리

Used-Tmp  : 마지막 실행시 메모리가 부족하여 temporary space 를 대신 사용할 때 나타남.

                      보이는값에 1024 를 곱해야함.
 
                     예를들어 32K
로 나타나면 32MB 를 의미함.

Max-Tmp    : 메모리가 부족하여 temporary space 를 사용할 때 최대 temp 사용량임.

                     USED-TMP 와 다른점은 마지막 수행시가 아니라 SQL을 여러 번 수행했을경우에 
                     항상 최대값만 보인다는 것이다.
                     보이는값에 1024 를 곱해야함.
                     예를들어 32K 로 나타나면 32MB 를 의미함.

결론:
이상으로 PLAN 상에 나오는 각 항목에 대하여 빠짐없이 알아보았다.
특히 Runtime 통계,  I/O 통계및 Memory 통계중의 일부항목은 Tkprof 보고서에도 나오지않는 정보들로
튜닝시 요긴하게 사용할수 있다는 점을 기억 해야 한다.  
다음시간에는 3가지 함수의 여러가지 옵션에 대하여 알아볼것이다.

Reference :
1.Ttroubleshooting Oracle Performance (Christian Antognini)
2.Oracle 10g Manual : PLSQL Packages and Types Reference

Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://xsoft.tistory.com BlogIcon 강정식 2009.04.08 15:02  댓글주소  수정/삭제  댓글쓰기

    안녕하세요 동규님 ^^ 강정식입니다.
    XPLAN에서 여쭤볼것이 있어서 이렇게 질문을 남깁니다.

    제가 'XPLAN과 10046 event를 이용한 튜닝방법' 세미나를 준비중이라
    DBMS_XPLAN.DISPLAY(이하 DISPLAY), DBMS_XPLAN.DISPLAY_CURSOR(이하 CURSOR)
    2개를 테스트 해 보고 있습니다.

    그런데 ADVANCED Format을 통해 나온 Query Block Name(이하 블럭)을 이용하여
    인덱스를 변경하는 건을 테스트 하다가 생각지도 못한 Case가 발견되어 질문을 드립니다.

    이야기로 풀어 정리를 하자면 DISPLAY를 통해 나온 블럭을 이용하여 힌트를 교정한 뒤
    CURSOR로 수행을 해보니 그 힌트가 적용되지 않았습니다.

    하여 그 원인을 확인해본 결과 Predicate Information에서 바인드 변수가 TO_NUMBER(:B1)로
    바뀌어서 들어가는데 이로 인해 DISPLAY 할 때와 CURSOR 할 때가 PLAN이 바뀌어져 버렸고
    이로 인해 블럭또한 바뀌어서 적용이 안되었습니다.

    하여 DISPLAY시 바인드 변수의 데이터타입을 지정한 뒤 PLAN을 확인해보니 여전히
    TO_NUMBER(:B1)로 적용이 되고 있었습니다. 결국 PLAN을 확인할 때는 바인드 변수의
    데이터타입을 핸들링 할 수 없었는데 혹시 동규님께서 이를 핸들링하는 방법에 대해
    아시고 계신게 있으신지요?

    아래에 간단하게 데이터타입에 대한 내용을 확인할 수 있는 스크립트를 올려드립니다.

    -- create table
    DROP TABLE TEST_X PURGE;

    CREATE TABLE TEST_X AS
    SELECT LEVEL EMPNO
    FROM DUAL
    CONNECT BY LEVEL <= 100
    ;

    -- create index
    CREATE UNIQUE INDEX EMP_U1 ON TEST_X (EMPNO) COMPUTE STATISTICS;

    -- gather statistics
    EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST_X', CASCADE => TRUE);

    -- 테스트
    VAR B1 NUMBER;
    :B1 := 1;

    EXPLAIN PLAN FOR
    SELECT *
    FROM TEST_X
    WHERE EMPNO = :B1
    ;

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

    --------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 16 | 1 (0)| 00:00:01 |
    | 1 | TABLE ACCESS BY INDEX ROWID| TEST_X | 1 | 16 | 1 (0)| 00:00:01 |
    |* 2 | INDEX UNIQUE SCAN | EMP_U1 | 1 | | 0 (0)| 00:00:01 |
    --------------------------------------------------------------------------------------

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

    2 - access("EMPNO"=TO_NUMBER(:B1))
    ;

    SET SERVEROUTPUT OFF;

    VAR B1 NUMBER;
    :B1 := 1;

    SELECT /*+ GATHER_PLAN_STATISTICS */
    *
    FROM TEST_X
    WHERE EMPNO = :B1
    ;

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

    --------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | | | 1 (100)| |
    | 1 | TABLE ACCESS BY INDEX ROWID| TEST_X | 1 | 16 | 1 (0)| 00:00:01 |
    |* 2 | INDEX UNIQUE SCAN | EMP_U1 | 1 | | 0 (0)| |
    --------------------------------------------------------------------------------------

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

    2 - access("EMPNO"=:B1)

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.04.14 17:41 신고  댓글주소  수정/삭제

      :+ 변수명을 사용하면 implicit 형변환을 피할수 없습니다.
      explin plan 에서 : + 변수명을 사용할경우 값은 제대로 binding 되지만 변수자체는 항상 varchar2 로 인식되기 때문입니다.
      컬럼이 varchar2 형이 아닌 경우 항상 형변환이 발생 하겠죠.
      형변환을 피할수 있는 방법은 :+ 변수명 대신에 & + 변수명을 사용하시기 바랍니다.
      또한 accept 명령어를 사용해도 같은 효과를 볼수 있습니다.
      하지만 & + 변수명을 사용하면 binding 을 하지않고 변수를 상수로 replace 하는 개념입니다.
      하지만 explain plan 이라는 것은 어짜피 adhoc query 이므로 latch 걱정은 안하셔도 됩니다.

      아래의 스크립트를 참고 하시기 바랍니다.

      SQL> EXPLAIN PLAN FOR
      2 SELECT *
      3 FROM (SELECT E1.*
      4 FROM EMP E1
      5 UNION ALL
      6 SELECT E1.*
      7 FROM EMP E1
      8 UNION ALL
      9 SELECT E1.*
      10 FROM EMP E1
      11 UNION ALL
      12 SELECT E1.*
      13 FROM EMP E1) EMP_V,
      14 DEPT D
      15 WHERE EMP_V.DEPTNO = D.DEPTNO
      16 AND D.DEPTNO = &v_deptno
      17 ;
      v_deptno의 값을 입력하십시오: 10
      구 16: AND D.DEPTNO = &v_deptno
      신 16: AND D.DEPTNO = 10

      해석되었습니다.

      SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'ADVANCED'));

      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------

      -------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
      -------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 1 | 100 | 2 (0)|
      | 1 | NESTED LOOPS | | 1 | 100 | 2 (0)|
      | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 62 | 1 (0)|
      |* 3 | INDEX UNIQUE SCAN | DEPT_U1 | 1 | | 1 (0)|
      | 4 | VIEW | | 1 | 38 | 1 (0)|
      | 5 | UNION-ALL | | | | |
      | 6 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 18 | 1 (0)|
      |* 7 | INDEX RANGE SCAN | EMP_N1 | 1 | | 1 (0)|
      | 8 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 18 | 1 (0)|
      |* 9 | INDEX RANGE SCAN | EMP_N1 | 1 | | 1 (0)|
      | 10 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 18 | 1 (0)|
      |* 11 | INDEX RANGE SCAN | EMP_N1 | 1 | | 1 (0)|
      | 12 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 18 | 1 (0)|
      |* 13 | INDEX RANGE SCAN | EMP_N1 | 1 | | 1 (0)|
      -------------------------------------------------------------------------------

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

      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------

      3 - access("D"."DEPTNO"=10)
      7 - access("E1"."DEPTNO"=10)
      9 - access("E1"."DEPTNO"=10)
      11 - access("E1"."DEPTNO"=10)
      13 - access("E1"."DEPTNO"=10)
      ... 이하생략
      즐거운 하루 되세요.

  2. Favicon of http://xsoft.tistory.com BlogIcon 강정식 2009.04.15 16:33  댓글주소  수정/삭제  댓글쓰기

    답변 감사드립니다.
    제가 이런 질문을 드리는 이유는 bind 변수가 explain plan과 execution plan에서 틀린 경우가 있어서 그랬구요...
    이와 관련되어 테스트 한 내용을 링크걸어 드리니 한번 봐주시기 바랍니다 ^^

    http://blog.naver.com/xsoft/150045721858

    감사합니다.

  3. Favicon of http://xsoft.tistory.com BlogIcon 강정식 2009.04.16 09:12  댓글주소  수정/삭제  댓글쓰기

    메일 보내 드렸습니다. 확인 부탁 드립니다 ^^

  4. Favicon of http://xsoft.tistory.com BlogIcon 강정식 2009.04.16 15:00  댓글주소  수정/삭제  댓글쓰기

    동규님. 상세한 답변 감사드립니다. ^^
    FPD가 JPPD보다 COST가 낮아 옵티마이저가 먼저 고려되는 것인지 덕분에 처음 알았습니다.
    정말 동규님의 깊은 내공을 잠시나마 뵐 수 있었습니다.

  5. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2009.04.24 11:45  댓글주소  수정/삭제  댓글쓰기

    안녕하세요. 동규님

    'DBMS_XPLAN.DISPLAY_CURSOR'의 A-Time에 대해 궁금한 점이 있어서 질문을 드립니다.
    제가 알고 있기로는 A-Time이 실제 수행시간을 표현하는 것으로 알고 있는데 이 값이 Trace와
    차이가 나는 부분이 확인되어 이에 대해 질문을 드리고자 합니다.

    아래는 이 내용을 재현할 수 있는 스크립트와 해당 내용에 대해 나열한 것입니다.


    1. 수행 스크립트
    -- create table
    DROP TABLE EMP PURGE;
    DROP TABLE DEPT PURGE;

    CREATE TABLE EMP AS
    SELECT LEVEL EMPNO,
    TO_CHAR(LEVEL) EMPNO_VARCHAR,
    CHR(65 + CEIL(LEVEL / 500000) - 1) JOB,
    TO_DATE('00010101', 'YYYYMMDD') + CEIL(LEVEL / 10) - 1 HIREDATE,
    LENGTH(LEVEL) * 10 DEPTNO
    FROM DUAL
    CONNECT BY LEVEL <= 10000000
    ;

    CREATE TABLE DEPT AS
    SELECT LEVEL * 10 DEPTNO,
    'SALES_' || LEVEL DNAME,
    'ZONE_' || LEVEL LOC
    FROM DUAL
    CONNECT BY LEVEL <= 9
    ;

    -- create index
    CREATE UNIQUE INDEX USER.EMP_U1 ON APPS.EMP (EMPNO) COMPUTE STATISTICS PARALLEL 8;
    ALTER INDEX USER.EMP_U1 NOPARALLEL;

    CREATE INDEX USER.EMP_N1 ON APPS.EMP (DEPTNO) COMPUTE STATISTICS PARALLEL 8;
    ALTER INDEX USER.EMP_N1 NOPARALLEL;

    CREATE INDEX USER.EMP_N2 ON APPS.EMP (HIREDATE) COMPUTE STATISTICS PARALLEL 8;
    ALTER INDEX USER.EMP_N2 NOPARALLEL;

    CREATE INDEX USER.EMP_N3 ON APPS.EMP (EMPNO_VARCHAR) COMPUTE STATISTICS PARALLEL 8;
    ALTER INDEX USER.EMP_N3 NOPARALLEL;

    CREATE UNIQUE INDEX USER.DEPT_U1 ON APPS.DEPT (DEPTNO);

    -- gather statistics
    EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMP', CASCADE => TRUE, DEGREE => 8);

    EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEPT', CASCADE => TRUE);




    2. 테스트
    1) Trace 내용
    SElECT *
    FROM EMP E,
    DEPT D
    WHERE E.DEPTNO = D.DEPTNO
    AND (E.DEPTNO = :B1 -- 10
    OR
    E.HIREDATE BETWEEN TO_DATE(:B2, 'YYYYMMDD') -- 2009/04/01
    AND TO_DATE(:B3, 'YYYYMMDD') -- 2009/04/02
    )
    ;

    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 3 9.25 9.06 0 44245 0 29
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 5 9.25 9.06 0 44245 0 29

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

    Rows Row Source Operation
    ------- ---------------------------------------------------
    29 HASH JOIN (cr=44245 pr=0 pw=0 time=9059820 us)
    9 TABLE ACCESS FULL DEPT (cr=3 pr=0 pw=0 time=180 us)
    29 TABLE ACCESS FULL EMP (cr=44242 pr=0 pw=0 time=9057805 us)


    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    SQL*Net message to client 3 0.00 0.00
    SQL*Net message from client 3 0.31 0.57






    2) DBMS_XPLAN.DISPLAY_CURSOR 내용
    TEST >
    1 SElECT /*+ GATHER_PLAN_STATISTICS */
    2 *
    3 FROM EMP E,
    4 DEPT D
    5 WHERE E.DEPTNO = D.DEPTNO
    6 AND (E.DEPTNO = :B1 -- 10
    7 OR
    8 E.HIREDATE BETWEEN TO_DATE(:B2, 'YYYYMMDD') -- 2009/04/01
    9 AND TO_DATE(:B3, 'YYYYMMDD') -- 2009/04/02
    10 )
    11 ;

    EMPNO EMPNO_VARCHAR JOB HIREDATE DEPTNO DEPTNO DNAME
    ---------- ------------- ------ ------------ ---------- ---------- --------
    1 1 A 01-JAN-01 10 10 SALES_1
    2 2 A 01-JAN-01 10 10 SALES_1
    3 3 A 01-JAN-01 10 10 SALES_1
    4 4 A 01-JAN-01 10 10 SALES_1
    5 5 A 01-JAN-01 10 10 SALES_1
    6 6 A 01-JAN-01 10 10 SALES_1
    7 7 A 01-JAN-01 10 10 SALES_1
    8 8 A 01-JAN-01 10 10 SALES_1
    9 9 A 01-JAN-01 10 10 SALES_1
    7334991 7334991 O 01-APR-09 70 70 SALES_7
    7334992 7334992 O 01-APR-09 70 70 SALES_7
    7334993 7334993 O 01-APR-09 70 70 SALES_7
    7334994 7334994 O 01-APR-09 70 70 SALES_7
    7334995 7334995 O 01-APR-09 70 70 SALES_7
    7334996 7334996 O 01-APR-09 70 70 SALES_7
    7334997 7334997 O 01-APR-09 70 70 SALES_7
    7334998 7334998 O 01-APR-09 70 70 SALES_7
    7334999 7334999 O 01-APR-09 70 70 SALES_7
    7335000 7335000 O 01-APR-09 70 70 SALES_7
    7335001 7335001 O 02-APR-09 70 70 SALES_7
    7335002 7335002 O 02-APR-09 70 70 SALES_7
    7335003 7335003 O 02-APR-09 70 70 SALES_7
    7335004 7335004 O 02-APR-09 70 70 SALES_7
    7335005 7335005 O 02-APR-09 70 70 SALES_7
    7335006 7335006 O 02-APR-09 70 70 SALES_7
    7335007 7335007 O 02-APR-09 70 70 SALES_7
    7335008 7335008 O 02-APR-09 70 70 SALES_7
    7335009 7335009 O 02-APR-09 70 70 SALES_7
    7335010 7335010 O 02-APR-09 70 70 SALES_7

    29 rows selected.

    Elapsed: 00:00:09.67
    TEST >
    1 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID gczuawz61s195, child number 0
    -------------------------------------
    SElECT /*+ GATHER_PLAN_STATISTICS */ * FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND
    (E.DEPTNO = :B1 -- 10 OR E.HIREDATE BETWEEN TO_DATE(:B2, 'YYYYMMDD') -- 2009/04/01
    AND TO_DATE(:B3, 'YYYYMMDD') -- 2009/04/02 )

    Plan hash value: 1093152308

    -------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
    -------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 2021K| 29 |00:00:00.01 | 44245 | 2 | 1023K| 1023K| 811K (0)|
    | 2 | TABLE ACCESS FULL| DEPT | 1 | 9 | 9 |00:00:00.01 | 3 | 0 | | | |
    |* 3 | TABLE ACCESS FULL| EMP | 1 | 2021K| 29 |00:00:00.01 | 44242 | 2 | | | |
    -------------------------------------------------------------------------------------------------------------------------

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

    1 - access("E"."DEPTNO"="D"."DEPTNO";)
    3 - filter(("E"."DEPTNO"=:B1 OR ("E"."HIREDATE">=TO_DATE(:B2,'YYYYMMDD') AND
    "E"."HIREDATE"<=TO_DATE(:B3,'YYYYMMDD'))))




    내용을 보시면 아시겠지만 Trace에서는 수행속도가 9초 정도 걸렸고 이 내용이 row operation에서도
    정확히 표현이 되고 있습니다.
    하지만 'DBMS_XPLAN.DISPLAY_CURSOR'에서는 수행 시 'Elapsed: 00:00:09.67'가 걸렸지만 A-Time에서는
    '00.01'초만 걸린 것으로 확인이 되고 있습니다.

    왜 이런 현상이 나오는지 알 수 있을까요?

  6. Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.04.24 15:14 신고  댓글주소  수정/삭제  댓글쓰기

    오라클 버그 입니다.
    이런 경우에는
    alter session set STATISTICS_LEVEL = ALL ;
    을 설정 하시고 다시한번 SQL 과 DBMS_XPLAN.DISPLAY_CURSOR 를 실행 해보시기 바랍니다.
    감사합니다.

  7. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2009.04.24 18:43  댓글주소  수정/삭제  댓글쓰기

    답변 감사드립니다 ^^

이글을 쓰기전에 필자는 QUEST 사와 아무런 관계가 없음을 먼저 밝혀둔다.
이글의 목적은 개발자, DBA, 튜닝 컨설턴트 들이 이런 리포트들을 뽑기위하여 OEM(엔터프라이즈 매니져) 를
사용할수 없는경우 발을 동동 구르고 있다는 사실이다.
하지만 사실은 우리주위에 있는 가장흔한 쿼리툴(TOAD) 로도 쉽게 출력이 가능하다.
아래처럼 토드에서 Database --> Monitor --> ADDM/AWR 을 클릭하면
1.AWR(Automatic Workload Repository),
2.ADDM(AUTOMATIC DATABASE DIAGNOSTIC MONITOR),
3.ASH(Active Session History) 를 관리및 출력할수 있는 화면이 나온다.
참고로 AWR 리포트는 9i 이하버젼에서 사용한 Statspack 리포트의 진화된 버젼이라고 할수 있다.
 

사용자 삽입 이미지































아래는 AWR 의 관리화면이다.
Snapshot Interval 은 10분이며 최장 보관기간은 30 일 임을 알수 있다.
이화면에서 관리및 변경이 가능하다.

사용자 삽입 이미지




아래는 ASH 를 출력할수 있는 탭을 보여준다.
그림 FROM ~ TO 로 START 와 END TIME 을 지정하고 RAC 인경우 인스턴스를 지정하고 상단에 있는 연두색 버트늘 누르면 ASH 보고서가 쉽계 출력된다.

사용자 삽입 이미지



아래는 AWR 보고서 화면이다.
SNAPSHOT 구간을 선택하고 인스턴스를 선택하고 상단의 연두색 버튼을 누르면 쉽게 보고서가 출력된다.
사용자 삽입 이미지


아래는 ADDM 리포트이다.
AWR 리포트와 출력하는 방법이 같다.
사용자 삽입 이미지


이상으로서 거칠것 없이 AWR 과 ASH, ADDM 의 보고서를 출력 해보았다.
복잡한 절차없이 몇번의 클릭만으로 3종류의 보고서를 출력하였다.
이 3개의 보고서를 잘활용하면 특정구간대의 DBMS 차원의 성능진단및 Wait Event, TOP SQL,
TOP Object 등을 도출해 낼수 있다.
특히 ADDM 보고서에는 문제가 무엇이고 어떻게 조치해야 되는지 까지 조언을 해준다.
또한 ASH 나 AWR 의 보고서에는 TOP 이벤트를 발생시키는 SQL 을 찾아줄뿐 아니라
연관된 TOP OBJECT 까지 찾아준다.
예를 들면 특정 SQL 이 Index Contention 을 발생시킬경우에 관련 인덱스가 무엇인지 쉽게 찾을수 있다.
오늘 소개한것은 토드의 일부기능에 불과하다.
우리주위에 흔히 볼수 있는 쿼리툴도 찾아보면 멋진 기능들이 많이 숨어있다.
단지 어디에 뭐가 있는지 몰라서 사용하지 않을뿐.....
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of https://ukja.tistory.com BlogIcon 욱짜 2008.10.16 16:04 신고  댓글주소  수정/삭제  댓글쓰기

    추가적으로 AWR SQL Report 기능도 때로는 대단히 유용하더군요.

    그리고 Oracle 10gR2부터는 dbms_workload_repository 패키지를 통서 동일한 작업이 다 가능합니다.

    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_wkrpos.htm

    그리고 $ORACLE_HOME/rdbms/admin 디렉토리에 있는 ashrpt.sql, addmrpt.sql, awrrpt.sql, awrsqrpt.sql 스크립트를 사용해도 좋구요.

  2. Favicon of https://scidb.tistory.com BlogIcon extremedb 2008.10.16 19:49 신고  댓글주소  수정/삭제  댓글쓰기

    아주 유용한 정보입니다.
    감사합니다.