[20151221]11g設定db_flash_cache_file.txt
[20151221]11g設定db_flash_cache_file.txt
--11GR2下可以設定db_flash_cache_file與db_flash_cache_size引數，提高資料庫的效能。
--我曾經透過例子，快速建立測試資料庫，在裡面使用ram盤，今天測試使用該引數看看。
http://blog.itpub.net/267265/viewspace-1845062/
1.環境：
SYS@book> @ &r/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
# mkdir -p /mnt/ramdisk
# mount -t tmpfs -o size=8G tmpfs /mnt/ramdisk
2.設定引數：
SYS@book> show parameter db_flash_cache
NAME TYPE VALUE
-------------------- ------------ -----------------
db_flash_cache_file string
db_flash_cache_size big integer 0
SYS@book> alter system set db_flash_cache_file='/mnt/ramdisk/ram.dbf' scope=spfile;
System altered.
SYS@book> alter system set db_flash_cache_size=200M scope=spfile;
System altered.
SYS@book> show spparameter db_flash_cache
SID NAME TYPE VALUE
-------- ----------------------------- ------------- ----------------------------
* db_flash_cache_file string /mnt/ramdisk/ram.dbf
* db_flash_cache_size big integer 200M
3.重啟資料庫看看:
SYS@book> startup
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 243270696 bytes
Database Buffers 373293056 bytes
Redo Buffers 7507968 bytes
Database mounted.
Database opened.
$ ll -l /mnt/ramdisk/ram.dbf
-rw-r--r-- 1 oracle oinstall 209715200 2015-12-21 15:50:03 /mnt/ramdisk/ram.dbf
--注意一定要有寫許可權，我第一次沒有許可權報錯。
4.如何測試呢？
--理論講找一個大表，執行全表掃描。
--參考連結：
Statistics
All I/O operations from DB smart flash cache are counted as physical I/O however Oracle also collects such informations
in new columns.
V$SQL - OPTIMIZED_PHY_READ_REQUESTS
V$SQLAREA - OPTIMIZED_PHY_READ_REQUESTS
V$FILESTAT - OPTIMIZED_PHYBLKRD
select name from v$statname where name like 'physical%optimized%';
NAME
------------------------------------
physical read requests optimized
physical read total bytes optimized
You can see such stats in V$SESSTAT and V$SYSSTAT
--在11g的AWR報告中出現了新的段落來描述資料庫物件和SQL分別體現的高和低的Smart flash cache命中率。這些段落是：
Segment by unoptimized reads
Segment by Optimized reads
SQL ordered by Physical Reads (Unoptimized)
--在 AWR報告中I/O讀取請求收益於Smart flash cache的被稱作"Optimized reads"， 僅僅是從普通SAS DISK讀取的稱作"Unoptimized Reads"
--參照連結測試我修改1點點：
create table test_tbl1 (id number, name varchar2(20)) storage(flash_cache keep);
$ strings /mnt/ramdisk/ram.dbf | grep -i aaaaaa
begin
for i in 1..1000000
loop
insert into test_tbl1 values(i, 'aaaaaa');
end loop;
commit;
end;
/
SYS@book> select status, count(*) from v$bh group by status;
STATUS COUNT(*)
---------- ----------
xcur 25765
free 12136
cr 16
--我並沒有看到作者的status='flashcur'.
$ strings /mnt/ramdisk/ram.dbf | grep -i aaaaaa
--也沒有顯示。
--插入多次, 記錄數達到16000000。
--避開direct path read。
SCOTT@test> alter session set events '10949 trace name context forever, level 1';
Session altered.
SCOTT@book> select count(*) from test_tbl1;
COUNT(*)
----------
16000000
STATUS COUNT(*)
---------- ----------
xcur 42781
flashcur 4972
free 7252
cr 51
--這次看到了status='flashcur'.不知道是否是這個原因。
$ strings /mnt/ramdisk/ram.dbf | grep -i aaaaaa |wc
2090338 2090338 16717810
--很明顯這次存在資訊，不知道什麼條件觸發使用。我分析表，當前佔用：
SCOTT@book> select BLOCKS from dba_tables where owner='SCOTT' and table_name='TEST_TBL1';
BLOCKS
----------
38401
SCOTT@book> select 38401*8192/1024/1024 from dual ;
38401*8192/1024/1024
--------------------
300.007813
SCOTT@book> select * from V$SEGMENT_STATISTICS where owner=user and object_name='TEST_TBL1' and statistic_name like 'optimized%' order by value desc;
OWNER OBJECT_NAME SUBOBJECT_ TABLESPACE_NAME TS# OBJ# DATAOBJ# OBJECT_TYPE STATISTIC_NAME STATISTIC# VALUE
------ ------------ ---------- --------------- --- ---------- ---------- ------------------ -------------------------- ---------- ----------
SCOTT TEST_TBL1 USERS 4 88927 88927 TABLE optimized physical reads 11 4610
SCOTT TEST_TBL1 USERS 4 88927 88927 TABLE optimized physical writes 12 0
SCOTT@book> select count(*) from test_tbl1;
COUNT(*)
----------
16000000
SCOTT@book> select * from V$SEGMENT_STATISTICS where owner=user and object_name='TEST_TBL1' and statistic_name like 'optimized%' order by value desc;
OWNER OBJECT_NAME SUBOBJECT_ TABLESPACE_NAME TS# OBJ# DATAOBJ# OBJECT_TYPE STATISTIC_NAME STATISTIC# VALUE
------ ------------ ---------- ---------------- --- ---------- ---------- ------------------ --------------------------- ---------- ----------
SCOTT TEST_TBL1 USERS 4 88927 88927 TABLE optimized physical reads 11 9454
SCOTT TEST_TBL1 USERS 4 88927 88927 TABLE optimized physical writes 12 0
--可以發現統計資訊optimized physical reads發生變化。
--建立awr報表 exec dbms_workload_repository.create_snapshot();
Segments by Optimized Reads DB/Inst: BOOK/book Snaps: 264-265
-> Total Optimized Read Requests: 12,131
-> Captured Segments account for 92.9% of Total
Tablespace Subobject Obj. Optimized
Owner Name Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SCOTT USERS TEST_TBL1 TABLE 9,454 77.93
SYS SYSTEM ICOL$ TABLE 1,183 9.75
SYS SYSTEM OBJ$ TABLE 585 4.82
SYS SYSTEM I_COL_USAGE$ INDEX 9 .07
SYS SYSTEM I_OBJ2 INDEX 6 .05
------------------------------------------------------
--如果沒有開啟，Segments by Optimized Reads基本沒有資訊。
5.做一次10046跟蹤看看：
SCOTT@book> @ &r/10046on 12
old 1: alter session set events '10046 trace name context forever, level &1'
new 1: alter session set events '10046 trace name context forever, level 12'
Session altered.
SCOTT@book> select count(*) from test_tbl1;
COUNT(*)
----------
16000000
SCOTT@book> @ &r/10046off
Session altered.
********************************************************************************
SQL ID: 50gz4qp0t60yt Plan Hash: 1237300194
select count(*)
from
test_tbl1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 25 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.99 1.00 4883 37672 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.00 1.00 4883 37697 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 83
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=37672 pr=4883 pw=0 time=1000780 us)
16000000 16000000 16000000 TABLE ACCESS FULL TEST_TBL1 (cr=37672 pr=4883 pw=0 time=1262409 us cost=10474 size=0 card=16000000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
Disk file operations I/O 2 0.00 0.00
db flash cache single block physical read
525 0.00 0.00
db flash cache multiblock physical read 426 0.00 0.01
db file scattered read 124 0.00 0.00
db file sequential read 55 0.00 0.00
SQL*Net message from client 2 2.93 2.93
********************************************************************************
--注意看db flash cache single block physical read，db flash cache multiblock physical read應該是相關的等待事件。
$ strings /mnt/ramdisk/ram.dbf | grep -i aaaaaa |wc
5778202 5778202 46212379
--快取的記錄也增加許多。
SCOTT@book> select sql_text,child_number,OPTIMIZED_PHY_READ_REQUESTS from V$SQL where sql_id='50gz4qp0t60yt';
SQL_TEXT CHILD_NUMBER OPTIMIZED_PHY_READ_REQUESTS
-------------------------------- ------------ ---------------------------
select count(*) from test_tbl1 0 4338
select count(*) from test_tbl1 1 38882
SCOTT@book> select * from V$FILESTAT;
FILE# PHYRDS PHYWRTS PHYBLKRD OPTIMIZED_PHYBLKRD PHYBLKWRT SINGLEBLKRDS READTIM WRITETIM SINGLEBLKRDTIM AVGIOTIM LSTIOTIM MINIOTIM MAXIORTM MAXIOWTM
---------- ---------- ---------- ---------- ------------------ ---------- ------------ ---------- ---------- -------------- ---------- ---------- ---------- ---------- ----------
1 16082 144 29027 2056 188 15456 15 0 11 0 0 0 0 0
2 2090 659 3772 0 801 1834 2 1 1 0 0 0 0 0
3 77 6062 77 0 22326 77 0 19 0 0 0 0 0 0
4 53729 21555 56788 53136 55749 53408 33 63 32 0 0 0 0 0
5 297 0 22779 0 0 56 1 0 0 0 0 0 0 0
6 4 0 4 0 0 4 0 0 0 0 0 0 0 0
6 rows selected.
--注意看 OPTIMIZED_PHYBLKRD欄位。
6.最後做一個粗暴的測試，破壞ram盤看看會發生什麼?
$ dd if=/dev/zero of=/mnt/ramdisk/ram.dbf bs=1M count=120;
120+0 records in
120+0 records out
125829120 bytes (126 MB) copied, 0.125441 seconds, 1.0 GB/s
SCOTT@book> select count(*) from test_tbl1;
COUNT(*)
----------
16000000
--可以發現沒有任何問題。
--檢查alert*.log出現如下提示：
Mon Dec 21 17:03:50 2015
Encounter problem verifying flash cache /mnt/ramdisk/ram.dbf. Disable flash cache and issue an ORA-700 for diagnostics
Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_gen0_25635.trc (incident=70483):
ORA-00700: soft internal error, arguments: [kcbl2vfyfh_action], [db_flash_cache_file integrity check failed], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/book/book/incident/incdir_70483/book_gen0_25635_i70483.trc
Flash Cache: disabling started.
Flash Cache: future reads are disabled.
Start disabling flash cache writes..
Flash cache: future write-issues disabled
Mon Dec 21 17:03:51 2015
Flash cache: DBW1 stopping flash writes...
Mon Dec 21 17:03:51 2015
Flash cache: DBW0 stopping flash writes...
Mon Dec 21 17:03:51 2015
Flash cache: DBW2 stopping flash writes...
Flash cache: DBW1 garbage-collecting for issued writes..
Flash cache: DBW1 invalidating existing flash buffers..Flash cache: DBW0 garbage-collecting for issued writes..
Flash cache: DBW0 invalidating existing flash buffers..
Flash cache: DBW2 garbage-collecting for issued writes..
Flash cache: DBW2 invalidating existing flash buffers..
Flash cache: DBW1 done with write disabling. Checking other DBWs..
Flash cache: DBW0 done with write disabling. Checking other DBWs..
Flash cache: DBW2 done with write disabling. Checking other DBWs..
Mon Dec 21 17:03:52 2015
Dumping diagnostic data in directory=[cdmp_20151221170352], requested by (instance=1, osid=25635 (GEN0)), summary=[incident=70483].
Mon Dec 21 17:03:52 2015
Sweep [inc][70483]: completed
Sweep [inc2][70483]: completed
Flash cache: disable completed
db_flash_cache_file closed by DBW0. Flash cache disabled
$ cat /u01/app/oracle/diag/rdbms/book/book/trace/book_gen0_25635.trc
*** 2015-12-21 17:03:50.524
*** SESSION ID:(57.1) 2015-12-21 17:03:50.524
*** CLIENT ID:() 2015-12-21 17:03:50.524
*** SERVICE NAME:(SYS$BACKGROUND) 2015-12-21 17:03:50.524
*** MODULE NAME:() 2015-12-21 17:03:50.524
*** ACTION NAME:() 2015-12-21 17:03:50.524
Verifying flash cache header read wrong values: corrupt 1, bpid 0, inst_id 0, bsz 0, db_unique_name db id 0, polluted 0, file_id_string (retry 0)
Verifying flash cache header read wrong values: corrupt 1, bpid 0, inst_id 0, bsz 0, db_unique_name db id 0, polluted 0, file_id_string (retry 1)
Verifying flash cache header read wrong values: corrupt 1, bpid 0, inst_id 0, bsz 0, db_unique_name db id 0, polluted 0, file_id_string (retry 2)
Incident 70483 created, dump file: /u01/app/oracle/diag/rdbms/book/book/incident/incdir_70483/book_gen0_25635_i70483.trc
ORA-00700: soft internal error, arguments: [kcbl2vfyfh_action], [db_flash_cache_file integrity check failed], [], [], [], [], [], [], [], [], [], []
Start disabling flash cache..
*** 2015-12-21 17:03:52.212
Disabling completed.
--也就是flash盤出現問題，oracle自動關閉。
SYS@book> select status, count(*) from v$bh group by status;
STATUS COUNT(*)
---------- ----------
xcur 46636
cr 18
總結：
--實際上現在有一些伺服器出廠已經自帶ssd，充分利用這個快速讀寫裝置，可以一定程度提高資料庫的效能。當然我的測試測不出來。
--當然要達到exadata的效果那是不可能的。
--補充：
--如果我重啟資料庫，自動修復ram盤的錯誤資訊。
$ strings /mnt/ramdisk/ram.dbf | less
Oracle RDBMS Flash Cache File
book
--另外不知道條件下觸發會進入flash cache。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1876305/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 11g buffer cache的設定Oracle
- 11g記憶體的設定記憶體
- 11g資料庫設定歸檔模式資料庫模式
- Oracle 11g 密碼設定為不過期Oracle密碼
- Oracle 11g 關於 AWR 的引數設定Oracle
- oracle 11G 自動記憶體管理設定Oracle記憶體
- oracle 11g之instance自動啟動設定Oracle
- ORACLE 11g sqlnet.ora 設定限制IP 訪問OracleSQL
- Oracle 11g 在solaris 10 上核心引數的設定Oracle
- 11g RAC - single client access name (scan) 的設定問題client
- [20151221]sql語句優化.txtSQL優化
- Oracle 11G 資料庫重要的初始化引數設定Oracle資料庫
- solaris 10 安裝 oracle 11g R2核心引數設定Oracle
- oracle 11g 密碼不過期、不限制錯誤次數設定Oracle密碼
- oracle 11G 自動記憶體管理設定修改為手功管理Oracle記憶體
- 線上重定義表ORACLE 11GOracle
- Oracle 11g 中 cursor_sharing 設定為SIMILAR 導致的問題OracleMILA
- Laravel setting 設定 / 系統設定 / 網站設定Laravel網站
- 20151221jquery學習筆記---日曆UIjQuery筆記UI
- 20151221jqueryUI---日曆UI程式碼備份jQueryUI
- 11g下設定sga_max_size=4g時出現的報錯
- 20151221jquery學習筆記--驗證外掛jQuery筆記
- win10電腦 tls安全設定怎麼設定為預設設定Win10TLS
- 如何設定 HomePod？HomePod設定教程分享
- oracle 9i、10g、11g資料庫設定listener密碼的方法Oracle資料庫密碼
- Jenkins定時設定Jenkins
- scrapy預設設定
- Pycharm 設定PyCharm
- 路由設定路由
- webstorm設定WebORM
- vim設定
- 設定NFSNFS
- MTU設定
- 設定 sar
- crontab 設定
- EVENTS設定
- 設定dhcpd
- 設定tftpFTP