使用10046 event trace跟蹤全表掃描操作

1.建立測試用表T
SQL> create table t as select * from dba_objects;

Table created.

2.檢視T表的Block號和Block數量
SQL> select file_id,block_id,blocks from dba_extents where segment_name='T';

[@more@]

FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
3 5025 8
3 5033 8
3 5041 8
3 5049 8
3 5057 8
3 5065 8
3 5073 8
3 5081 8
3 5089 8
3 5097 8

10 rows selected.

3.開啟10046 event trace跟蹤當前session
SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

4.執行全表掃描
SQL> select count(*) from t;

COUNT(*)
----------
6279

5.結束10046 event trace
SQL> alter session set events '10046 trace name context off';

Session altered.

6.檢視跟蹤檔案路徑
SQL> select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||
2 p.spid || '.trc' trace_file_name
3 from (select p.spid
4 from v$mystat m, v$session s, v$process p
5 where m.statistic# = 1
6 and s.sid = m.sid
7 and p.addr = s.paddr) p,
8 (select t.instance
9 from v$thread t, v$parameter v
10 where v.name = 'thread'
11 and (v.value = 0 or t.thread# = to_number(v.value))) i,
12 (select value from v$parameter where name = 'user_dump_dest') d
13 /

TRACE_FILE_NAME
------------------------------------------------------------------------------
/disk3/oradata/admin/ora7a/udump/ora7a_ora_6128.trc

7.檢視跟蹤檔案
SQL> ! grep scatt /disk3/oradata/admin/ora7a/udump/ora7a_ora_6128.trc
WAIT #1: nam='db file scattered read' ela= 311 p1=3 p2=5026 p3=7
WAIT #1: nam='db file scattered read' ela= 211 p1=3 p2=5033 p3=8
WAIT #1: nam='db file scattered read' ela= 302 p1=3 p2=5041 p3=8
WAIT #1: nam='db file scattered read' ela= 226 p1=3 p2=5049 p3=8
WAIT #1: nam='db file scattered read' ela= 219 p1=3 p2=5057 p3=8
WAIT #1: nam='db file scattered read' ela= 300 p1=3 p2=5065 p3=8
WAIT #1: nam='db file scattered read' ela= 501 p1=3 p2=5073 p3=8
WAIT #1: nam='db file scattered read' ela= 466 p1=3 p2=5081 p3=8
WAIT #1: nam='db file scattered read' ela= 470 p1=3 p2=5089 p3=8
WAIT #1: nam='db file scattered read' ela= 139 p1=3 p2=5097 p3=6

8.跟蹤檔案中P1,P2,P3的欄位說明
SQL> select name, parameter1 p1, parameter2 p2, parameter3 p3
2 from v$event_name
3 where name = 'db file scattered read'
4 /

NAME P1 P2 P3
------------------------------ ---------- ---------- ----------
db file scattered read file# block# blocks