2群基础群 适合刚入门的同学，会共享最佳入门实践和资料 QQ群 # QQ群号:171092051 # 已经升级到 500人的超级群，空位多多，无需面试
Exadata测试CELL_FLASH_CACHE KEEP性能
Exadata测试CELL_FLASH_CACHE KEEP SMART Flash Cache性能
Segments by Optimized Reads
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$sysstat a , 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$sysstat a , 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$sysstat a , 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 |
- 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 |
posted on 2013-03-19 00:47 Oracle和MySQL 阅读(426) 评论(0) 编辑 收藏 举报
