Re: Simple Oracle Query...but slow

wwwmike_at_gmx.ch (michi) wrote in message news:<ec0e7914.0401262050.5fe55412_at_posting.google.com>...
> Hello
>
> I am using a query which takes arround .5 sec to execute.
>
> select usr_id from table1,table2
> where yy = '022021900'
> and table1.usr_id = table2.usr_id
> and table1.zz in ('000001005')
>
> this query takes arround .5 sec ...but...if I write
>
> select usr_id from table1,table2
> where yy = '022021900'
> and table1.usr_id = table2.usr_id
> and table1.zz in ('000001005','000001005') <-- add new (same) value
>
> this query only takes some milliseconds...
>
> I analyzed explain plan and found out that the first query has a much
> greater "consistent get" than the 2nd query.
>

And the plans you get are probably different. If you post them or even the parts of them which are different, somebody might really help you...

> 1 query) Consistent get: 85575
> 2nd query) Consistent get: 9
>
> This problem occured recently on one of our oracle instances. If I use
> the same query in another oracle installation (not the same machine)
> then everything is OK. I also restarted the DB, but with no luck.
>
> Thanks for helping
>
> Michael
Received on Tue Jan 27 2004 - 11:38:52 CST