[20121212]謹慎使用set autotrace traceonly檢視執行計劃[補充].txt

使用toad自帶sqlmonitor,toad10以上版本現在叫sqltrace.

12:00:24 SQL> set autotrace traceonly ;
12:01:23 SQL> select * from t2 where id=45;

10000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 |   634K|     8   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   | 10000 |   634K|     8   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("ID"=45)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        155  consistent gets
          0  physical reads
          0  redo size
     144716  bytes sent via SQL*Net to client
        902  bytes received via SQL*Net from client
         51  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

檢視跟蹤內容如下:
----------------------------------
Timestamp: 12:01:23.218
SELECT DECODE('A','A','1','2') FROM DUAL
----------------------------------
Timestamp: 12:01:23.234
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', :1))
:1 = ''
----------------------------------
Timestamp: 12:01:23.234
SELECT USERENV('SESSIONID') FROM DUAL
:1 = ''
----------------------------------
Timestamp: 12:01:23.250
SELECT DISTINCT SID FROM V$MYSTAT
:1 = 'SELECT DISTINCT SID FROM V$MYST'
----------------------------------
Timestamp: 12:01:23.265
Successful logon attempt (session tag: 0x26601F0)
username: 'scott'; database: ''
----------------------------------
Timestamp: 12:01:23.265
SELECT STATISTIC# S, NAME FROM SYS.V_$STATNAME WHERE NAME IN ('recursive calls','db block gets','consistent gets',
'physical reads','redo size','bytes sent via SQL*Net to client','bytes received via SQL*Net from client',
'SQL*Net roundtrips to/from client','sorts (memory)','sorts (disk)') ORDER BY S
----------------------------------
Timestamp: 12:01:25.125
SELECT PT.VALUE FROM SYS.V_$SESSTAT PT WHERE PT.SID=:1 AND PT.STATISTIC# IN (8,63,67,72,169,555,556,557,565,566) ORDER BY
PT.STATISTIC#
:1 = 21
----------------------------------
Timestamp: 12:01:25.140
select * from t2 where id=45
:1 = 21
----------------------------------
Timestamp: 12:01:25.218
SELECT PT.VALUE FROM SYS.V_$SESSTAT PT WHERE PT.SID=:1 AND PT.STATISTIC# IN (8,63,67,72,169,555,556,557,565,566) ORDER BY
PT.STATISTIC#
:1 = 21
----------------------------------
Timestamp: 12:01:25.218
DELETE FROM PLAN_TABLE WHERE STATEMENT_ID=:1
:1 = 'PLUS1010552'
----------------------------------
Timestamp: 12:01:25.250
EXPLAIN PLAN SET STATEMENT_ID='PLUS1010552' FOR select * from t2 where id=45
:1 = 'PLUS1010552'
----------------------------------
Timestamp: 12:01:25.281
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', :1))
:1 = 'PLUS1010552'
----------------------------------
Timestamp: 12:01:25.296
DELETE FROM PLAN_TABLE WHERE STATEMENT_ID=:1
:1 = 'PLUS1010552'

可以發現實際上set autotrace traceonly生成的執行計劃實際上執行的是:

EXPLAIN PLAN SET STATEMENT_ID='PLUS1010552' FOR select * from t2 where id=45

這樣不一定生成的是真實的執行計劃!