| Please help me to tune this query [message #571913] |
Tue, 04 December 2012 01:21  |
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 #572067 is a reply to message #571922] |
Wed, 05 December 2012 14:35  |
 |
Flyby
Messages: 188
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
