SQL query takes 6 hours to execute
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 34

Thread: SQL query takes 6 hours to execute

Hybrid View

  1. #1
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322

    SQL query takes 6 hours to execute

    Could you assist me in finding the problematic areas in the attached query?
    Attached Files Attached Files
    lucky

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Do you have Toad? Run it through a tuning lab.

    Why select from the mam_assets table twice for a.id?

    Code:
    FROM MAM_ASSETS A
     11  WHERE ((A.ACL_ID=65536) AND
     12  (A.ASSET_GROUP_XID=120) AND
     13  (A.IS_CURRENT_VERSION=1) AND
     14  A.ID IN ( SELECT A.ID ASSET_ID FROM MAM_ASSETS A
     15             WHERE (A.ID IN (SELECT SV65539.ID FROM MAM_ATTR_STRING_VALUES SV65539

  3. #3
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by stecal View Post
    Do you have Toad? Run it through a tuning lab.

    Why select from the mam_assets table twice for a.id?
    1. I don't have toad. Any other way, I can find out where the time is consumed.
    2. There is a UI based query wizard at the application level that generates this query. Even I spotted the same thing but I don't have any control on it.
    3. I checked the health of indexes involved using 'analyze index validate structure'. They are good.

    Any other comments, I would be grateful.
    lucky

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    trace the session, see where time is being taken - stop guessing

    validating the structure of an index is pointless

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Whats the output of

    Code:
    select ATTRIBUTE_XID,count(*)
    from MAM_ATTR_STRING_VALUES
    group by ATTRIBUTE_XID
    
    select count(*)
    from MAM_ATTR_STRING_VALUES

  6. #6
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    I executed this query in a a session and traced the session.

    Attached is the formatted output after tkprof.

    Mainly time is taken by CPU.

    How can we reduce the execution of this query? It's teasing me a lot now.
    Attached Files Attached Files
    lucky

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by mahajanakhil198 View Post
    Mainly time is taken by CPU.
    Look harder...
    issue is I/O... 217,972,192 buffer_gets
    get execution plan alongside trace-10046.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by PAVB View Post
    Look harder...
    issue is I/O... 217,972,192 buffer_gets
    get execution plan alongside trace-10046.
    "get execution plan alongside trace-10046".
    I have not used it ever. Would you please guide me?
    lucky

  9. #9
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by hrishy View Post
    Hi

    Whats the output of

    Code:
    select ATTRIBUTE_XID,count(*)
    from MAM_ATTR_STRING_VALUES
    group by ATTRIBUTE_XID
    
    select count(*)
    from MAM_ATTR_STRING_VALUES
    Please see the attachment.
    Attached Files Attached Files
    lucky

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    .out is your session log
    .prf is the one where the fun begins
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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