Paradigm Shift


What do you think is the hardest part about eating sushi? Wouldn ‚ t you agree that it requires a paradigm shift? You have to get over the raw-fish mentality. If you are stuck thinking of sushi as bait, then you will never be a sushi eater. Likewise, the hardest part of the OWI methodology is not the methodology itself, but the paradigm shift. Once you have developed the mentality to focus on response time, you are home free. Sounds simple, but many DBAs struggle in the transition, mainly due to the mental baggage they carry with them from the old school that mainly relied on ratio-based tuning. (We hasten to clarify that not all of the tuning methods from your old school are useless. Some of the methods, such as capacity utilization and resource consumption are still valid, but those methods must account for response time.)

There are three key behavioral changes that need to happen before you can master the OWI method:

  • You must stop measuring performance using non-throughput related ratios, such as the BCHR, sorts-to-disk ratio, and so on.

  • You need to start measuring process response time and/or throughput.

  • You must look at resource consumption from the response time perspective.

We talked about the first point at length at the beginning of this chapter, so we won ‚ t cover that again. Here, we will point out the fallacy of the resource consumption-based tuning and explain why you must evaluate resource consumption with time element.

The old school teaches DBAs to identify and tune SQL statements that consume a lot of CPU, use a lot of buffers, or make a lot of physical I/O calls, because they are a threat to performance and therefore must be eliminated. What is wrong with this concept? It has no regard for bottlenecks and response time. When you judge a SQL statement based on its resource consumption alone, you will not be able to forecast the improvement or the return on your tuning effort because the time element is missing. You can ‚ t tell how much faster a process will run after you reduce the level of resource consumption because you don ‚ t know what impact the level of consumption has on the process in terms of processing time. You may tune your heart out and still not make a dent in the process response time. With OWI, however, you know what impact each bottleneck has on processing time because every bottleneck is timed. When a bottleneck is removed, the process can potentially run that much faster.

Furthermore, a statement may rank No.1 on CPU used, buffer gets, or I/O calls, but the process that it belongs to may spend the majority of its time on a certain bottleneck that, when removed or reduced, could yield a far greater performance gain than what could be realized by reducing the level of resource consumption. For example, you may reduce your car ‚ s gasoline consumption by ensuring that the engine is properly tuned , but that doesn ‚ t necessarily guarantee that you will get to your next destination any faster, especially if you are behind a school bus.

You should never base your tuning decisions on quantity; rather, you should focus on response time. In Chapter 2, you will learn to query the V$SYSTEM_EVENT view in the order of TIME_WAITED and not TOTAL_WAITS, because quantity not accompanied by time can be deceiving.

Does this mean that you should ignore high resource-consuming SQL statements all together? No. But you should consider them with response time in mind. Start thinking in terms of response time before you undertake any tuning task. That will help you focus on the ‚“big fish ‚½ and prioritize your tuning efforts accordingly . Remember the optimization mantra ‚ response time = service time + wait time ‚ and you won ‚ t see much gain if the service time and wait time already are low.




Oracle Wait Interface
Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning (Osborne ORACLE Press Series)
ISBN: 007222729X
EAN: 2147483647
Year: 2004
Pages: 114

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net