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

DA Morgan <damorgan_at_exesolutions.com> writes:

> > 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'.
> >
>
> Some questions and statements in no particular order:
>
> 1. Did you run DBMS_STATS to gather statistics for the cost-based optimizer?
> 2. You should not rely on implicit conversion ... use TO_DATE
> 3. Did you intend the date in the coorelated subquery to be different from the date in
> the parent?
> 4. Are there indexes? Local or global?
> 5. Have you run EXPLAIN PLAN? If so ... post it.
>
> Daniel Morgan

Hello,

thanks a lot for your attention/questions.

1. yes
2. in the real query the dates are date parameters, so there is no conversion going on
3. yes
4. local indexes
5. yes, the plan shows the correct (local) index is being used:

SELECT STATEMENT, GOAL = CHOOSE 476 1 296  FILTER
  PARTITION RANGE SINGLE
   TABLE ACCESS FULL BIS PORTFOLIO 476 1 296   PARTITION RANGE SINGLE

   TABLE ACCESS BY LOCAL INDEX ROWID	BIS	PORTFOLIO	26	22	242
    INDEX RANGE SCAN	BIS	PORTFOLIO_IDX2	1	22	

(formatting somewhat unclear, sorry for that).

The real table is named portfolio, and the index portfolio_idx2 is a local index on the id field.

By the way I did increase db_block_buffers from 8000 to 80000 and performance is much better now. In fact it runs 2-3 times as fast on our old and small 4GB machine, compared to our fast new machine with 8GB of RAM.

So I do suspect that it is beneficial for Oracle to take a large part of the table and/or index in buffer cache to quickly execute the subquery so many times.

Still I am wondering why the cache at filesystem level is not being used. At least it looks like it since performance went up a lot when I increased the db_block_buffers. Otherwise one would expect the filesytem level cache to help as well and there would not be a large difference when increasing this parameters.

-- 
Peter Mutsaers, Dübendorf, Switzerland.