[20210429]檔案頭塊不會快取.txt
[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部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2770463/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20170221]資料檔案與檔案系統快取.txt快取
- php如何上傳txt檔案，並且讀取txt檔案PHP
- Javascript寫入txt和讀取txt檔案示例JavaScript
- [20171122]恢復資料檔案塊頭5.txt
- [20171114]恢復資料檔案塊頭2.txt
- Laravel 路由快取檔案清理Laravel路由快取
- FileProvider共享檔案、快取IDE快取
- nginx靜態檔案快取Nginx快取
- [20160405]bbed修改檔案頭.txt
- 系統快取全解析5：檔案快取依賴快取
- [20171115]恢復資料檔案塊頭3補充.txt
- [20171115]恢復資料檔案塊頭4補充.txt
- Java 讀取txt檔案生成Word文件Java
- 怎麼利用js讀取txt檔案JS
- Android儲存讀取txt檔案Android
- 修改完後,不會清除快取的問題?快取
- LNMP–配置靜態檔案快取LNMP快取
- nginx 只快取靜態檔案Nginx快取
- Mac打不開txt檔案怎麼辦Mac
- 【折騰】發表文章的時候yaml檔案頭不會被解析YAML
- C# 讀取txt檔案生成Word文件C#
- 用C#讀取txt檔案的方法C#
- [20170406]關於檔案頭轉儲.txt
- node 之fs 操作檔案 ？ 快取Buffer ?快取
- 簡單的檔案快取函式快取函式
- 檔案快取（配合JSON陣列）快取JSON陣列
- 快取依賴（檔案、資料庫）快取資料庫
- Mac打不開txt檔案怎麼解決Mac
- Java解析ELF檔案：使用Java讀取檔案頭部、節區頭部表、程式頭部表Java
- 讀取txt檔案的簡易演算法演算法
- java 讀取.txt檔案時，注意的問題Java
- 【易語言】編輯框讀取txt檔案
- LIUNUX如何擷取txt檔案中的內容，並建立新檔案UX
- 20170224測試資料庫塊在檔案系統快取多少資料庫快取
- 檔案快取是不是有問題啊快取
- 如何在蘋果 Mac上清除快取檔案？蘋果Mac快取
- 解密騰訊課堂視訊快取檔案解密快取
- PHP遞迴方式刪除快取檔案PHP遞迴快取