When we were planning migration from commodity hardware to Exadata X5-2 one open topic we had was if it makes sense to enable write-back flash cache in Exadata.
The default mode is write-through where read operations utilize flash cache but not the write operations. Write-back then uses the flash cache to both read AND write where appropriate (Read more for example here or from Exadata Write-Back Flash Cache – FAQ Doc ID 1500257.1).
Before migration I had read Expert Oracle Exadata book which gives really solid basis for you with Exadata. Thanks for the writers!
In the book they mention that in some cases it is worth considering changing the default write-through to write-back and this is exactly what we did. We figured since the amount of production databases we were going to put on Exadata is low we probably will not utilize flash cache as much as possible so we could enable write-back flash cache.
Also one of the driving factors was that we expected e-Business Suite instances to be write-heavy so we could get some additional benefits due to this.
Problem was our EBS databases were still on 11.2.0.3! You can only see statistics for flash cache write usage from 11.2.0.4 and 12.1.0.2 forward so there was no good way of seeing instance specific statistics.
Last August we finally upgraded our EBS databases to 12.1.0.2. We have a distributed configuration for EBS which means there are two separate database instances for source (actual ERP instance) and destination (planning instance, VCP).
To see flash cache statistics for instance I used Tanel Poder’s sys script and followed his blog post and then ran the script on both instances.
ERP:
SQL> @sys cell%flash
old 1: select name, value sys_value from v$sysstat where lower(name) like lower('%&1%')
new 1: select name, value sys_value from v$sysstat where lower(name) like lower('%cell%flash%')
NAME VALUE
---------------------------------------------------------------- --------------------------
cell writes to flash cache 100498759
cell overwrites in flash cache 83292766
cell partial writes in flash cache 2966
cell flash cache read hits 4569897795
next day:
<strong> </strong>NAME VALUE ---------------------------------------------------------------- -------------------------- cell writes to flash cache 112267869 cell overwrites in flash cache 93351887 cell partial writes in flash cache 3360 cell flash cache read hits 5056009206
So initially looking on ERP side where most of the transactions are happening the ratio is around 1:45 with writes/reads utilizing flash cache. I’m looking on to “cell writes to flash cache” and “cell flash cache read hits” on this.
I was quite surprised by this because I thought the amount of cached writes would be lot higher on the ERP instance.
VCP:
VCP instance is where most of the batch jobs happen over night and it has Demantra instance included in it as well. So lot of read heavy operations. But!
SQL> @sys cell%flash
old 1: select name, value sys_value from v$sysstat where lower(name) like lower('%&1%')
new 1: select name, value sys_value from v$sysstat where lower(name) like lower('%cell%flash%')
NAME VALUE
---------------------------------------------------------------- --------------------------
cell writes to flash cache 377827994
cell overwrites in flash cache 270095454
cell partial writes in flash cache 8810
cell flash cache read hits 248136982
next day:
NAME VALUE ---------------------------------------------------------------- -------------------------- cell writes to flash cache 397415193 cell overwrites in flash cache 283811642 cell partial writes in flash cache 9265 cell flash cache read hits 258127575
Now the ratio is actually 1.5:1 and the opposite what was on the ERP side! So there are more requests for write operations compared to read operations although not so much.
This seems to be due to batch jobs writing heavily during night and weekends. When I checked the writes in the evening and in the morning the value had grown a lot compared to daytime activity when writes don’t grow so much.
Just for comparison our OBIEE database:
SQL> /
old 1: select name, value sys_value from v$sysstat where lower(name) like lower('%&1%')
new 1: select name, value sys_value from v$sysstat where lower(name) like lower('%cell%flash%')
NAME VALUE
---------------------------------------------------------------- --------------------------
cell writes to flash cache 1849558161
cell overwrites in flash cache 1221403212
cell partial writes in flash cache 96084
cell flash cache read hits 8682813632
To sum it up:
Even though I thought the write-back flash cache would be more used on ERP side it actually is used more on planning instance where there are more write heavy operations during the night. This is of course case for us but could be different for some other EBS customers.