Q

Index scans affecting database file sequential read waits

I found high database file sequential read waits in my Statspack report. When I dug down to find the reason, I...

found that unnecessary index scans may be one of the reasons. (Is this true?) I have many large tables with indexes on duplicate columns, say one index on columns (X,Y) and a second one on columns (Z,X). We access the data using both indexes:

where X=... and Y=...

where Z=... and X=...
1) Should I remove the second index (on Z and X) and create an index on Z only? In Oracle9i, column position is not a problem. I can also use the index skip scan hint to use an index when column without a leading position is used in the WHERE clause. Please suggest to me which of the column indexes should be there.

2) If there are two indexes, one on columns X,Y and second only on Y, should I drop second the index and pass a hint in a query which uses i=only column Y in the WHERE clause? If yes, will this degrade the performance of the SQL?

As you've correctly observed, database file sequential read waits are due to index reads. If your optimizer statistics are up to date, Oracle should use only the index or indexes that will provide the most efficient execution of your query. To determine the proper indexing strategy, you should experiment with different index configurations, observing both the execution plans produced and the run time. Just remember to gather stats on a newly-created index so that the optimizer can use it. I doubt that you'll have to resort to hints.

The key here is to test possible configurations and make your decision based on the test results.

This was last published in April 2005

Dig Deeper on Oracle database performance problems and tuning

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close