-
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
There is always a better way to do the things.
-
Maybe there is an index on lockwait which is not used by optimizer in the first query but forced to use in second query.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
I too had the CPU pegged at 99% and not allowing any logins to the unix box:-(
Badrinath
There is always a better way to do the things.
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Thanks Jurji for the clear explaination!
Badrinath
There is always a better way to do the things.
-
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
There is always a better way to do the things.
-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Bug No:2591262 Which Oracle says closed becuase similar to Bug No: 2578217
Bug:2591262
Bug:2578217
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
Badrinath :
I think you have opened a thread in metalink & gotta response?
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|