Re: Problem with cache usage -> very bad performance?

From: <plm_at_gmx.li>
Date: 25 Feb 2003 15:56:46 +0100
Message-Id: <1046185007.96803.0@damia.uk.clara.net>

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> writes:

> Almost certainly this value is too small (though I wouldn't expect it to
> affect this query especially). I'd also like to see the breakdown of the SGA
> since you seem to say that Oracle's memory usage is 2gb of which only 128mb
> is allocated to the buffer cache.

No, of 4GB total only 1.5GB was in use in total, of which less than 500MB by oracle. The SGA overall was very small.

All relevant parameters from init.ora are:

bitmap_merge_area_size                  = 8388608
db_block_buffers                        = 8000
db_block_size                           = 16384
hash_area_size                          = 33554432
log_buffer                              = 172032
log_checkpoint_interval                 = 11500
shared_pool_size                        = 104857600
sort_area_retained_size                 = 4194304
sort_area_size                          = 4194304

java_pool_size= 25971520
large_pool_size= 9000000

I don know if these settings make sense on a 4GB or 8GB machine. We have our own DBA group but I doubting if they know what theye doing :)

directio appears not to have been enabled. Should we? : #_filesystemio_options = directio

Is there much difference between filesystem level caching (UFS filesystem) and oracls own caching in db_block_buffers? I would guess the latter can be much more specific and thus much more efficient.

>
> As for the particular query, I would expect Oracle to be able to do
> partition elimination with a slightly modified version of the query and with
> statistics in place.

Yes, it does. In fact I did use a real date in the real query (no implicit conversion) and the explain plan tells me everything runs optimally, but slowly which is why I suspecting 'physical'causes.

Thank you,

-- 
Peter Mutsaers, Dübendorf, Switzerland.

Received on Tue Feb 25 2003 - 08:56:46 CST