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>The application situation has profoundly changed. Oracle is now processing fewer logical IOs while at the same time executing more SQL statements. This means users are getting more work done but consuming fewer resources! The addition of the status column index had a much larger and positive impact than we anticipated. Clearly, there were other SQL statements that benefited from the index creation. Over the 30-minute interval, we anticipated Oracle CPU consumption would decrease from 1,257 seconds down to 1,027 seconds, but in reality, the consumption decreased to a staggering 344 seconds.
p><p>Comparing the top SQL statements in Figure 9-29 (before index addition) with Figure 9-34, notice there is now a new top logical IO-consuming SQL statement along with the previous number three statement (SQL_ID ending in ggt). If performance is to be further improved, we have once again clearly identified (and supported by an OraPub 3-circle ORTA-focused approach) the next two SQL statements to address.
p><p>Figure 9-34. Shown is the essential application SQL information. Because of the index addition, the targeted high logical IO SQL statements no longer appear on the top SQL report! In addition, the top logical IO SQL statements now consume 8.4M and 4.1M logical IOs, compared to the earlier case where the top two statements consumed 22.6M and 10.3M logical IOs, respectively (Figure 9-29). The status column index has had a profound impact on the most resource-consuming SQL.
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!
|
