[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。

[20151221]11g設定db_flash_cache_file.txt

相關文章