DBAsupport.com Forums - Powered by vBulletin
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 34

Thread: SQL query takes 6 hours to execute

  1. #11
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by PAVB View Post
    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.
    Thanks.
    So, I will need to look into two files at the end of execution of this script.
    1. The generated spool file (so as to see the execution file).
    2. The formatted output file after TKPROFing the generated trace file.
    lucky

  2. #12
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    .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.

  3. #13
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Check if this gets you the same result as your original query

    Code:
    SELECT TO_CHAR(A.ID) A118,A.ASSET_GROUP_XID A2,A.ACL_ID A106,A.OWNER A4,CONVERT(A.NAME,'WE8MACROMAN8') A1,
    A.FIRST_VERSION_CREATED_BY A15,LPAD(LTRIM(TO_CHAR(A.FIRST_VERSION_DATE_CREATED,'SYYYYMMDDHH24MISS')),15,'+') A16,
    LPAD(LTRIM(TO_CHAR(A.DATE_CREATED,'SYYYYMMDDHH24MISS')),15,'+') A18,CONVERT(A.CHECKOUT_LOCATION,'WE8MACROMAN8') A24,
    TO_CHAR(A.ASSET_SIZE) A7,A.CHECKED_OUT_BY A23,A.ARCHIVE_STATUS A27,A.STORAGE_CHANGE_REQUEST A110,A.HAS_HISTORY A74,
    TO_CHAR(A.CONTAINED_IN_COUNT) A73,TO_CHAR(A.CONTAINER_OF_COUNT) A72,TO_CHAR(A.RELATED_WITH_COUNT) A71,
    CONVERT(A.FILE_TYPE,'WE8MACROMAN8') A12,CONVERT(A.FILE_CREATOR_APPLICATION,'WE8MACROMAN8') A13,
    CONVERT(A.FILE_EXTENSION,'WE8MACROMAN8') A9,TO_CHAR(A.REVISION) A21,A.IS_CURRENT_VERSION A109,
    TO_CHAR(A.FAMILY_ID) A112,LPAD(LTRIM(TO_CHAR(A.FILE_DATE_CREATED,'SYYYYMMDDHH24MISS')),15,'+') A11,
    LPAD(LTRIM(TO_CHAR(A.FILE_DATE_LAST_MODIFIED,'SYYYYMMDDHH24MISS')),15,'+') A10
    FROM MAM_ASSETS A
    WHERE ((A.ACL_ID=65536) AND
    (A.ASSET_GROUP_XID=120) AND
    (A.IS_CURRENT_VERSION=1) AND
    EXISTS
    (SELECT SV65539.ID FROM MAM_ATTR_STRING_VALUES SV65539
    WHERE (SV65539.LOWER_CASE_STRING_VALUE LIKE 
    LOWER(CONVERT('%813982%','WE8MSWIN1252','WE8MACROMAN8')))
    AND   (SV65539.ATTRIBUTE_XID=65539)
    AND  A.ID=SV65539.ID)
    ORDER BY A.ASSET_SIZE
    Also see if you can or already have a index on

    MAM_ATTR_STRING_VALUES (ID,ATTRIBUTE_XID,LOWER_CASE_STRING_VALUE )
    Last edited by hrishy; 05-01-2010 at 01:29 AM.

  4. #14
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by PAVB View Post
    .out is your session log
    .prf is the one where the fun begins
    Here are the two files attached.
    Could you please guide me on how I could proceed further?

    I was seeing the trace file. Can you please tell what is 'pr', 'pw' and 'us' (which is unit of time, I guess it is microsecond)?
    Attached Files Attached Files
    lucky

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

    Check if this gets you the same result as your original query

    Code:
    SELECT TO_CHAR(A.ID) A118,A.ASSET_GROUP_XID A2,A.ACL_ID A106,A.OWNER A4,CONVERT(A.NAME,'WE8MACROMAN8') A1,
    A.FIRST_VERSION_CREATED_BY A15,LPAD(LTRIM(TO_CHAR(A.FIRST_VERSION_DATE_CREATED,'SYYYYMMDDHH24MISS')),15,'+') A16,
    LPAD(LTRIM(TO_CHAR(A.DATE_CREATED,'SYYYYMMDDHH24MISS')),15,'+') A18,CONVERT(A.CHECKOUT_LOCATION,'WE8MACROMAN8') A24,
    TO_CHAR(A.ASSET_SIZE) A7,A.CHECKED_OUT_BY A23,A.ARCHIVE_STATUS A27,A.STORAGE_CHANGE_REQUEST A110,A.HAS_HISTORY A74,
    TO_CHAR(A.CONTAINED_IN_COUNT) A73,TO_CHAR(A.CONTAINER_OF_COUNT) A72,TO_CHAR(A.RELATED_WITH_COUNT) A71,
    CONVERT(A.FILE_TYPE,'WE8MACROMAN8') A12,CONVERT(A.FILE_CREATOR_APPLICATION,'WE8MACROMAN8') A13,
    CONVERT(A.FILE_EXTENSION,'WE8MACROMAN8') A9,TO_CHAR(A.REVISION) A21,A.IS_CURRENT_VERSION A109,
    TO_CHAR(A.FAMILY_ID) A112,LPAD(LTRIM(TO_CHAR(A.FILE_DATE_CREATED,'SYYYYMMDDHH24MISS')),15,'+') A11,
    LPAD(LTRIM(TO_CHAR(A.FILE_DATE_LAST_MODIFIED,'SYYYYMMDDHH24MISS')),15,'+') A10
    FROM MAM_ASSETS A
    WHERE ((A.ACL_ID=65536) AND
    (A.ASSET_GROUP_XID=120) AND
    (A.IS_CURRENT_VERSION=1) AND
    EXISTS
    (SELECT SV65539.ID FROM MAM_ATTR_STRING_VALUES SV65539
    WHERE (SV65539.LOWER_CASE_STRING_VALUE LIKE 
    LOWER(CONVERT('%813982%','WE8MSWIN1252','WE8MACROMAN8')))
    AND   (SV65539.ATTRIBUTE_XID=65539)
    AND  A.ID=SV65539.ID)
    ORDER BY A.ASSET_SIZE
    Also see if you can or already have a index on

    MAM_ATTR_STRING_VALUES (ID,ATTRIBUTE_XID,LOWER_CASE_STRING_VALUE )


    I cannot change the query. It is generated by the application by the UI of user session. Moreover, the development on application has stopped and it is only under support plan. Thanks for your effort anyways.
    Last edited by mahajanakhil198; 05-03-2010 at 10:42 AM.
    lucky

  6. #16
    Join Date
    May 2002
    Posts
    2,645
    Quote Originally Posted by mahajanakhil198 View Post
    Here are the two files attached.
    Could you please guide me on how I could proceed further?

    I was seeing the trace file. Can you please tell what is 'pr', 'pw' and 'us' (which is unit of time, I guess it is microsecond)?
    Put fingers on keyboard and type/search. Open book(s) and read.
    http://www.oracle-base.com/articles/...dTkprof10g.php

  7. #17
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Are your stats upto date on all the tables involved
    How do you collect stats ?
    is there a way to get back to the development team on this ?

    Whats the acceptable time within which this query should finish ?

  8. #18
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Answers are inline.

    Quote Originally Posted by hrishy View Post
    Hi

    Are your stats upto date on all the tables involved --- Yes
    How do you collect stats ? ---- There is an automated job that runs every day at 12 midnight and it uses dbms_stats.gather_schema_stats.
    is there a way to get back to the development team on this ? --- No

    Whats the acceptable time within which this query should finish ? --- Probably 5 minutes as the UI based user session logs off with an error if it doesn't get the results within this time frame. There is no way this value can be increased.
    lucky

  9. #19
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    I noticed a strange thing.

    "select count(*) from ....." version of the posted query executes in 2 seconds. There seems to be some problem with the select list. I tried to spot it out using the binary method but the problem seems to be with multiple columns in select list.

    Description of MAM_ASSETS is attached.

    I am trying to fix it up with this information. Please let me know if you have any comments regarding the same.
    Attached Files Attached Files
    lucky

  10. #20
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Did it ever run in less then 5 minutes ?
    (your users are very patient to wait for 5 minutes )

    Have you thought of a MV

    Code:
    CREATE MATERIALIZED VIEW LOG ON MAM_ATTR_STRING_VALUES WITH ROWID
    
    CREATE MATERIALIZED VIEW MAM_ATTR_STRING_VALUES_MV
    CLUSTER m_lt_li (id)
    REFRESH FAST ON COMMIT
    ENABLE QUERY REWRITE
    AS
    SELECT SV65539.ID 
    FROM MAM_ATTR_STRING_VALUES SV65539
    WHERE SV65539.LOWER_CASE_STRING_VALUE LIKE LOWER(CONVERT('%813982%','WE8MSWIN1252','WE8MACROMAN8'))
    AND   SV65539.ATTRIBUTE_XID=65539

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