Home » RDBMS Server » Performance Tuning » query tuning (10g/windows)
| query tuning [message #426042] |
Tue, 13 October 2009 23:39  |
ayush_anand
Messages: 417
Registered: November 2008
|
Senior Member |
|
|
Here is the query
SELECT
HR.HR_ALL_ORGANIZATION_UNITS.ATTRIBUTE2,
GL.GL_CODE_COMBINATIONS.SEGMENT1,
GL.GL_CODE_COMBINATIONS.SEGMENT2,
GL.GL_CODE_COMBINATIONS.SEGMENT3,
GL.GL_CODE_COMBINATIONS.SEGMENT4,
GL.GL_CODE_COMBINATIONS.SEGMENT5,
GL.GL_CODE_COMBINATIONS.segment6,
INV.MTL_SYSTEM_ITEMS_B.DESCRIPTION,
INV.MTL_SYSTEM_ITEMS_B.SEGMENT1,
AR.RA_CUSTOMER_TRX_ALL.INVOICE_CURRENCY_CODE,
AR.RA_CUST_TRX_LINE_GL_DIST_ALL.AMOUNT,
AR.RA_CUST_TRX_LINE_GL_DIST_ALL.ACCTD_AMOUNT,
NULL,
NULL,
'',
'',
GL.GL_SETS_OF_BOOKS.CURRENCY_CODE,
AR.RA_CUSTOMER_TRX_LINES_ALL.QUANTITY_ORDERED,
(SELECT ATTRIBUTE_VALUE FROM APPS.XXG_GAAP_ITEM_ATTRIBUTE
WHERE SKU_NUM = INV.MTL_SYSTEM_ITEMS_B.SEGMENT1 AND ATTRIBUTE_CODE = 'FINDURATIN' AND EFFECTIVE_END_DATE IS NULL),
(SELECT ATTRIBUTE_VALUE FROM APPS.XXG_GAAP_ITEM_ATTRIBUTE
WHERE SKU_NUM = INV.MTL_SYSTEM_ITEMS_B.SEGMENT1 AND ATTRIBUTE_CODE = 'FINSTRTDAT' AND EFFECTIVE_END_DATE IS NULL),
XXG.XXG_GAAP_ITEM_ATTRIBUTE.ATTRIBUTE_VALUE,
(SELECT XXG.ATTRIBUTE_VALUE FROM
APPS.XXG_GAAP_ITEM_ATTRIBUTE XXG
WHERE XXG.SKU_NUM = INV.MTL_SYSTEM_ITEMS_B.SEGMENT1 AND XXG.ATTRIBUTE_CODE = 'FINCOGSTRE' and XXG.effective_end_date is null),
AR.RA_CUSTOMER_TRX_ALL.CT_REFERENCE
FROM
HR.HR_ALL_ORGANIZATION_UNITS,
GL.GL_CODE_COMBINATIONS,
INV.MTL_SYSTEM_ITEMS_B,
AR.RA_CUSTOMER_TRX_ALL,
AR.RA_CUST_TRX_LINE_GL_DIST_ALL,
GL.GL_SETS_OF_BOOKS,
AR.RA_CUSTOMER_TRX_LINES_ALL,
XXG.XXG_GAAP_ITEM_ATTRIBUTE,
(
SELECT 'OP' Status_flag
,Current_Flag Current_Previous
,period_num
,Period
,period_year
,month_start_date
,month_end_date
,quarter_num
,quarter_end_date
FROM (SELECT case
when TRUNC(sysdate) between TRUNC(glp.start_date) and TRUNC(glp.end_date) then 'C' -- current period
when TRUNC(glp.end_date) = (Select TRUNC(MAX(al2.start_date) - 1)
FROM gl.gl_periods al2
WHERE (NOT al2.period_num = 13)
AND al2.period_set_name = 'GlobalCalendar'
AND TRUNC(sysdate) >= TRUNC(al2.start_date)) then 'P' -- previous period
else null
end current_flag
,glp.period_num
,glp.period_name Period
,glp.period_year
,glp.start_date month_start_date
,glp.end_date month_end_date
,glp.quarter_num
,glq.quarter_end_date
FROM gl.gl_periods glp
,(SELECT MAX(end_date) quarter_end_date
,period_year
,quarter_num
,period_set_name
FROM gl.gl_periods
GROUP BY period_year
,quarter_num
,quarter_start_date
,period_set_name
) glq
WHERE glp.period_year = glq.period_year
AND glp.quarter_num = glq.quarter_num
AND glp.period_set_name = glq.period_set_name
AND (NOT glp.period_num = 13)
AND glp.period_set_name = 'GlobalCalendar'
)
WHERE current_flag IN ('P')
) PERIODS_DETAILS_CURR_PREV
WHERE
( AR.RA_CUST_TRX_LINE_GL_DIST_ALL.CODE_COMBINATION_ID=GL.GL_CODE_COMBINATIONS.CODE_COMBINATION_ID )
AND ( AR.RA_CUST_TRX_LINE_GL_DIST_ALL.GL_DATE between PERIODS_DETAILS_CURR_PREV.MONTH_START_DATE and PERIODS_DETAILS_CURR_PREV.MONTH_END_DATE )
AND ( AR.RA_CUST_TRX_LINE_GL_DIST_ALL.ORG_ID=HR.HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID )
AND ( AR.RA_CUSTOMER_TRX_ALL.SET_OF_BOOKS_ID=GL.GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID
)
AND ( AR.RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID(+)=AR.RA_CUST_TRX_LINE_GL_DIST_ALL.CUSTOMER_TRX_LINE_ID )
AND ( AR.RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID=AR.RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID )
AND ( AR.RA_CUSTOMER_TRX_LINES_ALL.INVENTORY_ITEM_ID=INV.MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID )
AND ( AR.RA_CUSTOMER_TRX_LINES_ALL.ORG_ID=INV.MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID )
AND ( XXG.XXG_GAAP_ITEM_ATTRIBUTE.SKU_NUM=INV.MTL_SYSTEM_ITEMS_B.SEGMENT1 )
AND ( XXG.XXG_GAAP_ITEM_ATTRIBUTE.INVENTORY_ITEM_ID=INV.MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID )
AND
(
HR.HR_ALL_ORGANIZATION_UNITS.ATTRIBUTE11 In ('APJ')
AND
PERIODS_DETAILS_CURR_PREV.CURRENT_PREVIOUS In ('P')
AND
AR.RA_CUST_TRX_LINE_GL_DIST_ALL.ATTRIBUTE13 = 'Initial'
AND
AR.RA_CUST_TRX_LINE_GL_DIST_ALL.ATTRIBUTE11 In ( 'R77','R83','R79','R101','R104' )
AND
AR.RA_CUSTOMER_TRX_ALL.interface_header_context = 'ORDER ENTRY'
AND
AR.RA_CUSTOMER_TRX_ALL.CREATED_FROM = 'RAXTRX'
AND
XXG.XXG_GAAP_ITEM_ATTRIBUTE.attribute_code = 'FINREVCLS'
)
the execution plan is
Execution Plan
----------------------------------------------------------
Plan hash value: 3503208966
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 454 | 75210 (1)| 00:15:03 | | |
|* 1 | TABLE ACCESS BY INDEX ROWID | XXG_GAAP_ITEM_ATTRIBUTE | 1 | 35 | 4 (0)
|* 2 | INDEX RANGE SCAN | XXG_GAAP_ITEM_ATTRIBUTE_N1 | 1 | | 3 (0)| 00:00:0
|* 3 | TABLE ACCESS BY INDEX ROWID | XXG_GAAP_ITEM_ATTRIBUTE | 1 | 35 | 4 (0)
|* 4 | INDEX RANGE SCAN | XXG_GAAP_ITEM_ATTRIBUTE_N1 | 1 | | 3 (0)| 00:00:0
|* 5 | TABLE ACCESS BY INDEX ROWID | XXG_GAAP_ITEM_ATTRIBUTE | 1 | 35 | 4 (0)
|* 6 | INDEX RANGE SCAN | XXG_GAAP_ITEM_ATTRIBUTE_N1 | 1 | | 3 (0)| 00:00:0
| 7 | HASH GROUP BY | | 1 | 454 | 75210 (1)| 00:15:03 | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | XXG_GAAP_ITEM_ATTRIBUTE | 1 | 51 | 3 (0
| 9 | NESTED LOOPS | | 1 | 454 | 75205 (1)| 00:15:03 | | |
| 10 | NESTED LOOPS | | 1 | 403 | 75202 (1)| 00:15:03 | | |
| 11 | NESTED LOOPS | | 1 | 379 | 75201 (1)| 00:15:03 | | |
| 12 | NESTED LOOPS | | 1 | 330 | 75200 (1)| 00:15:03 | | |
| 13 | NESTED LOOPS | | 1 | 245 | 75198 (1)| 00:15:03 | | |
| 14 | NESTED LOOPS | | 1 | 225 | 75197 (1)| 00:15:03 | | |
| 15 | NESTED LOOPS | | 1 | 162 | 75195 (1)| 00:15:03 | | |
| 16 | NESTED LOOPS | | 1 | 130 | 75193 (1)| 00:15:03 | | |
| 17 | MERGE JOIN | | 1 | 100 | 75190 (1)| 00:15:03 | | |
| 18 | SORT JOIN | | 63 | 2961 | 75184 (1)| 00:15:03 | | |
|* 19 | TABLE ACCESS FULL | RA_CUST_TRX_LINE_GL_DIST_ALL | 63 | 2961 | 75183 (1)| 00
|* 20 | FILTER | | | | | | | |
|* 21 | SORT JOIN | | 2 | 106 | 5 (20)| 00:00:01 | | |
|* 22 | TABLE ACCESS FULL | GL_PERIODS | 2 | 106 | 4 (0)| 00:00:01 |
| 23 | SORT AGGREGATE | | 1 | 26 | | | | |
|* 24 | TABLE ACCESS FULL | GL_PERIODS | 12 | 312 | 4 (0)| 00:00:01 | |
|* 25 | TABLE ACCESS BY INDEX ROWID| GL_PERIODS | 3 | 90 | 3 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | GL_PERIODS_U2 | 13 | | 2 (0)| 00:00:01 |
| 27 | PARTITION HASH ITERATOR | | 1 | 32 | 2 (0)| 00:00:01 | KEY | KEY
|* 28 | TABLE ACCESS BY INDEX ROWID| RA_CUSTOMER_TRX_LINES_ALL | 1 | 32 | 2 (0)| 00
|* 29 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_LINES_U1 | 1 | | 1 (0)| 00:0
| 30 | PARTITION HASH ITERATOR | | 1 | 63 | 2 (0)| 00:00:01 | KEY | KEY
|* 31 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL | 1 | 63 | 2 (0)| 00:00:
|* 32 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_U1 | 1 | | 1 (0)| 00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID | GL_SETS_OF_BOOKS | 1 | 20 | 1 (0)| 00:00:01
|* 34 | INDEX UNIQUE SCAN | GL_SETS_OF_BOOKS_U2 | 1 | | 0 (0)| 00:00:01 |
| 35 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B | 1 | 85 | 2 (0)|
|* 36 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 1 | | 1 (0)| 00:00:01 |
| 37 | TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS | 1 | 49 | 2 (0)
|* 38 | INDEX UNIQUE SCAN | GL_CODE_COMBINATIONS_U1 | 1 | | 0 (0)| 00
|* 39 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS | 1 | 24 | 1
|* 40 | INDEX UNIQUE SCAN | HR_ORGANIZATION_UNITS_PK | 1 | | 0 (0)| 00
|* 41 | INDEX RANGE SCAN | XXG_GAAP_ITEM_ATTRIBUTE_UN | 1 | | 2 (0)| 00:00
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EFFECTIVE_END_DATE" IS NULL)
2 - access("SKU_NUM"=:B1 AND "ATTRIBUTE_CODE"='FINDURATIN')
3 - filter("EFFECTIVE_END_DATE" IS NULL)
4 - access("SKU_NUM"=:B1 AND "ATTRIBUTE_CODE"='FINSTRTDAT')
5 - filter("XXG"."EFFECTIVE_END_DATE" IS NULL)
6 - access("XXG"."SKU_NUM"=:B1 AND "XXG"."ATTRIBUTE_CODE"='FINCOGSTRE')
8 - filter("XXG_GAAP_ITEM_ATTRIBUTE"."SKU_NUM"="MTL_SYSTEM_ITEMS_B"."SEGMENT1")
19 - filter("RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE13"='Initial' AND
"RA_CUST_TRX_LINE_GL_DIST_ALL"."CUSTOMER_TRX_LINE_ID" IS NOT NULL AND ("RA_CUST_TRX_LINE_GL_D
"RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R104' OR "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIB
"RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R79' OR "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBU
20 - filter("RA_CUST_TRX_LINE_GL_DIST_ALL"."GL_DATE"<="GLP"."END_DATE")
21 - access(INTERNAL_FUNCTION("RA_CUST_TRX_LINE_GL_DIST_ALL"."GL_DATE")>=INTERNAL_FUNCTION("GLP"."
filter(INTERNAL_FUNCTION("RA_CUST_TRX_LINE_GL_DIST_ALL"."GL_DATE")>=INTERNAL_FUNCTION("GLP"."
22 - filter("GLP"."PERIOD_NUM"<>13 AND "GLP"."PERIOD_SET_NAME"='GlobalCalendar' AND CASE WHEN
(TRUNC(SYSDATE@!)>=TRUNC(INTERNAL_FUNCTION("GLP"."START_DATE")) AND
TRUNC(SYSDATE@!)<=TRUNC(INTERNAL_FUNCTION("GLP"."END_DATE"))) THEN 'C' WHEN TRUNC(INTERNAL_FU
(SELECT TRUNC(MAX("AL2"."START_DATE")-1) FROM "GL"."GL_PERIODS" "AL2" WHERE "AL2"."PERIOD_NUM
TRUNC(INTERNAL_FUNCTION("AL2"."START_DATE"))<=TRUNC(SYSDATE@!) AND "AL2"."PERIOD_SET_NAME"='G
NULL END ='P')
24 - filter("AL2"."PERIOD_NUM"<>13 AND TRUNC(INTERNAL_FUNCTION("AL2"."START_DATE"))<=TRUNC(SYSDATE
"AL2"."PERIOD_SET_NAME"='GlobalCalendar')
25 - filter("GLP"."QUARTER_NUM"="QUARTER_NUM")
26 - access("PERIOD_SET_NAME"='GlobalCalendar' AND "GLP"."PERIOD_YEAR"="PERIOD_YEAR")
filter("GLP"."PERIOD_YEAR"="PERIOD_YEAR")
28 - filter("RA_CUSTOMER_TRX_LINES_ALL"."INVENTORY_ITEM_ID" IS NOT NULL)
29 - access("RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_LINE_ID"="RA_CUST_TRX_LINE_GL_DIST_ALL"."CUS
31 - filter("RA_CUSTOMER_TRX_ALL"."INTERFACE_HEADER_CONTEXT"='ORDER ENTRY' AND
"RA_CUSTOMER_TRX_ALL"."CREATED_FROM"='RAXTRX')
32 - access("RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_ID"="RA_CUSTOMER_TRX_ALL"."CUSTOMER_TRX_ID")
34 - access("RA_CUSTOMER_TRX_ALL"."SET_OF_BOOKS_ID"="GL_SETS_OF_BOOKS"."SET_OF_BOOKS_ID")
36 - access("RA_CUSTOMER_TRX_LINES_ALL"."INVENTORY_ITEM_ID"="MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_I
"RA_CUSTOMER_TRX_LINES_ALL"."ORG_ID"="MTL_SYSTEM_ITEMS_B"."ORGANIZATION_ID")
38 - access("RA_CUST_TRX_LINE_GL_DIST_ALL"."CODE_COMBINATION_ID"="GL_CODE_COMBINATIONS"."CODE_COMB
39 - filter("HR_ALL_ORGANIZATION_UNITS"."ATTRIBUTE11"='APJ')
40 - access("RA_CUST_TRX_LINE_GL_DIST_ALL"."ORG_ID"="HR_ALL_ORGANIZATION_UNITS"."ORGANIZATION_ID")
41 - access("XXG_GAAP_ITEM_ATTRIBUTE"."INVENTORY_ITEM_ID"="MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_ID"
"XXG_GAAP_ITEM_ATTRIBUTE"."ATTRIBUTE_CODE"='FINREVCLS')
|
|
|
|
| Re: query tuning [message #426045 is a reply to message #426042] |
Tue, 13 October 2009 23:43  |
ayush_anand
Messages: 417
Registered: November 2008
|
Senior Member |
|
|
the problem is if I replace
AND ( AR.RA_CUST_TRX_LINE_GL_DIST_ALL.GL_DATE between PERIODS_DETAILS_CURR_PREV.MONTH_START_DATE and PERIODS_DETAILS_CURR_PREV.MONTH_END_DATE )
with
AND ( AR.RA_CUST_TRX_LINE_GL_DIST_ALL.GL_DATE between SYSDATE-30 and SYSDATE )
the excution plan becomes lot more better to look at
Execution Plan
----------------------------------------------------------
Plan hash value: 573003422
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 454 | 4996 (1)| 00:01:00 | | |
|* 1 | TABLE ACCESS BY INDEX ROWID | XXG_GAAP_ITEM_ATTRIBUTE | 1 | 35 | 4 (0)| 00:0
|* 2 | INDEX RANGE SCAN | XXG_GAAP_ITEM_ATTRIBUTE_N1 | 1 | | 3 (0)| 00:00:01 | | |
|* 3 | TABLE ACCESS BY INDEX ROWID | XXG_GAAP_ITEM_ATTRIBUTE | 1 | 35 | 4 (0)| 00:0
|* 4 | INDEX RANGE SCAN | XXG_GAAP_ITEM_ATTRIBUTE_N1 | 1 | | 3 (0)| 00:00:01 | | |
|* 5 | TABLE ACCESS BY INDEX ROWID | XXG_GAAP_ITEM_ATTRIBUTE | 1 | 35 | 4 (0)| 00:0
|* 6 | INDEX RANGE SCAN | XXG_GAAP_ITEM_ATTRIBUTE_N1 | 1 | | 3 (0)| 00:00:01 | | |
| 7 | HASH GROUP BY | | 1 | 454 | 4996 (1)| 00:01:00 | | |
|* 8 | FILTER | | | | | | | |
|* 9 | TABLE ACCESS BY INDEX ROWID | GL_PERIODS | 1 | 53 | 3 (0)| 00:00:01 | |
| 10 | NESTED LOOPS | | 1 | 454 | 4991 (1)| 00:01:00 | | |
| 11 | MERGE JOIN CARTESIAN | | 1 | 401 | 4988 (1)| 00:01:00 | | |
|* 12 | TABLE ACCESS BY INDEX ROWID | XXG_GAAP_ITEM_ATTRIBUTE | 1 | 51 | 3 (0)|
| 13 | NESTED LOOPS | | 1 | 371 | 4984 (1)| 00:01:00 | | |
| 14 | NESTED LOOPS | | 1 | 320 | 4981 (1)| 00:01:00 | | |
| 15 | NESTED LOOPS | | 1 | 300 | 4980 (1)| 00:01:00 | | |
| 16 | NESTED LOOPS | | 1 | 237 | 4978 (1)| 00:01:00 | | |
| 17 | NESTED LOOPS | | 1 | 152 | 4976 (1)| 00:01:00 | | |
| 18 | NESTED LOOPS | | 1 | 120 | 4974 (1)| 00:01:00 | | |
| 19 | NESTED LOOPS | | 1 | 71 | 4972 (1)| 00:01:00 | | |
|* 20 | TABLE ACCESS BY INDEX ROWID| RA_CUST_TRX_LINE_GL_DIST_ALL | 1 | 47 | 4971 (
|* 21 | INDEX RANGE SCAN | RA_CUST_TRX_LINE_GL_DIST_N2 | 88150 | | 549 (1)| 00:00:07 |
|* 22 | TABLE ACCESS BY INDEX ROWID| HR_ALL_ORGANIZATION_UNITS | 1 | 24 | 1 (0)|
|* 23 | INDEX UNIQUE SCAN | HR_ORGANIZATION_UNITS_PK | 1 | | 0 (0)| 00:00:01 | |
| 24 | TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS | 1 | 49 | 2 (0)| 00:
|* 25 | INDEX UNIQUE SCAN | GL_CODE_COMBINATIONS_U1 | 1 | | 0 (0)| 00:00:01 | | |
| 26 | PARTITION HASH ITERATOR | | 1 | 32 | 2 (0)| 00:00:01 | KEY | KEY |
|* 27 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_LINES_ALL | 1 | 32 | 2 (0)|
|* 28 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_LINES_U1 | 1 | | 1 (0)| 00:00:01 |
| 29 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B | 1 | 85 | 2 (0)| 00:00:
|* 30 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 1 | | 1 (0)| 00:00:01 | | |
| 31 | PARTITION HASH ITERATOR | | 1 | 63 | 2 (0)| 00:00:01 | KEY | KEY |
|* 32 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL | 1 | 63 | 2 (0)| 00:00
|* 33 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_U1 | 1 | | 1 (0)| 00:00:01 | KEY |
| 34 | TABLE ACCESS BY INDEX ROWID | GL_SETS_OF_BOOKS | 1 | 20 | 1 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | GL_SETS_OF_BOOKS_U2 | 1 | | 0 (0)| 00:00:01 | | |
|* 36 | INDEX RANGE SCAN | XXG_GAAP_ITEM_ATTRIBUTE_UN | 1 | | 2 (0)| 00:00:01 | | |
| 37 | BUFFER SORT | | 260 | 7800 | 4985 (1)| 00:01:00 | | |
|* 38 | TABLE ACCESS FULL | GL_PERIODS | 260 | 7800 | 4 (0)| 00:00:01 | | |
|* 39 | INDEX RANGE SCAN | GL_PERIODS_U2 | 12 | | 2 (0)| 00:00:01 | | |
| 40 | SORT AGGREGATE | | 1 | 26 | | | | |
|* 41 | TABLE ACCESS FULL | GL_PERIODS | 12 | 312 | 4 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EFFECTIVE_END_DATE" IS NULL)
2 - access("SKU_NUM"=:B1 AND "ATTRIBUTE_CODE"='FINDURATIN')
3 - filter("EFFECTIVE_END_DATE" IS NULL)
4 - access("SKU_NUM"=:B1 AND "ATTRIBUTE_CODE"='FINSTRTDAT')
5 - filter("XXG"."EFFECTIVE_END_DATE" IS NULL)
6 - access("XXG"."SKU_NUM"=:B1 AND "XXG"."ATTRIBUTE_CODE"='FINCOGSTRE')
8 - filter(SYSDATE@!-30<=SYSDATE@!)
9 - filter("GLP"."QUARTER_NUM"="QUARTER_NUM" AND CASE WHEN (TRUNC(SYSDATE@!)>=TRUNC(INTERNAL_FUN
AND TRUNC(SYSDATE@!)<=TRUNC(INTERNAL_FUNCTION("GLP"."END_DATE"))) THEN 'C' WHEN TRUNC(INTERNA
(SELECT TRUNC(MAX("AL2"."START_DATE")-1) FROM "GL"."GL_PERIODS" "AL2" WHERE "AL2"."PERIOD_NUM
TRUNC(INTERNAL_FUNCTION("AL2"."START_DATE"))<=TRUNC(SYSDATE@!) AND "AL2"."PERIOD_SET_NAME"='G
END ='P')
12 - filter("XXG_GAAP_ITEM_ATTRIBUTE"."SKU_NUM"="MTL_SYSTEM_ITEMS_B"."SEGMENT1")
20 - filter("RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE13"='Initial' AND "RA_CUST_TRX_LINE_GL_DIST_A
IS NOT NULL AND ("RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R101' OR "RA_CUST_TRX_LINE_GL_
"RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R77' OR "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBU
"RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R83'))
21 - access("RA_CUST_TRX_LINE_GL_DIST_ALL"."GL_DATE">=SYSDATE@!-30 AND "RA_CUST_TRX_LINE_GL_DIST_A
22 - filter("HR_ALL_ORGANIZATION_UNITS"."ATTRIBUTE11"='APJ')
23 - access("RA_CUST_TRX_LINE_GL_DIST_ALL"."ORG_ID"="HR_ALL_ORGANIZATION_UNITS"."ORGANIZATION_ID")
25 - access("RA_CUST_TRX_LINE_GL_DIST_ALL"."CODE_COMBINATION_ID"="GL_CODE_COMBINATIONS"."CODE_COMB
27 - filter("RA_CUSTOMER_TRX_LINES_ALL"."INVENTORY_ITEM_ID" IS NOT NULL)
28 - access("RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_LINE_ID"="RA_CUST_TRX_LINE_GL_DIST_ALL"."CUS
30 - access("RA_CUSTOMER_TRX_LINES_ALL"."INVENTORY_ITEM_ID"="MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_I
"RA_CUSTOMER_TRX_LINES_ALL"."ORG_ID"="MTL_SYSTEM_ITEMS_B"."ORGANIZATION_ID")
32 - filter("RA_CUSTOMER_TRX_ALL"."INTERFACE_HEADER_CONTEXT"='ORDER ENTRY' AND "RA_CUSTOMER_TRX_AL
33 - access("RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_ID"="RA_CUSTOMER_TRX_ALL"."CUSTOMER_TRX_ID")
35 - access("RA_CUSTOMER_TRX_ALL"."SET_OF_BOOKS_ID"="GL_SETS_OF_BOOKS"."SET_OF_BOOKS_ID")
36 - access("XXG_GAAP_ITEM_ATTRIBUTE"."INVENTORY_ITEM_ID"="MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_ID"
"XXG_GAAP_ITEM_ATTRIBUTE"."ATTRIBUTE_CODE"='FINREVCLS')
38 - filter("PERIOD_SET_NAME"='GlobalCalendar')
39 - access("GLP"."PERIOD_SET_NAME"='GlobalCalendar' AND "GLP"."PERIOD_YEAR"="PERIOD_YEAR")
filter("GLP"."PERIOD_NUM"<>13 AND "GLP"."PERIOD_YEAR"="PERIOD_YEAR")
41 - filter("AL2"."PERIOD_NUM"<>13 AND TRUNC(INTERNAL_FUNCTION("AL2"."START_DATE"))<=TRUNC(SYSDATE
"AL2"."PERIOD_SET_NAME"='GlobalCalendar')
Can you please suggest what I am missing here
|
|
|
|
| Re: query tuning [message #426050 is a reply to message #426045] |
Wed, 14 October 2009 00:25 |
ayush_anand
Messages: 417
Registered: November 2008
|
Senior Member |
|
|
In production these are the exec plans
date coming from subquery
Execution Plan
----------------------------------------------------------
Plan hash value: 2701487423
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 520 | 2177K (1)| 07:15:29 |
|* 1 | TABLE ACCESS BY INDEX ROWID | XXG_GAAP_ITEM_ATTRIBUTE | 1 | 33 | 4 (
|* 2 | INDEX RANGE SCAN | XXG_GAAP_ITEM_ATTRIBUTE_N1 | 1 | | 3 (0)| 00:00
|* 3 | TABLE ACCESS BY INDEX ROWID | XXG_GAAP_ITEM_ATTRIBUTE | 1 | 33 | 4 (
|* 4 | INDEX RANGE SCAN | XXG_GAAP_ITEM_ATTRIBUTE_N1 | 1 | | 3 (0)| 00:00
|* 5 | TABLE ACCESS BY INDEX ROWID | XXG_GAAP_ITEM_ATTRIBUTE | 1 | 33 | 4 (
|* 6 | INDEX RANGE SCAN | XXG_GAAP_ITEM_ATTRIBUTE_N1 | 1 | | 3 (0)| 00:00
| 7 | HASH GROUP BY | | 1 | 520 | 2177K (1)| 07:15:29 |
|* 8 | TABLE ACCESS BY INDEX ROWID | XXG_GAAP_ITEM_ATTRIBUTE | 1 | 49 | 3
| 9 | NESTED LOOPS | | 1 | 520 | 2177K (1)| 07:15:29 |
| 10 | NESTED LOOPS | | 1 | 471 | 2177K (1)| 07:15:29 |
| 11 | NESTED LOOPS | | 1 | 441 | 2177K (1)| 07:15:29 |
| 12 | NESTED LOOPS | | 1 | 354 | 2177K (1)| 07:15:29 |
| 13 | NESTED LOOPS | | 1 | 334 | 2177K (1)| 07:15:29 |
| 14 | NESTED LOOPS | | 1 | 278 | 2177K (1)| 07:15:29 |
| 15 | NESTED LOOPS | | 1 | 243 | 2177K (1)| 07:15:29 |
| 16 | NESTED LOOPS | | 1 | 194 | 2177K (1)| 07:15:29 |
| 17 | NESTED LOOPS | | 1 | 173 | 2177K (1)| 07:15:29 |
|* 18 | TABLE ACCESS FULL | GL_PERIODS | 1 | 53 | 4 (0)| 00:00:01 |
| 19 | SORT AGGREGATE | | 1 | 26 | | |
|* 20 | TABLE ACCESS FULL | GL_PERIODS | 5 | 130 | 4 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID| RA_CUST_TRX_LINE_GL_DIST_ALL | 1 | 120 | 2177K (1)|
|* 22 | INDEX RANGE SCAN | RA_CUST_TRX_LINE_GL_DIST_N2 | 48M| | 315K (1)| 0
|* 23 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS | 1 | 21 | 1 (0)|
|* 24 | INDEX UNIQUE SCAN | HR_ORGANIZATION_UNITS_PK | 1 | | 0 (0)| 00
| 25 | TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS | 1 | 49 | 2 (0)| 00
|* 26 | INDEX UNIQUE SCAN | GL_CODE_COMBINATIONS_U1 | 1 | | 1 (0)| 00:
|* 27 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_LINES_ALL | 1 | 35 | 3 (0)|
|* 28 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_LINES_U1 | 1 | | 2 (0)| 00:0
|* 29 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL | 1 | 56 | 2 (0)| 00:
|* 30 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_U1 | 1 | | 1 (0)| 00:00:01
| 31 | TABLE ACCESS BY INDEX ROWID | GL_SETS_OF_BOOKS | 1 | 20 | 1 (0)|
|* 32 | INDEX UNIQUE SCAN | GL_SETS_OF_BOOKS_U2 | 1 | | 0 (0)| 00:00:01
| 33 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B | 1 | 87 | 2 (0
|* 34 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 1 | | 1 (0)| 0
|* 35 | TABLE ACCESS BY INDEX ROWID | GL_PERIODS | 3 | 90 | 2 (0)| 00:0
|* 36 | INDEX RANGE SCAN | GL_PERIODS_U2 | 13 | | 1 (0)| 00:00:01
|* 37 | INDEX RANGE SCAN | XXG_GAAP_ITEM_ATTRIBUTE_UN | 1 | | 2 (0)| 00:
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EFFECTIVE_END_DATE" IS NULL)
2 - access("SKU_NUM"=:B1 AND "ATTRIBUTE_CODE"='FINDURATIN')
3 - filter("EFFECTIVE_END_DATE" IS NULL)
4 - access("SKU_NUM"=:B1 AND "ATTRIBUTE_CODE"='FINSTRTDAT')
5 - filter("XXG"."EFFECTIVE_END_DATE" IS NULL)
6 - access("XXG"."SKU_NUM"=:B1 AND "XXG"."ATTRIBUTE_CODE"='FINCOGSTRE')
8 - filter("XXG_GAAP_ITEM_ATTRIBUTE"."SKU_NUM"="MTL_SYSTEM_ITEMS_B"."SEGMENT1")
18 - filter("GLP"."PERIOD_NUM"<>13 AND "GLP"."PERIOD_SET_NAME"='GlobalCalendar' AND CASE WHEN
(TRUNC(SYSDATE@!)>=TRUNC(INTERNAL_FUNCTION("GLP"."START_DATE")) AND
TRUNC(SYSDATE@!)<=TRUNC(INTERNAL_FUNCTION("GLP"."END_DATE"))) THEN 'C' WHEN
TRUNC(INTERNAL_FUNCTION("GLP"."END_DATE"))= (SELECT TRUNC(MAX("AL2"."START_DATE")-1) FROM "GL
"AL2" WHERE "AL2"."PERIOD_NUM"<>13 AND TRUNC(INTERNAL_FUNCTION("AL2"."START_DATE"))<=TRUNC(SY
"AL2"."PERIOD_SET_NAME"='GlobalCalendar') THEN 'P' ELSE NULL END ='P')
20 - filter("AL2"."PERIOD_NUM"<>13 AND TRUNC(INTERNAL_FUNCTION("AL2"."START_DATE"))<=TRUNC(SYSDATE
"AL2"."PERIOD_SET_NAME"='GlobalCalendar')
21 - filter(("RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R101' OR
"RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R104' OR "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIB
"RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R79' OR "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBU
"RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE13"='Initial')
22 - access("RA_CUST_TRX_LINE_GL_DIST_ALL"."GL_DATE">="GLP"."START_DATE" AND
"RA_CUST_TRX_LINE_GL_DIST_ALL"."GL_DATE"<="GLP"."END_DATE")
23 - filter("HR_ALL_ORGANIZATION_UNITS"."ATTRIBUTE11"='APJ')
24 - access("RA_CUST_TRX_LINE_GL_DIST_ALL"."ORG_ID"="HR_ALL_ORGANIZATION_UNITS"."ORGANIZATION_ID")
26 - access("RA_CUST_TRX_LINE_GL_DIST_ALL"."CODE_COMBINATION_ID"="GL_CODE_COMBINATIONS"."CODE_COMB
)
27 - filter("RA_CUSTOMER_TRX_LINES_ALL"."INVENTORY_ITEM_ID" IS NOT NULL)
28 - access("RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_LINE_ID"="RA_CUST_TRX_LINE_GL_DIST_ALL"."CUS
INE_ID")
29 - filter("RA_CUSTOMER_TRX_ALL"."INTERFACE_HEADER_CONTEXT"='ORDER ENTRY' AND
"RA_CUSTOMER_TRX_ALL"."CREATED_FROM"='RAXTRX')
30 - access("RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_ID"="RA_CUSTOMER_TRX_ALL"."CUSTOMER_TRX_ID")
32 - access("RA_CUSTOMER_TRX_ALL"."SET_OF_BOOKS_ID"="GL_SETS_OF_BOOKS"."SET_OF_BOOKS_ID")
34 - access("RA_CUSTOMER_TRX_LINES_ALL"."INVENTORY_ITEM_ID"="MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_I
"RA_CUSTOMER_TRX_LINES_ALL"."ORG_ID"="MTL_SYSTEM_ITEMS_B"."ORGANIZATION_ID")
35 - filter("GLP"."QUARTER_NUM"="QUARTER_NUM")
36 - access("PERIOD_SET_NAME"='GlobalCalendar' AND "GLP"."PERIOD_YEAR"="PERIOD_YEAR")
filter("GLP"."PERIOD_YEAR"="PERIOD_YEAR")
37 - access("XXG_GAAP_ITEM_ATTRIBUTE"."INVENTORY_ITEM_ID"="MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_ID"
"XXG_GAAP_ITEM_ATTRIBUTE"."ATTRIBUTE_CODE"='FINREVCLS')
date explicitly given
Execution Plan
----------------------------------------------------------
Plan hash value: 3535227385
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 520 | 5535 (1)| 00:01:07 |
|* 1 | TABLE ACCESS BY INDEX ROWID | XXG_GAAP_ITEM_ATTRIBUTE | 1 | 33 | 4 (0)| 00:0
|* 2 | INDEX RANGE SCAN | XXG_GAAP_ITEM_ATTRIBUTE_N1 | 1 | | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID | XXG_GAAP_ITEM_ATTRIBUTE | 1 | 33 | 4 (0)| 00:0
|* 4 | INDEX RANGE SCAN | XXG_GAAP_ITEM_ATTRIBUTE_N1 | 1 | | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID | XXG_GAAP_ITEM_ATTRIBUTE | 1 | 33 | 4 (0)| 00:0
|* 6 | INDEX RANGE SCAN | XXG_GAAP_ITEM_ATTRIBUTE_N1 | 1 | | 3 (0)| 00:00:01 |
| 7 | HASH GROUP BY | | 1 | 520 | 5535 (1)| 00:01:07 |
|* 8 | FILTER | | | | | |
|* 9 | TABLE ACCESS BY INDEX ROWID | GL_PERIODS | 1 | 53 | 2 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 520 | 5530 (1)| 00:01:07 |
| 11 | MERGE JOIN CARTESIAN | | 1 | 467 | 5528 (1)| 00:01:07 |
|* 12 | TABLE ACCESS BY INDEX ROWID | XXG_GAAP_ITEM_ATTRIBUTE | 1 | 49 | 3 (0)|
| 13 | NESTED LOOPS | | 1 | 437 | 5524 (1)| 00:01:07 |
| 14 | NESTED LOOPS | | 1 | 388 | 5521 (1)| 00:01:07 |
| 15 | NESTED LOOPS | | 1 | 368 | 5520 (1)| 00:01:07 |
| 16 | NESTED LOOPS | | 1 | 312 | 5518 (1)| 00:01:07 |
| 17 | NESTED LOOPS | | 1 | 225 | 5516 (1)| 00:01:07 |
| 18 | NESTED LOOPS | | 1 | 190 | 5513 (1)| 00:01:07 |
| 19 | NESTED LOOPS | | 1 | 141 | 5511 (1)| 00:01:07 |
|* 20 | TABLE ACCESS FULL | HR_ALL_ORGANIZATION_UNITS | 1 | 21 | 3 (0)| 00:00:
|* 21 | TABLE ACCESS BY INDEX ROWID| RA_CUST_TRX_LINE_GL_DIST_ALL | 1 | 120 | 5508 (
|* 22 | INDEX RANGE SCAN | RA_CUST_TRX_LINE_GL_DIST_N2 | 25095 | | 4549 (1)| 00:00:55 |
| 23 | TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS | 1 | 49 | 2 (0)| 00:
|* 24 | INDEX UNIQUE SCAN | GL_CODE_COMBINATIONS_U1 | 1 | | 1 (0)| 00:00:01 |
|* 25 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_LINES_ALL | 1 | 35 | 3 (0)|
|* 26 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_LINES_U1 | 1 | | 2 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B | 1 | 87 | 2 (0)| 00:00:
|* 28 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 1 | | 1 (0)| 00:00:01 |
|* 29 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL | 1 | 56 | 2 (0)| 00:00:
|* 30 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_U1 | 1 | | 1 (0)| 00:00:01 |
| 31 | TABLE ACCESS BY INDEX ROWID | GL_SETS_OF_BOOKS | 1 | 20 | 1 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | GL_SETS_OF_BOOKS_U2 | 1 | | 0 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | XXG_GAAP_ITEM_ATTRIBUTE_UN | 1 | | 2 (0)| 00:00:01 |
| 34 | BUFFER SORT | | 104 | 3120 | 5525 (1)| 00:01:07 |
|* 35 | TABLE ACCESS FULL | GL_PERIODS | 104 | 3120 | 4 (0)| 00:00:01 |
|* 36 | INDEX RANGE SCAN | GL_PERIODS_U2 | 12 | | 1 (0)| 00:00:01 |
| 37 | SORT AGGREGATE | | 1 | 26 | | |
|* 38 | TABLE ACCESS FULL | GL_PERIODS | 5 | 130 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EFFECTIVE_END_DATE" IS NULL)
2 - access("SKU_NUM"=:B1 AND "ATTRIBUTE_CODE"='FINDURATIN')
3 - filter("EFFECTIVE_END_DATE" IS NULL)
4 - access("SKU_NUM"=:B1 AND "ATTRIBUTE_CODE"='FINSTRTDAT')
5 - filter("XXG"."EFFECTIVE_END_DATE" IS NULL)
6 - access("XXG"."SKU_NUM"=:B1 AND "XXG"."ATTRIBUTE_CODE"='FINCOGSTRE')
8 - filter(SYSDATE@!-30<=SYSDATE@!)
9 - filter("GLP"."QUARTER_NUM"="QUARTER_NUM" AND CASE WHEN
(TRUNC(SYSDATE@!)>=TRUNC(INTERNAL_FUNCTION("GLP"."START_DATE")) AND
TRUNC(SYSDATE@!)<=TRUNC(INTERNAL_FUNCTION("GLP"."END_DATE"))) THEN 'C' WHEN
TRUNC(INTERNAL_FUNCTION("GLP"."END_DATE"))= (SELECT TRUNC(MAX("AL2"."START_DATE")-1) FROM "GL
"AL2" WHERE "AL2"."PERIOD_NUM"<>13 AND TRUNC(INTERNAL_FUNCTION("AL2"."START_DATE"))<=TRUNC(SY
"AL2"."PERIOD_SET_NAME"='GlobalCalendar') THEN 'P' ELSE NULL END ='P')
12 - filter("XXG_GAAP_ITEM_ATTRIBUTE"."SKU_NUM"="MTL_SYSTEM_ITEMS_B"."SEGMENT1")
20 - filter("HR_ALL_ORGANIZATION_UNITS"."ATTRIBUTE11"='APJ')
21 - filter(("RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R101' OR
"RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R104' OR "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIB
"RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R79' OR "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBU
"RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE13"='Initial')
22 - access("RA_CUST_TRX_LINE_GL_DIST_ALL"."GL_DATE">=SYSDATE@!-30 AND
"RA_CUST_TRX_LINE_GL_DIST_ALL"."ORG_ID"="HR_ALL_ORGANIZATION_UNITS"."ORGANIZATION_ID" AND
"RA_CUST_TRX_LINE_GL_DIST_ALL"."GL_DATE"<=SYSDATE@!)
filter("RA_CUST_TRX_LINE_GL_DIST_ALL"."ORG_ID"="HR_ALL_ORGANIZATION_UNITS"."ORGANIZATION_ID")
24 - access("RA_CUST_TRX_LINE_GL_DIST_ALL"."CODE_COMBINATION_ID"="GL_CODE_COMBINATIONS"."CODE_COMB
25 - filter("RA_CUSTOMER_TRX_LINES_ALL"."INVENTORY_ITEM_ID" IS NOT NULL)
26 - access("RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_LINE_ID"="RA_CUST_TRX_LINE_GL_DIST_ALL"."CUS
E_ID")
28 - access("RA_CUSTOMER_TRX_LINES_ALL"."INVENTORY_ITEM_ID"="MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_I
"RA_CUSTOMER_TRX_LINES_ALL"."ORG_ID"="MTL_SYSTEM_ITEMS_B"."ORGANIZATION_ID")
29 - filter("RA_CUSTOMER_TRX_ALL"."INTERFACE_HEADER_CONTEXT"='ORDER ENTRY' AND
"RA_CUSTOMER_TRX_ALL"."CREATED_FROM"='RAXTRX')
30 - access("RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_ID"="RA_CUSTOMER_TRX_ALL"."CUSTOMER_TRX_ID")
32 - access("RA_CUSTOMER_TRX_ALL"."SET_OF_BOOKS_ID"="GL_SETS_OF_BOOKS"."SET_OF_BOOKS_ID")
33 - access("XXG_GAAP_ITEM_ATTRIBUTE"."INVENTORY_ITEM_ID"="MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_ID"
"XXG_GAAP_ITEM_ATTRIBUTE"."ATTRIBUTE_CODE"='FINREVCLS')
35 - filter("PERIOD_SET_NAME"='GlobalCalendar')
36 - access("GLP"."PERIOD_SET_NAME"='GlobalCalendar' AND "GLP"."PERIOD_YEAR"="PERIOD_YEAR")
filter("GLP"."PERIOD_NUM"<>13 AND "GLP"."PERIOD_YEAR"="PERIOD_YEAR")
38 - filter("AL2"."PERIOD_NUM"<>13 AND TRUNC(INTERNAL_FUNCTION("AL2"."START_DATE"))<=TRUNC(SYSDATE
"AL2"."PERIOD_SET_NAME"='GlobalCalendar') |
|
|
|
| Re: query tuning [message #426246 is a reply to message #426042] |
Wed, 14 October 2009 10:02 |
cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Well the first thing I'd do is simplify that inline view, because you appear do selecting far more things in it than you actually use. As far as I can see you need to select only 2 columns from it.
And the case statement in the inline view should just be part of its where clause.
So simplify all that and run it again - probably won't speed it up but the more concise it is the easier it'll be for the rest of us to help you.
Also pretty sure you're outer join isn't actually outer-joining since you haven't specified it in enough places - so you might want to remove that or fix it so it works.
|
|
|
|
|
|
Goto Forum:
Current Time: Sat May 18 04:01:47 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
