dbms_xplan.display_cursor 사용시 팁 1 4 8,268

by 강정식 [강정식] dbms_xplan [2009.03.03 15:54:58]


음.. 제가 xplan 사용할 떄 ’dbms_xplan.display’ 이거는 잘 사용이 되는데
’dbms_xplan.display_cursor’ 이거 사용할때 sql_id를 잘 못가져와서 값이 안나오는
경우가 있더라구요.

그래서 지난번 웅이형 챕터에서 테스트 할때 ’V$SQLAREA’에서 sql_id 뒤진담에
’dbms_xplan.display_cursor’ 여기에 일일이 넣어서 테스트를 했었져 -_-;

근데 안되는 경우가 많아서 욱짜에게 문의를 해보니 ’set serveroutput off;’
설정하고 하면 된다고 하는군요. 그리고 해보니 정말 되네여.. ㅎㅎ;

혹시 모르시는 분 계실까봐 글 남겨여...

아래는 테스트

/*****************************************/
/* 1. 필요한 Object 생성 */
/*****************************************/
-- 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);

/*****************************************/
/* 2. dbms_xplan.display_cursor 테스트   */
/*****************************************/
-- SQL 실행
SELECT /*+ GATHER_PLAN_STATISTICS */
   COUNT(*)
FROM   T1
WHERE  C1 = ’B’
;

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

1 row selected.

-- dbms_xplan.display_cursor 실행
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)

;

/*****************************************/
/* 3. sql_id 얻기 위해 V$SQLAREA 조회    */
/* 1) 노가다(-_-;)    */
/*****************************************/
-- 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’)
;

/*****************************************/
/* 4. set serveroutput off 설정   */
/*****************************************/
set serveroutput off;

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

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

1 row selected.

-- dbms_xplan.display_cursor 실행
select * from table(dbms_xplan.display_cursor(null
, 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’)   

by 웅 [2009.03.03 17:07:03]
어..정말 serveroutput 셋팅으로 문제가 생길 수 있네..

그런데 ..sql_id얻는것은 v$sql에서 안뒤져두 되는데..
dbms_xplan으로 실행계획을 보면 실행된 sql의 sql_id는 기본적으로 나오거든..
내가 진행 할 때는 그거 복사해서 이용했었지 별도로 v$sql까지 찾아가진 았았거든
dbms_xplan의 table fuction에서 serveroutput on 사용시에는 충돌나는 코드가 있다고 봐야되지 않을까?

by 강정식 [2009.03.03 17:37:39]
음.. 형.. 저도 dbms_xplan.display_cursor에서 나오는 sql_id 가지고
파라미터로 사용해 보니까 안나오더라구여..
위에서 제가 테스트한거에서도

set serveroutput on : 9babjv8yq8ru3
set serveroutput off : 3ngqqmd0a1m3t

이렇게 값이 틀리더라구여....
음.. 이게 제 pc에서만 테스트 해서 그런건지 모르겠어여..

그리고 set serveroutput on 이랑 dbms_xplan.display_cursor 이거랑은
형 말대로 충돌이 나는 뭔가가 있는거 같아여...

by drakula [2009.03.03 22:55:14]
진짜 웃기다. 모, 저런게 다있어~ ㅎㅎㅎㅎ;;
그래도, 덕분에 좋은거 알았어~^^
근데, 역시 난 EM에서 확인하는게 좋아~ ㅋㅋㅋ

by 타락천사 [2009.03.04 18:03:08]
테스트 중에 바로 나오네 ㅎㅎ
굿 잡 !!
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입