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.
-------------------------------
Based solely on this information, you would expect to find a few SQL statements dominating block read consumption. Looking at the SQL statements, sorted by block reads, you see that two statements clearly are consuming the majority of block read requests. The connection between the Oracle and the application subsystems has been made.
Notice that you have not pointed a finger at anyone; therefore, it's unlikely that you have offended members of any group and caused them to take an immediate defensive posture. There is a very high likelihood that performance-improving changes can be made to both the SQL and Oracle. So right away, two teams can get involved in solving the problem!
From an operating system perspective, because Oracle processes are waiting for blocks outside Oracle's buffer cache and on average take 22 ms8 to be received from the IO subsystem, you can clearly expect an IO bottleneck. You have not implied that there is a problem with the IO subsystem or that the SQL is poorly written, but the fact remains that it takes 22 ms to complete a multiple-block IO call.
©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.
|