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

dyou98_at_aol.com (D.Y.) writes:

> 100 rows every 10 seconds is way too slow. Other than checking what
> others suggested (index, explain plan, etc.), also consider reducing
> logical I/O. This is a good example where performance may be impacted
> by excessive reads. Look into anti-join,
>
> select * from sometab where month=to_date('31-jan-2003','dd-mon-yyyy')
> and id not in (select /*+ hash_aj */ id from sometab where
> to_date('31-dec-2002','dd-mon-yyyy'));

Yes, as I said in the other reply I shall try to use the optimizer hint, though I don't have much hope since I have hardly ever been able to convince Oracle to do something else via hints :).

> Use explain plan to make sure partition elimination is done for both the
> main query and subquery. You will end up with a hash anti-join which uses
> a lot less logical I/Os than filtering. If your index is global, partition
> scans may be better than index scan. This works very well for large tables.

Another solution in this case was to use a minus: select * from (
select id from sometab where month='31-jan-2003' minus
select id from sometab where month='31-dec-2002' ) a, sometab b
where a.id=b.id and b.month='31-jan-2003'

This was 10 times as fast, but I cannot use it since the real query is much more complex and can be done only with 'and not in (...'.

>
> BTW, more cache will help too. I would at least up db block buffers 5 or
> 10 times.

Yes, I have done this today and it did help. Now I get 100 rows every 2 seconds :) which still is way too slow.

-- 
Peter Mutsaers, Dübendorf, Switzerland.