Home » RDBMS Server » Performance Tuning » Importance of index unique scan (cost=0) (Oracle 10g R2, HPUX)
Importance of index unique scan (cost=0) [message #318593] Wed, 07 May 2008 04:32 Go to next message
santoshorcl
Messages: 24
Registered: August 2007
Location: pune
Junior Member

Hi,

As i saw few times cost of index unique scan shows cost=0. And i found such queries are performing slow. I check stats for all indexes/tables but stats was proper even though queries are not performing well.I am attaching plan. Please suggest me what to do.
  • Attachment: explan.txt
    (Size: 6.44KB, Downloaded 217 times)
Re: Importance of index unique scan (cost=0) [message #318629 is a reply to message #318593] Wed, 07 May 2008 06:20 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Are you sure your statistics are correct?

Reember that costs are just estimates, not an accurate predictor of absolute performance.

Also that even if an index scan only finds one row, it still must hit the table to get the rest of the data (in your example). BUT, then it must repeat that once for every time through the nested loop. So a one item index fetch that is fairly fast can all of a sudden be quite slow if repeated a million times.

Might want to run a 10046 trace and tkprof on it, and look in particular at the row source operation lines. Check those row counts against the estimated cardinalities in your explain plan. Does each and every step really only return one row?
Re: Importance of index unique scan (cost=0) [message #318639 is a reply to message #318629] Wed, 07 May 2008 07:07 Go to previous message
santoshorcl
Messages: 24
Registered: August 2007
Location: pune
Junior Member

Thanks for quick reply.

I am sure about stats of indexes/tables.
I too don't believe 100% on cost. But I didn't get the reason why the index unique scan cost shows 0 few times.
Previous Topic: Oracle Compression Question
Next Topic: impact of this parameter bloom_filter_enabled =false
Goto Forum:
  


Current Time: Sun Dec 11 02:09:37 CST 2016

Total time taken to generate the page: 0.08767 seconds