1 Reply Latest reply: Dec 4, 2012 3:24 AM by APC RSS

    Please help me to tune this query

    976208
      Hi Experts,
      I am new to performance tuning.

      Sometimes the following query is taking 10 to 15 minutes of time,
      sometimes the output is coming in seconds based on the data.
      SELECT 
                          BOM.MODEL AS MODEL,
                          BOM.MAJOR_CLASS AS OPTION_CLASS,
                          (select CLASS_DESCRIPTION
                             from APPS_JP.GEDIS_BOM_DESCRIPTION
                            where VK_UNIT = BOM.MAJOR_CLASS
                              and rownum = 1) AS CLASS_DESCRIPTION,
                          (select CLASS_DESCRIPTION
                             from APpS_JP.GEDIS_BOM_DESCRIPTION
                            where VK_UNIT = BOM.MODEL
                              and rownum = 1) AS MODEL_DESCRIPTION,
                          BOM.VK_UNIT AS SKU,
                          BOM.ITEM_TYPE,
                          BOM.LOB,
                          BOM.PRODUCT_GROUP,
                          BOM_DESCRIPTION.SHORT_DESC AS DESCRIPTION,
                          BOM_DESCRIPTION.SHORT_DESC AS EN_DESCRIPTION, 
                          BOM.DESCRIPTION AS LONG_DESCRIPTION,
                          BOM.CLASS_ORDER,
                          PRICE.LIST_PRICE,
                          PRICE.COST_PRICE,
                          CS.MAX_YIELD AS MAX_YIELD,
                          CS.AVG_YIELD AS AVG_YIELD,
                          BOM.DELETE_DOWN AS DELETE_DOWN,
                          DISCOUNTS.DISCOUNTABLE AS DISCOUNTABLE,
                          DISCOUNT_RATE AS MAXIMUM_DISCOUNT,
                          BOM.TAX_CODE,
                          BOM.MAT_CLASS,
                          BOM.OPTION_NO,
                          BOM.SKU_TYPE,
                          BOM.ITEM_SOURCE
                      FROM APPS_JP.GEDIS_BOM BOM
                          INNER JOIN APPS_JP.GEDIS_BOM_DESCRIPTION BOM_DESCRIPTION
                              ON BOM.VK_UNIT = BOM_DESCRIPTION.VK_UNIT 
                                  AND BOM.CLASS = BOM_DESCRIPTION.CLASS 
              AND BOM_DESCRIPTION.LANGUAGE_CODE = 'EN'
                          INNER JOIN APPS_JP.GEDIS_PRICE PRICE
                              ON BOM.VK_UNIT = PRICE.VK_UNIT 
                          INNER JOIN APPS_JP.GEDIS_DISCOUNTS DISCOUNTS
                              ON BOM.PRODUCT_GROUP = DISCOUNTS.PRODUCT_GROUP 
                          INNER JOIN APPS_JP.GEDIS_CLASS_HEURISTICS CS
                              ON BOM.CLASS = CS.CLASS 
                                  AND BOM.MODEL = CS.MODEL 
                                  AND PRICE.CURRENCY = CS.CURRENCY 
                                  AND CS.BUSINESS_SEGMENT = PRICE.BUSINESS_SEGMENT
                      WHERE
                              SKU_TYPE = 'ADJUST' 
                              AND rownum = 1
                              AND NVL(BOM.INACTIVE, 'N') = 'N'
                              AND SYSDATE BETWEEN NVL(BOM.START_DATE_ACTIVE, date '0001-01-01') AND NVL(BOM.END_DATE_ACTIVE, date '3999-12-31');
      The following is the explain plan for the SQL

      SELECT STATEMENT ALL_ROWS Cost: 334 Bytes: 410 Cardinality: 1 
            3 COUNT STOPKEY 
                  2 TABLE ACCESS BY INDEX ROWID TABLE APPS_JP.GEDIS_BOM_DESCRIPTION Cost: 4 Bytes: 156 Cardinality: 2 
                        1 INDEX RANGE SCAN INDEX APPS_JP.GEDIS_BOM_DESCRIPTION_N2 Cost: 3 Cardinality: 5 
            6 COUNT STOPKEY 
                  5 TABLE ACCESS BY INDEX ROWID TABLE APPS_JP.GEDIS_BOM_DESCRIPTION Cost: 4 Bytes: 156 Cardinality: 2 
                        4 INDEX RANGE SCAN INDEX APPS_JP.GEDIS_BOM_DESCRIPTION_N2 Cost: 3 Cardinality: 5 
            22 COUNT STOPKEY 
                  21 NESTED LOOPS OUTER Cost: 334 Bytes: 410 Cardinality: 1 
                        18 NESTED LOOPS Cost: 333 Bytes: 384 Cardinality: 1 
                              15 NESTED LOOPS Cost: 327 Bytes: 584 Cardinality: 2 
                                    12 NESTED LOOPS Cost: 319 Bytes: 798 Cardinality: 3 
                                          10 NESTED LOOPS Cost: 291 Bytes: 510 Cardinality: 2 
                                                7 TABLE ACCESS FULL TABLE APPS_JP.GEDIS_BOM Cost: 285 Bytes: 9,143 Cardinality: 41 
                                                9 TABLE ACCESS BY INDEX ROWID TABLE APPS_JP.GEDIS_CLASS_HEURISTICS_B Cost: 3 Bytes: 32 Cardinality: 1 
                                                      8 INDEX RANGE SCAN INDEX APPS_JP.GEDIS_CLASS_HEURISTICS_N1 Cost: 2 Cardinality: 1 
                                          11 TABLE ACCESS FULL TABLE APPS_JP.GEDIS_DISCOUNTS Cost: 14 Bytes: 22 Cardinality: 2 
                                    14 TABLE ACCESS BY INDEX ROWID TABLE APPS_JP.GEDIS_PRICE Cost: 3 Bytes: 26 Cardinality: 1 
                                          13 INDEX RANGE SCAN INDEX APPS_JP.GEDIS_PRICE Cost: 2 Cardinality: 1 
                              17 TABLE ACCESS BY INDEX ROWID TABLE APPS_JP.GEDIS_BOM_DESCRIPTION Cost: 3 Bytes: 92 Cardinality: 1 
                                    16 INDEX RANGE SCAN INDEX APPS_JP.GEDIS_BOM_DESCRIPTION_U1_MARKT Cost: 2 Cardinality: 1 
                        20 TABLE ACCESS BY INDEX ROWID TABLE APPS_JP.GEDIS_CLASS_ATTRIBUTES Cost: 1 Bytes: 26 Cardinality: 1 
                              19 INDEX UNIQUE SCAN INDEX (UNIQUE) APPS_JP.GEDIS_CLASS_ATTRIBUTES_U1 Cost: 0 Cardinality: 1 
      Explain plan after creating the following indexes.
      CREATE INDEX START_DATE ON GEDIS_BOM (NVL(START_DATE_ACTIVE, date '0001-01-01'))
      
      CREATE INDEX END_DATE_ ON GEDIS_BOM (NVL(END_DATE_ACTIVE, date '3999-12-31'))
      SELECT STATEMENT ALL_ROWS Cost: 202 Bytes: 419 Cardinality: 1 
            3 COUNT STOPKEY 
                  2 TABLE ACCESS BY INDEX ROWID TABLE APPS_JP.GEDIS_BOM_DESCRIPTION Cost: 4 Bytes: 156 Cardinality: 2 
                        1 INDEX RANGE SCAN INDEX APPS_JP.GEDIS_BOM_DESCRIPTION_N2 Cost: 3 Cardinality: 5 
            6 COUNT STOPKEY 
                  5 TABLE ACCESS BY INDEX ROWID TABLE APPS_JP.GEDIS_BOM_DESCRIPTION Cost: 4 Bytes: 156 Cardinality: 2 
                        4 INDEX RANGE SCAN INDEX APPS_JP.GEDIS_BOM_DESCRIPTION_N2 Cost: 3 Cardinality: 5 
            23 COUNT STOPKEY 
                  22 NESTED LOOPS OUTER Cost: 202 Bytes: 419 Cardinality: 1 
                        19 NESTED LOOPS Cost: 201 Bytes: 393 Cardinality: 1 
                              16 NESTED LOOPS Cost: 198 Bytes: 367 Cardinality: 1 
                                    13 NESTED LOOPS Cost: 195 Bytes: 275 Cardinality: 1 
                                          10 HASH JOIN Cost: 192 Bytes: 243 Cardinality: 1 
                                                8 TABLE ACCESS BY INDEX ROWID TABLE APPS_JP.GEDIS_BOM Cost: 173 Bytes: 232 Cardinality: 1 
                                                      7 INDEX RANGE SCAN INDEX APPS_JP.END_DATE_ACTIVE_RAM Cost: 15 Cardinality: 4,542 
                                                9 TABLE ACCESS FULL TABLE APPS_JP.GEDIS_DISCOUNTS Cost: 18 Bytes: 21,098 Cardinality: 1,918 
                                          12 TABLE ACCESS BY INDEX ROWID TABLE APPS_JP.GEDIS_CLASS_HEURISTICS_B Cost: 3 Bytes: 32 Cardinality: 1 
                                                11 INDEX RANGE SCAN INDEX APPS_JP.GEDIS_CLASS_HEURISTICS_N1 Cost: 2 Cardinality: 1 
                                    15 TABLE ACCESS BY INDEX ROWID TABLE APPS_JP.GEDIS_BOM_DESCRIPTION Cost: 3 Bytes: 92 Cardinality: 1 
                                          14 INDEX RANGE SCAN INDEX APPS_JP.GEDIS_BOM_DESCRIPTION_U1_MARKT Cost: 2 Cardinality: 1 
                              18 TABLE ACCESS BY INDEX ROWID TABLE APPS_JP.GEDIS_PRICE Cost: 3 Bytes: 26 Cardinality: 1 
                                    17 INDEX RANGE SCAN INDEX APPS_JP.GEDIS_PRICE Cost: 2 Cardinality: 1 
                        21 TABLE ACCESS BY INDEX ROWID TABLE APPS_JP.GEDIS_CLASS_ATTRIBUTES Cost: 1 Bytes: 26 Cardinality: 1 
                              20 INDEX UNIQUE SCAN INDEX (UNIQUE) APPS_JP.GEDIS_CLASS_ATTRIBUTES_U1 Cost: 0 Cardinality: 1 
      After creating the index cost is reduced to 202 from 334 and the bytes got increased from 410 to 419.

      Is this Query tuned well.

      Please help me to tune this query.

      Thanks in advance.
        • 1. Re: Please help me to tune this query
          APC
          973205 wrote:
          Sometimes the following query is taking 10 to 15 minutes of time,
          sometimes the output is coming in seconds based on the data.
          So what are you expecting from us? Your data obviously has a huge degree of skew in it. So it's going to be pretty hard to produce a single execution path which satisfies all uses.

          The thing you need to start with is isolating which data values produce good performance and which produce bad performance. Categorise them. If you're lucky perhaps they will fall into different usage types which will allow you to offer sets of users different queries.

          If not you'll need to choose a tuning goal. As I said it's unlikely you'll be able to tune this query so it always runs in 10 seconds regardless of data values. So you need to decide (on ask for) an acceptable and realistic outcome. Will bringing the maximum run time down to (say) five minutes justify increasing the minimum run time to (say) one minute. That's a political question more than a technical one.

          Beyond that you should refer to the threads referenced in the Forum's FAQ: {message:id=9360003} .

          Cheers, APC