Simple Oracle Query...but slow

Giganews Newsgroups
Subject: Simple Oracle Query...but slow
Posted by:  wwwmi…@gmx.ch
Date: 26 Jan 2004

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.

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

Replies