[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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20191009]檔案頭fuzzy.txt
- php如何上傳txt檔案,並且讀取txt檔案PHP
- FileProvider共享檔案、快取IDE快取
- Laravel 路由快取檔案清理Laravel路由快取
- Java 讀取txt檔案生成Word文件Java
- [20201218]資料檔案OS頭的修復.txt
- C# 讀取txt檔案生成Word文件C#
- 簡單的檔案快取函式快取函式
- node 之fs 操作檔案 ? 快取Buffer ?快取
- LIUNUX如何擷取txt檔案中的內容,並建立新檔案UX
- 【易語言】編輯框讀取txt檔案
- 讀取txt檔案的簡易演算法演算法
- 檔案快取是不是有問題啊快取
- win10 office tmp快取檔案怎麼刪除_win10 office tmp快取檔案如何刪除Win10快取
- [20220826]ls顯示檔案不區分大小寫.txt
- [work] python讀取txt檔案最後一行Python
- 如何清理Mac系統中殘留的檔案和快取檔案Mac快取
- iOS檔案的高階快取– DDGDataCache_OC 快取庫的高階用法iOS快取
- iOS檔案的高階快取-- DDGDataCache_OC 快取庫的高階用法iOS快取
- [20210318]bbed讀取資料塊.txt
- 解密騰訊課堂視訊快取檔案解密快取
- 如何在蘋果 Mac上清除快取檔案?蘋果Mac快取
- 讀取txt檔案將文字行組合成特定格式
- [20180906]測試同一會話多個子遊標是否快取.txt會話快取
- [20181119]firefox更改快取資料夾.txtFirefox快取
- VMware複製檔案產生的快取路徑快取
- 8.13 標頭檔案剖析:標頭檔案路徑(下)
- Hadoop分散式檔案系統(HDFS)會不會被淘汰?Hadoop分散式
- java呼叫window本地應用程式;讀取TXT型別檔案Java型別
- SQLSERVER匯出TXT文字檔案,ORACLE SQL LOADER匯入TXT文字檔案SQLServerOracle
- locate標頭檔案和庫檔案
- [20210831]bbed讀取資料塊6.txt
- [20210323]bbed讀取資料塊5.txt
- [20210318]bbed讀取資料塊2.txt
- [20210319]bbed讀取資料塊3.txt
- http快取實戰(讓你再也不會學過就忘)HTTP快取
- 使用nginx快取伺服器上的靜態檔案Nginx快取伺服器
- printf重寫,可存入檔案、也可存入快取buff快取