分享程序员开发的那些事...
更新时间：2022-09-10 16:19:59
[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.如何测试呢？
--理论讲找一个大表，执行全表扫描。
--参考链接：http://dbaora.com/db-smart-flash-cache-in-oracle-11g/
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。