Problem with cache usage -> very bad performance?

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?

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,

-- 
Peter Mutsaers, Dübendorf, Switzerland.