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).
>
> 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?

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.

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.

select * from sometab p
 where p.month=to_date('31-jan-2003','dd-mon-yyyy') and not exists (select 1 from sometab q where p.id=q.id and q.month=to_date('31-dec-2002','dd-mon-yyyy');

all this assumes month is a date column. Is the index on id global or partitioned

--
Niall Litchfield
Oracle DBA
Audit Commission UK