Re: Table access slow

I have more info:

   I realised that I had a very low cache hit ratio (about 17%) on buffer cache when I execute the problematic query. It's so weird because I can make a select * from table and things go just fine. Cache hit ratio over 80% and results come out in seconds. It's when I execute this query that things go wrong: cache hit ratio starts falling until 30, 20% or even 13% and the query lasts for 7 or 8 hours instead of 2 minutes.
. I increased db_block_buffers from 200 to 5000, but it's the same.   I also put db_block_lru_extended_statistics to 10000. This makes Oracle to report statistics about those 10000 extra buffers. view x$kcbrbh hosts buffer index and extra cache hits that Oracle would have if that buffer exists. It always says that it would use extra buffers.

   It seems like I could be increasing buffer cach for ever and database behaviour would be the same.

I hope that this info is useful for somebody.

Meanwhile I will keep on listening to Springsteen: "No retreat, baby, no Surrender" :-( Received on Mon Feb 09 2004 - 11:43:00 CST