[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.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2770463/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
