最近一直在处理新系统的性能优化问题，这两天特地测试了下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.Your database is running on the Solaris or Oracle Linux operating systems. The flash cache is supported on these operating systems only.SQL> startup;ORA-00439: feature not enabled: Server Flash Cache[oracle@oel-12c ~]$ sqlplus "/as sysdba" SQL*Plus: Release 12.2.0.1.0 Production on 星期五 9月 7 20:13:00 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. 连接到: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> create tablespace my_ts_2 datafile ‘/u01/app/oracle/oradata/nfs_to_14/my_ts02.dbf‘ size 1g autoextend on next 10m maxsize unlimited; 表空间已创建。 SQL> create table my_big_table as select * from dba_tables; 表已创建。 SQL> alter table my_big_table move tablespace my_ts_2; --移动到NFS存储上 表已更改。 SQL> insert into my_big_table select * from my_big_table; 已创建 2106 行。 SQL> / 已创建 4212 行。 SQL> / 已创建 8424 行。 SQL> / 已创建 16848 行。 SQL> / 已创建 33696 行。 SQL> / 已创建 67392 行。 SQL> commit; 提交完成。 SQL> / 提交完成。 SQL> insert into my_big_table select * from my_big_table; 已创建 134784 行。 SQL> / 已创建 269568 行。 SQL> commit; 提交完成。 SQL> insert into my_big_table select * from my_big_table; 已创建 539136 行。 SQL> commit;
使用智能闪存：
SQL> set autotrace on; SQL> select count(1) from my_big_table; COUNT(1) ---------- 1078272 已用时间: 00: 00: 33.06 执行计划 ---------------------------------------------------------- Plan hash value: 1307946652 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 12973 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| MY_BIG_TABLE | 969K| 12973 (1)| 00:00:01 | --------------------------------------------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=2) 统计信息 ---------------------------------------------------------- 124 recursive calls 0 db block gets 47210 consistent gets 47193 physical reads 132 redo size 546 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 1 rows processed SQL> / COUNT(1) ---------- 1078272 已用时间: 00: 00: 03.00 执行计划 ---------------------------------------------------------- Plan hash value: 1307946652 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 12973 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| MY_BIG_TABLE | 969K| 12973 (1)| 00:00:01 | --------------------------------------------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=2) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 47094 consistent gets 47006 physical reads 0 redo size 546 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> / COUNT(1) ---------- 1078272 已用时间: 00: 00: 04.54 执行计划 ---------------------------------------------------------- Plan hash value: 1307946652 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 12973 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| MY_BIG_TABLE | 969K| 12973 (1)| 00:00:01 | --------------------------------------------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=2) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 47094 consistent gets 47020 physical reads 0 redo size 546 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> / COUNT(1) ---------- 1078272 已用时间: 00: 00: 02.24
不使用智能闪存：
SQL> alter system set db_flash_cache_size=0 ; 系统已更改。 已用时间: 00: 00: 01.01 SQL> select count(1) from my_big_table; COUNT(1) ---------- 1078272 已用时间: 00: 00: 30.75 执行计划 ---------------------------------------------------------- Plan hash value: 1307946652 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 12973 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| MY_BIG_TABLE | 969K| 12973 (1)| 00:00:01 | --------------------------------------------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=2) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 47094 consistent gets 47020 physical reads 0 redo size 546 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> / COUNT(1) ---------- 1078272 已用时间: 00: 00: 32.20 执行计划 ---------------------------------------------------------- Plan hash value: 1307946652 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 12973 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| MY_BIG_TABLE | 969K| 12973 (1)| 00:00:01 | --------------------------------------------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=2) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 47094 consistent gets 47020 physical reads 0 redo size 546 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> / COUNT(1) ---------- 1078272 已用时间: 00: 00: 17.56 执行计划 ---------------------------------------------------------- Plan hash value: 1307946652 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 12973 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| MY_BIG_TABLE | 969K| 12973 (1)| 00:00:01 | --------------------------------------------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=2) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 47094 consistent gets 47019 physical reads 0 redo size 546 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> / COUNT(1) ---------- 1078272 已用时间: 00: 00: 15.11 执行计划 ---------------------------------------------------------- Plan hash value: 1307946652 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 12973 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| MY_BIG_TABLE | 969K| 12973 (1)| 00:00:01 | --------------------------------------------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=2) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 47094 consistent gets 47019 physical reads 0 redo size 546 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> / COUNT(1) ---------- 1078272 已用时间: 00: 00: 15.69
如上所述，性能相差了6-7倍。可见智能闪存效果还是不错的。
不过需要注意的是，如果95%+的常用数据都已经在iops足够高的磁盘上了比如SSD，这个时候又拿一部分SSD作为智能缓存，性能反而会下降比直接访问磁盘高达1倍。
在oracle 11g中，闪存文件只能配置1个，在12c中没有这个限制了。
oracle 12cR2 smart flash cache测试
原文：https://www.cnblogs.com/zhjh256/p/9607343.html