cell flash cache 的使用

可以cache在cell flash cache的对象类型（表 索引 分区 lob字段）

SQL> alter table t1 modify lob(blob1) (storage (flash_cache keep));

Table altered.

貌似lob的修改不起作用

SQL> alter table t1 storage(cell_flash_cache none);

Table altered.

SQL> alter index t1_owner storage (cell_flash_cache keep);

Index altered.

SQL> select segment_name,cell_flash_cache from dba_segments where segment_name in ('SYS_LOB0000073938C00028$$','T1','T1_OWNER');

SEGMENT_NAME						     CELL_FL
------------------------------------------------------------ -------
SYS_LOB0000073938C00028$$				     DEFAULT
T1							     NONE
T1_OWNER						     KEEP

Exadata测试CELL_FLASH_CACHE KEEP SMART Flash Cache性能
imageinfo

Kernel version: 2.6.18-274.18.1.0.1.el5 #1 SMP Thu Feb 9 19:07:16 EST 2012 x86_64
Cell version: OSS_11.2.3.1.1_LINUX.X64_120607
Cell rpm version: cell-11.2.3.1.1_LINUX.X64_120607-1

Active image version: 11.2.3.1.1.120607
Active image activated: 2012-08-13 18:00:09 -0400
Active image status: success
Active system partition on device: /dev/md6
Active software partition on device: /dev/md8

In partition rollback: Impossible

Cell boot usb partition: /dev/sdm1
Cell boot usb version: 11.2.3.1.1.120607

Inactive image version: 11.2.2.4.2.111221
Inactive image activated: 2012-08-09 15:36:25 -0400
Inactive image status: success
Inactive system partition on device: /dev/md5
Inactive software partition on device: /dev/md7

Boot area has rollback archive for the version: 11.2.2.4.2.111221
Rollback to the inactive partitions: Possible

CellCLI> list flashcache detail
name: dm01cel01_FLASHCACHE
cellDisk: FD_15_dm01cel01,FD_11_dm01cel01,FD_09_dm01cel01,FD_14_dm01cel01,FD_00_dm01cel01,FD_12_dm01cel01,FD_03_dm01cel01,FD_01_dm01cel01,FD_13_dm01cel01,FD_07_dm01cel01,FD_04_dm01cel01,FD_08_dm01cel01,FD_05_dm01cel01,FD_10_dm01cel01,FD_02_dm01cel01,FD_06_dm01cel01
creationTime: 2012-08-13T17:58:02-04:00
degradedCelldisks:
effectiveCacheSize: 365.25G
id: f7118853-fd8d-4df4-917e-738c093530a7
size: 365.25G
status: normal

CellCLI> LIST METRICCURRENT WHERE objecttype=‘FLASHCACHE’;
FC_BYKEEP_OVERWR FLASHCACHE 0.000 MB
FC_BYKEEP_OVERWR_SEC FLASHCACHE 0.000 MB/sec
FC_BYKEEP_USED FLASHCACHE 8,350 MB
FC_BY_USED FLASHCACHE 8,518 MB
FC_IO_BYKEEP_R FLASHCACHE 8,328 MB
FC_IO_BYKEEP_R_SEC FLASHCACHE 0.000 MB/sec
FC_IO_BYKEEP_W FLASHCACHE 8,201 MB
FC_IO_BYKEEP_W_SEC FLASHCACHE 0.000 MB/sec
FC_IO_BY_R FLASHCACHE 8,700 MB
FC_IO_BY_R_MISS FLASHCACHE 8,704 MB
FC_IO_BY_R_MISS_SEC FLASHCACHE 0.000 MB/sec
FC_IO_BY_R_SEC FLASHCACHE 0.000 MB/sec
FC_IO_BY_R_SKIP FLASHCACHE 69,824 MB
FC_IO_BY_R_SKIP_SEC FLASHCACHE 0.001 MB/sec
FC_IO_BY_W FLASHCACHE 9,783 MB
FC_IO_BY_W_SEC FLASHCACHE 0.000 MB/sec
FC_IO_ERRS FLASHCACHE 0
FC_IO_RQKEEP_R FLASHCACHE 8,340 IO requests
FC_IO_RQKEEP_R_MISS FLASHCACHE 8,340 IO requests
FC_IO_RQKEEP_R_MISS_SEC FLASHCACHE 0.0 IO/sec
FC_IO_RQKEEP_R_SEC FLASHCACHE 0.0 IO/sec
FC_IO_RQKEEP_R_SKIP FLASHCACHE 15 IO requests
FC_IO_RQKEEP_R_SKIP_SEC FLASHCACHE 0.0 IO/sec
FC_IO_RQKEEP_W FLASHCACHE 8,343 IO requests
FC_IO_RQKEEP_W_SEC FLASHCACHE 0.0 IO/sec
FC_IO_RQ_R FLASHCACHE 38,219 IO requests
FC_IO_RQ_R_MISS FLASHCACHE 19,694 IO requests
FC_IO_RQ_R_MISS_SEC FLASHCACHE 0.0 IO/sec
FC_IO_RQ_R_SEC FLASHCACHE 0.0 IO/sec
FC_IO_RQ_R_SKIP FLASHCACHE 246,344 IO requests
FC_IO_RQ_R_SKIP_SEC FLASHCACHE 0.1 IO/sec
FC_IO_RQ_W FLASHCACHE 137,932 IO requests
FC_IO_RQ_W_SEC FLASHCACHE 0.0 IO/sec

列出度量定义

CellCLI> LIST METRICDEFINITION FC_BY_USED DETAIL
name: FC_BY_USED
description: “Number of megabytes used on FlashCache”
metricType: Instantaneous
objectType: FLASHCACHE
unit: MB

SQL> alter table larget storage (cell_flash_cache keep);

Table altered.

SQL>
SQL> select a.name,b.value
2 from v$sysstata,v$mystat b
3 where
a.statistic#=b.statistic#
and (a.name in (‘physical read total bytes’,‘physical write total bytes’,
‘cell IO uncompressed bytes’) or a.name like ‘cell phy%’
or a.name like ‘%flash cache read hits’); 4 5 6 7

NAME VALUE

---

physical read total bytes 114688
physical write total bytes 0
cell physical IO interconnect bytes 114688
cell physical IO bytes pushed back due to excessive CPU on cell 0
cell physical IO bytes saved during optimized file creation 0
cell physical IO bytes saved during optimized RMAN file restore 0
cell physical IO bytes eligible for predicate offload 0
cell physical IO bytes saved by storage index 0
cell physical IO interconnect bytes returned by smart scan 0
cell IO uncompressed bytes 0
cell flash cache read hits 0

11 rows selected.

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from larget;

COUNT(*)

242778112

SQL> set timing on;
SQL> select a.name,b.value
2 from v$sysstata,v$mystat b
3 where
a.statistic#=b.statistic#
and (a.name in (‘physical read total bytes’,‘physical write total bytes’,
‘cell IO uncompressed bytes’) or a.name like ‘cell phy%’
or a.name like ‘%flash cache read hits’); 4 5 6 7

NAME VALUE

---

physical read total bytes 2.6262E+10
physical write total bytes 0
cell physical IO interconnect bytes 3018270928
cell physical IO bytes pushed back due to excessive CPU on cell 0
cell physical IO bytes saved during optimized file creation 0
cell physical IO bytes saved during optimized RMAN file restore 0
cell physical IO bytes eligible for predicate offload 2.6262E+10
cell physical IO bytes saved by storage index 0
cell physical IO interconnect bytes returned by smart scan 3018090704
cell IO uncompressed bytes 2.6284E+10
cell flash cache read hits 55

11 rows selected.

Elapsed: 00:00:00.01
SQL> select count(*) from larget;

COUNT(*)

242778112

Elapsed: 00:00:06.83
SQL> select a.name,b.value
2 from v$sysstata,v$mystat b
3 where
a.statistic#=b.statistic#
and (a.name in (‘physical read total bytes’,‘physical write total bytes’,
‘cell IO uncompressed bytes’) or a.name like ‘cell phy%’
or a.name like ‘%flash cache read hits’); 4 5 6 7

NAME VALUE

---

physical read total bytes 5.2525E+10
physical write total bytes 0
cell physical IO interconnect bytes 6036394312
cell physical IO bytes pushed back due to excessive CPU on cell 0
cell physical IO bytes saved during optimized file creation 0
cell physical IO bytes saved during optimized RMAN file restore 0
cell physical IO bytes eligible for predicate offload 5.2524E+10
cell physical IO bytes saved by storage index 0
cell physical IO interconnect bytes returned by smart scan 6036214088
cell IO uncompressed bytes 5.2570E+10
cell flash cache read hits 27999

11 rows selected.

Elapsed: 00:00:00.00

cell server IO calibrate

ellCLI> calibrate force;
Calibration will take a few minutes…
Aggregate random read throughput across all hard disk LUNs: 1936 MBPS
Aggregate random read throughput across all flash disk LUNs: 4148.56 MBPS
Aggregate random read IOs per second (IOPS) across all hard disk LUNs: 4906
Aggregate random read IOs per second (IOPS) across all flash disk LUNs: 142303
Controller read throughput: 1939.98 MBPS
Calibrating hard disks (read only) …
LUN 0_0 on drive [28:0 ] random read throughput: 168.39 MBPS, and 419 IOPS
LUN 0_1 on drive [28:1 ] random read throughput: 165.32 MBPS, and 412 IOPS
LUN 0_10 on drive [28:10 ] random read throughput: 170.72 MBPS, and 421 IOPS
LUN 0_11 on drive [28:11 ] random read throughput: 169.51 MBPS, and 412 IOPS
LUN 0_2 on drive [28:2 ] random read throughput: 171.15 MBPS, and 421 IOPS
LUN 0_3 on drive [28:3 ] random read throughput: 170.58 MBPS, and 413 IOPS
LUN 0_4 on drive [28:4 ] random read throughput: 166.37 MBPS, and 413 IOPS
LUN 0_5 on drive [28:5 ] random read throughput: 167.69 MBPS, and 424 IOPS
LUN 0_6 on drive [28:6 ] random read throughput: 171.89 MBPS, and 427 IOPS
LUN 0_7 on drive [28:7 ] random read throughput: 167.78 MBPS, and 425 IOPS
LUN 0_8 on drive [28:8 ] random read throughput: 170.74 MBPS, and 423 IOPS
LUN 0_9 on drive [28:9 ] random read throughput: 168.56 MBPS, and 420 IOPS
Calibrating flash disks (read only, note that writes will be significantly slower) …
LUN 1_0 on drive [FLASH_1_0] random read throughput: 272.06 MBPS, and 19867 IOPS
LUN 1_1 on drive [FLASH_1_1] random read throughput: 272.06 MBPS, and 19892 IOPS
LUN 1_2 on drive [FLASH_1_2] random read throughput: 271.68 MBPS, and 19869 IOPS
LUN 1_3 on drive [FLASH_1_3] random read throughput: 272.40 MBPS, and 19875 IOPS
LUN 2_0 on drive [FLASH_2_0] random read throughput: 272.54 MBPS, and 20650 IOPS
LUN 2_1 on drive [FLASH_2_1] random read throughput: 272.67 MBPS, and 20683 IOPS
LUN 2_2 on drive [FLASH_2_2] random read throughput: 271.98 MBPS, and 20693 IOPS
LUN 2_3 on drive [FLASH_2_3] random read throughput: 272.48 MBPS, and 20683 IOPS
LUN 4_0 on drive [FLASH_4_0] random read throughput: 271.85 MBPS, and 19932 IOPS
LUN 4_1 on drive [FLASH_4_1] random read throughput: 272.22 MBPS, and 19924 IOPS
LUN 4_2 on drive [FLASH_4_2] random read throughput: 272.38 MBPS, and 19908 IOPS
LUN 4_3 on drive [FLASH_4_3] random read throughput: 271.73 MBPS, and 19901 IOPS
LUN 5_0 on drive [FLASH_5_0] random read throughput: 271.61 MBPS, and 19906 IOPS
LUN 5_1 on drive [FLASH_5_1] random read throughput: 271.39 MBPS, and 19897 IOPS
LUN 5_2 on drive [FLASH_5_2] random read throughput: 270.85 MBPS, and 19901 IOPS
LUN 5_3 on drive [FLASH_5_3] random read throughput: 270.99 MBPS, and 19884 IOPS
CALIBRATE results are within an acceptable range.
Calibration has finished.

SQL> Select data_object_id from dba_objects where object_name=‘LARGET’;

DATA_OBJECT_ID

     17425

SELECT statistic_name, value
FROM V$SEGMENT_STATISTICS
WHERE dataobj#= 17425 AND ts#=7 AND
statistic_name=‘optimized physical reads’;

STATISTIC_NAME VALUE

---

optimized physical reads 43687

CellCLI> LIST FLASHCACHECONTENT where objectnumber=17425 detail
cachedKeepSize: 8755838976
cachedSize: 8757706752
dbID: 2080757153
dbUniqueName: DBM
hitCount: 12940
hoursToExpiration: 23
missCount: 78488
objectNumber: 17425
tableSpaceNumber: 7

  V$SYSSTAT视图中累计性地记录了从flash cache中获益的I/O request数目，这些累计数目来自于所有的CELL存储服务器， 相关的统计名字叫做'cell flash cache read hits'，相似的统计信息在V$SESSTAT和V$MYSTAT中都有。 另一个统计值‘physical read requests optimized’ 反映了Exadata storage index与cell flash cache一起获益的磁盘IO数目。 在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"  

Segments by UnOptimized Reads

Total UnOptimized Read Requests: 66,587
Captured Segments account for 86.9% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type UnOptimized Reads %Total
SYS SYSTEM AUD$ TABLE 38,376 57.63
PIN PIN02 PURCHASED_PRODUCT_T TABLE 5,149 7.73
PIN PINX02 I_PURCHASED_PRODUCT__ID INDEX 3,617 5.43
PIN PIN00 IDX_TRANS_LOG_MSISDN INDEX 2,471 3.71
PIN PIN02 BILLLOG_T P_R_02292012 TABLE PARTITION 1,227 1.84
Segments by Optimized Reads
Total Optimized Read Requests: 207,547
Captured Segments account for 88.9% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Optimized Reads %Total
SYS SYSTEM AUD$ TABLE 92,198 44.42
PIN PIN02 PURCHASED_PRODUCT_T TABLE 23,142 11.15
PIN PINX02 I_PURCHASED_PRODUCT__ID INDEX 10,781 5.19
PIN PIN00 IDX_TRANS_LOG_MSISDN INDEX 9,354 4.51
PIN PIN02 SERVICE_T TABLE 7,818 3.77

参考了
https://blog.csdn.net/weixin_34226706/article/details/85545789