测试表为如下的TT表:
SQL> select count(*) from tt;
COUNT(*)
----------
1
SQL> desc tt
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME CHAR(2000)
SQL> alter session set events '10046 trace name context forever, level 12';
会话已更改。
SQL> select * from tt;
ID
----------
NAME
--------------------------------------------------------------------------------
1
A
ID
----------
NAME
--------------------------------------------------------------------------------
ID
----------
NAME
--------------------------------------------------------------------------------
SQL> alter session set events '10046 trace name context off';
会话已更改。
SQL>
生成的跟踪文件为:mytest_ora_2144.trc
D:\oracle\product\10.2.0\admin\mytest\udump>tkprof mytest_ora_2144.trc t.txt
TKPROF: Release 10.2.0.1.0 - Production on 星期五 6月 26 07:56:13 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
select *
from
tt
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.12 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.13 0 7 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 56
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL TT (cr=7 pr=0 pw=0 time=86 us)
从此跟踪文件中可以看到select * from tt;语句的统计情况:
7个一致性读.
另开一个session:
SQL> select object_id from dba_objects where object_name='TT';
OBJECT_ID
----------
51456
SQL> select file#, dbablk, state, tch from x$bh where bj=51456;
FILE# CLASS DBABLK STATE TCH
---------- ---------- ---------- ---------- ----------
4 1 86 1 2
4 4 83 1 2
4 1 88 1 2
4 1 85 1 2
4 1 87 1 2
4 1 84 1 2
已选择6行。
可以看到在内存中表TT被读进了6个块,其中83#块是块头(class=4).而且每个块被访问过两次.因为前面做过两过查询(select count(*),select *)
照这么说来逻辑读也应该是6呀?
可是从跟踪文件里看到的的确是7次.
select * from tt
END OF STMT
PARSE #5:c=15625,e=424301,p=2,cr=6,cu=0,mis=1,r=0,dep=0,og=1,tim=1704415700
BINDS #5:
EXEC #5:c=0,e=5442,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1704447861
WAIT #5: nam='SQL*Net message to client' ela= 6 driver id=1111838976 #bytes=1 p3=0 obj#=51456 tim=1704453325
WAIT #5: nam='SQL*Net more data to client' ela= 24 driver id=1111838976 #bytes=2214 p3=0 obj#=51456 tim=1704458885
FETCH #5:c=0,e=5642,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1704464396
WAIT #5: nam='SQL*Net message from client' ela= 274 driver id=1111838976 #bytes=1 p3=0 obj#=51456 tim=1704470243
FETCH #5:c=0,e=42,p=0,cr=4,cu=0,mis=0,r=0,dep=0,og=1,tim=1704475918
可以看到fetch两次取数,3+4=7.
如何解释呢?有的网友解释说段头块会多读两次.也就是说段头块是两次,而其他数据库块共5次. 但这与x$中的tch计数又不一致.因为我们看到所有块的计数都是一样的.我也试过每做一次select * from tt; 所有块的tch数均加1.
再接着实验.
SQL> select count(*) from tt;
COUNT(*)
----------
20
SQL> select extent_id,file_id, block_id, blocks from dba_extents where segment_name='TT';
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 4 81 8
1 4 2769 8
再收集此时的select * from tt;跟踪文件.
select *
from
tt
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.03 0.12 0 18 0 20
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.03 0.13 0 18 0 20
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 56
Rows Row Source Operation
------- ---------------------------------------------------
20 TABLE ACCESS FULL TT (cr=18 pr=0 pw=0 time=118 us)
这一次是18个逻辑读.
SQL> select file#, class,dbablk, state, tch from x$bh where bj=51456 and tch>0 order by 3;
FILE# CLASS DBABLK STATE TCH
---------- ---------- ---------- ---------- ----------
4 4 83 1 3
4 1 84 1 2
4 1 85 1 2
4 1 86 1 2
4 1 87 1 2
4 1 88 1 2
4 1 2769 1 2
4 1 2770 1 2
4 1 2771 1 2
4 1 2772 1 2
4 1 2773 1 2
FILE# CLASS DBABLK STATE TCH
---------- ---------- ---------- ---------- ----------
4 1 2774 1 2
4 1 2775 1 2
4 1 2776 1 2
已选择14行。
内存块14个.
SQL> insert into tt select * from tt;
已创建20行。
SQL> commit;
提交完成。
SQL> select extent_id,file_id, block_id, blocks from dba_extents where segment_name='TT';
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 4 81 8
1 4 2769 8
2 4 2777 8
SQL> alter system flush buffer_cache;
系统已更改。
SQL> select file#, class,dbablk, state, tch from x$bh where bj=51456 and tch>0 order by 3;
未选定行
SQL> select count(*) from tt;
COUNT(*)
----------
40
SQL> select file#, class,dbablk, state, tch from x$bh where bj=51456 and tch>0 order by 3;
FILE# CLASS DBABLK STATE TCH
---------- ---------- ---------- ---------- ----------
4 4 83 1 1
4 1 84 1 1
4 1 85 1 1
4 1 86 1 1
4 1 87 1 1
4 1 88 1 1
4 1 2769 1 1
4 1 2770 1 1
4 1 2771 1 1
4 1 2772 1 1
4 1 2773 1 1
FILE# CLASS DBABLK STATE TCH
---------- ---------- ---------- ---------- ----------
4 1 2774 1 1
4 1 2775 1 1
4 1 2776 1 1
4 1 2778 1 1
4 1 2779 1 1
4 1 2780 1 1
4 1 2781 1 1
4 1 2782 1 1
4 1 2783 1 1
4 1 2784 1 1
已选择21行。
select *
from
tt
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 0.03 0.22 0 26 0 40
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.03 0.23 0 26 0 40
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 56
Rows Row Source Operation
------- ---------------------------------------------------
40 TABLE ACCESS FULL TT (cr=26 pr=0 pw=0 time=119 us)
由以上实验得到可以看到:
extents buffer blocks cr rows
1 6 7 1
2 14 18 20
3 21 26 40
4 29 37 80
默认ARRAYSIZE=15
似乎有以下的公式:cr=buffer blocks+ ceil(rows/15)+2(rows=1例外)
看来段头一般是读三次.只是这个不知如何进行证明.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9375/viewspace-607634/ ，如需转载，请注明出处，否则将追究法律责任。
