A Holistic Look at the Oracle Memory Layer

The memory layer is the actual area where the database caches, pools, and other memory structures reside. As such, the memory layer is holistic in nature since any changes there are immediately felt on all other areas. Figure 1 is a diagram of the various memory areas in Oracle11g.



Figure 1: Oracle11g memory Areas

Insufficient Memory Resources

Generally speaking, insufficient memory resource shows up as excessive physical reads. Usually the first indication in a properly designed system will be excessive db file sequential reads. If a system is using automatic memory management it may also show as numerous deferred actions or excessive swapping of memory between the shared pool components and the database cache areas. Figure 2 shows an example of memory starvation.



Figure 2: Memory Starvation Indication

Another indication is when the cache analysis section of the AWR or Statspack indicates a large amount of change in physical reads from increasing the cache size as shown in Figure 3.



Figure 3: Buffer Pool Advisory

As you can see in the example shown, doubling the cache size (if possible) would result in at least a 50% reduction in physical reads.

Correcting insufficient memory resources may be as easy as increasing the settings for AMM or the floor values for the various components in the Oracle memory. However, if the system memory has been exhausted then buying more memory and inserting it in the system is indicated. Many times this may not be enough.

Access to memory and the back path to the IO subsystem can also be stressed if there is too much memory. This can be helped by switching to a RAC based system, thus spreading the memory and IO pathways amongst several servers. In a RAC environment excessive interconnect traffic can be a sign that the individual servers have insufficient memory.

Too large a buffer area can result in excessive latching and CPU overhead. It is usually easily detected by the same areas of the AWR or Statspack reports showing the estimated physical reads don’t change starting at a range below our current settings.

Improper Memory Configuration

Within the Oracle memory area there are multiple caches and pools. The wary DBA should be utilizing proper settings for the shared pool database cache, keep, recycle, and multiple block size caches. Additional settings for the large pool, Java pool, and streams pool should be used as needed in a particular environment. Even when AMM is used, floor values should be set for these parameters to ensure minimum levels of performance are met.

When using SGA_TARGET and SGA_MAX_SIZE or MEMORY_TARGET and MEMORY_MAX_SIZE, be sure they aren’t set equal to each other, because this leads to memory thrashing as the internal memory pools and caches are forced to release and reclaim memory, leading to deferred actions. I usually recommend that at least 1-2 gigabytes be between either set of settings. Of course, as you gain operational experience for your system’s needs this gap can be reduced, or increased. The dynamic performance view, v$sga_resize_ops and the AWR resize operations section can be utilized to see if you need to rethink the floor settings for specific parameters.

A typical v$sga_resize_ops query:

select
COMPONENT, OPER_TYPE, OPER_MODE,
INITIAL_SIZE, TARGET_SIZE, FINAL_SIZE,
STATUS,
to_char(START_TIME,'mmdd hh24:mi') start_time,
to_char(END_TIME,'mmdd hh24:mi') end_time
from V$SGA_RESIZE_OPS order by start_time
/

Figure 4 shows an example output from the memory resize query.



Figure 4: Resize Operations Query Results

In some databases, notably data warehouse (DWH) and decision support (DSS), it may be impossible to provide enough cache to support all operations. However, in many cases the developers or the DBAs or both may not be aware of all the options Oracle provides. Many times when reviewing the AWR or Statspack for DWH systems I see they are not utilizing star joins. Star joins will reduce 30 minute queries to 30 seconds when utilized correctly.

In the case where you must have db file scattered reads (full table or index scans), you have to make sure the IO subsystem is properly sized to handle the load. As was said in the section on physical IO, to get adequate performance from disk based systems it may be necessary to over buy the number of disks needed to the tune of 30-40 or more times the number of disks needed for storage capacity alone to get the needed number of IOPS. The major cause of the need for all of these drives is the contention and queuing that result from too-few spindles. By utilizing low latency IO subsystems that don’t block, such as SSDs, you don’t need to over buy storage capacity to meet IOPS needs.

In the case where is it not economical or feasible to add memory to a server or servers (RAC) then replacing some of the high latency disks with low latency IO subsystems such as large cache or SSDs is a method to get higher performance from your system.

Sort Areas

Another area of memory that people tend to set and forget is the setting for PGA_AGGREGATE_TARGET (PGAT). This PGAT setting controls the SORT_AREA_SIZE, HASH_AREA_SIZE and various other sort and hash parameters. One temporary activity that isn’t controlled are the sizes of the BITMAP_MERGE_AREA and BITMAP_SORT_AREA sizes which can be of critical importance if you are using bitmap indexes. I usually look at the instance activity and PGA histogram sections of the AWR or Statspack reports. Some example report sections are shown in Figure 5.



Figure 5: Example Sort Statistics

Notice that the majority of sorts in Figure 5 are in the range of 2-16 megabyte, these should easily be covered by the PGAT setting, 1.0 gb in this database, so why are we getting these small sorts? Processes that do direct IO to and from the PGA may avoid the PGAT and use the traditional SORT_AREA_SIZE setting. In this case you may to go ahead and set the SORT_AREA_SIZE to 16 megabytes to handle these sort needs. A majority of the rest of the sorts are equal to or less than 512 megabytes which should be handled if we boost the setting to 4 gigabytes. Also, in the case of the above report, the temporary tablespace was the number one source of IO in the system as shown in Figure 6.



Figure 6: Temporary Tablespace #1 Source of IO

The PGA Memory Advisor section from the same report is shown in Figure 7.



Figure 7: Example PGA Advisory

When looking at this PGA Memory Advisory section you need to pay attention to the Estd PGA Overalloc Count column. In this report the value for the Overallocation count column doesn’t go to zero until 2400 megabytes. I have found that this section, when followed, tends to under allocate what you need. In this case we have determined that 4 gb is actually a better number. There have been times when there have been sorts (other than those accountable to non-PGAT controlled processes) in the range where the PGAT should have handled and yet the Overallocation count said everything was fine.

DB Flash Cache

If you are running on Solaris or Oracle Linux you can utilize the new 11gR2 DB Flash cache. The DB flash cache can be thought of as an L2 cache for the normal database default memory cache. If you have some spare memory and db sequential reads are your majority wait event, use of a DB flash cache may be the solution to holistically improve the performance of the system. Usually it should be set to between 2 to 10 times the size of your default DB cache size.

The DB Flash Cache is maintained by the DB writer processes. If you configure the DB Flash cache I suggest doubling the number of the DB writer processes to facilitate the management of the DB Flash cache. Usually the DB Flash cache will be on a PCIe based Flash card such as the RamSan70. However, there is no reason why you can’t utilize an external Flash appliance such as the RamSan 630/700/800 series for the DB Flash cache. The only caveat is that the DB Flash cache assignment device must have a latency that is very much less than the main source of the cached blocks or you will not get good performance improvements.

Oracle Structure Layer Holistic Implications

In this next Holistic tuning blog, I will examine database structures. The structure layer consists of the internal physical structures that make up the database. In the beginning it was just tables and indexes; now of course we have several types of tables, several types of indexes, materialized views, LOBs, objects, and many other structures to be concerned about.
Improperly Designed Tables
Even though processors have gotten more powerful and disks and storage arrays more capable, you can still defeat them with improper table design. Generally, improper table designs fall into two broad categories: insufficiently normalized and over normalized designs.

Insufficiently normalized tables result in repeating values within a table. Insufficient table design usually produces excessive IO, excessive sorts, and excessive use of DISTINCT.

Over-normalized tables usually result in too many joins, excessive sorts caused by Cartesian products from insufficient WHERE clauses, and overly complex SQL.

In most cases Oracle will not produce an efficient execution plan for over 6 tables unless you have a data warehouse and utilize a star join path. The undocumented parameter “_optimizer_max_permutations” sets the number of path determinations to 2000. Since the number of permutations is based on the factorial of the number of tables in the join, you reach 2000 between 6 tables (6 n!=720) and 7 tables (7 n!=5040). Place the most important (i.e. the ones that reduce the result set the most) tables first in the FROM clause if you have over 6 tables as (at least last time I reviewed this) the tables are evaluated from left to right when it is going through possible paths.

Use of flex fields can also be a show stopper for performance. Flex fields are fields whose content type is determined by the context of previous rows. In one case I was called on to help with creating a data warehouse from an application that was based on Oracle Applications. In this case they had used the “flex” fields in Oracle applications to specify that a single column was one of: a number, a date, or a character field, depending on other values in the row, in fact there where several different fields in the same row used the same way. In order to resolve one entry into the companion data warehouse we had to recursively join this table to itself seven times.

Use of improperly typed fields can also cause performance and storage problems, for example using a large CHAR based field for small length character values. CHAR reserves whatever space is specified regardless of the length of the input data. This requires extra programming for comparison semantics and wastes storage space. Another example is using CHAR or VARCHAR2 for numeric data, thereby forcing conversion (either implicit or explicit).

Non-use of partitioning is another possible design flaw. Partitioning is an extra license but can be worth it in time and resources saved. The biggest benefit from partitioning is partition elimination. Partition elimination is when only the partitions needed to resolve the query are scanned. Partitions, when used properly (as in with a proper partition key), reduce IO requirements for the entire database by eliminating full table scans. On the other hand, if a partitioned table has an improper partition key it may make performance worse.
Use of Materialized Views
Materialized views, also known as summaries, allow you to pre-build reports, summaries and other “end results” so that when someone issues a query that can use the materialized view, the results appear instantly. Use of proper materialized views is important to reduce IO and CPU usage as well as reducing sort space usage. You need to determine if you need “fresh” data or can stomach using “stale” data.

With fresh data you would want to use an “On-commit” materialized view that ties its refresh cycle to the main table that is in the materialized view. You can also use an “on request” materialized view if data doesn’t have to be fresh. The on request materialized view can be used with a schedule to refresh it at whatever periodicity is needed (based on how stale the results can be.)
Insufficient Indexes
Insufficient indexing results in full table scans, excessive sorting, and excessive CPU usage. Indexes, when used properly are probably one of the most performance enhancing features of Oracle.

Another area that can really affect holistic performance is when you move referential integrity (RI) from the database to the application. There was a large movement to do this type of design to allow the application to be database layer agnostic a few years ago. Unfortunately this design was sometimes only partially utilized leaving some database defined RI mixed with application based RI. In this mixed RI case the foreign keys would often not be indexed resulting in excessive row-locking in the application. Of course this can also happen if you use database based RI and don’t index foreign keys.
Over-Indexing
Over indexing results in excessive times for DML operations such as insert, update, and delete because the amount of IOPS required to perform each action is multiplied several fold with the addition of each index.

Several years ago Kevin Loney did a study to see the effects of multiple indexes on Insert, Update and Delete operations. Essentially the time to do the operations increased by large amounts as the number of indexes increased due to the increased number of IO operations required.
Use of an Incorrect Index
Using the wrong type of index can result in either the index not being used at all, or being used inefficiently. For example, using a single bitmap index will usually result in poorer performance than using the equivalent B-tree index. Using a regular index when a function-based index is required will result in a full table scan. Another problem with indexes is improper use such as using a single concatenated index rather than single indexes. Single indexes can sometimes provide a greater advantage than a single composite by allowing a more diverse group of SQL statements to utilize the single indexes, either as single indexes or by using two or more of the indexes. There are many other issues of this type; be sure to research the proper index to use in each situation.

Use of proper indexing and making sure you aren’t over indexing reduces IO and CPU cycles as well as speeding up queries; it will also make sure you don’t excessively penalize DML (Insert, Update and Delete) operations.

Holistic Tuning Part 1

What does the term “Holistic” really mean? From the online Webster Dictionary:

Holistic:

”… relating to or concerned with complete systems rather than with the analysis of, treatment of, or dissection into parts… ”

Given the above definition of “Holistic,” this next series of blogs will deal with looking at the entire database when performing a tuning exercise, not just the immediate statement or other item that seems to be the problem.

This Holistic view of tuning means that we must consider the total effects of design, structure, applications, and physical systems on tuning and treat each as a part of the whole rather than piecing them out into individual areas.

Holistic View of Oracle

Oracle is like an onion; each layer rests on the one underneath. If any layer is “rotten” then the entire structure is in danger. Likewise, a change to any layer may propagate effects up and down through other layers. Let’s look at an example.

Given: Statement X is generating excessive physical IOPS when it executes

Local Fix: Add indexes

Immediate local effect: Statement X runs faster with fewer IOPS

Holistic effects: Statements Y and Z switch from proper indexes for them to indexes added for statement X

Holistic Result: X runs faster, but Y and Z run slower, overall result: Slower system even though the tuning effort on X was a success!

Now, this is not to say that all local actions are going to have a negative effect. Usually a switch to a new index is because the optimizer has looked at statistics and determined the index would be better before using it. However, we all know this may not always work as designed!

Of course everyone generally agrees that caching is a good thing. The more we can cache data for Oracle to use, the faster the application will run. However, this comes at a cost. The cost of caching is that now instead of handing off IO to an external IO processor, IO has to be handled by the CPU using logical IO. Now, logical IO is several orders of magnitude faster than physical IO, except if it introduces so many additional CPU cycles that the system becomes CPU bound.

The converse is also true; without enough caching or disk IO bandwidth, adding memory or CPU to a system that is IO bound may not help at all. So again we have to truly understand the holistic view to take the proper course of action.

Local Actions that Have a Negative Holistic Effect

In my job with TMS I am tasked with doing system evaluations using statspack and AWR reports to determine if a client really needs a new IO subsystem or if other issues are causing their system performance issues. Believe me, it does us no good as a company to have someone say they bought our solution and it didn’t help their problem!

It is amazing how many people still suffer from basic, local problems that affect the holistic performance of the entire database. We will look at a few of these “local” issues that have a global effect over the next few blog entries.

Just What Are Blocking Reads and How Did They Get in My Database?

If you have been around Oracle for very long I am sure you have heard that in Oracle readers don’t block other readers and readers don’t block writers. Of course, this is referring to the processes that happen within Oracle locking structures inside Oracle memory caches. What happens within the physical storage is a completely different story.

Even if you hand Oracle raw devices (which means the Oracle kernel handles the IO instead of passing it off to the operating system) you will still get blocking reads (and blocking writes) if you are using standard hard disk drives. Imagine it this way: a person initiates a full table scan with a multi-block read count of 64 and a block size of 8 kilobytes. The disk subsystem is in a RAID10 with a stripe depth of 64 kilobytes going across 8 disks for a stripe width of 512 kilobytes. What happens to disk access while his full table scan of a multi-megabyte table is happening? Look at Figure 1.



Figure 1: A Blocking Read

So, even if the second user in a blocking read situation is going after totally unrelated data, they have to wait until the first user finishes his read before they get access to the disks. Now multiple this by the number of full table scans in your system and the number of users trying to access data and you can see the issue. How can blocking reads be mitigated? Well in hard drive based system you need to align stripe depth to the size read in a full a table scan to minimize the number of disks involved. Look at Figure 2.



Figure 2: Non-Blocking Read

In the situation in Figure 2 the disk stripe depth is aligned to the product of the db file multi-block read count setting and the block size. This allows each of the first users reads to only tie up one disk at a time. Now when user two tries to get access, as long as they are going for data on a different disk, they can get to their data with no problem.

ASM uses a 1 megabyte stripe depth for data in a normal setup and 4 megabytes for an Exadata cell for just his purpose, to prevent reads form blocking other reads or writes. But, why does this collision happen? The underlying cause is shown in Figure 3.




Figure 3: The Cause of Read Blocking

The ultimate cause of blocking reads and writes is the rotating disk and moving armature inside hard disks. The need to reposition the disks and armatures to read or write the individual data sectors results in blocking access to the disks during each discrete read or write by user for all other users. Of course the only way to get rid of blocking reads and writes, when not accessing the same data block, is by using solid-state memory that has no need to rotate a platter or move an armature. If you could create a read “surface” that could read any point on the disk surface without repositioning the disk, you could get near memory speeds from disk based systems. Unfortunately you can’t do this with current technologies, and, quite frankly, why would you want to? Modern flash technology provides read latency of 250 microseconds or less and with a little help from DDR buffering and advanced write algorithms, 85 microsecond or less write times.

But what if you don’t want to replace your entire SAN system with $20/GB flash technology? Well, in my next blog entry next week I’ll tell you how to eliminate read blocking and still benefit from flash while utilizing you existing SAN or disk technology.

Using AWR or Statspack for Tier Zero Object Placement Analysis

The SQL sections of either the Statspack or AWR reports are valuable sources of information for the determination of what objects should be placed on Tier Zero. When combined with execution plan analysis from the V$SQL_PLAN view, use of the Statspack or AWR SQL areas can quickly isolate not only the most critical SQL statements, but their underlying objects. The first indication that moving to a Tier Zero storage layer would be beneficial for your system is found in the “Load Profile” section of the Statspack or AWR. Let’s look at an example client report, Figure 1



Figure 1 Load Profile from AWRRPT

The report excerpt in Figure 1 was taken from client OLTP type database. Oracle is reporting 20.2K block IOPS against this setup, which is actually quite impressive. Of course most of these IOPS out of Oracle will be bundled at the controller, usually by a factor of 16, as can be seen in Figure 2 in the listing of total read and write IOs from the detailed statistics section of the report.



Figure 2 Total Read and Write Statistics from Instance Statistics

This concentrating of IOs leads to reducing the IOPS seen at the actual disks to around 750. Given that each disk is capable of between 90-150 random IOPS, that means we should be able to support about 2800 IOPS before we stress the disk system, if all of the IOPS are non-colliding. Of course with reads stomping on writes and writes stomping on reads even this relatively low number of physical IOs can result in IO subsystem stress. If we want to know what kind of stress we are actually seeing, we need to compare CPU time and idle time next. Figure 3 shows the Operating System Statistics section of the same report as in Figure 2.



Figure 3: Operating System Statistics

In Figure 3 we see that the idle time is large in comparison to the busy time and that the IO wait time is more than half the busy time. But is the AWR report giving us the complete information for the IOwait time it is reporting in this section? Let’s look at the Instance Activity Statistics section again, look at Figure 4.



Figure 4: User IOWait Statistic

This new statistic seems to indicate IOwait is actually greater than busy time, this indicates that CPUs are excessively waiting for IOs to complete, signaling that the system is stressed at the IO subsystem. But how stressed is the system?

A key indicator of the amount of IO stress is the IO latency. Disk latency is based on disk rotation speed for the most part. A 15K RPM disk should have a max latency of around 5 milliseconds, less if the disk is being “short stroked;” a short stroked disk is one that is not allowed to get to more than 30% capacity, specifically to reduce IO latency. The Tablespace IO statistics section will give us an idea of what the Oracle system is seeing as far as latency in the IO subsystem. Figure 5 shows the Tablespace IO section of the report (I have excluded the tablespace names to protect the clients ID).



Figure 5: Tablespace IO Report

From Figure 5 we can see that the IO latency for reads for our tablespaces are extremely high, 10-25 milliseconds respectively (other than the first tablespace at 2 ms). These extremely high latencies definitely indicate the IO subsystem where those tablespaces are located is being heavily stressed. High numbers of buffer waits also indicate long wait times for writes, as we can see the second tablespace is experiencing large numbers of buffer waits and each buffer wait is taking about 4 milliseconds to resolve. Other than moving all of the tablespaces, data tables, and indexes to Tier Zero, how can we determine the minimum number of objects that should be moved? Let’s go back to our original report.

Going back to the beginning of the report we can first examine the top wait events to see under what categories the IO stress occurs. Figure 6 shows the “Top Five Wait Events” for our Data warehouse database report.



Figure 6: Top Five Wait Events

I’ll bet you were expecting to see db file scattered reads or db file sequential reads as the top wait events, and you would have been correct in a non-partitioned, non-parallel query environment. However, this database is highly partitioned and uses table, index, and instance level (it is a 4-node RAC setup) parallel query. The direct path read wait event indicates asynchronous reads direct into the PGA, in this case because of the parallel queries being utilized by the system. The direct path read temp and direct path write temp indicate that our PGA_AGGREGATE_TARGET may be insufficient for the size of hashes and sorts being performed. So what does all this mean?

The direct path read wait events are being driven by full table/partition scans. If we examine the “Segment Statistics” section of the AWR report we can see what objects are undergoing the most physical IOs and, generally speaking, those will be the ones causing our direct path read waits. If we saw db file scattered reads or db file sequential reads we could look at the same report section to see what objects were most likely causing issues. Figure 7 shows the pertinent “Segment Statistics” sections of the report.



Figure 7: Segment Statistics Sections

Obviously the H_LINEITEM table is dominating our Segment Statistics. With only 8.1% of total IO being shown, it indicates that the physical IO is being spread over a large number of objects, in this case, probably more H_LINEITEM and H_ORDER partitions. From looking at all three sections we can see that the H_CUSTOMER, H_PART, and H_SUPPLIER tables are seeing a great deal of full scans at the partition level as well. However, with less than 50% of any of the Segment Statistics actually being shown, we are probably missing a great deal of important statistics for determining what should be placed on Tier Zero. However, as a start it looks like the following tables should be placed there:

• H_LINEITEM
• H_CUSTOMER
• H_PART
• H_PARTSUPP

In addition, the temporary tablespace stress indicates that the temporary tablespace datafiles should be moved there as well, assuming we cannot increase PGA_AGGREGATE_TARGET to accomplish relief of the temporary IO stress by moving the sorts and hashes into memory.

Since we are seeing less than half of the actual database activity being recorded in the Segment Statistics area of the report, we need to look at the SQL area of the report to determine what SQL is causing the most stress. By analyzing the top 5-10 problem SQL statements, defined as the SQL statements that are worst performing in their area of report, we can determine any additional objects that may need to move to Tier Zero or other high speed, low latency storage. Figure 8 shows the applicable top five SQL statements in the SQL physical reads section of the report.



Figure 8: Top 5 SQL Statements by Physical IOs

The full SQL statement for the top SQL statement from Figure 8 for Physical IOs, SQL ID 7zcfxggv196w2, with over 20 million reads is shown in Figure 9:



Figure 9: Full Text for Top SQL Query

Just by examining the SQL in Figure 9 we can’t really see much more than we knew before. However, if we generate an execution plan using the explain plan command, we get a much better look at what objects are actually being utilized by the query. Figure 10 shows the abbreviated explain plan for the query in Figure 9.



Figure 10: Abbreviated Execution Plan for Top Query

In the execution plan just the objects accessed are being shown; in this case we see the same tables from our full table scan list in addition to the LINEITEM_IDX2 index. The H_NATION table is very small and is usually fully cached early in any processing cycle so we can disregard it. In fact, if we do a select on the V$SQL_PLAN table we find that the following indexes are also being heavily used by the other queries:




So after looking at IO statistics and physical waits and doing some SQL analysis, we have determined that the following objects should be placed on Tier Zero if space allows:

• Temporary tablespace
• H_LINEITEM table
• H_CUSTOMER table
• H_PART table
• H_PART_SUPP table
• H_ORDER table
• CUSTOMER_IDX1 index
• PARTSUPP_IDX2 index
• LINEITEM_IDX1 index
• LINEITEM_IDX2 index
The Results
What happens if we move the selected items from hard disk to Tier Zero storage (in this case solid state devices ranging from 0.2 ms for data and index areas to .015 ms latency for temporary areas)? The graph in Figure 11 shows the performance of 22 queries with the objects on the SSD arrays and on the original hard drives.



Figure 11 Results from Moving to Tier Zero

Figure 11 clearly shows the advantages of Tier Zero storage. On the average the queries ran a factor of 5 times faster on the Tier Zero storage, with some queries being almost 20 times faster. Also shown in Figure 11 are the results of moving the temporary segments to Tier Zero; note the 4 queries (9, 13, 16 and 18) whose performance was improved just by moving the temporary tablespace. Of course moving all the suggested items to SSD gave the greatest improvement.

Summary

Through the analysis of IO profiles, wait events, and SQL object usage we were able to determine what objects would benefit from being moved to Tier Zero type storage. Through the use of AWR reports and some custom queries the determination of objects was rendered fairly easy. The final test of our selections by actually moving the objects to Tier Zero provided a 5 fold increase in average performance, with over a 20 fold improvement in some queries.

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.

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.