sql_trace、access path和db_file_mul详解

SQL> create table t as select * from dba_objects;

Table created.

SQL> select count(*) from t;

  COUNT(*)
----------
     30536

SQL> select file_id,block_id,blocks 
from dba_extents where segment_name=''T'';

   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         1      50921          8
         1      50929          8
         1      51233          8
         1      51241          8
         1      51249          8
         1      51257          8
         1      51265          8
         1      51273          8
         1      51281          8
         1      51289          8
         1      51297          8

   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         1      51305          8
         1      51313          8
         1      51321          8
         1      51329          8
         1      51337          8
         1      51465        128
         1      51593        128
         1      51721        128

19 rows selected.

SQL> show parameter db_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
db_file_multiblock_read_count        integer     16
db_file_name_convert                 string
db_files                             integer     200

SQL> alter session set events '
'10046 trace name context off'';

*** 2008-01-21 12:11:20.000
=====================
PARSING IN CURSOR #1 len=68 dep=0 uid=0 oct=42 lid=0 tim=2925420357 hv=570864185 ad=''668b0b0c''
alter session set events ''10046 trace name context forever,level 12''
END OF STMT
EXEC #1:c=0,e=156,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=2925420341
WAIT #1: nam=''SQL*Net message to client'' ela= 10 p1=1111838976 p2=1 p3=0
WAIT #1: nam=''SQL*Net message from client'' ela= 6417020 p1=1111838976 p2=1 p3=0
=====================
PARSING IN CURSOR #1 len=22 dep=0 uid=0 oct=3 lid=0 tim=2931845333 hv=2199322426 ad=''6710b08c''
select count(*) from t
END OF STMT
PARSE #1:c=0,e=88,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2931845326
BINDS #1:
EXEC #1:c=0,e=1534,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2931853164
WAIT #1: nam=''SQL*Net message to client'' ela= 9 p1=1111838976 p2=1 p3=0
WAIT #1: nam=''db file scattered read'' ela= 8813 p1=1 p2=51677 p3=16
WAIT #1: nam=''db file scattered read'' ela= 1307 p1=1 p2=51693 p3=16
WAIT #1: nam=''db file scattered read'' ela= 5570 p1=1 p2=51709 p3=12
WAIT #1: nam=''db file scattered read'' ela= 2616 p1=1 p2=51729 p3=13
WAIT #1: nam=''db file scattered read'' ela= 595 p1=1 p2=51749 p3=6
FETCH #1:c=15625,e=36032,p=63,cr=421,cu=0,mis=0,r=1,dep=0,og=4,tim=2931890915
WAIT #1: nam=''SQL*Net message from client'' ela= 409 p1=1111838976 p2=1 p3=0
FETCH #1:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=2931893361
WAIT #1: nam=''SQL*Net message to client'' ela= 4 p1=1111838976 p2=1 p3=0
*** 2008-01-21 11:49:52.000
WAIT #1: nam=''SQL*Net message from client'' ela= 15276041 p1=1111838976 p2=1 p3=0
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op=''SORT AGGREGATE ''
STAT #1 id=2 cnt=30536 pid=1 pos=1 obj=31687 op=''TABLE ACCESS FULL T ''
=====================
PARSING IN CURSOR #1 len=55 dep=0 uid=0 oct=42 lid=0 tim=2947176010 hv=3381932903 ad=''668aba24''
alter session set events ''10046 trace name context off''
END OF STMT
PARSE #1:c=0,e=91,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2947176003
BINDS #1:
EXEC #1:c=0,e=857,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2947181612