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.
PleaseOut 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.
-------------------------------
* Oracle focused: If the desired block had been found in the Oracle buffer cache, Oracle would not have asked the operating system for the block. Therefore, a larger Oracle buffer cache would reduce the likelihood of a db file scattered read. Of course, there needs to be both memory available and CPU available to manage the increased buffer cache. Other possibilities are using the keep or recycle pools, or other Oracle instance-focused solutions to increase the likelihood of the block being found in Oracle's buffer cache.
* Operating system-focused: From an operating system perspective, there is a high likelihood the CPU subsystem is the bottleneck. This is very common when the top event is IO read-related yet the operating system satisfies the request in only a couple of milliseconds. If the IO subsystem is part of the database server (think small Linux server), this is likely. For large systems with a separate IO subsystem, this is less likely. If there is a CPU bottleneck, look for ways to increase the CPU capacity.
* Application-focused: From an application perspective, there absolutely must be SQL asking for blocks that do not reside in Oracle's buffer cache. If the blocks were in the cache, the server process would not have issued a multiblock read request resulting in the db file scattered read wait event. To find the responsible SQL, look for the SQL with the most block reads (sometimes called block gets or what I call physical IO). The SQL must be there, and you will see it. To reduce the physical IO pressure the application is placing on Oracle, which then impacts the operating system, tune the SQL or reduce its execution rate.
©2009, 2010 by Craig Shallahamer. This is copyrighted material.
PleaseOut 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.
|