[20210429]文件头块不会缓存.txt

来源：这里教程网时间：2026-03-03 16:38:34 作者：

[20210429]文件头块不会缓存.txt --//昨天做数据库检查与优化，发现一个现象就是文件头不会进入数据缓存，自己在测试环境验证看看。 1.环境： SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production $ cat bh.sql set echo off -------------------------------------------------------------------------------- -- @name: bh -- @author: dion cho -- @note: show block header -- @usage: @bh f# b# state -------------------------------------------------------------------------------- col object_name format a20 col state format a10 select b.inst_id, b.hladdr, b.dbarfil, b.dbablk, b.class, decode(b.class,1,'data block',2,'sort block',3,'save undo block', 4, 'segment header',5,'save undo header',6,'free list',7,'extent map', 8,'1st level bmb',9,'2nd level bmb',10,'3rd level bmb', 11,'bitmap block', 12,'bitmap index block',13,'file header block',14,'unused', 15,'system undo header',16,'system undo block', 17,'undo header', 18,'undo block') class_type, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated') as state, b.tch, cr_scn_bas, cr_scn_wrp, cr_uba_fil, cr_uba_blk, cr_uba_seq, ba, b.LE_ADDR, (select object_name from dba_objects where data_object_id = b.obj) as object_name from x$bh b where dbarfil = &1 and dbablk = &2 ; 2.测试： SYS@book> select * from v$dbfile; FILE# NAME ---------- ---------------------------------------- 6 /mnt/ramdisk/book/tea01.dbf 5 /mnt/ramdisk/book/example01.dbf 4 /mnt/ramdisk/book/users01.dbf 3 /mnt/ramdisk/book/undotbs01.dbf 2 /mnt/ramdisk/book/sysaux01.dbf 1 /mnt/ramdisk/book/system01.dbf 7 /mnt/ramdisk/book/mssm01.dbf 8 /mnt/ramdisk/book/big.dbf 9 /mnt/ramdisk/book/test_ts01.dbf 10 /mnt/ramdisk/book/test_ts_aux.dbf 11 /mnt/ramdisk/book/mssm02.dbf 11 rows selected. $ seq 11 | xargs -IQ sqlplus -s -l / as sysdba @ bh Q 1 no rows selected no rows selected no rows selected no rows selected no rows selected no rows selected no rows selected no rows selected no rows selected no rows selected no rows selected --//确实不会缓存文件头。执行如下后再次 select * from v$datafile_header ; seq 11 | xargs -IQ sqlplus -s -l / as sysdba @ bh Q 1 --//结果一样。说明数据缓存不会缓存文件头。 3.可以做一个跟踪验证看看： @ 10046on 12 select * from v$datafile_header where file#=4; @ 10046off ===================== PARSING IN CURSOR #140118203128856 len=45 dep=0 uid=0 oct=3 lid=0 tim=1619665874016756 hv=1305478851 ad='7ea1c210' sqlid='6j6mc7j6x01q3' select * from v$datafile_header where file#=4 END OF STMT PARSE #140118203128856:c=1000,e=104,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=358502644,tim=1619665874016752 WAIT #140118203128856: nam='Disk file operations I/O' ela= 121 FileOperation=8 fileno=0 filetype=8 obj#=-1 tim=1619665874016965 EXEC #140118203128856:c=0,e=64,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=358502644,tim=1619665874017078 WAIT #140118203128856: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1619665874017158 WAIT #140118203128856: nam='control file sequential read' ela= 13 file#=0 block#=1 blocks=1 obj#=-1 tim=1619665874017264 WAIT #140118203128856: nam='control file sequential read' ela= 8 file#=0 block#=15 blocks=1 obj#=-1 tim=1619665874017307 WAIT #140118203128856: nam='control file sequential read' ela= 7 file#=0 block#=17 blocks=1 obj#=-1 tim=1619665874017341 WAIT #140118203128856: nam='control file sequential read' ela= 6 file#=0 block#=23 blocks=1 obj#=-1 tim=1619665874017369 WAIT #140118203128856: nam='db file sequential read' ela= 6 file#=4 block#=1 blocks=1 obj#=-1 tim=1619665874017404 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ FETCH #140118203128856:c=0,e=285,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=358502644,tim=1619665874017497 WAIT #140118203128856: nam='SQL*Net message from client' ela= 841 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1619665874018386 FETCH #140118203128856:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=358502644,tim=1619665874018465 STAT #140118203128856 id=1 cnt=1 pid=0 pos=1 obj=0 op='FIXED TABLE FIXED INDEX X$KCVFH (ind:1) (cr=0 pr=0 pw=0 time=232 us cost=0 size=533 card=1)' WAIT #140118203128856: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1619665874018602 *** 2021-04-29 11:11:17.616 WAIT #140118203128856: nam='SQL*Net message from client' ela= 3597989 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1619665877616618 CLOSE #140118203128856:c=0,e=12,dep=0,type=0,tim=1619665877616718 ===================== --//可以发现确实查询要读取文件头块。你每次执行都会出现db file sequential read等待事件。 4.为什么不缓存呢？ --//仔细想想oracle这样设置是有道理的，应用程序不会读取它，写入仅仅发生在alter systen checkpoint检查点的时候。 --//如果写入发生，保证更快的写入磁盘，当然也许还有安全的考虑。 5.如何发现的这个问题： --//实际上还是出在我们安装的监测软件上，我发现这个语句c6th1588hudjy存在一定的db file sequential read。 > @ sqlid c6th1588hudjy SQL_ID SQLTEXT ------------- --------------------------- c6th1588hudjy select status from v$backup --//服务器本来业务很少。 > @ d_buffer c6th1588hudjy 60 1 EXECUTIONS1 BUFFER_GETS1 ELAPSED_TIME1 ROWS_PROCESSED1 每次buffer_gets 每次执行时间平均处理记录数 INST_ID --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- 960913 168 4287373032 18257347 .00017483372584 4461.7702455894 19 1 ... sleep 60 , waiting .... EXECUTIONS2 BUFFER_GETS2 ELAPSED_TIME2 ROWS_PROCESSED2 每次buffer_gets 每次执行时间平均处理记录数 INST_ID --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- 960917 168 4287387387 18257423 .00017483299806 4461.7666114763 19 1 执行次数总buffer_gets 总执行时间总处理记录数每次buffer_gets 每次执行时间平均处理记录数 --------------- --------------- --------------- --------------- --------------- --------------- --------------- 4 0 14355 76 0 3588.75 19 SYS@127.0.0.1:9014/ywdb> @ d_buffer c6th1588hudjy 60 2 EXECUTIONS1 BUFFER_GETS1 ELAPSED_TIME1 ROWS_PROCESSED1 每次buffer_gets 每次执行时间平均处理记录数 INST_ID --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- 960950 36 4508910080 18258050 .00003746292731 4692.1380716999 19 2 ... sleep 60 , waiting .... EXECUTIONS2 BUFFER_GETS2 ELAPSED_TIME2 ROWS_PROCESSED2 每次buffer_gets 每次执行时间平均处理记录数 INST_ID --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- 960954 36 4508925284 18258126 .00003746277137 4692.1343623108 19 2 执行次数总buffer_gets 总执行时间总处理记录数每次buffer_gets 每次执行时间平均处理记录数 --------------- --------------- --------------- --------------- --------------- --------------- --------------- 4 0 15204 76 0 3801 19 --//基本上60/4=15秒执行1次监测，2个实例就是8次。 --//实际上我们服务器不忙，看到排前几乎全是监测执行的sql语句，参考http://blog.itpub.net/267265/viewspace-2745795/ --//对于rac读取文件头没有OS缓存，如果大量的读取文件头信息，自然会影响"性能"。 > @ ashtop sql_id,p1,p2,p3,event "event is not null and sql_id='c6th1588hudjy'" trunc(sysdate) sysdate Total Seconds AAS %This SQL_ID P1 P2 P3 EVENT FIRST_SEEN LAST_SEEN --------- ------- ------- ------------- ------------ ------------ ------------ ---------------------------------------- ------------------- ------------------- 2 .0 9% | c6th1588hudjy 0 39 1 control file sequential read 2021-04-29 07:35:54 2021-04-29 07:41:09 2 .0 9% | c6th1588hudjy 0 40 1 control file sequential read 2021-04-29 04:36:24 2021-04-29 11:17:39 2 .0 9% | c6th1588hudjy 0 42 1 control file sequential read 2021-04-29 00:38:24 2021-04-29 00:38:39 2 .0 9% | c6th1588hudjy 0 48 1 control file sequential read 2021-04-29 04:53:39 2021-04-29 10:03:54 2 .0 9% | c6th1588hudjy 4 1 1 db file sequential read 2021-04-29 04:42:39 2021-04-29 10:30:09 2 .0 9% | c6th1588hudjy 9 1 1 db file sequential read 2021-04-29 03:47:54 2021-04-29 10:50:09 2 .0 9% | c6th1588hudjy 14 1 1 db file sequential read 2021-04-29 00:58:54 2021-04-29 10:41:09 2 .0 9% | c6th1588hudjy 15 1 1 db file sequential read 2021-04-29 03:08:09 2021-04-29 04:29:54 1 .0 5% | c6th1588hudjy 0 1 1 control file sequential read 2021-04-29 08:36:24 2021-04-29 08:36:24 1 .0 5% | c6th1588hudjy 1 1 1 db file sequential read 2021-04-29 05:24:54 2021-04-29 05:24:54 1 .0 5% | c6th1588hudjy 2 1 1 db file sequential read 2021-04-29 06:12:09 2021-04-29 06:12:09 1 .0 5% | c6th1588hudjy 11 1 1 db file sequential read 2021-04-29 03:49:39 2021-04-29 03:49:39 1 .0 5% | c6th1588hudjy 17 1 1 db file sequential read 2021-04-29 10:37:54 2021-04-29 10:37:54 1 .0 5% | c6th1588hudjy 19 1 1 db file sequential read 2021-04-29 06:09:39 2021-04-29 06:09:39 14 rows selected.

编辑推荐：

返回列表

[20210429]文件头块不会缓存.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推