最近一直在处理新系统的性能优化问题，这两天特地测试了下oracle 11gR2开始引入的smart flash cache。
其介绍参考MOS文档，How To Size the Database Smart Flash Cache (文档 ID 1317950.1)
The Database Smart Flash Cache is a new feature in Oracle Database 11g Release 2 (11.2).
The Database Smart Flash Cache is a transparent extension of the database buffer cache using solid state device (SSD) technology.
The SSD acts as a Level 2 cache to the ( Level 1) SGA.
Database Smart Flash Cache can greatly improve the performance of Oracle databases by reducing the amount of disk I/O at a much lower cost than adding an equivalent amount of RAM.
简单的说，他比较适合于系统中绝大部分存储使用机械硬盘，但是又配备了小部分SSD的场景，比如说我们的某个系统4.5T存储，600GB的SSD。
Your database is running on the Solaris or Oracle Linux operating systems.
The flash cache is supported on these operating systems only .
如果不是solaris或者OEL，则启动时报错，这很有可能会是个最大的限制，因为很多企业限定了必须使用RHEL/CENTOS/SUSE。如下：
SQL> startup;
ORA-00439: feature not enabled: Server Flash Cache
这明显就是Oracle设置的障碍。
可以通过DB_FLASH_CACHE_FILE和db_flash_cache_size设置智能闪存的位置以及大小。
下面来看实际效果：
1[oracle@oel-12c ~]$ sqlplus "/as sysdba" 2 3SQL*Plus: Release 12.2.0.1.0 Production on 星期五 9月 7 20:13:00 2018 4 5Copyright (c) 1982, 2016, Oracle. All rights reserved. 6 7 8连接到: 9Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 10 11SQL> create tablespace my_ts_2 datafile '/u01/app/oracle/oradata/nfs_to_14/my_ts02.dbf' size 1g autoextend on next 10m maxsize unlimited; 12 13表空间已创建。 14 15SQL> create table my_big_table as select * from dba_tables; 16 17表已创建。 18 19SQL> alter table my_big_table move tablespace my_ts_2; --移动到NFS存储上 20 21表已更改。 22 23SQL> insert into my_big_table select * from my_big_table; 24 25已创建 2106 行。 26 27SQL> / 28 29已创建 4212 行。 30 31SQL> / 32 33已创建 8424 行。 34 35SQL> / 36 37已创建 16848 行。 38 39SQL> / 40 41已创建 33696 行。 42 43SQL> / 44 45已创建 67392 行。 46 47SQL> commit; 48 49提交完成。 50 51SQL> / 52 53提交完成。 54 55SQL> insert into my_big_table select * from my_big_table; 56 57已创建 134784 行。 58 59SQL> / 60 61已创建 269568 行。 62 63SQL> commit; 64 65提交完成。 66 67SQL> insert into my_big_table select * from my_big_table; 68 69已创建 539136 行。 70 71SQL> commit;
使用智能闪存：
1SQL> set autotrace on; 2SQL> select count(1) from my_big_table; 3 4 COUNT(1) 5---------- 6 1078272 7 8已用时间: 00: 00: 33.06 9 10执行计划 11---------------------------------------------------------- 12Plan hash value: 1307946652 13 14--------------------------------------------------------------------------- 15| Id | Operation | Name | Rows | Cost (%CPU)| Time | 16--------------------------------------------------------------------------- 17| 0 | SELECT STATEMENT | | 1 | 12973 (1)| 00:00:01 | 18| 1 | SORT AGGREGATE | | 1 | | | 19| 2 | TABLE ACCESS FULL| MY_BIG_TABLE | 969K| 12973 (1)| 00:00:01 | 20--------------------------------------------------------------------------- 21 22Note 23----- 24 - dynamic statistics used: dynamic sampling (level=2) 25 26 27统计信息 28---------------------------------------------------------- 29 124 recursive calls 30 0 db block gets 31 47210 consistent gets 32 47193 physical reads 33 132 redo size 34 546 bytes sent via SQL*Net to client 35 607 bytes received via SQL*Net from client 36 2 SQL*Net roundtrips to/from client 37 3 sorts (memory) 38 0 sorts (disk) 39 1 rows processed 40 41SQL> / 42 43 COUNT(1) 44---------- 45 1078272 46 47已用时间: 00: 00: 03.00 48 49执行计划 50---------------------------------------------------------- 51Plan hash value: 1307946652 52 53--------------------------------------------------------------------------- 54| Id | Operation | Name | Rows | Cost (%CPU)| Time | 55--------------------------------------------------------------------------- 56| 0 | SELECT STATEMENT | | 1 | 12973 (1)| 00:00:01 | 57| 1 | SORT AGGREGATE | | 1 | | | 58| 2 | TABLE ACCESS FULL| MY_BIG_TABLE | 969K| 12973 (1)| 00:00:01 | 59--------------------------------------------------------------------------- 60 61Note 62----- 63 - dynamic statistics used: dynamic sampling (level=2) 64 65 66统计信息 67---------------------------------------------------------- 68 0 recursive calls 69 0 db block gets 70 47094 consistent gets 71 47006 physical reads 72 0 redo size 73 546 bytes sent via SQL*Net to client 74 607 bytes received via SQL*Net from client 75 2 SQL*Net roundtrips to/from client 76 0 sorts (memory) 77 0 sorts (disk) 78 1 rows processed 79 80SQL> / 81 82 COUNT(1) 83---------- 84 1078272 85 86已用时间: 00: 00: 04.54 87 88执行计划 89---------------------------------------------------------- 90Plan hash value: 1307946652 91 92--------------------------------------------------------------------------- 93| Id | Operation | Name | Rows | Cost (%CPU)| Time | 94--------------------------------------------------------------------------- 95| 0 | SELECT STATEMENT | | 1 | 12973 (1)| 00:00:01 | 96| 1 | SORT AGGREGATE | | 1 | | | 97| 2 | TABLE ACCESS FULL| MY_BIG_TABLE | 969K| 12973 (1)| 00:00:01 | 98--------------------------------------------------------------------------- 99 100Note 101----- 102 - dynamic statistics used: dynamic sampling (level=2) 103 104 105统计信息 106---------------------------------------------------------- 107 0 recursive calls 108 0 db block gets 109 47094 consistent gets 110 47020 physical reads 111 0 redo size 112 546 bytes sent via SQL*Net to client 113 607 bytes received via SQL*Net from client 114 2 SQL*Net roundtrips to/from client 115 0 sorts (memory) 116 0 sorts (disk) 117 1 rows processed 118 119SQL> / 120 121 COUNT(1) 122---------- 123 1078272 124 125已用时间: 00: 00: 02.24
不使用智能闪存：
1SQL> alter system set db_flash_cache_size=0 ; 2 3系统已更改。 4 5已用时间: 00: 00: 01.01 6SQL> select count(1) from my_big_table; 7 8 COUNT(1) 9---------- 10 1078272 11 12已用时间: 00: 00: 30.75 13 14执行计划 15---------------------------------------------------------- 16Plan hash value: 1307946652 17 18--------------------------------------------------------------------------- 19| Id | Operation | Name | Rows | Cost (%CPU)| Time | 20--------------------------------------------------------------------------- 21| 0 | SELECT STATEMENT | | 1 | 12973 (1)| 00:00:01 | 22| 1 | SORT AGGREGATE | | 1 | | | 23| 2 | TABLE ACCESS FULL| MY_BIG_TABLE | 969K| 12973 (1)| 00:00:01 | 24--------------------------------------------------------------------------- 25 26Note 27----- 28 - dynamic statistics used: dynamic sampling (level=2) 29 30 31统计信息 32---------------------------------------------------------- 33 0 recursive calls 34 0 db block gets 35 47094 consistent gets 36 47020 physical reads 37 0 redo size 38 546 bytes sent via SQL*Net to client 39 607 bytes received via SQL*Net from client 40 2 SQL*Net roundtrips to/from client 41 0 sorts (memory) 42 0 sorts (disk) 43 1 rows processed 44 45SQL> / 46 47 COUNT(1) 48---------- 49 1078272 50 51已用时间: 00: 00: 32.20 52 53执行计划 54---------------------------------------------------------- 55Plan hash value: 1307946652 56 57--------------------------------------------------------------------------- 58| Id | Operation | Name | Rows | Cost (%CPU)| Time | 59--------------------------------------------------------------------------- 60| 0 | SELECT STATEMENT | | 1 | 12973 (1)| 00:00:01 | 61| 1 | SORT AGGREGATE | | 1 | | | 62| 2 | TABLE ACCESS FULL| MY_BIG_TABLE | 969K| 12973 (1)| 00:00:01 | 63--------------------------------------------------------------------------- 64 65Note 66----- 67 - dynamic statistics used: dynamic sampling (level=2) 68 69 70统计信息 71---------------------------------------------------------- 72 0 recursive calls 73 0 db block gets 74 47094 consistent gets 75 47020 physical reads 76 0 redo size 77 546 bytes sent via SQL*Net to client 78 607 bytes received via SQL*Net from client 79 2 SQL*Net roundtrips to/from client 80 0 sorts (memory) 81 0 sorts (disk) 82 1 rows processed 83 84SQL> / 85 86 COUNT(1) 87---------- 88 1078272 89 90已用时间: 00: 00: 17.56 91 92执行计划 93---------------------------------------------------------- 94Plan hash value: 1307946652 95 96--------------------------------------------------------------------------- 97| Id | Operation | Name | Rows | Cost (%CPU)| Time | 98--------------------------------------------------------------------------- 99| 0 | SELECT STATEMENT | | 1 | 12973 (1)| 00:00:01 | 100| 1 | SORT AGGREGATE | | 1 | | | 101| 2 | TABLE ACCESS FULL| MY_BIG_TABLE | 969K| 12973 (1)| 00:00:01 | 102--------------------------------------------------------------------------- 103 104Note 105----- 106 - dynamic statistics used: dynamic sampling (level=2) 107 108 109统计信息 110---------------------------------------------------------- 111 0 recursive calls 112 0 db block gets 113 47094 consistent gets 114 47019 physical reads 115 0 redo size 116 546 bytes sent via SQL*Net to client 117 607 bytes received via SQL*Net from client 118 2 SQL*Net roundtrips to/from client 119 0 sorts (memory) 120 0 sorts (disk) 121 1 rows processed 122 123SQL> / 124 125 COUNT(1) 126---------- 127 1078272 128 129已用时间: 00: 00: 15.11 130 131执行计划 132---------------------------------------------------------- 133Plan hash value: 1307946652 134 135--------------------------------------------------------------------------- 136| Id | Operation | Name | Rows | Cost (%CPU)| Time | 137--------------------------------------------------------------------------- 138| 0 | SELECT STATEMENT | | 1 | 12973 (1)| 00:00:01 | 139| 1 | SORT AGGREGATE | | 1 | | | 140| 2 | TABLE ACCESS FULL| MY_BIG_TABLE | 969K| 12973 (1)| 00:00:01 | 141--------------------------------------------------------------------------- 142 143Note 144----- 145 - dynamic statistics used: dynamic sampling (level=2) 146 147 148统计信息 149---------------------------------------------------------- 150 0 recursive calls 151 0 db block gets 152 47094 consistent gets 153 47019 physical reads 154 0 redo size 155 546 bytes sent via SQL*Net to client 156 607 bytes received via SQL*Net from client 157 2 SQL*Net roundtrips to/from client 158 0 sorts (memory) 159 0 sorts (disk) 160 1 rows processed 161 162SQL> / 163 164 COUNT(1) 165---------- 166 1078272 167 168已用时间: 00: 00: 15.69
如上所述，性能相差了6-7倍。可见智能闪存效果还是不错的。
不过需要注意的是，如果95%+的常用数据都已经在iops足够高的磁盘上了比如SSD，这个时候又拿一部分SSD作为智能缓存，性能反而会下降比直接访问磁盘高达1倍。
在oracle 11g中，闪存文件只能配置1个，在12c中没有这个限制了。