Using Tier Zero

It would be wonderful if we could put our entire database on fast low latency storage. Imagine getting sub-one two hundred microsecond response on the entire database? Well, we may soon be able to, in fact some are doing it now. Unfortunately not everyone can afford to replace all of their SAN assets with SSD just yet so for many tiering of data storage provides the answer. The lower the tier number, the higher the performance. usually the lowest tier is tier zero although some have proposed a tier -1...

Just what is “Tier Zero Storage.” No doubt you have heard it applied to fast disks, Flash disks, and of course DDR based SSD storage systems. Tier Zero is the fastest, lowest latency level of storage reserved for the parts of your database or system architectures that need high speed access (low latency) to ensure the performance of the entire system.

Many systems suffer from IO contention which results in high run queues, low CPU usage, and high IO waits. Increasing bandwidth usually is not a solution if the minimum latency for the existing storage media has been reached.

Usually less than 10% of your database tables and indexes need the performance offered by Tier Zero type storage, but the trick is picking which 10%. Many times we are sure we know that since table X or index Y is the most important it must have to go on Tier Zero; however, in many cases this is just not true.

Typically the speed increases and latency decreases as you move from higher numbered tiers to lower numbered tiers up the latency pyramid as is shown in Figure 1.



Figure 1: Storage Latency Pyramid


What should go on Tier Zero?
The determination of the tables, indexes or other structures that should be placed on the often costly and low capacity (volume wise) Tier Zero storage can be a complex issue. There are three sources for analysis data:

1. Explain plans showing access patterns or SQL showing poor performance
2. Wait event analysis
3. Analysis of IO patterns of tablespaces

Of course if an object is small enough that it will be read once from storage and then reside in the SGA buffer space, placing it on Tier Zero is a waste of Tier Zero space.