Home » RDBMS Server » Performance Tuning » Please help me to tune this query (Oracle 11g)
Please help me to tune this query [message #571913] Tue, 04 December 2012 01:21 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

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.

Re: Please help me to tune this query [message #571922 is a reply to message #571913] Tue, 04 December 2012 05:33 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Any body can please help me.
Re: Please help me to tune this query [message #572067 is a reply to message #571922] Wed, 05 December 2012 14:35 Go to previous message
Flyby
Messages: 143
Registered: March 2011
Location: Belgium
Senior Member
Can you create an index that includes both enddate and startdate instead of making 2 seperate indexes?

Personally I would rewrite AND SYSDATE BETWEEN NVL(BOM.START_DATE_ACTIVE, date '0001-01-01') AND NVL(BOM.END_DATE_ACTIVE, date '3999-12-31');
to
and (
 (bom.start_date_active<=sysdate and bom.end_date_active>=sysdate)
   or (bom.start_date_active is null and bom.end_date_active>=sysdate)
   or (bom.end_date_active is null and bom.start_date_active<=sysdate)
   or (bom.end_date_active is null and bom.end_date_active is null)
)

and have an index ix_mydateindex(end_date_active,start_date_active,not-null column). Non-nullable column added for statistics
Previous Topic: need more memory for hash joins
Next Topic: RBS usage and longops in toad
Goto Forum:
  


Current Time: Thu Jul 24 01:42:26 CDT 2014

Total time taken to generate the page: 0.15836 seconds