联系：QQ(5163721)
标题：Smart Flash Cache on Exadata(4)—使用flash cache
作者：Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
对于表和索引，可以在创建表时使用storage子句将表保存在flashcache中，如果表已经创建完成了，那么可以使用alert table或者alert index命令进行修改相应对象的storage属性，将对象混存在flash cache中。
我做了两张测试表，他们数据的内容基本一直，不过一张是压缩表，一张是非压缩表：
11:14:16 SQL> set autot off 11:16:40 SQL> col segment_name for a30 11:16:41 SQL> select /*+ parallel(a,24) */ SEGMENT_NAME,SUM(BYTES)/1024/1024/1024 GB 11:16:41 2 FROM USER_EXTENTS 11:16:41 3 WHERE SEGMENT_NAME LIKE '%COMPRESS%' 11:16:41 4 GROUP BY SEGMENT_NAME; SEGMENT_NAME GB ------------------------------ ---------- LUNAR_UNCOMPRESS 17.234436 LUNAR_COMPRESS 1.27032471 11:31:22 SQL> select count(*) from LUNAR_UNCOMPRESS; COUNT(*) ---------- 155607456 Elapsed: 00:00:00.54 11:31:24 SQL> select count(*) from LUNAR_COMPRESS; COUNT(*) ---------- 155680188 Elapsed: 00:00:00.18 11:31:30 SQL> SQL> select table_name, compression, compress_for FROM user_tables where table_name like '%COMPRESS%'; TABLE_NAME COMPRESS COMPRESS_FOR ------------------------------ -------- ------------ LUNAR_UNCOMPRESS DISABLED LUNAR_COMPRESS ENABLED QUERY HIGH Elapsed: 00:00:00.07 SQL>
缓存对象到flash cache的语法如下（类似这样对象的IO，我们都称之为 ”Smart Scan I/Os” ）：
--对于分区对象，可以缓存部分或者全部分表数据： CREATE TABLE lunar_par_tab (c1 number, c2 clob) TABLESPACE TBS_1 PARTITION BY RANGE(c1) ( PARTITION p1 VALUES LESS THAN (100) TABLESPACE TBS_2 STORAGE (CELL_FLASH_CACHE DEFAULT), PARTITION p2 VALUES LESS THAN (200) TABLESPACE TBS_3 STORAGE (CELL_FLASH_CACHE KEEP)); --对于lob对象，可以根据应用访问等具体情况确定是否需要缓存： CREATE TABLE lunar_lob_test (col1 number, col_lob clob) lob (col_lob) STORE AS securefile (cache nologging STORAGE (CELL_FLASH_CACHE NONE)) PCTFREE 0 TABLESPACE tbs_93 STORAGE (initial 128K next 128K pctincrease 0);
取消对象在flash cache的缓存：
alter TABLE LUNAR_TEST_PAR STORAGE (CELL_FLASH_CACHE none); alter INDEX IDX_LUNAR_TEST_PAR STORAGE (CELL_FLASH_CACHE none);
一般在POC或者生产上，我们会按照一定的条件（比如过滤掉超大的表或者分区等等）生成符合条件的表或者索引的keep 命令：
例如，使用下面的语句，将生成满足条件的表的缓存语句：
select 'alter table '||segment_name||' STORAGE (CELL_FLASH_CACHE keep);'
from dba_segments
where owner='LUNAR'
and segment_type in ('TABLE')
-- and TABLESPACE_NAME='DATA_DYNAMIC'
and segment_name not in ('TEST_TAB1','TEST_TAB2')
--group by owner||'.'||segment_name,segment_type
--having sum(bytes) > 1*1024*1024*1024
--order by sum(bytes)/1024/1024/1024;
alter table MM_LUNAR_TEMP_TEST STORAGE (CELL_FLASH_CACHE keep);
alter table MM_LUNAR_TEMP_TEST STORAGE (CELL_FLASH_CACHE keep);
…………
select 'alter INDEX '||segment_name||' STORAGE (CELL_FLASH_CACHE keep);'
from dba_segments
where owner='LUNAR'
and segment_type in ('INDEX')
-- and TABLESPACE_NAME='DATA_DYNAMIC'
--and segment_name not in ('TEST_TAB1','TEST_TAB2')
--group by owner||'.'||segment_name,segment_type
--having sum(bytes) > 1*1024*1024*1024
--order by sum(bytes)/1024/1024/1024;
alter INDEX IDX_LUNAR_TEST_PAR STORAGE (CELL_FLASH_CACHE keep);
alter INDEX IDX_LUNAR_TEST STORAGE (CELL_FLASH_CACHE keep);
…………
对于已经创建的对象，要修改其CELL_FLASH_CACHE属性，可以使用如下命令：
ALTER TABLE lunar_lob_test STORAGE( CELL_FLASH_CACHE DEFAULT); ALTER TABLE lunar_lob_test MODIFY LOB (col_lob) (STORAGE (CELL_FLASH_CACHE KEEP));
要知道当前对象的设置，可以查询dba_tables(all_tables, user_tables)或者dba_indexes(all_indexes, user_indexes)的CELL_FLASH_CACHE列：
SELECT TABLESPACE_NAME, TABLE_NAME, CELL_FLASH_CACHE FROM user_tables WHERE table_name='LUNAR_LOB_TEST'; SELECT CELL_FLASH_CACHE FROM ALL_INDEXES WHERE index_name='IDX_LUNAR_LOB_TEST';
还可以在cell上使用cellcli工具和命令” LIST FLASHCACHECONTENT”查看：
--(注意，cell的版本是11.2.3.2.1，是上周刚刚升级上来的，o(∩_∩)o 哈哈)
[root@dm02cel09 ~]# cellcli
CellCLI: Release 11.2.3.2.1 - Production on Tue May 28 11:24:29 CST 2013
Copyright (c) 2007, 2012, Oracle. All rights reserved.
Cell Efficiency Ratio: 416
CellCLI>
CellCLI> LIST FLASHCACHECONTENT WHERE objectNumber= 112912 DETAIL
--这说明该OBJECT_ID=112912对象没有被keep在flash cache上。
CellCLI> LIST FLASHCACHECONTENT WHERE objectNumber= 112913 DETAIL
cachedKeepSize: 0
cachedSize: 34340864
dbID: 722038086
dbUniqueName: DB06
hitCount: 652
missCount: 19377
objectNumber: 112913
tableSpaceNumber: 7
CellCLI>
我们注意到这里cachedKeepSize=0， 表示这个表曾经被cache了，后来执行了类似“alter table XXX STORAGE (CELL_FLASH_CACHE none);”的命令，取消的cache到flash cache的操作。
而上面的objectNumber= 112912没有任何输出，表示这个表没有被缓存过。
通常，POC或者生产上，我们更多的是生成批量查看对象缓存内容的语句：
11:23:17 SQL> select 'LIST FLASHCACHECONTENT WHERE objectNumber= '||b.object_id||' DETAIL'
11:23:18 2 from dba_segments a, dba_objects b
11:23:18 3 where a.owner='LUNAR'
11:23:18 4 and a.segment_name = b.OBJECT_NAME
11:23:18 5 and a.segment_type in ('TABLE')
11:23:18 6 and a.segment_name in ('LUNAR_UNCOMPRESS','LUNAR_COMPRESS')
11:23:18 7 /
'LISTFLASHCACHECONTENTWHEREOBJECTNUMBER='||B.OBJECT_ID||'DETAIL'
-------------------------------------------------------------------------------------------
LIST FLASHCACHECONTENT WHERE objectNumber= 112912 DETAIL
LIST FLASHCACHECONTENT WHERE objectNumber= 112913 DETAIL
具体我们看一下” LIST FLASHCACHE DETAIL”和”“ LIST FLASHCACHECONTENT”的官方说明：
CellCLI> DESCRIBE FLASHCACHE
name
cellDisk
creationTime
degradedCelldisks
effectiveCacheSize
id
size
status
CellCLI>
具体含义如下：
例如：
CellCLI> LIST FLASHCACHE DETAIL
name: dm02cel09_FLASHCACHE
cellDisk: FD_08_dm02cel09,FD_01_dm02cel09,FD_06_dm02cel09,FD_11_dm02cel09,
FD_00_dm02cel09,FD_12_dm02cel09,FD_05_dm02cel09,FD_07_dm02cel09,
FD_15_dm02cel09,FD_04_dm02cel09,FD_02_dm02cel09,FD_09_dm02cel09,
FD_13_dm02cel09,FD_10_dm02cel09,FD_14_dm02cel09,FD_03_dm02cel09
creationTime: 2012-08-10T16:03:25+08:00
degradedCelldisks: 需要关注这里是否有异常信息…………
effectiveCacheSize: 364.75G
id: 26514ed2-fa29-4b05-91b7-835f6180d6f3
size: 364.75G
status: normal
CellCLI>
查看Flash cache的内容：
CellCLI> DESCRIBE FLASHCACHECONTENT
cachedKeepSize
cachedSize
dbID
dbUniqueName
hitCount
hoursToExpiration
missCount
objectNumber
tableSpaceNumber
CellCLI>
例如：
CellCLI> LIST FLASHCACHECONTENT WHERE objectNumber= 112913 DETAIL
cachedKeepSize: 1315151872 被cache的对象大小为1.3G
cachedSize: 1315258368 该对象被用keep模式缓存的大小
dbID: 722038086
dbUniqueName: DB06
hitCount: 4541
hoursToExpiration: 24
missCount: 20676
objectNumber: 112913
tableSpaceNumber: 7
CellCLI>
CellCLI> LIST FLASHCACHECONTENT WHERE objectNumber= 112912 DETAIL
cachedKeepSize: 95485952
cachedSize: 95485952
dbID: 722038086
dbUniqueName: DB06
hitCount: 245
hoursToExpiration: 24
missCount: 545
objectNumber: 112912
tableSpaceNumber: 7
CellCLI>
当使用” STORAGE (CELL_FLASH_CACHE none)”子句取消对象在flash cache的缓存时：
SQL> ALTER table LUNAR_UNCOMPRESS STORAGE (CELL_FLASH_CACHE none); Table altered. Elapsed: 00:00:00.01 SQL> ALTER table LUNAR_COMPRESS STORAGE (CELL_FLASH_CACHE none); Table altered. Elapsed: 00:00:00.01 SQL>
可以看到cachedKeepSize的值立刻变成“0”：
CellCLI> LIST FLASHCACHECONTENT WHERE objectNumber= 112912 DETAIL
cachedKeepSize: 0
cachedSize: 95485952
dbID: 722038086
dbUniqueName: DB06
hitCount: 245
missCount: 545
objectNumber: 112912
tableSpaceNumber: 7
CellCLI> LIST FLASHCACHECONTENT WHERE objectNumber= 112913 DETAIL
cachedKeepSize: 0
cachedSize: 1315258368
dbID: 722038086
dbUniqueName: DB06
hitCount: 4541
missCount: 20676
objectNumber: 112913
tableSpaceNumber: 7
CellCLI>
看下这两个表的数据量，都在1.5亿行左右，没有索引情况下，count(*)的时间如下：
（当然，我这里测试不够严谨，应该每次执行前先执行清空buffer pool等）
SQL> select count(*) from LUNAR_COMPRESS; COUNT(*) ---------- 155680188 Elapsed: 00:00:23.67 SQL> SQL> select count(*) from LUNAR_UNCOMPRESS; COUNT(*) ---------- 155607456 Elapsed: 00:00:01.34 SQL>
修改为keep，再查看下：
SQL> ALTER table LUNAR_UNCOMPRESS STORAGE (CELL_FLASH_CACHE keep);
Table altered.
Elapsed: 00:00:00.07
SQL> ALTER table LUNAR_COMPRESS STORAGE (CELL_FLASH_CACHE keep);
Table altered.
Elapsed: 00:00:00.01
SQL>
--因为还没有访问，因此，这里cachedKeepSize依然是0：
CellCLI> LIST FLASHCACHECONTENT WHERE objectNumber= 112912 DETAIL
cachedKeepSize: 0
cachedSize: 95485952
dbID: 722038086
dbUniqueName: DB06
hitCount: 245
missCount: 1174
objectNumber: 112912
tableSpaceNumber: 7
CellCLI> LIST FLASHCACHECONTENT WHERE objectNumber= 112913 DETAIL
cachedKeepSize: 0
cachedSize: 1315258368
dbID: 722038086
dbUniqueName: DB06
hitCount: 4541
missCount: 24459
objectNumber: 112913
tableSpaceNumber: 7
CellCLI>
修改下会话的parallel选项（不指定具体的并行度，系统将使用缺省并行度），准备插入1000w行数据，测试下速度：
SQL> alter session force parallel query; Session altered. Elapsed: 00:00:00.00 SQL> alter session force parallel dml; Session altered. Elapsed: 00:00:00.00 SQL> alter session force parallel ddl; Session altered. Elapsed: 00:00:00.00 SQL>
插入1000w行数据，看下时间：
SQL> insert /*+ append parallel */ into LUNAR_COMPRESS 2 select /*+ parallel */ * from LUNAR_COMPRESS where rownum<=10000000; 3 10000000 rows created. Elapsed: 00:00:31.70 SQL> SQL> SQL> commit; Commit complete. Elapsed: 00:00:00.06 SQL> SQL> insert /*+ append parallel */ into LUNAR_UNCOMPRESS 2 select /*+ parallel */ * from LUNAR_UNCOMPRESS 3 where rownum<=10000000; 10000000 rows created. Elapsed: 00:00:27.84 SQL> SQL> commit; Commit complete. Elapsed: 00:00:00.08 SQL>
查看下对象大小：
SQL> col segment_name for a30 SQL> select SEGMENT_NAME,SUM(BYTES)/1024/1024/1024 GB 2 FROM USER_EXTENTS 3 WHERE sEGMENT_nAME LIKE '%COMPRESS%' 4 GROUP BY SEGMENT_NAME; SEGMENT_NAME GB ------------------------------ ---------- LUNAR_COMPRESS 1.45855713 LUNAR_UNCOMPRESS 19.4857178 Elapsed: 00:00:19.21 SQL>
可以查看下现在对象的cache情况：
可见，这时对象已经
CellCLI> LIST FLASHCACHECONTENT WHERE objectNumber= 112912 DETAIL
cachedKeepSize: 103546880
cachedSize: 103546880
dbID: 722038086
dbUniqueName: DB06
hitCount: 553
hoursToExpiration: 24
missCount: 1226
objectNumber: 112912
tableSpaceNumber: 7
CellCLI> LIST FLASHCACHECONTENT WHERE objectNumber= 112913 DETAIL
cachedKeepSize: 1495015424
cachedSize: 1496629248
dbID: 722038086
dbUniqueName: DB06
hitCount: 6234
hoursToExpiration: 24
missCount: 25126
objectNumber: 112913
tableSpaceNumber: 7
CellCLI>
SQL> select count(*) from LUNAR_COMPRESS;
COUNT(*)
----------
175680188
Elapsed: 00:00:00.27
SQL> select count(*) from LUNAR_UNCOMPRESS;
COUNT(*)
----------
175607456
Elapsed: 00:00:00.62
SQL>
Direct path insert缺省是不会缓存到Flash cache的，因此WriteBack和WriteThough模式的性能无差别。
未完，待续……
下一节，看看flash cache的监控和阀值…………
