1 4 5 6 7 8 Previous Next 117 Replies Latest reply: May 5, 2008 12:19 PM by 181444 Go to original post RSS
      • 105. Re: query tuning
        599375
        I feel like the Problem is due to Table Fragmentation :

        Do the following -------

        ALTER TABLE <TABLE_NAME> MOVE;

        SQLPLUS USERNAME/PASSWORD AS SYSDBA

        SPOOL ON
        SPOOL C:\REBUILD.SQL


        select 'alter INDEX '||owner||'.'||INDEX_NAME||' REBUILD ONLINE;' from dba_INDEXES where TABLE_NAME like '<TABLE_NAME>' AND OWNER LIKE '<SCHEMA_NAME>';

        SPOOL OFF

        SQLPLUS>@C:\REBUILD.SQL
        • 106. Re: query tuning
          Charles Hooper
          How did you reach the conclusion that the problem is caused by "table fragmentation"? As far as I can tell, there have been no statistics that suggest the existence of "table fragmentation".

          There are issues with the number of physical and logical reads, the number of rows transferred to the client, the client fetching only 15 rows at a time, the time required for single block reads - some of which apprear to be accessing the undo tablespace for consistent reads, the apparent requirement to perform a full tablescan on a large partitioned table, and possibly the server's time being adjusted (possibly twice) during the 42 minute execution time.

          Charles Hooper
          IT Manager/Oracle DBA
          K&M Machine-Fabricating, Inc.
          • 107. Re: query tuning
            599921
            Charles,

            Still the problem was not resolved. So please guide me for this issue.

            Thank you
            • 108. Re: query tuning
              599921
              Charles,
              I need an idea from you. We are using VIEW and SQL Query in the script.Is it possible to write PL/SQL code for the same script.If possible please give me idea related to that.Is it possible to reduce teh time by using PL/SQL?
              Please let me know..

              Thank you.
              • 109. Re: query tuning
                Jonathan Lewis
                You have a query that is reporting something like 16 million rows from a total of about 180 Milllion rows - how long do you think it should take to deliver the result to the front-end program, and what are you going to do with the data ?

                You seem to have about 3.8M blocks in the item_loc table - and need about 200,000 physical read requests at 16 blocks per read to scan it. Assume a modest 1/100 sec for a 16 block read (and you seem to have a severe disk load problem - so that's being kind) and you will need about 2,000 seconds to do the tablescan of the 180 Million rows.

                Since you seem to have about 45 rows per block, a query that picks up one in 10 will (if the data is uniformly spread) HAVE to visit every block in the table, so the tablescan is (almost) inevitable.

                You need to restructure your database if you want to find these 16M rows more quickly, or get more discs into play, or do silly things with memory, and then start working on parallel execution.

                How many different values do you have for item_loc.loc_type ? and how many rows for each value ?

                What indexes exist on item_loc ?


                Regards
                Jonathan Lewis
                http://jonathanlewis.wordpress.com
                http://www.jlcomp.demon.co.uk
                • 110. Re: query tuning
                  26741
                  Yikes ! Hadn't noticed the output row count (I saw 12million in one tkprof listing,
                  but what the heck -- 12 million or 16 million , either case is very large !)

                  12million or 16million * row_length results in some large output. Where is
                  it going to ? Since Charles has already observed that the arraysize seems to
                  be 15, going to a client with SQL*Net message waits, I wonder what the OP
                  is writing TO after reading from the database.
                  • 111. Re: query tuning
                    Charles Hooper
                    Charles,
                    I need an idea from you. We are using VIEW and SQL
                    Query in the script.Is it possible to write PL/SQL
                    code for the same script.If possible please give me
                    idea related to that.Is it possible to reduce teh
                    time by using PL/SQL?
                    Please let me know..

                    Thank you.
                    User71408,

                    It appears that you are working with a product named "Oracle Retail Advanced Inventory Planning" or "Oracle Retail Merchandising System", or something similar. I have no experience with these products, but maybe that information will help Jonathan determine exactly what additional optimizations are available in the database system.

                    http://download.oracle.com/docs/cd/E05438_01/aip/pdf/120/aip-120-impg.pdf
                    http://download.oracle.com/docs/cd/B25958_01/RMS/pdf/1109/rms-1109-og-addendum.pdf
                    http://download-west.oracle.com/docs/cd/B31318_01/rms/pdf/1201/rms-1201-og1.pdf
                    http://download.oracle.com/docs/cd/B31318_01/rms/pdf/1203/rms-1203-og1.pdf

                    Let's take a look at your WHERE clause, slightly reformatted:
                    WHERE
                      IEM.PACK_IND = 'N'
                      AND IEM.ITEM_LEVEL = IEM.TRAN_LEVEL
                      AND IEM.STATUS = 'A'
                      AND IEM.FORECAST_IND = 'Y'
                      AND IEM.ITEM = ITL.ITEM
                      AND ITL.LOC_TYPE = 'S'
                      AND IEM.ITEM = VCS.ITEM
                      AND VCS.PRIMARY_SUPP_IND = 'Y'
                      AND VCS.PRIMARY_COUNTRY_IND = 'Y'
                      AND ITL.LOC=S.STORE
                      AND NVL(S.STORE_CLOSE_DATE,'04-APR-4444')>=TO_DATE(20080403, 'YYYYMMDD')
                    Your WHERE clause appears to be similar to this under "Filtering Conditions" on Page 146 in the PDF:
                    http://download.oracle.com/docs/cd/E05438_01/aip/pdf/120/aip-120-impg.pdf
                      IM.ITEM_LEVEL = IM.TRAN_LEVEL
                      AND IM.STATUS = 'A'
                      AND IL.ITEM = IM.ITEM
                      AND (
                        (IM.PACK_IND = 'N' AND IM.FORECAST_IND = 'Y')
                        OR (IM.SIMPLE_PACK_IND = 'Y' AND IM.ITEM IN (
                          SELECT
                            PM.PACK_NO
                          FROM
                            ITEM_MASTER IM1,
                            PACKITEM PM
                          WHERE
                            PM.ITEM = IM1.ITEM
                            AND IM1.FORECAST_IND = 'Y')))
                      AND IL.LOC_TYPE = 'S'
                      AND IL.LOC = S.STORE
                      AND S.STORE_OPEN_DATE <= TO_DATE('${VDATE}', 'YYYYMMDD')
                      AND NVL(S.STORE_CLOSE_DATE,'04-APR-4444') >= TO_DATE('${VDATE}', 'YYYYMMDD')
                      AND IM.INVENTORY_IND = 'Y'
                      AND NOT(IM.SELLABLE_IND = 'Y' AND IM.ORDERABLE_IND = 'N')
                    On Page 171 in the same PDF, note the use of TO_DATE('${VDATE}','YYYYMMDD')+1), which at least to me is better than using '04-APR-4444' for the reasons that I have previously mentioned:
                    Filtering Conditions
                      ISC.PRIMARY_COUNTRY_IND = 'Y'
                      AND IM.ITEM = ISC.ITEM
                      AND IM.ITEM = ISUP.ITEM
                      AND IM.STATUS = 'A'
                      AND IM.TRAN_LEVEL = IM.ITEM_LEVEL
                      AND IM.INVENTORY_IND = 'Y'
                      AND IM.AIP_CASE_TYPE = 'I'
                      AND IM.PACK_IND = 'N'
                      AND IM.FORECAST_IND = 'Y'
                      AND ISUP.SUPPLIER = ISC.SUPPLIER
                      AND NVL(ISUP.SUPP_DISCONTINUE_DATE,TO_DATE('${VDATE}','YYYYMMDD')+1) > TO_DATE('${VDATE}','YYYYMMDD')
                    Other examples from that PDF:
                    Page 96 in the PDF
                      ISC.PRIMARY_COUNTRY_IND = 'Y'
                      AND IM.ITEM = ISC.ITEM
                      AND IM.ITEM = ISUP.ITEM
                      AND IM.STATUS = 'A'
                      AND IM.TRAN_LEVEL = IM.ITEM_LEVEL
                      AND IM.INVENTORY_IND = 'Y'
                      AND IM.AIP_CASE_TYPE = 'I'
                      AND IM.PACK_IND = 'N'
                      AND IM.FORECAST_IND = 'Y'
                      AND ISUP.SUPPLIER = ISC.SUPPLIER
                      AND NVL(ISUP.SUPP_DISCONTINUE_DATE, TO_DATE('${VDATE}','YYYYMMDD')+1) > TO_DATE('${VDATE}','YYYYMMDD')

                    Page 94 in the PDF
                    Filtering Conditions
                      IM.ITEM = ISUP.ITEM
                      AND IM.STANDARD_UOM=UC.UOM
                      AND IM.HANDLING_TEMP=CD.CODE(+)
                      AND ISUP.ITEM=ISC.ITEM
                      AND ISUP.SUPPLIER=ISC.SUPPLIER AND
                      IM.PACK_IND='N'
                      AND ISC.SUPP_PACK_SIZE> 1
                      AND IM.STATUS='A'
                      AND IM.ITEM_LEVEL=IM.TRAN_LEVEL
                      AND IM.FORECAST_IND = 'Y'
                      AND IM.INVENTORY_IND = 'Y'
                    Maybe the above will help you see a different way of putting together the query. As has been pointed out, you are retrieving to the client 12,295,541 rows, 15 rows at a time. See if you are able to increase the number of rows retrieved at one time from 15 to a larger number, such as 100.

                    Charles Hooper
                    IT Manager/Oracle DBA
                    K&M Machine-Fabricating, Inc.
                    • 112. Re: query tuning
                      599921
                      >
                      note the use of TO_DATE('${VDATE}','YYYYMMDD')+1), which at least to me is better than using '04-APR-4444' for the reasons that I have previously mentioned:


                      can you please explain me what is the advantage of using "vdate+1" instead of
                      "'04-APR-4444' "

                      Thank you
                      • 113. Re: query tuning
                        Charles Hooper
                        >
                        note the use of TO_DATE('${VDATE}','YYYYMMDD')+1),
                        which at least to me is better than using
                        '04-APR-4444' for the reasons that I have previously
                        mentioned:
                        can you please explain me what is the advantage of
                        using "vdate+1" instead of
                        "'04-APR-4444' "

                        Thank you
                        From one of my replies to you on Apr 18, 2008:

                        Another thing that I noticed is this in the WHERE clause:
                        AND NVL(s.STORE_CLOSE_DATE,'04-APR-4444')>=TO_DATE(20080403, 'YYYYMMDD')
                        You are using TO_DATE on the right of ">=", but not in the NVL function. Also, you are attempting to use a date that is far in the future.

                        Try changing this line in the WHERE clause so that it is
                        AND NVL(s.STORE_CLOSE_DATE,TO_DATE(20080403, 'YYYYMMDD'))>=TO_DATE(20080403, 'YYYYMMDD')
                        In other words, put exactly what is on the right of ">=" inside the NVL function.


                        I seem to recall a couple reasons from Jonathan Lewis' "Cost-Based Oracle Fundamentals" book related to severely out of bounds values (very large date values), and what problems those values cause for cost calculations. That problem may not apply in this case due to the use of the NVL function and the hints that Jonathan provided to you. For those with Jonathan's book, take a look at pages 124, 128-130, 178.

                        The other potential problem is the use of '04-APR-4444' without specifically telling Oracle how to handle that set of numbers and letters - TO_DATE was not used. There is the potential that Oracle will not handle this set of numbers and letters as a date without using the TO_DATE function.

                        Charles Hooper
                        IT Manager/Oracle DBA
                        K&M Machine-Fabricating, Inc.
                        • 114. Re: query tuning
                          599921
                          Charles,
                          I have tried with all the filter conditions what u have sent.But still performance was not increased.If I am using to_date(date,'yyyymmdd')+1) ,it's taking more time.
                          Still now the problem was not resolved.Plese give me any more ideas.

                          Thank you.
                          • 115. Re: query tuning
                            277993
                            Did you try any of my suggestions above or provide answers to my questions?
                            • 116. Re: query tuning
                              599921
                              Hi ! I have tried every ones suggestions.One thing I am not able to try.i.e DBMS_STATS .because I have no privilies to using stats.

                              Thank you
                              • 117. Re: query tuning
                                181444
                                If you do not have privilege to execute dbms_stats then ask the DBA to
                                1- issue the statement for you and/or 2- grant you execute permission on the package if possible.

                                HTH -- Mark D Powell --

                                Message was edited by: Mark to add CR
                                mpowel01
                                1 4 5 6 7 8 Previous Next