This discussion is archived
1 2 3 4 5 8 Previous Next 117 Replies Latest reply: May 5, 2008 10:19 AM by 181444 Go to original post RSS
  • 30. Re: query tuning
    599921 Newbie
    Currently Being Moderated
    Deepa,
    Hope you seen the Explain plan and Charles messg. So can you please give me the ide for the next step.I got the statistics as follows.
    Statistics
    -------------------------------------------------------
           2100  recursive calls
             12  db block gets
        3689668  consistent gets
        3602433  physical reads
              0  redo size
       10679182  bytes sent via SQL*Net to client
         191062  bytes received via SQL*Net from client
          27258  SQL*Net roundtrips to/from client
              4  sorts (memory)
              2  sorts (disk)
         408842  rows processed

    Thank you.

    Message was edited by:
            User71408                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
  • 31. Re: query tuning
    599921 Newbie
    Currently Being Moderated
    Charles,

    I have given

    Set autotrace on
    <select stmt>
    O/P: it displays 400000 records.

    what is the next step to do.Pls give me idea for this.

    Thank you.
  • 32. Re: query tuning
    CharlesHooper Expert
    Currently Being Moderated
    Charles,
    Thanks for giving great explanation. Then what is the
    next step to resolve this issue.Please let me know.

    Thank you
    It might be that the cardinaltiy estimates are incorrect - maybe it has been a while since you collected statistics on the tables and indexes. It might be that Oracle needs to resolve/join the view first, rather than last. It might be that predicates are not being pushed into the view. It might be that hints are needed in the view. It might be that you need to eliminate the view. It might be that you need histograms on some of the columns. It might be... and the list goes on.

    Take a look at the 10046 trace. Passing the 10046 trace through TKPROF might help, but it might also hide details that are useful. For example, in the portion of the 10046 trace that I posted, those wait events are pointing at this line of the row source plan, determined by obj=35205:
    STAT #2 id=12 cnt=1781 pid=3 pos=2 obj=35205 op='TABLE ACCESS BY INDEX ROWID EDI830_LINES (cr=143 pr=10 pw=0 time=47302 us)'
    That would tell me that I possibly need to address the cause of that delay, if it is significant (47302/1000000 seconds).

    You might try a hint to resolve/join CASE_SIZE as the first step in the plan, but I would recommend the collection of a 10046 trace at level 8 before and after the use of the hint. To try the idea of the hint, your SQL statement would begin like this:
    SELECT /*+ LEADING(CASE_SIZE) */

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 33. Re: query tuning
    599921 Newbie
    Currently Being Moderated
    Charles ,
    Sorry to ask the same again...Am not a DBA.Am pl/sql developer. So please tell me where we'll give the hint.. can you please let me know.I have given the query. So please give me rough idea with that query.. Please don't mind..

    Thank you.
  • 34. Re: query tuning
    CharlesHooper Expert
    Currently Being Moderated
    Examples of setting a 10046 trace:
    Re: SQL_TRACE help to newbie in oracle

    The above link shows how to enable a 10046 trace at level 12 using various methods - the first method is the most appropriate for you, as you are able to execute the SQL statement in SQL*Plus. After executing your SQL statement, execute a simple SQL statement, as this will make certain the STAT lines from the previous SQL statement are written to the trace file. An example of a simple SQL statement:
    SELECT
    SYSDATE
    FROM
    DUAL;

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 35. Re: query tuning
    599921 Newbie
    Currently Being Moderated
    Charles,
    I have followed otherway as per Deepa's suggestion.
    After getting Statistics,I have run the query again
    then i exec the taables as follows
    exec dbms_stats.gather_table_stats('<TABLE OWNER>,'TABLE_NAME',CASCADE=>TRUE);
    for tables it's succeeded. But for VIew CASE i am getting error. What is the solution for this. After that what i have to follow.Please give me idea for this also. Before that i will do what u have mentioned in the above message.

    Thank you
  • 36. Re: query tuning
    CharlesHooper Expert
    Currently Being Moderated
    It is a good idea to collect statistics as the first step, but as you found out, you cannot connect statistics on views. Instead, you need to collect statistics on the tables and indexes that are used by the view.

    You have an index PK_CASE_UPDATES that is used by the view - gather statistics on that index and the table to which it belongs.

    The view references the table ITEM_SUPPLIER_COUNTRY - gather statistics on that table and its indexes.

    The view uses the index PK_ITEM_SUPPLIER_COUNTRY, make certain that statistics are gathered for that index (it might belong to the table ITEM_SUPPLIER_COUNTRY).

    I might not have listed all of the tables and indexes that need statistics, please double-check the plan that you posted.

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 37. Re: query tuning
    599921 Newbie
    Currently Being Moderated
    Charles,

    I have done what u told in the prevoius message(for statistics).
    Now can I run the script for checking the performance or is there any other procedure to follow. please let me know.

    Thank you,
  • 38. Re: query tuning
    599921 Newbie
    Currently Being Moderated
    Charles,
    I am getting following error when am running the script.
    Data WILL NOT be truncated to match the output schema and the output data file may be improperly formatted.
    but output schema length and input schema lenght are same. but I am getting program failure.Pls give me any idea for this.

    Thank you.
  • 39. Re: query tuning
    CharlesHooper Expert
    Currently Being Moderated
    Google and Metalink searches might be helpful.
    http://www.google.com/search?hl=en&q=Oracle+%22Data+WILL+NOT+be+truncated%22+to+match+the+output+schema+and+the+output+data+file+may+be+improperly+formatted

    A Metalink search for the exact phrase "Data WILL NOT be truncated" (without quotes) finds several hits, including Note:340193.1, Subject: RETL Flow Causes Fatal E100 Error java.lang.ArrayIndexOutOfBoundsException

    Take a look at those documents, and maybe you will find the source of the problem with the assistance of one of those documents. Or, maybe someone else has a suggestion.

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc
  • 40. Re: query tuning
    599921 Newbie
    Currently Being Moderated
    Hi ,
    I am not getting required result using Statistics. So could you please let me know after doing Explain plan what we have to do.Deepa and all please help me in this issue.

    Thank you.
  • 41. Re: query tuning
    599921 Newbie
    Currently Being Moderated
    Deepa,
    I have sent u the execution plan.Then what will do for this .Please let me know. I have posted Statistics also.Pls help me in this.

    Thank you.
  • 42. Re: query tuning
    599921 Newbie
    Currently Being Moderated
    How to use HINTS for this query . I have used as follows.
    SELECT /*+All_ROWS*/ ,Stmt>
    what is the next step for checking the increment in Performance.

    Thank you.
  • 43. Re: query tuning
    CharlesHooper Expert
    Currently Being Moderated
    How to use HINTS for this query . I have used as
    follows.
    SELECT /*+All_ROWS*/ ,Stmt>
    what is the next step for checking the increment in
    Performance.

    Thank you.
    I am not sure where you received the suggestion for using the All_ROWS hint. In one of my replies to you, I did suggest to you to try using the leading hint to force the CASE_SIZE view to be resolved first, the results of which will drive into the MASTER table. That may or may not help. One of the problems with the CASE_SIZE view is that you have views that depend on views, and those views depend on other views - the predicates from the tables in the query may not be pushing deep enough into the multiple levels of views. You may need to rewrite the CASE_SIZE view to improve performance, and better yet include the logic of the optimized version of the view as an inline view in your SQL statement. You can start determining the SQL that makes up the view by executing the following when connected to the database as the view owner:
    SELECT
      *
    FROM
      USER_VIEWS
    WHERE
      VIEW_NAME='CASE_SIZE';
    You will likely see other tables and views referenced in the SQL statement, continue collecting the SQL statements for the other views referenced.

    To convert the view into an inline view, in your main SQL statement, find CASE_SIZE in the FROM clause. Directly in front of CASE_SIZE type ( then type the SQL statement from the view, then type ) followed by a space - you aliased CASE_SIZE as CS, so you can then delete the word CASE_SIZE. For example, assume that you found that the CASE_SIZE view contains the following SQL statement (note that it definitely does not):
    SELECT
      SYSDATE
    FROM
      DUAL;
    To convert the CASE_SIZE view to an inline view, you would change your original SQL statement to:
    SELECT
      IM.LOCATION,
      IM.ITEM,
      CS.CASE_SIZE,
      IM.ONDATE,
      IM.OFFDATE,
      MAS.STATUS
    FROM
      (SELECT
        SYSDATE
      FROM
        DUAL) CS,
      ITEM IM,
      MASTER MAS,
      WAREHOUSE WH
    WHERE
      MAS.PACK_IND = 'N'
      AND MAS.ITEM_LEVEL = MAS.TRNS_LEVEL
      AND MAS.STATUS = 'A'
      AND MAS.FOREIND = 'Y'
      AND MAS.ITEM = IM.ITEM
      AND IM.LOCATION_TYPE = 'S'
      AND MAS.ITEM = CS.ITEM
      AND CS.SUPPLY_INDICATOR = 'Y'
      AND CS.COUNTRY_INDICATOR = 'Y'
      AND IM.LOCATION =WH.WAREHOSE_NO
      AND NVL(WH.CLOSE_DATE,'04-APR-9999')>=TO_DATE(&VERSDATE}, 'YYYYMMDD')
    The one hint that I suggested is:
    SELECT /*+ LEADING(CASE_SIZE) */

    Please, re-read all of my responses in this thread. I have suggested a couple other steps to take to tune this SQL statement. You may need the involvement of the DBA.

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc
  • 44. Re: query tuning
    CharlesHooper Expert
    Currently Being Moderated
    Minor correction to my previous post, as I noticed that the CASE_SIZE view is aliased as CS. The hint needs to use the alias name, NOT the view name. With the hint that I suggested trying, the SQL statement would begin like this:
    SELECT /*+ LEADING(CS) */

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
1 2 3 4 5 8 Previous Next