Ask the Expert

Too many buffer reads

I have Oracle version 9.2 with cost-based optimizer. Statistics are collected frequently. The query is tuned perfectly. My problem is that performance is slow and shows too many buffer reads in statspack. What is the root cause of high buffer gets if there is no table full scan? Please help.

    Requires Free Membership to View

Your statspack report will show you the SQL statement associated with a high number of buffer gets. Examine the optimizer plan for this query to understand the reasons for them. It may be that the query is not "perfectly tuned"; your goal should be to reduce logical I/Os. Tracing the SQL in question can also help you identify the source of excessive buffer gets.

This was first published in September 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: