DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 34

Thread: SQL query takes 6 hours to execute

  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,334
    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,555
    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 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

  9. #9
    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

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by mahajanakhil198 View Post
    I have not used it ever. Would you please guide me?
    Two pieces of code...

    1- target sql statement. my_target_sql.sql in this example.
    2- trace shell script. my_trace_script.sh in this example - see below.

    Once you have both of them in place.

    a) set your oracle_home
    b) set your oracle_sid
    c) nohup my_trace_script.sh > my_trace.out &
    d) tkprof trace file on user_dump_destination with sys=no option

    Build your my_trace_script.sh including code below...

    sqlplus -s / < set linesize 180
    set pagesize 0
    set timing on
    spool my_trace_script.out
    select instance_name, host_name from v\$instance;
    drop table PLAN_TABLE;
    @?/rdbms/admin/utlxplan
    alter session set timed_statistics = true;
    alter session set sql_trace = true;
    set autotrace on explain
    alter session set events '10046 trace name context forever, level 12';
    -- execute sql to be traced like...
    @my_target_sql.sql
    --
    alter session set sql_trace = false;
    alter session set timed_statistics = false;
    spool off
    EOF

    Last but not least, if my_target_sql.sql is expected to return a large number of rows you may want to wrap it up on a count shell like...
    select count(*) from(
    --put your sql statement here
    );
    ...after all you are looking for trace/execution plan, not for the actual output.
    Last edited by PAVB; 04-30-2010 at 09:05 AM. Reason: typo
    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