使用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
这样不一定生成的是真实的执行计划!