XtremCache for Oracle

Jeff,

I really like your answer and reference to Bill Gaynor's blog. Here is an interesting thought: What if by day the database is weighted towards reads but in the evenings data loads occur and a shift to database writes take place. I believe the miTrend Workload Profile Assessment still applies, correct?

The more interesting question is do you turn off XtremCache during periods of heavy writes? Perhaps you can see where I

m going with this situation as XtremCache is not providing the same performance value during periods of heavy reads. Certainly, would be an interesting case study. So automate turning XtremCache off and in the morning flush the cache and restart XtremCache.

What do you think? 

Saty:

Yes, it is relatively easy to tell what the read / write profile for an Oracle database is over time. My particular favorite tool for this is called miTrend Workload Profile Assessment (WPA). Bill Gaynor has done a great job on putting the miTrend portal together. Basically, you upload a set of AWR reports to the portal, and then you can receive a PPT which will give you detailed stats on your database's read / write profile, sequential vs. random, backend storage stats, etc.

Regards,

Jeff

Good discussion and the practical question raised.  The best practices for this situation is very important.

Like it was said before, it's very easy to estimate read/write ratio of the database. AWR is one of the tools, but you could simplify the job and use pure OS statistics in such case. The question to ask should be rather if the workload is OLTP or OLAP and what is the locality of data -- if database reuses the same data that it recently used and if it fits within flash capacity.

> do you turn off XtremCache during periods of heavy writes?

Sam, why would one want to turn it off? Does it degrade write performance at all?

Maciek

Maciek,

In response to your comment "The question to ask should be rather if the workload is OLTP or OLAP and what is the locality of data." In fact these are aspects that the Mitrend Oracle WPA addresses and it does it in at least several ways. First we estimate the percentage of single-block reads and writes, where block here is Oracle default data block size (2, 4, 8, 16 or 32KB) dependent on what was specified at database creation or if some non-default block size tablespaces are in use. Then we estimate the size of the multi-block reads and writes. This analysis is available for versions 10gR2 or greater of Oracle. We are obviously trying to understand if the database is more OLTP-like or OLAP in nature -- single-block random or multi-block sequential. We also via the WPA get at locality of reference by ranking the tablespaces by their read/write activity over the sample AWR or Statspack reports supplied. This is a little crude, but gives some idea of locality of data reference. Often it is just a few tablespaces that are the most active. Plus of course we include an estimate of the read/write ratio and any existing IO-related latencies.

bg

Maciek,

Let me state that there is no recommendation around turning off XtremCache when write workloads dominate database activity. So in answering your question (with absolutely no evidence) I was thinking that the XtremCache software uses CPU cycles in monitoring I/O activity. Those CPU cycles "might" be better spent towards supporting heavy database writes if XtremCache isn't significantely utilized. So hence the idea of turning it off during heavy write related database activity.

Jeff,

Thanks for the Mitrend plug and the kind remarks. See my comment above.

bg