음.. 제가 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’)