Simple query - taking long

Hi,
I have this simple query
select distinct e.id1
from v$session d, v$lock e
where d.lockwait = e.kaddr;

which is taking for ever, where as when I issue

select distinct e.id1
from v$session d, v$lock e
where d.lockwait = e.kaddr and d.lockwait is not null;

it comes back in a minute.

What could be the reason?
Badrinath

Maybe there is an index on lockwait which is not used by optimizer in the first query but forced to use in second query.

I tried to run the query it never came back, but one significant thing i see is CPU utilazation was 100%.

When I killed the session, CPU utilization was back to normal 2 to 3%.
I wonder this strange behaviour.

BTW, plan for the above 2 queries are same ( Every fixed table going for table scan ), so thers no Q of index being used or not.

Abhay.

I too had the CPU pegged at 99% and not allowing any logins to the unix box:-(

Badrinath

It's obviously a bug in Oracle's optimizer.

If you observe the explain plan of your queries, you'll notice that the scan of V$SESSION is performed by executing self merge join (CARTESIAN!) of the fixed table X$KSUSE. But if you look at the explain plan of the simple "SELECT * FROM V$SESSION" you'll noticed that the explain plan show only a simple full scan of X$KSUSE. So Oracle has absolutely no justifiable reason to do that self join (cartesian merge join) in your query.

Now why is jour second query comming out *much* quicker? Your second query is probably returning only a few rows (if any at all), because you probably don't have many sessions waiting on locks. So for example if you have only few records with non-null value of LOCKWAIT in V$SESSION, that cartesian merge join is performed quickly. If there is no rows with LOCKWAIT, it doesn't have to perform that merge join at all. But if you don't have that LOCKWAIT IS NULL condition in your query, the cartesian product in merge join could take forever!

If you want to avoid that and get a quick responce for your first query, ad a hint:

Code:

---

select /*+ ORDERED */ distinct e.id1
from v$session d, v$lock e
where d.lockwait = e.kaddr;

---

If you look at the explain plan of this hinted query you'll notice the merge join for V$SESSION dissapears (as it shouldn't be there in the first place).

You can file a bug as it surely is.

Thanks Jurji for the clear explaination!:)

Badrinath

But I just wonder, why should a cartisan product of 20 X 20 take a long time.

I have 20 records in the v$session and around ~19 records in v$lock. Please clarify.

Badrinath

If even one row has LOCKWAIT non NULL, then your second query also goes for 100% CPU utilization. Try it.

Only if you have hint ( Ordered ) as suggested by Jurij is coming out irrespective of LOCKWAIT being null or not.

Bug No:2591262 Which Oracle says closed becuase similar to Bug No: 2578217

Bug:2591262

Bug:2578217

Badrinath :

I think you have opened a thread in metalink & gotta response?

Abhay.

Thanks Abhay,

As usuall, the response from Metalink was way behind this discussion forum...I rely more on this....
Thank you very much...for all

Badrinath