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.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Reply With Quote
Bookmarks