Simple query - taking long
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Simple query - taking long

  1. #1
    Join Date
    Jan 2001
    Posts
    642

    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.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,460

    Cool

    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

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  4. #4
    Join Date
    Jan 2001
    Posts
    642
    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.

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  6. #6
    Join Date
    Jan 2001
    Posts
    642
    Thanks Jurji for the clear explaination!

    Badrinath
    There is always a better way to do the things.

  7. #7
    Join Date
    Jan 2001
    Posts
    642
    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.

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  9. #9
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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"

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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
  •  



Click Here to Expand Forum to Full Width