Establish Performance Thresholds during Oracle Tuning

One of the goals of Oracle tuning is to establish acceptable thresholds for Oracle performance. We typically "frame" the threshold within an elapsed time period. For example, consider the buffer hit ratio. By definition, the buffer hit ratio is the hit ratio for an elapsed period of time. In Oracle, if you measure the buffer hit ratio over one-minute periods, you will see ratios between 10% and 99% as I/O patterns change within your data buffer. On the other end of the spectrum, measuring the buffer hit ratio over a 12-hour period is meaningless, because there could be periods during the 12 hours when the buffer hit ratio is very poor, and you cannot see that in the overall average. Hence, we must choose a reasonable level of granularity for any elapsed time measure, usually between 15 minutes and one hour.

You should always choose thresholds for Oracle metrics that are reasonable for your Oracle database and take into account the key functions of your database. For example, a system that does huge sorts all day will always show a high number of disk sorts. In other applications, data warehouse queries may be running all day, making the buffer hit ratio a meaningless metric of performance. Here is a sample of Oracle performance threshold numbers and elapsed times:

Notice that there are two types of measures above. The ratios are expressed as a percentage over a predetermined time period, whereas the others - such as disk sorts and Redo Log space requests - are single snapshot measures that simply count a number of occurrences during the pre-determined time period. Once established, you can easily write an alert monitor to report any of these out-of-bounds conditions. You could also implement Oracle intelligent agents to gather this information and report it to the OEM console. You will learn more about these later in the course. You will learn about monitoring for exceptional conditions.

Now click the Quiz link below to check your knowledge of tuning goals and thresholds.
Oracle Tuning Requirements - Quiz