태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

DBMS_XPLAN에 대한 이해

오라클 2009.02.04 14:34
dbms_xplan 패키지는 Oracle이 제공하는 모든 패키지들 중 가장 중요한 것이다.

왕 중의 왕이다.

적어도 Query를 튜닝하는 사람들에게는 이 명제가 진실일 것이다. dbms_xplan 패키지는 실행 계획을 분석하는 기능을 제공하기 때문이다. 이 패키지가 없이는 Query 튜닝은 불가능하다.

Oracle이 버전업되면서 실행 계획이라는 말의 범위가 점점 넓어지고 있다. 최신 버전인 11g 기준으로, 총 5 가지 종류의 실행 계획이 존재한다. 이제는 실행 계획이라는 말을 들으면 "어떤 실행 계획 말하는거야"라고 물어봐야 할 지경이다.

아래 Oracle Virus 동영상에 이것이 잘 설명되어 있다.




신고
tags :
Trackback 0 : Comments 16
  1. 강정식 2009.02.06 17:38 신고 Modify/Delete Reply

    안녕하세요. 조동욱님
    오랜만에 뵙겠습니다. ^^ 요즘 스터디때문에 OOO책으로 열공 중입니다.

    하나 궁금한점이 있어서 리플로 남겨뵙니다.

    dbms_xplan 패키지에서 제공하는 함수 중 'display_cursor'가 매력적으로
    다가오는데요. /*+ gather_plan_statistics */ 힌트를 통해 트레이스를 뜨지
    않고 e-rows, a-rows의 값을 확인하여 explain plan과 runtime plan의 간극을
    확인할 수 있는게 좋은점인 것 같습니다.

    다만 제가 생각할 때 /*+ gather_plan_statistics */ 힌트를 통해 'display_cursor'
    함수를 이용하여 runtime plan을 확인하려면 실제 데이터를 흘려보아야 한다는
    단점이 있는 것 같습니다.

    display 함수는 현재 조동욱님이 여러 가이드를 해주셔서 참 편하기 쓰고 있습니다.
    display의 장점은 말 그대로 데이터를 흘려보지 않고 여러 각도에서 분석을 할 수
    있는 것인데 display_cursor 함수는 데이터를 실제 흘려봐야 하므로 제 경우에는
    차라리 10046 trace이 더 좋다고 판단되어 이를 사용하고 있습니다.

    제가 잘 못 이해하고 있는것인지요? 아니면 display_cursor를 이용하려면 실제
    데이터를 흘려봐야 하는 단점이 있는건지요?

    ps. outline hint에 대해 혹시 더 스터디하신 내용 없으신가요?
    제가 근무하는 사이트에서는 요즘 너무 plan이 복잡하여 outline hint의
    사용 비중이 2~3%에서 2~30%로 증가하였습니다.
    아마 향후에는 5~60%로 늘어나지 않을까 싶은데요. 이를 위해서는 자유롭게
    outline을 사용할 수 있도록 해야 하는데 이에 대한 기술문서가 없어서
    애를 먹고 있습니다.
    기술 선도를 하고 계시는 조동욱님께서 이에 대해 분석해 주시면 엔지니어들에게
    많은 도움이 될 것 같은데요. 혹시 시간이 되시면 스터디하신 내용에 대해
    공유 부탁드리겠습니다. ^^

    며칠전에 입춘이 지나갔습니니다. 2009년도 입춘대길 하시길 바랍니다.

    감사합니다.

  2. 강정식 2009.02.06 18:01 신고 Modify/Delete Reply

    동욱님 기왕 쓴 김에 하나 더 글 올려봅니다 ^^;
    OOO 및 조동욱님이 집필하신 책에 대해 독자들이 책을 보다가 궁금한점이 있을 때
    질문할 수 있는 게시판이있었으면 좋겠는데요. 여기 동욱님 블로그가 그 기능을
    하기에 좋을 거라 생각됩니다.

    혹시 그 기능을 하는 게시판 하나 개설해주실 수 있으신가요?

  3. 욱짜 2009.02.07 12:55 신고 Modify/Delete Reply

    1. 지금 엑셈에서 ask.ex-em.com 이라는 이름으로 Oracle 성능 관련 Q&A가 가능한 간단한 시스템을 개발하고 있습니다. 조만간 오픈될 것입니다. 이 사이트가 오픈되면 다양한 목적으로 활용 가능할 것입니다.

    만일 별도의 시스템이 필요하다고 판단되면 구글 그룹을 만드는 것도 방법이 될 거 같습니다.

    2. dbms_xplan.display_cursor 함수는 "현재 Shared Pool"에 등록되어 있는 실행 계획을 보는 기능이죠? 일단 한번 수행하고 나면 그 정보가 Shared Pool에 보관되어 있는 한은 거의 영구적으로 사용이 가능합니다. 한번 수행을 해보아야 한다는 사실에는 변함이 없지만, 결과를 보기 위해서 계속해서 수행할 필요는 없다는 것이지요. sql_id와 child_number만 알면 계속 조회할 수 있습니다.

    3. Outline 힌트라면 OUTLINE 옵션에 의해 나오는 힌트들의 목록을 말씀하시는건가요? 아니면 Stored Outline을 말씀하시는건가요?

  4. 욱짜 2009.02.08 00:10 신고 Modify/Delete Reply

    영문 블로그에 제 생각을 간단하게 정리했습니다.

    http://dioncho.wordpress.com/2009/02/07/dbms_statsdisplay_cursor-vs-sql-trace/

  5. 강정식 2009.02.09 08:59 신고 Modify/Delete Reply

    동욱님 답변 감사드립니다. 영문 블로그에 올리신 내용 참고하겠습니다.

    그리고 제가 추가적으로 문의한 Outline 힌트는 OUTLINE 옵션에 의해 나오는 힌트를 말씀드렸습니다.
    제가 지원하는 사이트가 View를 참조하는 프로그램들이 많다보니 phsh_pred, push_subq들을
    자유자재로 핸들링 하는것이 관건인데 이들을 기존 힌트들에서 Outline 힌트들을 참조하는 빈도수가
    많아져서요.

    더욱 스터디가 필요하다고 생각되는 부분은 아래의 내용인데요.

    NO_ACCESS(@SEL$5DAD4AD6 XRPSV@SEL$1)
    OUTLINE(@SEL$3)
    MERGE(@SEL$65)

    여기서 NO_ACCESS 부분에서 View Merging이 안되는 query_block과 Alias가 나오는데 이를
    ACCESS로 바꿔도 Merging이 안되는것들이 Outline 힌트 하단에 나오는 OUTLINE, MERGE
    부분을 일부 변경해보니 Merging이 되는것을 확인이 되서요.

    근데 이는 운이 좋게 된거라 아직 핸들링 할 수준은 아니어서 자유자재로 구사는 하지 못하고 있습니다.

    이에 대해 동욱님께서 스터디하신 내용이 있으시면 공유 부탁드리겠습니다.

    감사합니다 ^^

  6. 욱짜 2009.02.09 16:59 신고 Modify/Delete Reply

    Outline 옵션을 아주 잘 사용하고 계시는군요.
    가능한 명시적인 Block 이름(가령 QB_NAME Hint 사용)을 사용하는 것이 추후 유지보수를 위해 더 좋을 거 같다는 생각은 듭니다.

    Outline으로 사용된 Hint들에 대해서는 더 많은 정보를 얻게 되면 공유하도록 하겠습니다.

  7. 강정식 2009.03.03 14:16 신고 Modify/Delete Reply

    안녕하세요 욱짜님.

    다름이 아니라 display_cursor 사용할 때 sql_id를 찾지 못한다는 메세지와 함께 결과가 안나올때가 있어서요...
    그래서 'V$SQLAREA'에서 sql_id를 찾은 다음에 그 인자값으로 찾아야 하는데요.

    왜 이렇게 sql_id를 매칭시키지 못하는것에 대해 알려주실 수 있으신가요?

    아래는 테스트 스크립트입니다.

    -- create object
    DROP TABLE T1 PURGE;

    CREATE TABLE T1(C1 VARCHAR2(1), C2 INT);

    CREATE INDEX T1_N1 ON T1(C1);

    -- create data
    INSERT INTO T1
    SELECT 'A',
    LEVEL
    FROM DUAL
    CONNECT BY LEVEL <= 10000
    UNION ALL
    SELECT 'B', LEVEL
    FROM DUAL
    CONNECT BY LEVEL <= 10
    ;

    COMMIT;

    -- gather stats "without" histogram
    EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1', METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', NO_INVALIDATE => FALSE);

    SELECT /*+ GATHER_PLAN_STATISTICS */
    COUNT(*)
    FROM T1
    WHERE C1 = 'B'
    ;

    COUNT(*)
    ----------
    10

    1 row selected.

    -- not matched sql_id
    select * from table(dbms_xplan.display_cursor(null, null, 'iostats cost last'));

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------
    SQL_ID 9babjv8yq8ru3, child number 1

    BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

    NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 1
    Please verify value of SQL_ID and CHILD_NUMBER;
    It could also be that the plan is no longer in cursor cache (check v$sql_plan)
    ;

    -- get sql_id
    SELECT SUBSTR(SQL_TEXT, 1, 600) SQL_TEXT,
    SQL_ID
    FROM V$SQLAREA
    WHERE UPPER(SQL_TEXT) LIKE '%GATHER_PLAN_STATISTICS%'
    ORDER BY FIRST_LOAD_TIME DESC
    ;

    SQL_TEXT SQL_ID
    ------------------------------------------------------------------------------ -------------
    SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*) FROM T1 WHERE C1 = 'B' 3ngqqmd0a1m3t
    ;

    -- re find display_cursor
    select * from table(dbms_xplan.display_cursor('3ngqqmd0a1m3t', null, 'iostats cost last'));

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------
    SQL_ID 3ngqqmd0a1m3t, child number 0
    -------------------------------------
    SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*) FROM T1 WHERE C1 = 'B'

    Plan hash value: 3693069535

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

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

    2 - filter("C1"='B')

  8. 욱짜 2009.03.03 15:02 신고 Modify/Delete Reply

    1. Windows 환경에서 SQL*Plus 사용
    2. set serveroutput on 상태인 경우
    에 발생하는 문제입니다.

    set serveroutput off 를 지정하시면 해결될겁니다.

  9. 강정식 2009.03.03 15:34 신고 Modify/Delete Reply

    감사합니다 ^^

  10. 강정식 2009.04.09 00:05 신고 Modify/Delete Reply

    안녕하세요 욱짜님 ^^ 강정식입니다.
    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)) <--- var B1 number로 선언해도 to_number()를 함
    ;

    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) <--- var B1 number한것이 적용됨

  11. 욱짜 2009.04.09 06:45 신고 Modify/Delete Reply

    번거로우시겠지만 이 내용을 다른 분들도 공유할 수 있게 Ask Exem에 올려주시겠습니까?

  12. 강정식 2009.04.09 09:21 신고 Modify/Delete Reply

    네 올려드렸습니다. ^^

  13. 욱짜 2009.04.09 10:52 신고 Modify/Delete Reply

    답변을 간단하게 올렸습니다. 원래 제가 답변을 올리면 질문자에게 이메일이 발송되게 되어 있는데 이상하게 입력하신 이메일로는 반송(서버에서 거부 당함)이 되더라구요. 다음 번에 이용하시게 되면 다른 이메일 주소를 사용해보시면 좋겠습니다.

  14. 엑셥 2009.04.09 13:48 신고 Modify/Delete Reply

    욱짜님 답변 감사드립니다.
    그런데 실 사례를 공유해 드리지 못한것 같아 다시한번 사례를 올려드렸습니다.
    해당 스크립트도 같이 올려드렸으니 local에서 테스트 해보실 수 있으실겁니다.

    여기에도 url를 올려드리겠습니다.

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

    감사합니다.

  15. 욱짜 2009.04.09 15:28 신고 Modify/Delete Reply

    혹시 Ask Exem에서 메일이 발송되었나요? 되었다면 여기에 제가 답글을 남기지 않아도 될텐데요.

    Ask Exem에 답글을 남겼습니다. 확인해보세요.

  16. 양용성 2010.05.27 18:18 신고 Modify/Delete Reply

    욱짜님의 글을 보고 공부해서 아래와 같이 프로그램을 만들어 봤습니다.
    늘 자료 감사히 보고 있습니다.
    http://sqlgate.wordpress.com/2010/05/18/%EC%83%88%EB%A1%9C%EC%9A%B4-dbms_xplan-display-%EA%B8%B0%EB%8A%A5/

Write a comment

티스토리 툴바