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

What about
select * from sometab p
where p.month='31-jan-2003'
and
p.id not in (select q.id from sometab q where q.month='31-dec-2002')

(will only work if id not null ) - should be able to use HASH_AJ and not

that *!$%"£$"£$%$ FILTER "$£%"$£%"$£%" operator .

Sorry. Language extremely foul. Filter not a nice operator. it's oracle's last resort - can't do it any better operator. This is why HASH_AJ (antijoin) was introduced
<plm_at_gmx.li> wrote in message
news:1046182989.79753.0_at_despina.uk.clara.net...
> 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.
Received on Tue Feb 25 2003 - 09:09:57 CST