分类:
2008-10-17 13:22:29
sql_trace、access path和db_file_multiblock_read_count的实际应用。
环境:winxp、oracle9.2
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';
至此,得到下面的结果,采用的是全表扫描,共扫描了5次。
其中p1是文件号,p2起始块号,p3读取数据块数量。
*** 2006-04-21 11:49:30.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
=====================
[1]