What Do I Put on Tier Zero?

In the blog entry previous to this one we looked at tier zero usage in general. But how do we determine what should be placed 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 generally 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. So small tables that are easily cached (<5% of SGA cache size) we can exclude them from the list of tier zero candidates. In the next series of blogs let’s look at each of these three sources and see how they can be utilized to determine optimal placement of assets on Tier Zero storage. We will start with use of explain plans to determine tier zero eligibility.
Use of Explain Plans
Depending on the release of Oracle you may have a very good source of explain plans already built into your metadata. On Oracle9i and releases since Oracle9i the dynamic performance views include v$sql_plan. The contents of the v$sql_plan view are shown in Figure 1.



Figure 1: Contents of the V$SQL_PLAN Dynamic Performance View

If you have worked with explain plan and the plan_table then the contents of the V$sql_plan view should be familiar; other than the ADDRESS, SQL_ID, and HASH_VALUE columns the plan_table and the v$sql_plan view are virtually identical.

The major difference between the plan_table table and the v$sql_plan view is that the plan_table must be populated by request of the user with the explain plan command and usually contains the plan for only a few specific SQL statements, while the v$sql_plan table is automatically populated and contains the plans for all active SQL statements in the SGA SQL area.

Through the use of queries against the v$sql_plan view after your application has been running and established, its working set of SQL can yield detailed information about what indexes are frequently used and what tables are accessed by inefficient (from the storage point of view) full or partial scans. An example SQL script to pull information about table and index access paths is shown in Figure 2.

rem based on V$SQL_PLAN table
col operation format a13
col object_name format a32
col options format a30
col fts_meg format 999,999,999.99
column dt new_value today noprint
select to_char(sysdate,'ddmonyyyyhh24miss') dt from dual;
set pages 55 lines 132 trims on
ttitle 'Table/Index Access '||&&owner
spool access&&today
select
a.object_name, rtrim(a.operation) operation, a.options,
sum((b.executions+1)*c.bytes)/(1024*1024) fts_meg
from
v$sql_Plan a, v$sqlarea b, dba_segments c
where
(a.object_owner=c.owner
and a.object_name=c.segment_name)
and a.address=b.address
and a.operation IN ('TABLE ACCESS','INDEX')
and a.object_owner=upper('&&owner')
group by a.object_name, rtrim(a.operation), a.options
order by a.object_name, rtrim(a.operation), a.options
/
spool off
set pages 20
ttitle off

Figure 2: SQL Script to Extract Full Table and Index Accesses

An example report generated from the script in Figure 2 is shown in Listing 1.



Listing 1: Example Output of V$SQL_PLAN Report

To determine which objects are candidates for Tier Zero storage, look at the number of paths as show by the OPTIONS column which use the object and amount of access as shown by the FTS_MEG column. The objects with the greatest number of executions and sized such that it is unlikely they will fit in the SGA buffer area are excellent candidates for Tier Zero storage.

In the report in Listing 1 we see that for overall volume of access the H_LINEITEM table and H_LINEITEM index LINEITEM_IDX2 is the first candidate for moving to Tier Zero. In fact, about the only objects which aren’t candidates are the tables H_NATION, H_REGION and H_SUPPLIER and the index SUPPLIER_IDX1.

Of course if the table hasn’t been accessed in the period since startup, or, its plan has been bumped out of the SQL cache, then it won’t show up in the report. It is suggested that the report be generated several times during the day, or over several days to make sure all valid tables and indexes are accounted for. Once you have the list of candidates, review them for size, small tables or indexes that will be cached in the normal memory cache shouldn’t be placed on tier zero. In fact, small tables and indexes that you know should be cached in memory should be placed into a designated KEEP cache using the DB_KEEP_CACHE_SIZE to size the cache and the ALTER TABLE or ALTER INDEX command to specify the objects should use the keep cache.

In a RAC environment you may need to run the report on multiple instances in the RAC cluster if access patterns are different based on instance usage.

In my next blog we will cover using statspack or AWR reports to determine tier zero eligibility.