Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Problem with cache usage -> very bad performance?
plm_at_gmx.li wrote:
> 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.
Some questions and statements in no particular order:
Daniel Morgan Received on Mon Feb 24 2003 - 16:01:54 CST