Hi Dba's I was working on Database flash cache and found it very interesting feature of 11G so thought to share with you as well. well database flash cache works as l2 level of cache for database and is very useful when memory become a bottleneck in oltp-kinda database by these feature we can configure a flash drive(either cheap as pen drive or may be expensive high quality flash drive) to store clean buffer that aged out from db_buffer cache
NOTE:-Database flash cache feature can only be configured in ORALCE ENTERPRISES LINUX or SOLARIS.
TO be able to use database flash cache you should follow these steps.
1.login to database and shutdown the system in normal mode.
sql>shutdown immediate
2. Install Patch 9352237 by using opatch utility
[oracle@node1 9352237]$/u01/app/oracle/product/11.2.0/db_home/Opatch/opatch apply.
3.Install patch 8974084 which allows you to use the database flash cache on Oracle Enterprise Linux even if you don't have exadata storage. The patch is named:
§ 8974084:META BUG FOR FLASH CACHE 11.2PL BUGS TO BACKPORT TO 11.2.0.1 OEL
Once you install the patch you can use any old flash device as a database flash cache if you just want to experiment using an USB flash device, then you first need to get that device mounted. On my test machine I created a directory "/mnt/usbflash" then created an /etc/fstab entry like this:
/dev/sda1 /mnt/usbflash vfat noauto,users,rw,umask=0 0 0
On your system you might need to change "/dev/sda1" to another device depending on how your fixed disks are configured. You should then be able to mount the flashdrive by typiing "mount /dev/sda1". Make sure that the mount point is writable by oracle (chmod 777 /mnt/usbflash).
Once mounted, you configure the flash cache by setting the parameters DB_FLASH_CACHE_FILE and DB_FLASH_CACHE_SIZE
alter system set db_flash_cache_size='/mnt/usbflash/oraflashcache.dat;'
alter system set db_flash_cache_size=1000M;
Note that the value of DB_FLASH_CACHE_FILE needs to be a file on the flash drive, not the flash drive mount point itself.
Once these parameters are set, the flash cache will be enabled and will act as a secondary cache to the buffer cache. When a block is removed from the primary cache, it will still exist in the flash cache, and can be read back without a physical read to the spinning disk.
Monitoring
There's a few ways to examine how the flash cache is being used. Firstly, V$SYSSTAT contains some new statistics showing the number of blocks added to the cache and the number of "hits" that were satisfied from cache:
SELECT name,value FROM v$sysstat WHERE name IN ('physical read flash cache hits',
'physical reads', 'consistent gets', 'db block gets', 'flash cache inserts')
Although the flash reads save time when compared to db file sequential reads, the overhead of maintaining the cache can be high because flash based SSD has a relatively severe write penalty.
All flash-based Solid State Disk have issues with write performance. However, cheap Multi Level Cell (MLC) flash take about 3 times as long to write as the more expensive Single Level Cell (SLC). When flash drives are new, the empty space can be written to in single page increments (usually 4KB). However, when the flash drive is older, writes typically require erasing a complete 128 page block which is very much slower. My cheap USB drive was old and MLC, so it had very poor write performance. But even the best flash based SSD is going to be much slower for writes than for reads, and in some cases using a flash cache might slow a database down as a result. So monitoring is important.
There's a couple of other V$SYSSTAT statistics of interest:
select name,value from v$sysstat where name like '%flash cache%'
To examine the contents of the cache, we can examine the V$BH view. Buffers in the flash cache have STATUS values such as 'flashcur', allowing us to count the buffers from each object in the main buffer cache and in the flash cache
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) GROUP BY owner, object_name order by 4 desc
Conclusion
We should be cautious however, because the flash write penalty may result in performance problems.
Related links:
HOME
Related links:
HOME
