How to display content attributes on Exadata Storage?

On Exadata, After We created flashcache on exadata storage. How to know it work!!! Because on database, we don't change anything.
On Database: we can check...

SQL> select name,value from v$sysstat where name in ('physical read total IO requests','cell flash cache read hits');

NAME VALUE
---------------------------------------------------------------- ----------
physical read total IO requests 17467680
cell flash cache read hits 108435

By the way, On Exadata Storage: We can use "LIST FLASHCACHECONTENT" command to display content attributes on Exadata Storage.

CellCLI> help list flashcachecontent

Usage: LIST FLASHCACHECONTENT [<filters>] [<attribute_list>] [DETAIL]

Purpose: Displays specified attributes for flash cache entries.

Arguments:
<filters>: An expression which determines the entries to be displayed.
<attribute_list>: The attributes that are to be displayed.
ATTRIBUTES {ALL | attr1 [, attr2]... }

Options:
[DETAIL]: Formats the display as an attribute on each line, with
an attribute descriptor preceding each value.

Examples:
LIST FLASHCACHECONTENT DETAIL

So, I would like to test some... about " LIST FLASHCACHECONTENT" command

CellCLI> list flashcachecontent detail
cachedKeepSize: 0
cachedSize: 131072
dbID: 2080757153
dbUniqueName: DB
hitCount: 0
missCount: 2
objectNumber: 8
tableSpaceNumber: 0

cachedKeepSize: 0
cachedSize: 65536
dbID: 2080757153
dbUniqueName: DB
hitCount: 0
missCount: 3
objectNumber: 29
tableSpaceNumber: 0

cachedKeepSize: 0
cachedSize: 229376
dbID: 2080757153
dbUniqueName: DB
hitCount: 6
missCount: 4
objectNumber: 36
tableSpaceNumber: 0

However, We can display specific object:
On Database:

SQL> alter table SALES storage (cell_flash_cache keep);

Table altered.

SQL> select * from sales;

SQL> select object_id from user_objects where object_name='SALES';

OBJECT_ID
----------
25076

On Storage: Check from objectNumber=25076

CellCLI> list flashcachecontent where objectnumber=25076 detail
cachedKeepSize: 153632768
cachedSize: 153632768
dbID: 2080757153
dbUniqueName: DB
hitCount: 9
hoursToExpiration: 24
missCount: 154
objectNumber: 25076
tableSpaceNumber: 673

What did we see? database name/tablespace number/object number/cached size/...

SQL> select name from v$database;

NAME
---------
DB

SQL> select name from ts$ where ts#=673;

NAME
------------------------------
EXA_TEST

What should we contain Online Redo Log Files in Flash Disk?

I asked about How to use Flash Disk (Exadata)? on oracle-l - I think this may have benefit for someone. However, We should know about Flash Disk benefits on Oracle Database. and I have some question: What should we contain Online Redo Log Files in Flash Disk?

Online redo log files record all changes made to Oracle Database data files and control file. When a change is made to the database, Oracle Database generates a redo record in the redo buffer. Log Writer (LGWR) writes the contents of the redo buffer to the online redo log. LGWR performs sequential writes of redo to disk.

That's a concept what we know, and LGWR can perform fast sequential writes of redo to disk, the database improves performance.

A solid-state drive (SSD) is a data storage device that uses solid-state memory to store persistent data with the intention of providing access in the same manner of a traditional block i/o hard disk drive. SSD has write speeds lower than its read speeds and sequential writes isn't better faster than Hard Disk.

So, I think It's not good idea to contain Online Redo Log Files in Flash Disk. How do you think?

Reference:
http://www.freelists.org/post/oracle-l/How-do-you-use-FlashDisk
http://www.emc.com/collateral/hardware/white-papers/h5967-leveraging-clariion-cx4-oracle-deploy-wp.pdf

What's Happening - if flash cache file corrupt

This is something, I wish to know. if flash cache file corrupt.
Question: What's happening - if flash cache file has the problem(corrupt)?

Flash Cache parameters.

SQL> show parameter db_flash_cache_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file string /dev/sdc

SQL> show parameter db_flash_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 20G

Number of database blocks in flash cache script.

SELECT
SUM (CASE WHEN b.status LIKE 'flash%' THEN 1 else 0 END) flash_blocks, SUM
(CASE WHEN b.status LIKE 'flash%' THEN 0 else 1 END) cache_blocks,
count(*) total_blocks
FROM v$bh b
/

- Check on flash cache file.

$ dd if=/dev/sdc count=1024 | strings
Oracle RDBMS Flash Cache File
orcl
1024+0 records in
1024+0 records out

- Check database blocks in flash cache.

FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------ ------------ ------------
1352 3417 4769

A. Test To Destroy Flash Cache File.

$ dd if=/dev/zero of=/dev/sdc bs=4k count=10000
10000+0 records in
10000+0 records out
40960000 bytes (41 MB) copied, 0.044147 seconds, 928 MB/s

$ dd if=/dev/sdc count=1024 | strings
1024+0 records in
1024+0 records out

Flash Cache File corrupt.

- Check Alert Log File.

Sun Feb 28 22:33:59 2010
Encounter problem verifying flash cache /dev/sdc. Disable flash cache and issue an ORA-700 for diagnostics
Errors in file /u01/app/diag/rdbms/orcl/orcl/trace/orcl_gen0_9755.trc (incident=71223):
ORA-00700: soft internal error, arguments: [kcbl2vfyfh_action], [db_flash_cache_file integrity check failed], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/diag/rdbms/orcl/orcl/incident/incdir_71223/orcl_gen0_9755_i71223.trc
Sun Feb 28 22:33:59 2010
L2 cache file closed by dbwr 0
L2 cache disabled for dbwr 0
Sun Feb 28 22:33:59 2010
Sweep [inc][71223]: completed
Sweep [inc2][71223]: completed

- Check database blocks in flash cache.

FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------ ------------ ------------
0 3412 3412

B. Test DML on table.

SQL> update tb_test01 set object_id=101;

- Check Alert Log File.

Nothing Error

- Check flash cache file.

$ dd if=/dev/sdc count=1024 | strings
1024+0 records in
1024+0 records out

- Check database blocks in flash cache.

FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------ ------------ ------------
0 3412 3412

The flash cache is disabled, So database could not use flash cache.

C. Solve this issue.

SQL> show parameter db_flash_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 20G

SQL> alter system set db_flash_cache_size=20G;

System altered.

SQL> show parameter db_flash_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 20G

- Check Alert Log File.

Sun Feb 28 22:36:47 2010
Dynamically re-enabling flash cache db_flash_cache_file (/dev/sdc) to size 21474836480
ALTER SYSTEM SET db_flash_cache_size='20G' SCOPE=BOTH;

- Check flash cache file

$ dd if=/dev/sdc count=1024 | strings
Oracle RDBMS Flash Cache File
orcl
1024+0 records in
1024+0 records out

- Check database blocks in flash cache

FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------ ------------ ------------
1877 3437 5314

Answer: The Flash Cache is disabled, If Flash Cache File has the problem.

How To Clear DB Blocks In Flash Cache

For each database block moved from the buffer cache to the flash cache. We check...

SELECT
SUM (CASE WHEN b.status LIKE 'flash%' THEN 1 else 0 END) flash_blocks, SUM
(CASE WHEN b.status LIKE 'flash%' THEN 0 else 1 END) cache_blocks,
count(*) total_blocks
FROM v$bh b
/

We'll see number of database blocks in flash cache.

FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------ ------------ ------------
3132 3427 6559

We see 3132 blocks in flash cache.
How to clear database blocks in flash cache?

- Disable Flash Cache

SQL> show parameter db_flash_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 20G

SQL> alter system set db_flash_cache_size=0;

System altered.

SQL> show parameter db_flash_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 0

Check

FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------ ------------ ------------
0 3427 3427

- Flush Buffer Cache

The FLUSH BUFFER_CACHE clause lets you clear all data from the buffer cache in the system global area (SGA), including the KEEP, RECYCLE, and DEFAULT buffer pools.

I'm curious with it, so test & check.

FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------ ------------ ------------
2404 3382 5786

Alter system...

SQL> alter system flush buffer_cache;

System altered.

Check

FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------ ------------ ------------
0 3382 3382

After, I flushed buffer_cache, I don't see database block in flash cache. However I think we should use Disable Flash Cache.
This is just fun for test system, don't flush buffer cache on production system.

Oracle 11gR2 Database Flash Cache

Posted to setup the Database Flash Cache, then start to Test.

SQL> show parameter db_flash_cache_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file string /dev/sdc

SQL> show parameter db_flash_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 10G

Good idea to use the flash cache, when "db file sequential read" is a top wait event.

The FLASH_CACHE clause lets you override the automatic buffer cache policy and specify how specific schema objects are cached in flash memory. To use this clause, Database Smart Flash Cache (flash cache) must be configured on your system. The flash cache is an extension of the database buffer cache that is stored on a flash disk, a storage device that uses flash memory. Because flash memory is faster than magnetic disks, the database can improve performance by caching buffers in the flash cache instead of reading from magnetic disk.

Example:

SQL> create table tb_test01 as select * from all_objects where rownum <=10;
Table created.

SQL> select table_name, flash_cache from user_tables where table_name='TB_TEST01';

TABLE_NAME FLASH_C
------------------------------ -------
TB_TEST01 DEFAULT

KEEP Specify KEEP if you want the schema object buffers to remain cached in the flash cache as long as the flash cache is large enough.

NONE Specify NONE to ensure that the schema object buffers are never cached in the flash cache. This allows you to reserve the flash cache space for more frequently accessed objects.

DEFAULT Specify DEFAULT if you want the schema object buffers to be written to the flash cache when they are aged out of main memory, and then be aged out of the flash cache with the standard buffer cache replacement algorithm. This is the default if flash cache is configured and you do not specify KEEP or NONE.

SQL> create table TB_TEST01 storage( flash_cache keep) as select * from all_objects;

Table created.

SQL> select table_name,flash_cache from user_tables;

TABLE_NAME FLASH_C
------------------------------ -------
TB_TEST01 KEEP

Check V$SYSSTAT

SQL> select * from v$sysstat where name like 'flash cache%';

STATISTIC# NAME CLASS VALUE STAT_ID
---------- ---------------------------------------- ---------- ---------- ----------
87 flash cache inserts 8 27933 2941771786
91 flash cache eviction: invalidated 8 22256 3647825193
92 flash cache eviction: buffer pinned 8 0 3134415242
93 flash cache eviction: aged out 8 0 3307935113
94 flash cache insert skip: not current 8 12 1693383402
95 flash cache insert skip: DBWR overloaded 8 0 3504558414
96 flash cache insert skip: exists 8 8150 3291155557
97 flash cache insert skip: not useful 8 3738 3620030478
98 flash cache insert skip: modification 8 195 4128442906
99 flash cache insert skip: corrupt 8 0 2560222967

Check number of blocks(segment) in the flash cache.

SQL> SELECT owner || '.' || object_name object,
SUM (CASE WHEN b.status LIKE 'flash%' THEN 1 END) flash_blocks,
SUM (CASE WHEN b.status LIKE 'flash%' THEN 0 else 1 END) cache_blocks,
count(*) total_blocks
FROM v$bh b
JOIN
dba_objects
ON (objd = object_id)
where owner='SURACHART' and object_name='TB_TEST01'
group by owner,object_name
/

OBJECT FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------------------------ ------------ ------------ ------------
SURACHART.TB_TEST01 67 1607 1674

Or number of blocks(all segments) in the flash cache

SQL> SELECT
SUM (CASE WHEN b.status LIKE 'flash%' THEN 1 END) flash_blocks, SUM (CASE WHEN b.status LIKE 'flash%' THEN 0 else 1 END) cache_blocks,
count(*) total_blocks
FROM v$bh b
/

FLASH_BLOCKS CACHE_BLOCKS TOTAL_BLOCKS
------------ ------------ ------------
5535 14281 19816

However, We can disable the flash cache by set db_flash_cache_size to zero.

SQL> show parameter db_flash_cache_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 10G

SQL> alter system set db_flash_cache_size=0;

System altered.

SQL> show parameter db_flash_cache_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 0

Reference:
Oracle Document 11gR2
Using the Oracle 11GR2 database flash cache
Pardon Me, Where Is That Flash Cache? Part I.
Pardon Me, Where Is That Flash Cache? Part II.

11gR2(Test) Flash Cache on Redhat

I'd like to test Flash Array , then I installed Oracle 11gR2 ( + patched 8974084) on RHEL 5.3 and want to test Flash Cache Feature.

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.3 (Tikanga)

I don't check much oracle document, just read about configuration.
Choose disk:

# fdisk -l /dev/sdc

Disk /dev/sdc: 24.5 GB, 24575868928 bytes
255 heads, 63 sectors/track, 2987 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdc doesn't contain a valid partition table
# chmod 777 /dev/sdc

set Oracle initialization parameters:

$ sqlplus / as sysdba

SQL> alter system set db_flash_cache_file='/dev/sdc' scope=spfile;

System altered.

SQL> alter system set db_flash_cache_size=10G scope=spfile;

System altered.

Stop/Start database

SQL> shutdown immediate

SQL> startup
ORA-00439: feature not enabled: Server Flash Cache
ORA-01078: failure in processing system parameters

I found above error, then asked ... and find out. I found (oracle docs):

Your database is running on the Solaris or Oracle Enterprise Linux operating systems. The flash cache is supported on these operating systems only.

So, I had plan to install OEL. But... I'd like to figure out on Redhat as well.

Thank You Kevin Closson for figure me out. He is helpful me. I can use flash cache feature on Redhat.

SQL> startup;
ORACLE instance started.

Total System Global Area 7549435904 bytes
Fixed Size 2214456 bytes
Variable Size 4026533320 bytes
Database Buffers 3489660928 bytes
Redo Buffers 31027200 bytes
Database mounted.
Database opened.

SQL> show parameter db_flash_cache_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file string /dev/sdc

SQL> show parameter db_flash_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_size big integer 10G

-)

Remark:
Using db_flash_cache_file =/dev/sdc, Check -> A device name is Flash Cache File, isn't it?

$ dd if=/dev/sdc count=1024 | strings
Oracle RDBMS Flash Cache File
orcl
1024+0 records in
1024+0 records out
524288 bytes (524 kB) copied, 0.008165 seconds, 64.2 MB/s

Just Idea.