|
|
|
|
Tuning Oracle Without
Cache-Hit Ratios
Gaja Krishna Vaidyanatha, Quest Software
Inc.
INTRODUCTION
From time immemorial, Oracle performance tuning has the infamous label of witchcraft, secretly practiced by an elite group of individuals, who allegedly use voodoo to cure the performance problems of an Oracle system. To compound this misperception there exists many thousands of pages of published material that propagate the idea of tuning Oracle with cache-hit ratios. There are many references to how performance is good when the ratios are high and when those ratios are below a “desired percentage” there is great cause for concern. All of this causes confusion, with very scarce mention about unearthing the actual source of the bottlenecks that are inflicting performance pain on the entire system.
Even today, many tuning efforts result in arbitrary attempts to cure performance problems by allocating more memory to the Oracle System Global Area (SGA). This is done with vain hope and the perception that any system performance problem will cure itself, if only physical I/O is reduced or even eliminated. This effort is taken to the nth degree by allocating inordinate amounts of memory to the Oracle’s SGA. The practice (when overdone and that seems to be a common phenomenon) can actually result in worse performance. The common thinking goes on the lines of “The more memory you allocate to Oracle, the better”. This thought process assumes that logical I/O (reading from memory) should any day be preferred to physical I/O (reading from disk).
Cary Millsap[1] eloquently describes the chronic problem of performing excessive logical I/O and its impact on system performance. In his paper he describes the pain that applications can inflict on the system, in the form of contention for the cache buffers lru chain latch (relevant prior to Oracle 8i due to a significant change in the database buffer cache management algorithm in Oracle8i) and the cache buffers chains latch, when repeatedly performing large amounts of logical I/O, across many user sessions. Cary also provides data that suggests that the performance difference between logical I/O and physical I/O (in the Oracle context) is one order of magnitude at best and not three orders of magnitude (which is common knowledge for comparing memory reads to disk reads).
Some performance-tuning engagements fall prey to the habit of configuring significant amounts of memory for the database buffer cache, in an effort to attain high cache-hit ratios (upper 90s), only to discover that performance has not improved one bit, after such an exercise has been completed. In some extreme cases, such efforts resulted in the operating system engaging in significant levels of paging (20000+ pages/sec.) and swapping. In others, contention for the aforementioned latches in the database buffer cache, literally crippled system performance.
The shared pool is another structure where arbitrarily allocating inordinate amounts of memory just to attain “high library and dictionary cache-hit ratios”, can result in SQL parsing hiccups and contention for the various latches in the library cache. In some production environments, it was observed that excessive memory allocated to the shared pool area resulted in increased parse times and hanging of SQL statements. When select * from dual; takes 15 minutes to return the 1-row result set, you should be convinced that something is seriously wrong. To add insult to injury, as an antidote to cure such parsing hiccups, extreme measures such as flushing the shared pool every 5 minutes are undertaken. Aaarrgghhh!
Bottom line, Oracle tuning efforts need to be based on isolating and pinpointing bottlenecks (the disease) not cache-hit ratios (the symptoms). The mission of this paper is to dispel any misconceptions that correlate Oracle database performance with cache-hit ratios. It also provides a methodology that guarantees repeated success in unearthing the actual source of performance problems.
A COMMONSENSE PERFORMANCE TUNING METHOD
So why do we need a method? Why should it have an element of common sense to it? Because tuning without using a methodology does not guarantee positive results and is potentially very risky. Plus, any tuning effort that does not involve commonsense could be endless. Before engaging in any tuning effort, specific and reasonable performance goals need to be agreed upon and set. Benchmarking of the current performance needs to be done first, so that effective performance measurement can be facilitated. The goal for every tuning effort needs definition of the scope of the effort itself. It is not enough to say, “this database/query runs poorly and it needs to run faster”. The required performance numbers need to be quantified, so that the performance tuner knows what to tune, how to tune, when to tune, and more importantly when to stop tuning. It is as important to stop tuning at the right time, as it is to start tuning at the right time.
Failure to set specific goals and the lack of a meaningful methodology, can result in unnecessary time and effort spent on tuning the system without significant and/or measurable gains. Haphazard attempts to alter initialization parameters and allocating more than required memory to the Oracle SGA is not only counter-productive, but can potentially cause worse performance.
Here is a summary of an industry-tested method to Oracle Performance Management:
– If you begin your troubleshooting efforts with the Oracle wait interface (OWI) on one side and the operating system (OS) on the other, and consciously move each effort towards the other, when the information derived from the two efforts meet, you would have defined the complete performance problem. And accurately defining the problem is half the challenge!
The OWI is defined as the collection of information available in the v$system_event, v$session_event, v$session_wait dynamic performance views and trace files generated by setting the 10046 event.
The following describes the two-pronged performance tuning methodology in greater detail. This methodology has been utilized for many tuning efforts. The results derived from using this methodology are repeatable and predictable. One of the key strengths of this methodology is its consistency in producing results. The two-pronged methodology requires you to:
WARNING
Do not tune any component, if it is not the source of your system-wide bottleneck. Doing so, can and will result in unpredictable and potentially undesirable effects. For example, if your system is currently suffering from I/O bottlenecks and you upgrade your CPUs with twice as fast processors, you will in fact make your I/O bottlenecks doubly worse. It is as important to cease all tuning efforts when the set tuning goals are attained, as it is to begin a tuning engagement.
Now, let us focus on steps 3 & 4, as they are key to the success you will achieve in step 7.
IDENTIFYING THE ORACLE BOTTLENECKS – PRONG I
OVERVIEW
A bottleneck in an Oracle environment triggers within the Oracle kernel a wait event. A wait event in its true sense is a section of the Oracle kernel code that facilitates us to determine the cause and location of bottlenecks inside Oracle. There are approximately 200 events in Oracle 8i. But most “production” tuning problems are characterized by a dozen or so wait events.
Wait events are categorized into two types, idle and non-idle. Idle events indicate that Oracle is waiting for some work and is usually not an indication of a “database” performance problem. Some common idle events are client message, NULL event, pipe get, pmon timer, rdbms ipc message, smon timer, and SQL*Net message from client. Non-idle wait events are actions that are specific to Oracle caused by inefficient SQL, inefficient system architecture or improper instance configuration. Some common non-idle wait events are buffer busy waits, db file scattered read, db file sequential read, enqueue, free buffer waits, latch free, log file parallel write, and log file sync.
The crusade to diagnose Oracle bottlenecks should begin with v$system_event in the OWI as this displays the top events on the system, since the last instance startup. You then follow that up, by a peek into v$session_event for the current sessions attached to the Oracle instance, that cause and/or experience the bottlenecks. The final step is to get the details behind the event, i.e., the location of the wait event and that can be obtained from v$session_wait. Concurrent to this effort, the operating system’s CPU, Memory and I/O-related statistics need to be collected (Prong II). The 2 prongs should consciously move towards one another, and when they meet, the bottleneck is unearthed right there.
THE FIRST LINE OF OFFENSE - V$SYSTEM_EVENT
The v$system_event view provides a bird’s eye view of all the events in an Oracle system, since the last instance startup. Even though it does not contain session-level specific information (current or the past), it does sum all the waits, since the last time the instance was bounced. The statistics in this dynamic performance view is reset to zero on every instance startup. For that reason, it should be noted that the statistics in this view, should be sampled over time.
The columns in the v$system_event dynamic performance view are:
EVENT: This is the name of an event such as enqueue wait, buffer busy waits, latch free, db file scattered read, db file sequential read, free buffer waits and so on.
TOTAL_WAITS: This is the total number of waits for a given event since the time the instance was bounced.
TOTAL_TIMEOUTS: This column provides the total number of wait time-outs for the specific event since the time the instance was bounced.
TIME_WAITED: This is the total wait time in centiseconds (1/100ths of a second) for all sessions for a given event since the time the instance was bounced.
AVERAGE_WAIT: This is the average wait time in centiseconds (in 1/100ths of a second) for all sessions for a given event since the instance was bounced. Average_Wait = (time_waited/total_waits.)
THE SECOND ZONE - V$SESSION_EVENT
The v$session_event view provides the same information as v$system_event at the session level, plus relevant session information. (i.e., SID etc). This view allows the drill down from the “system-wide events”, to the session level, to determine which session is causing and/or experiencing a given wait event. Due to the inherent nature of this dynamic performance view, the data for a session is lost when the session terminates.
THE FINAL FRONTIER - V$SESSION_WAIT
The v$session_wait view is quite possibly the most complex dynamic performance view within Oracle. It provides low-level drill down information at the session level for a given event. Unlike some of the other views, which display totals, this view displays session level wait information “real time”. This is the real stuff, as it unfolds. It is important to note that, iteratively querying this view may show different results. This could be attributed to the activity that is happening on the database.
As mentioned before, this view displays the drill down information for the wait events. For example, if a session is waiting on an index scan (db file sequential read), the file number, the data block number and the number of blocks in the read system call issued by Oracle can be ascertained.
The relevant columns that provide us with the needed information in the v$session_wait dynamic performance view are:
SID: This is the session identifier number.
SEQ#: This number is an internal sequence number for the wait event related to this session. This column can be utilized to determine the number of waits for a given event that a session has experienced. If you need to determine the top session that is causing pain or suffering the most, look for the sessions with the largest Seq#.
EVENT: This is the name of the event such as enqueue wait, buffer busy waits, latch free, db file scattered read, db file sequential read, and free buffer waits.
P[1-3]: This is the treasure that we have been hunting for. These 3 parameters point to more details about the specific wait. The values in these parameters having logical relationships (foreign keys) to other views and the interpretation of a given value is wait-event dependent.
For example, for the wait event - db file scattered read (full table scan), p1 is the file number (points to v$filestat or dba_data_files) p2 is the block number (related to dba_extents, sys.uet$) and p3 is the number of blocks in the read system call.
But for latch free, p2 is the number of the latch, which points to v$latch. For the complete list of the waits with their associated parameters, please refer to one of the appendices in the Oracle Reference Manual.
STATE: The state of given event is a very important indicator, as it provides details for interpreting the following two columns, namely Wait_Time and Seconds_In_Wait. Without fully understanding the State column, the numbers ascertained from Wait_Time and Seconds_In_Wait may be meaningless. There are 4 possible states.
Waiting - The session is currently waiting for the event.
Waited Unknown Time - The Oracle initialization parameter, TIMED_STATISTICS is not set to TRUE, i.e., is set to FALSE either explicitly or implicitly.
Waited Short Time - The session waited for an insignificant amount of time, i.e., the duration of the wait lasted less than 1 centisecond. This may be worth looking in subsequent iterations of a performance tuning effort, after events with a state of Waited Known Time are first dealt with.
Waited Known Time – The session waited for the wait event for a duration that lasted more than 1 centisecond. If the resource that is waited upon is gained at a later time, the state changes from Waiting to Waited Known Time.
WAIT_TIME. The value for this column is State dependent and is only relevant for wait events that have completed waiting.
If State = (Waiting or Waited Unknown Time or Waited Short Time) then
Wait_Time = Irrelevant;
End If;
If State = (Waited Known Time) then
Wait_Time = Actual wait time, in seconds;
End If;
But if the system is very busy, and the session is waiting on multiple resources and begins to wait for another resource, State will change to Waiting, and Wait_Time = Irrelevant – Per first if condition. Basically, you look at Wait_Time only if wait event has completed.
SECONDS_IN_WAIT. The value for this column is also State dependent and is only relevant for wait events that are currently in progress.
If State = (Waited Unknown Time or Waited Short Time or Waited Known Time) then
Seconds_In_Wait = Irrelevant;
End If;
If State = (Waiting) then
Seconds_In_Wait = Actual Wait Time in Seconds;
End If;
Values in this column may not re-appear on multiple iterations of querying. If the values do re-appear, the session is waiting for a long time for the given event. Multiple iterations of the querying this view will provide information about the duration of the wait by a session, for a given event. Again, you look at Seconds_In_Wait only if the wait event is currently in progress. For wait events that have already completed, you need to look at Wait_Time.
NOTE: The data in these columns are refreshed every 3 seconds synchronized with the 3-second writing event of LGWR.
IDENTIFYING THE OS BOTTLENECKS – PRONG II
The second and equally important prong of a managed Oracle performance tuning engagement is the OS prong. The OS prong looks at the health and functioning of the operating system via different metrics. The goal here is to unearth any bottlenecks plaguing the operating system
WHY IS THE OS PRONG NEEDED?
The OS prong is needed because:
COMPLETING THE PERFORMANCE PROBLEM DEFINITION WITH OS METRICS
Some of the OS metrics that are useful in identifying bottlenecks are CPU utilization, I/O statistics, Queue information with their respective counters and number of waits, amount of paging and swapping, the number of context switches and so on. The following are some sample commands used to measure the required OS metrics:
Alternatively, using any software or freeware utilities such as Glance or SysMon that assist in identification of OS bottlenecks. The OS bottlenecks, especially the ones that are outside the realm of the Oracle database can and will affect the performance of the database and every effort to unearth them should be pursued.
THE HOLY GRAIL OF ORACLE PERFORMANCE TUNING – WRITING WAIT EVENTS TO A TRACE FILE
If you are having trouble tracking down the wait events on your system and the current active sessions in your Oracle database, because of the dynamics of your environment, these wait events can be traced and written to a trace file. Here are the steps:
FOR YOUR CURRENT SESSION:
FOR SOMEONE ELSE’S SESSION:
PUTTING IT ALL TOGETHER
As mentioned in previous sections, using session wait statistics facilitates one to pursue a different yet more productive performance management effort. The key to the method is drilling-down to the core of the problem and curing the diseases that plague our systems, rather than just treating symptoms.
Here is an example. In a recent performance tuning effort it was observed by the client that the database buffer cache-hit ratio of Oracle was very high (99.6%), even though the total throughput (amount of useful work done) on the system was very poor. Users had to wait for very unacceptable periods of time to perform their job functions.
We started our performance tuning efforts with the first prong, by querying the OWI’s v$system_event view, to determine the top waits for this system since the last instance startup. In our case, we found the top offenders to be db file sequential read (wait event associated for reads on index or sort segments), buffer busy waits (wait event associated with in-memory contention for the same buffers, a typical symptom due to lack of freelists) and latch free (wait event associated with latches).
On drilling-down to the v$session_event view, we were able to determine the sessions inflicting and enduring these wait events. This step assisted us in determining who was causing the pain and who was suffering. The final step was to look at v$session_wait which contained the true source of the bottleneck in its columns P1-P3, State, Wait_Time & Seconds_In_Wait. This v$ view provided us the bottlenecks experienced by the current sessions connected to the database.
For db file sequential read and buffer busy waits, P1-P2 provided the file# and block# for the buffers in question. With the value in P1 we queried dba_data_files to determine the problem file and with the value of P2 we queried dba_extents to determine the problem segment. For the latch free wait event, the value in P2 gave us the actual latch# on which there is contention. To determine the name of the latch, we queried v$latch with this value and ascertained that it was the cache buffers chains latch that was the problem. The combination of these three wait events suggested that the system experienced I/O contention in reading index blocks from disk into memory, followed by very high in-memory contention (due to too much logical I/O) while reading the same set of index blocks in the database buffer cache. The blocks in question were read concurrently multiple times across multiple sessions. It should be noted here that the number of cache buffers chains configured on this database using the _DB_BLOCK_HASH_BUCKETS parameter was the same value as DB_BLOCK_BUFFERS.
As you probably know 80% or more of any system’s performance problems can be fixed if only we optimized the SQL in the application. The SQL statements causing the wait events were retrieved by tracing back from v$session_wait to v$session to v$sql. When we determined the execution plan for the problem queries, we noticed that they were all “forced to use an index via a hint”.
Using the second prong, the relevant OS bottlenecks were ascertained. Given that the first prong had unearthed the top wait event as db file sequential read, the second prong corroborated that by displaying significant I/O waits on the storage devices where the indexes were stored. The logical storage devices where the I/O wait were occurring was determined by backtracking the filesystem mount point to the logical device name in the device table of the OS. There were no other indicators in the OS with respect to the in-memory contention for the same set of blocks.
At any rate, the “problem queries” were all using indexes and because they performed a high number of logical I/Os on these index blocks after they were read from disk. This caused the database buffer cache-hit ratio to be highly inflated. Even though the cache-hit ratio was high, overall system performance was very poor. People were not able to get their jobs done.
The tuning effort for these queries involved hinting them with the /*+ FULL */ hint as the number of blocks visited for the index scan far exceeded the number of blocks in the table. The full-scan hint generated more physical I/O than before and this had a negative impact on the database buffer cache-hit ratio. The cache-hit ratio was measured after the tuned queries were run and it had dropped to 68.7%. For the heavy-concurrent insert tables, multiple freelists were configured, to overcome the buffer busy waits bottleneck.
However, the throughput on the system had improved significantly, as the queries executed much faster, generated less contention and this resulted in more useful work accomplished in a shorter period of time. The higher productivity was directly as a result of elimination of the index-scan related I/O bottlenecks, contention for the database buffers in memory and its associated latches. When v$session_event and v$session_wait were queried again, the db file sequential read wait event had literally vanished and the latch free and buffer busy waits wait events had dropped to much lower values. There were occasional occurrences of the db file scattered read wait event, but in comparison the frequency of the wait event was significantly lower.
MORAL OF THE STORY – Don’t bet your life with cache-hit ratios. They are useless numbers. The two-pronged performance tuning effort is repeatable and is far more consistent than any ratio in your database. This method needs to be iteratively undertaken until all system-wide bottlenecks are unearthed and resolved. As you probably would have guessed, performance management is a continuous and iterative process.
CONCLUSION
Managing Oracle Database Performance efforts requires adherence to a systematic methodology to ensure that all the core components of a system are addressed in a systematic fashion. By using the two-pronged performance management approach, you will experience repeated success in your performance tuning engagements. Discipline needs to be exercised in setting reasonable tuning goals and ceasing all efforts when the tuning goals are achieved. Knowing where one is going is important, but even more important is, knowing when one has reached the destination.
Don’t bet your life on performance tuning a system with cache-hit ratios. If the database buffer cache-hit ratio is low and you are beginning to get alarmed, stop and look at the session’s wait events. If there are no wait events causing bottlenecks in the system, then your perception of a performance problem is unfounded. On the flip side, if your cache-hit ratios are in the upper 90s and you find sessions waiting for events, don’t just sit back thinking that everything is fine, because in reality it is not, and something is brewing. Stay the course, look for the bottlenecks and resolve the real performance problem in your Oracle databases.
ABOUT THE AUTHOR
Gaja Krishna Vaidyanatha is the Director of Storage Management Products with Quest Software Inc., providing technical and strategic direction for the storage management product line. He has more than 10 years of technical expertise, with over 9 years of industry experience working with Oracle systems.
Prior to joining Quest, Gaja worked as a Technical Manager at Andersen Consulting, where he specialized in Oracle Systems Performance Management and lead the Oracle Performance Management SWAT Team within the Technology Product Services Group. In a prior life to that, Gaja was also a Consultant and Instructor at Oracle Corporation specializing in the core technologies of Oracle.
Gaja is the primary author of Oracle Performance Tuning 101 published by Osborne McGraw-Hill (Oracle Press), which focuses on the rudiments of Oracle Performance Management. His key areas of interests include performance architectures, scalable storage solutions, highly available systems and system performance management for data warehouses and transactional systems. Gaja holds a Masters Degree in Computer Science from Bowling Green State University, Ohio. He can be reached at gajav@yahoo.com.
REFERENCES
