1 2 3 4 5 Previous Next 117 Replies Latest reply: May 5, 2008 12:19 PM by 181444 Go to original post RSS
      • 30. Re: query tuning
        599921
        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
          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
            Charles Hooper
            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
              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
                Charles Hooper
                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
                  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
                    Charles Hooper
                    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
                      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
                        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
                          Charles Hooper
                          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
                            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
                              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
                                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
                                  Charles Hooper
                                  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
                                    Charles Hooper
                                    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 Previous Next