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.