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

plm_at_gmx.li wrote in message news:<1046121623.65620.0_at_dyke.uk.clara.net>...
> Hello,
>
> On a Solaris 8 with 8GB of ram and oracle 9i I run a query like this:
>
> select * from sometab p
> where p.month='31-jan-2003'
> and not exists (select 1 from sometab q where p.id=q.id and q.month='31-dec-2002')
>
>
> sometab is a table (partitioned on month b.t.w), index on id is being
> used. It contains about 80000 records per 'month' totalling about
> 100MB per 'month'.
>
> The first rows appear pretty quickly, but then the rows start coming
> one by one very slowly (100 rows take about 10 seconds each).
>

100 rows every 10 seconds is way too slow. Other than checking what others suggested (index, explain plan, etc.), also consider reducing logical I/O. This is a good example where performance may be impacted by excessive reads. Look into anti-join,

select * from sometab where month=to_date('31-jan-2003','dd-mon-yyyy') and id not in (select /*+ hash_aj */ id from sometab where   to_date('31-dec-2002','dd-mon-yyyy'));

Use explain plan to make sure partition elimination is done for both the main query and subquery. You will end up with a hash anti-join which uses a lot less logical I/Os than filtering. If your index is global, partition scans may be better than index scan. This works very well for large tables.

BTW, more cache will help too. I would at least up db block buffers 5 or 10 times.

> I had expected that the whole of sometab (at least for the subquery
> for month='31-dec-2002') would have been loaded in RAM, making this
> query pretty fast.
>
> What I observe is that of 8GB only 2GB is being used, an executing
> queries like this don't change anything.
>
> Our db_block_buffers value is only 8000, using 16KB blocks, resulting
> in only 128MB of buffers. Is this way too little, causing this
> problem?
>
> If the table is not being cached at oracle level, I would have
> expected that at least at physical file level (Solaris filesystem
> buffer cache level) the whole 'sometab' table should soon be in RAM,
> resulting in a fast query.
>
> How does Oracle 9i deal with physical files? Does it somehow disable
> the Solaris filesystem cache (could make sense to guarantee
> consistency on disk after commits and because Oracle already does its
> own buffering)? That is, should we set db_block_buffers with this in
> mind?
>
> Thanks,
Received on Tue Feb 25 2003 - 12:11:14 CST