Craig Shallahamer's Blog
|
You were brought to this page based on an internet search
and as a free service to Oracle DBAs.
The text below is an except from the book,
Oracle Performance Firefighting, written by
Craig Shallahamer of
OraPub, Inc.
Figures and tables are not included on this page, only their reference.
To order the book in either print or PDF form, click
here.
©2009, 2010 by Craig Shallahamer. This is copyrighted material.
Please—Out of respect for those involved in the creation of the book and also for
their familes, we ask you to respect the copyright both in intent and deed. Thank you.
-------------------------------
<p>Figure 8-11 also shows that to Oracle, the average log writer background process write call takes over 200 ms! Here is an example of where OraPub's 3-circle analysis shines. Instead of the usual, "It's the application SQL" or "The IO subsystem is operating poorly," at this point, all we confidently know is that Oracle's sequential IO write requirements have exceeded the IO subsystem's capacity. And, as I'll detail shortly, we can develop solutions from an Oracle, application, and operating system perspective. This is how you build a more cooperative solution and avoid the usual adverse and destructive finger-pointing sessions.
p><p>Figure 8-12 is a good example of how having more than just the average wait can create a more informative and persuasive argument. The OSM swhistx.sql report is based on an interval of time (you simply run the report once, pause as long as you want, and then run it again) and clearly shows only 11.5% of all log writer background process write requests completed in 16 ms or less. If your IO write goal is 5 ms or less, or perhaps 10 ms or less, you're not even close! If you're feeling feisty, you can even say that only around half of all log writer background process writes completed in 64 ms or less (just make sure you didn't cause the problem in the first place).
p><p>If you start telling people write calls are taking over 200 ms, you're asking for a fight, so be prepared. Even if you are correct, you should expect to be aggressively challenged. So make sure you trace the log writer background process in summary, as shown in Figure 8-13, and in detail, as shown in Figure 8-10. Figure 8-10 highlights the log writer background process is issuing 1MB write calls, Oracle is gathering its timing information from the gettimeofday call and also gathering log writer background process CPU consumption details from the times call. If you run an IO subsystem-focused command, such as iostat, its numbers could be significantly different based on IO caching and physical configuration. So focus on how long it takes the Oracle log writer background process to have its writes serviced.
p>
©2009, 2010 by Craig Shallahamer. This is copyrighted material.
Please—Out of respect for those involved in the creation of the book and also for
their familes, we ask you to respect the copyright both in intent and deed. Thank you.
|
|
Know what's important before it's too late!
OraPub's
Performance Training
is like no other...
More Class Pics...
Get student testimonials!
|
