Home » RDBMS Server » Performance Tuning » Please help me on tune this query (Oracle 11g)
| Please help me on tune this query [message #572358] |
Tue, 11 December 2012 03:27  |
ajaykumarkona
Messages: 399
Registered: August 2010
|
Senior Member |
|
|
Hi Experts,
Please hrlp me to tune this query.
To below query is taking nearly 1 hour time.
The inner query before using ROW_NUMBER is fetching records.
I have gathered STATISTICS for all tables and indexes and I have rebuild the indexes
then also the query is taking one hour time.
The number records in all tables.
SELECT COUNT(*) FROM ORL.FAX_TRANSITION_V OFT;--3190748
SELECT COUNT(*) FROM ORL.FAX_OFFER_DETAIL OD; --991595
SELECT COUNT(*) FROM ORL.FAX_HEADER FH;--839835
SELECT COUNT(*) FROM ORL.WORKGROUP WG;--392
SELECT COUNT(*) FROM APPS_GLOBAL.GLOBAL_BU_MAPPING GBM;--9
SELECT COUNT(*) FROM APPS_JP.GEDIS_OFFER_HEADER OH;--5185757
SELECT COUNT(*) FROM ORL.FAX_SOURCE FS;--227
I have provided the explain plan.
2 SELECT FAX_LIST2.*
3 FROM (SELECT ROW_NUMBER () OVER (ORDER BY FAX_LIST.FAX_ID) RN, FAX_LIST.*
4 FROM (SELECT FH.ID AS FAX_ID,
5 FS.ACCOUNT_TYPE_CODE,
6 FS.PRIORITY_CODE,
7 FS.FAX_NUMBER AS Fax_Dest_Num,
8 FS.DESCRIPTION,
9 OD.ORDER_NUMBER AS ORDER_NUMBER,
10 OD.PURCHASE_ORDER_NUMBER,
11 OD.ID AS OD_ID,
12 OD.OFFER_NUMBER,
13 OD.VERSION_NO,
14 OD.SOURCE_OFFER_NO,
15 OD.OMEGA_ORDER_NUMBER,
16 OD.VALUE,
17 OD.IS_EMC_ORDER,
18 OD.CREATION_DATE,
19 OD.UPDATED_DATE,
20 OD.CREATED_BY,
21 OD.UPDATED_BY,
22 WG.NAME AS STATUS,
23 OH.ORDER_TYPE,
24 OH.ORDER_TYPE_ID,
25 OH.ORDER_DATE AS DATEORDERENTERED,
26 FH.IS_LOCKED,
27 FH.CUSTOMER_NUMBER,
28 FH.CUSTOMER_OMEGA_NUMBER,
29 FH.BU_FILE_LOCATION,
30 FH.CUSTOMER_NAME,
31 FH.GENIFAX_RECIPIENT_ID,
32 FROM_TZ (FH.DATE_RECEIVED, 'UTC')
33 AT TIME ZONE GBM.ORACLE_TZ_NAME
34 AS DATE_RECEIVED,
35 FH.ACCOUNT_TYPE,
36 FH.PAYMENT_TYPE,
37 FH.WORKGROUP_ID,
38 GBM.COUNTRY_NAME,
39 FROM_TZ ( (SELECT MAX (CHANGED_DATE)
40 FROM ORL.FAX_TRANSITION_V OFT
41 WHERE OFT.FAX_ID = FH.ID),
42 'UTC')
43 AT TIME ZONE GBM.ORACLE_TZ_NAME
44 AS CHANGED_DATE,
45 NULL AS DATA_VALUE,
46 (SELECT SUM (VALUE)
47 FROM ORL.FAX_OFFER_DETAIL OD
48 WHERE OD.FAX_ID = FH.ID)
49 AS ORDER_VALUE
50 FROM ORL.FAX_HEADER FH
51 INNER JOIN ORL.WORKGROUP WG
52 ON (WG.ID = FH.WORKGROUP_ID)
53 INNER JOIN APPS_GLOBAL.GLOBAL_BU_MAPPING GBM
54 ON (GBM.BU_ID = WG.BUID AND WG.BUID = 3535)
55 LEFT JOIN
56 (SELECT DISTINCT OD.FAX_ID,
57 OD.ORDER_NUMBER AS ORDER_NUMBER,
58 OD.PURCHASE_ORDER_NUMBER,
59 OD.ID,
60 OD.OFFER_NUMBER,
61 OD.VERSION_NO,
62 OD.SOURCE_OFFER_NO,
63 OD.OMEGA_ORDER_NUMBER,
64 OD.VALUE,
65 OD.IS_EMC_ORDER,
66 OD.CREATION_DATE,
67 OD.UPDATED_DATE,
68 OD.CREATED_BY,
69 OD.UPDATED_BY
70 FROM ORL.FAX_HEADER FHH
71 INNER JOIN ORL.FAX_OFFER_DETAIL OD
72 ON (OD.FAX_ID = FHH.ID)) OD
73 ON (OD.FAX_ID = FH.ID)
74 LEFT JOIN
75 APPS_JP.GEDIS_OFFER_HEADER OH
76 ON ( OH.ORDER_NO = OD.ORDER_NUMBER
77 AND OH.ORDER_NO != 0)
78 LEFT JOIN ORL.FAX_SOURCE FS ON (FS.ID = FH.FAX_SOURCE)
79 WHERE FH.WORKGROUP_ID = 245
80 AND TRUNC (FH.DATE_RECEIVED) >='01-JAN-2010'
81 AND TRUNC (FH.DATE_RECEIVED) <='04-DEC-2012'
82 ) FAX_LIST) FAX_LIST2
83 WHERE RN BETWEEN 1 AND 20;
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1504 | 3708K| | 12475 (1)|
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | VIEW | FAX_TRANSITION_V | 4 | 104 | | 42 (10)|
| 3 | SORT UNIQUE | | 4 | 2190 | | 42 (53)|
| 4 | UNION-ALL | | | | | |
| 5 | NESTED LOOPS OUTER | | 1 | 1443 | | 22 (10)|
| 6 | NESTED LOOPS OUTER | | 1 | 1403 | | 21 (10)|
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
| 7 | NESTED LOOPS | | 1 | 221 | | 4 (0)|
| 8 | TABLE ACCESS BY INDEX ROWID | FAX_HEADER | 1 | 168 | | 3 (0)|
|* 9 | INDEX UNIQUE SCAN | PK_FAX_HEADER | 1 | | | 2 (0)|
|* 10 | INDEX RANGE SCAN | IDX_WRKGRP_COMP_3 | 392 | 20776 | | 1 (0)|
|* 11 | VIEW | | 1 | 1182 | | 17 (12)|
|* 12 | FILTER | | | | | |
| 13 | SORT GROUP BY | | 1 | 146 | | 17 (12)|
| 14 | NESTED LOOPS | | 3 | 438 | | 16 (7)|
| 15 | NESTED LOOPS | | 3 | 243 | | 13 (8)|
| 16 | NESTED LOOPS | | 3 | 189 | | 10 (10)|
|* 17 | HASH JOIN | | 3 | 135 | | 7 (15)|
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
|* 18 | INDEX RANGE SCAN | IDX_FAX_TRANS_HIST_COMP_3 | 3 | 45 | | 3 (0)|
|* 19 | INDEX RANGE SCAN | IDX_FAX_TRANS_HIST_COMP_3 | 3 | 90 | | 3 (0)|
|* 20 | INDEX RANGE SCAN | IDX_WRKG_TRNS_RSN_COMP_3 | 1 | 18 | | 1 (0)|
|* 21 | INDEX RANGE SCAN | IDX_WRKGRP_TRANS_COMP_3 | 1 | 18 | | 1 (0)|
|* 22 | INDEX RANGE SCAN | IDX_WRKGRP_COMP_3 | 1 | 65 | | 1 (0)|
|* 23 | INDEX RANGE SCAN | IDX_ROUTING_RULE_COMP_2 | 1 | 40 | | 1 (0)|
| 24 | NESTED LOOPS | | 3 | 747 | | 18 (0)|
| 25 | NESTED LOOPS | | 3 | 588 | | 15 (0)|
| 26 | NESTED LOOPS | | 3 | 429 | | 12 (0)|
| 27 | NESTED LOOPS | | 3 | 402 | | 9 (0)|
| 28 | TABLE ACCESS BY INDEX ROWID| FAX_TRANSITION_HISTORY | 3 | 195 | | 6 (0)|
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
|* 29 | INDEX RANGE SCAN | FAX_TRANSITION_HISTORY_IDX1 | 3 | | | 3 (0)|
|* 30 | INDEX RANGE SCAN | IDX_WRKG_TRNS_RSN_COMP_3 | 1 | 69 | | 1 (0)|
|* 31 | INDEX RANGE SCAN | IDX_WRKGRP_TRANS_COMP_3 | 1 | 9 | | 1 (0)|
|* 32 | INDEX RANGE SCAN | IDX_WRKGRP_COMP_3 | 1 | 53 | | 1 (0)|
|* 33 | INDEX RANGE SCAN | IDX_WRKGRP_COMP_3 | 1 | 53 | | 1 (0)|
| 34 | SORT AGGREGATE | | 1 | 10 | | |
| 35 | TABLE ACCESS BY INDEX ROWID | FAX_OFFER_DETAIL | 2 | 20 | | 5 (0)|
|* 36 | INDEX RANGE SCAN | FAX_OFFER_DETAIL_IDX1 | 2 | | | 3 (0)|
|* 37 | VIEW | | 1504 | 3708K| | 12475 (1)|
|* 38 | WINDOW SORT PUSHED RANK | | 1504 | 1111K| 1216K| 12475 (1)|
| 39 | NESTED LOOPS OUTER | | 1504 | 1111K| | 12232 (1)|
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
| 40 | NESTED LOOPS OUTER | | 1148 | 812K| | 8919 (1)|
|* 41 | HASH JOIN | | 745 | 435K| | 6286 (2)|
|* 42 | TABLE ACCESS FULL | GLOBAL_BU_MAPPING | 1 | 29 | | 7 (0)|
|* 43 | HASH JOIN RIGHT OUTER | | 1583 | 879K| | 6278 (2)|
| 44 | TABLE ACCESS FULL | FAX_SOURCE | 227 | 50848 | | 5 (0)|
| 45 | NESTED LOOPS | | 1583 | 533K| | 6273 (2)|
|* 46 | TABLE ACCESS BY INDEX ROWID | WORKGROUP | 1 | 35 | | 1 (0)|
|* 47 | INDEX UNIQUE SCAN | PK_WORKGROUP | 1 | | | 0 (0)|
|* 48 | TABLE ACCESS BY INDEX ROWID | FAX_HEADER | 1583 | 425K| | 6272 (2)|
|* 49 | INDEX FULL SCAN | IDX_FAX_HEADER_COMP_5 | 2100 | | | 5682 (2)|
| 50 | TABLE ACCESS BY INDEX ROWID | FAX_OFFER_DETAIL | 2 | 254 | | 4 (0)|
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
|* 51 | INDEX RANGE SCAN | FAX_OFFER_DETAIL_IDX1 | 2 | | | 2 (0)|
| 52 | TABLE ACCESS BY INDEX ROWID | GEDIS_OFFER_HEADER | 1 | 32 | | 3 (0)|
|* 53 | INDEX RANGE SCAN | GEDIS_ORDER_HEADER_N2 | 1 | | | 2 (0)|
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("FH"."ID"=:B1)
10 - access("W1"."ID"="FH"."WORKGROUP_ID")
11 - filter("FTH"."FAX_ID"(+)="FH"."ID")
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
12 - filter("CHANGED_DATE"=MIN("CHANGED_DATE"))
17 - access("FAX_ID"="FAX_ID")
18 - access("FAX_ID"=:B1)
19 - access("FAX_ID"=:B1)
20 - access("WTR"."ID"="TRANSITION_REASON_ID")
21 - access("WT"."ID"="WTR"."WORKGROUP_TRANSITION_ID")
22 - access("W"."ID"="WT"."CURRENT_WORKGROUP_ID")
23 - access("RR"."ID"(+)="FH"."APPLIED_ROUTING_RULE")
29 - access("FAX_ID"=:B1)
30 - access("WTR"."ID"="TRANSITION_REASON_ID")
31 - access("WT"."ID"="WTR"."WORKGROUP_TRANSITION_ID")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
32 - access("WT"."CURRENT_WORKGROUP_ID"="FROMW"."ID")
33 - access("WT"."NEXT_WORKGROUP_ID"="TOW"."ID")
36 - access("OD"."FAX_ID"=:B1)
37 - filter("RN">=1 AND "RN"<=20)
38 - filter(ROW_NUMBER() OVER ( ORDER BY "FH"."ID")<=20)
41 - access("GBM"."BU_ID"="WG"."BUID")
42 - filter("GBM"."BU_ID"=3535)
43 - access("FS"."ID"(+)="FH"."FAX_SOURCE")
46 - filter("WG"."BUID"=3535)
47 - access("WG"."ID"=245)
48 - filter("FH"."WORKGROUP_ID"=245)
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
49 - filter(TRUNC(INTERNAL_FUNCTION("FH"."DATE_RECEIVED"))>=TO_DATE(' 2010-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND TRUNC(INTERNAL_FUNCTION("FH"."DATE_RECEIVED"))<=TO_DATE(' 2012-12-04
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
51 - access("OD"."FAX_ID"(+)="FH"."ID")
filter("OD"."FAX_ID"(+) IS NOT NULL)
53 - access("OH"."ORDER_NO"(+)="OD"."ORDER_NUMBER")
filter("OH"."ORDER_NO"(+)<>0)
[Updated on: Wed, 30 October 2013 08:02] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Please help me on tune this query [message #572378 is a reply to message #572369] |
Tue, 11 December 2012 06:21  |
burasami
Messages: 20
Registered: April 2010
|
Junior Member |
|
|
Hi ,
Oracle would choose not to use an index sometimes, if you're reading a lot of rows, or your index is not selective, or you're using a column other than the leading one in a concatenated index. What about if you want to do a case-insensitive search? Something like:
WHERE UPPER(first_name) = 'JOHN'
This won't use an index on first_name. Why? Because Oracle would have to go and apply the UPPER function on ALL values in the index, so it might as well do the full table scan. This was such a common need that Oracle created the function-based index for this purpose.
remove trunc function or create function based index on
AND TRUNC (FH.DATE_RECEIVED) >='01-JAN-2010'
AND TRUNC (FH.DATE_RECEIVED) <='04-DEC-2012'
Thanks
Sami
|
|
|
|
|
|
|
|
| Re: Please help me on tune this query [message #572394 is a reply to message #572381] |
Tue, 11 December 2012 09:00 |
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
ajaykumarkona wrote on Tue, 11 December 2012 07:58I have create functional based index cost got reduced ,but still the query is taking more time.
Please help me.
Now get rid of it. It is not needed. Create index on (FH.WORKGROUP_ID,FH.DATE_RECEIVED) if you don't have it and change
WHERE FH.WORKGROUP_ID = 245
AND TRUNC (FH.DATE_RECEIVED) >='01-JAN-2010'
AND TRUNC (FH.DATE_RECEIVED) <='04-DEC-2012'
to:
WHERE FH.WORKGROUP_ID = 245
AND FH.DATE_RECEIVED >= DATE '2010-01-01'
AND FH.DATE_RECEIVED < DATE '2010-12-05'
Also,
SELECT MAX (CHANGED_DATE)
40 FROM ORL.FAX_TRANSITION_V OFT
41 WHERE OFT.FAX_ID = FH.ID
might benefit form having index on FAX_ID,CHANGED_DATE, if it is possible since, I assume, ORL.FAX_TRANSITION_V is a view and we don't know if both FAX_ID,CHANGED_DATE are coming from same table.
SY.
|
|
|
|
| Re: Please help me on tune this query [message #572422 is a reply to message #572394] |
Tue, 11 December 2012 23:46 |
ajaykumarkona
Messages: 399
Registered: August 2010
|
Senior Member |
|
|
I have created index on (FH.WORKGROUP_ID,FH.DATE_RECEIVED) but cost got increased from previous.
And ORL.FAX_TRANSITION_V is a view.
Below is the view script.
Please help me.
CREATE OR REPLACE FORCE VIEW ORL.FAX_TRANSITION_V
(
FAX_ID,
FROM_WORKGROUP_ID,
FROM_WORKGROUP_NAME,
FROM_WORKGROUP_DESCRIPTION,
TO_WORKGROUP_ID,
TO_WORKGROUP_NAME,
TO_WORKGROUP_DESCRIPTION,
REASON_ID,
REASON_DESCRIPTION,
CHANGED_BY,
CHANGED_DATE,
COMMENTS,
IMPERSONATED_BY
)
AS
SELECT fh.ID AS fax_id,
NULL AS from_workgroup_id,
NULL AS from_workgroup_name,
CASE
WHEN fh.genifax_recipient_id IS NULL THEN TO_NCHAR ('N/A')
ELSE TO_NCHAR ('ORL FEEDER STAGING')
END
AS from_workgroup_description,
NVL (fth.ID, w1.ID) AS to_workgroup_id,
NVL (fth.NAME, w1.NAME) AS to_workgroup_name,
NVL (fth.description, w1.description) AS to_workgroup_description,
NULL AS reason_id,
CASE
WHEN NVL (fh.applied_routing_rule, 0) = 0
THEN
TO_NCHAR ('Reroute')
ELSE
TO_NCHAR (
'Reroute By Rule:''' || rr.NAME || '''(#' || rr.ID || ')')
END
AS reason_description,
CASE
WHEN fh.genifax_recipient_id IS NULL THEN TO_NCHAR ('Admin')
ELSE TO_NCHAR ('ORL FEEDER')
END
AS changed_by,
fh.date_received AS changed_date,
TO_NCHAR ('NO COMMENTS') AS comments,
fh.impersonated_by
FROM orl.fax_header fh
INNER JOIN orl.workgroup w1 ON w1.ID = fh.workgroup_id
LEFT JOIN orl.routing_rule rr ON rr.ID = fh.applied_routing_rule
LEFT OUTER JOIN
(SELECT fhis.fax_id AS fax_id,
w.ID,
w.NAME,
w.description
FROM (SELECT ID
FROM orl.fax_transition_history
WHERE (changed_date, fax_id) IN
( SELECT MIN (changed_date), fax_id
FROM orl.fax_transition_history
GROUP BY fax_id)) gfh
INNER JOIN orl.fax_transition_history fhis
ON fhis.ID = gfh.ID
INNER JOIN orl.workgroup_transition_reason wtr
ON wtr.ID = fhis.transition_reason_id
INNER JOIN orl.workgroup_transition wt
ON wt.ID = wtr.workgroup_transition_id
INNER JOIN orl.workgroup w
ON w.ID = wt.current_workgroup_id) fth
ON fth.fax_id = fh.ID
UNION
SELECT fhis.fax_id AS fax_id,
fromw.ID AS from_workgroup_id,
fromw.NAME AS from_workgroup_name,
fromw.description AS from_workgroup_description,
tow.ID AS to_workgroup_id,
tow.NAME AS to_workgroup_name,
tow.description AS to_workgroup_description,
wtr.ID AS reason_id,
wtr.reason AS reason_description,
fhis.changed_by,
fhis.changed_date,
fhis.comments,
fhis.impersonated_by
FROM (SELECT fax_id, ID FROM orl.fax_transition_history) gfh
INNER JOIN
orl.fax_transition_history fhis
INNER JOIN
orl.workgroup_transition_reason wtr
INNER JOIN
orl.workgroup_transition wt
INNER JOIN orl.workgroup fromw
ON wt.current_workgroup_id = fromw.ID
INNER JOIN orl.workgroup tow
ON wt.next_workgroup_id = tow.ID
ON wt.ID = wtr.workgroup_transition_id
ON wtr.ID = fhis.transition_reason_id
ON fhis.ID = gfh.ID
ORDER BY fax_id, changed_date ASC; |
|
|
|
|
|
| Re: Please help me on tune this query [message #572453 is a reply to message #572427] |
Wed, 12 December 2012 03:13 |
ajaykumarkona
Messages: 399
Registered: August 2010
|
Senior Member |
|
|
Hi,
I have modified my query as below and I have created functional based inex on
AND TRUNC (FH.DATE_RECEIVED) >=TRUNC (:DATE_FROM)
AND TRUNC (FH.DATE_RECEIVED) <=TRUNC (:DATE_TO)
But still the query is executing for morethan one hour.
Please help me how to improve the performance
SQL> explain plan for
2 WITH OD AS (SELECT /*+ INLINE */ DISTINCT OD.FAX_ID,
3 OD.ORDER_NUMBER AS ORDER_NUMBER,
4 OD.PURCHASE_ORDER_NUMBER,
5 OD.ID,
6 OD.OFFER_NUMBER,
7 OD.VERSION_NO,
8 OD.SOURCE_OFFER_NO,
9 OD.OMEGA_ORDER_NUMBER,
10 OD.VALUE,
11 OD.IS_EMC_ORDER,
12 OD.CREATION_DATE,
13 OD.UPDATED_DATE,
14 OD.CREATED_BY,
15 OD.UPDATED_BY
16 FROM ORL.FAX_HEADER FHH
17 INNER JOIN ORL.FAX_OFFER_DETAIL OD
18 ON (OD.FAX_ID = FHH.ID))
19 SELECT FAX_LIST2.*
20 FROM (SELECT /*+ INDEX_ASC(FH PK_FAX_HEADER) NOPARALLEL_INDEX(FH PK_FAX_HEADER) */ ROW_NUMBER () OVER (ORDER BY FAX_LIST.FAX_ID) RN, FAX_LIST.*
21 FROM (SELECT FH.ID AS FAX_ID,
22 FS.ACCOUNT_TYPE_CODE,
23 FS.PRIORITY_CODE,
24 FS.FAX_NUMBER AS Fax_Dest_Num,
25 FS.DESCRIPTION,
26 OD.ORDER_NUMBER AS ORDER_NUMBER,
27 OD.PURCHASE_ORDER_NUMBER,
28 OD.ID AS OD_ID,
29 OD.OFFER_NUMBER,
30 OD.VERSION_NO,
31 OD.SOURCE_OFFER_NO,
32 OD.OMEGA_ORDER_NUMBER,
33 OD.VALUE,
34 OD.IS_EMC_ORDER,
35 OD.CREATION_DATE,
36 OD.UPDATED_DATE,
37 OD.CREATED_BY,
38 OD.UPDATED_BY,
39 WG.NAME AS STATUS,
40 OH.ORDER_TYPE,
41 OH.ORDER_TYPE_ID,
42 OH.ORDER_DATE AS DATEORDERENTERED,
43 FH.IS_LOCKED,
44 FH.CUSTOMER_NUMBER,
45 FH.CUSTOMER_OMEGA_NUMBER,
46 FH.BU_FILE_LOCATION,
47 FH.CUSTOMER_NAME,
48 FH.GENIFAX_RECIPIENT_ID,
49 FROM_TZ (FH.DATE_RECEIVED, 'UTC')
50 AT TIME ZONE GBM.ORACLE_TZ_NAME
51 AS DATE_RECEIVED,
52 FH.ACCOUNT_TYPE,
53 FH.PAYMENT_TYPE,
54 FH.WORKGROUP_ID,
55 GBM.COUNTRY_NAME,
56 FROM_TZ ( (SELECT MAX (CHANGED_DATE)
57 FROM ORL.FAX_TRANSITION_V OFT
58 WHERE OFT.FAX_ID = FH.ID),
59 'UTC')
60 AT TIME ZONE GBM.ORACLE_TZ_NAME
61 AS CHANGED_DATE,
62 NULL AS DATA_VALUE,
63 (SELECT SUM (VALUE)
64 FROM ORL.FAX_OFFER_DETAIL OD
65 WHERE OD.FAX_ID = FH.ID)
66 AS ORDER_VALUE
67 FROM ORL.FAX_HEADER FH
68 INNER JOIN ORL.WORKGROUP WG
69 ON (WG.ID = FH.WORKGROUP_ID)
70 INNER JOIN APPS_GLOBAL.GLOBAL_BU_MAPPING GBM
71 ON (GBM.BU_ID = WG.BUID AND WG.BUID = 3535)
72 LEFT JOIN
73 OD
74 ON (OD.FAX_ID = FH.ID)
75 LEFT JOIN
76 APPS_JP.GEDIS_OFFER_HEADER OH
77 ON ( OH.ORDER_NO = OD.ORDER_NUMBER
78 AND OH.ORDER_NO !=0)
79 LEFT JOIN ORL.FAX_SOURCE FS ON (FS.ID = FH.FAX_SOURCE)
80 WHERE FH.WORKGROUP_ID =245
81 AND TRUNC (FH.DATE_RECEIVED) >=TRUNC (:DATE_FROM)
82 AND TRUNC (FH.DATE_RECEIVED) <=TRUNC (:DATE_TO)
83 ) FAX_LIST) FAX_LIST2
84 WHERE RN BETWEEN 1 AND 20;
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1504 | 3708K| | 6625 (1)|
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | VIEW | FAX_TRANSITION_V | 4 | 104 | | 42 (10)|
| 3 | SORT UNIQUE | | 4 | 2190 | | 42 (53)|
| 4 | UNION-ALL | | | | | |
| 5 | NESTED LOOPS OUTER | | 1 | 1443 | | 22 (10)|
| 6 | NESTED LOOPS OUTER | | 1 | 1403 | | 21 (10)|
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
| 7 | NESTED LOOPS | | 1 | 221 | | 4 (0)|
| 8 | TABLE ACCESS BY INDEX ROWID | FAX_HEADER | 1 | 168 | | 3 (0)|
|* 9 | INDEX UNIQUE SCAN | PK_FAX_HEADER | 1 | | | 2 (0)|
|* 10 | INDEX RANGE SCAN | IDX_WRKGRP_COMP_3 | 392 | 20776 | | 1 (0)|
|* 11 | VIEW | | 1 | 1182 | | 17 (12)|
|* 12 | FILTER | | | | | |
| 13 | SORT GROUP BY | | 1 | 146 | | 17 (12)|
| 14 | NESTED LOOPS | | 3 | 438 | | 16 (7)|
| 15 | NESTED LOOPS | | 3 | 243 | | 13 (8)|
| 16 | NESTED LOOPS | | 3 | 189 | | 10 (10)|
|* 17 | HASH JOIN | | 3 | 135 | | 7 (15)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
|* 18 | INDEX RANGE SCAN | IDX_FAX_TRANS_HIST_COMP_3 | 3 | 45 | | 3 (0)|
|* 19 | INDEX RANGE SCAN | IDX_FAX_TRANS_HIST_COMP_3 | 3 | 90 | | 3 (0)|
|* 20 | INDEX RANGE SCAN | IDX_WRKG_TRNS_RSN_COMP_3 | 1 | 18 | | 1 (0)|
|* 21 | INDEX RANGE SCAN | IDX_WRKGRP_TRANS_COMP_3 | 1 | 18 | | 1 (0)|
|* 22 | INDEX RANGE SCAN | IDX_WRKGRP_COMP_3 | 1 | 65 | | 1 (0)|
|* 23 | INDEX RANGE SCAN | IDX_ROUTING_RULE_COMP_2 | 1 | 40 | | 1 (0)|
| 24 | NESTED LOOPS | | 3 | 747 | | 18 (0)|
| 25 | NESTED LOOPS | | 3 | 588 | | 15 (0)|
| 26 | NESTED LOOPS | | 3 | 429 | | 12 (0)|
| 27 | NESTED LOOPS | | 3 | 402 | | 9 (0)|
| 28 | TABLE ACCESS BY INDEX ROWID| FAX_TRANSITION_HISTORY | 3 | 195 | | 6 (0)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
|* 29 | INDEX RANGE SCAN | FAX_TRANSITION_HISTORY_IDX1 | 3 | | | 3 (0)|
|* 30 | INDEX RANGE SCAN | IDX_WRKG_TRNS_RSN_COMP_3 | 1 | 69 | | 1 (0)|
|* 31 | INDEX RANGE SCAN | IDX_WRKGRP_TRANS_COMP_3 | 1 | 9 | | 1 (0)|
|* 32 | INDEX RANGE SCAN | IDX_WRKGRP_COMP_3 | 1 | 53 | | 1 (0)|
|* 33 | INDEX RANGE SCAN | IDX_WRKGRP_COMP_3 | 1 | 53 | | 1 (0)|
| 34 | SORT AGGREGATE | | 1 | 10 | | |
| 35 | TABLE ACCESS BY INDEX ROWID | FAX_OFFER_DETAIL | 2 | 20 | | 5 (0)|
|* 36 | INDEX RANGE SCAN | FAX_OFFER_DETAIL_IDX1 | 2 | | | 3 (0)|
|* 37 | VIEW | | 1504 | 3708K| | 6625 (1)|
|* 38 | WINDOW SORT PUSHED RANK | | 1504 | 1073K| 1216K| 6625 (1)|
|* 39 | FILTER | | | | | |
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
| 40 | NESTED LOOPS OUTER | | 1504 | 1073K| | 6388 (1)|
| 41 | NESTED LOOPS OUTER | | 1148 | 783K| | 3075 (1)|
|* 42 | HASH JOIN RIGHT OUTER | | 745 | 416K| | 442 (1)|
| 43 | TABLE ACCESS FULL | FAX_SOURCE | 227 | 50848 | | 5 (0)|
| 44 | MERGE JOIN CARTESIAN | | 745 | 253K| | 436 (0)|
| 45 | NESTED LOOPS | | 1 | 64 | | 8 (0)|
|* 46 | TABLE ACCESS BY INDEX ROWID| WORKGROUP | 1 | 35 | | 1 (0)|
|* 47 | INDEX UNIQUE SCAN | PK_WORKGROUP | 1 | | | 0 (0)|
|* 48 | TABLE ACCESS FULL | GLOBAL_BU_MAPPING | 1 | 29 | | 7 (0)|
| 49 | BUFFER SORT | | 1583 | 439K| | 429 (0)|
|* 50 | TABLE ACCESS BY INDEX ROWID| FAX_HEADER | 1583 | 439K| | 428 (0)|
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
|* 51 | INDEX RANGE SCAN | IDX_DATE_RECEIVED_RAM | 3779 | | | 12 (0)|
| 52 | TABLE ACCESS BY INDEX ROWID | FAX_OFFER_DETAIL | 2 | 254 | | 4 (0)|
|* 53 | INDEX RANGE SCAN | FAX_OFFER_DETAIL_IDX1 | 2 | | | 2 (0)|
| 54 | TABLE ACCESS BY INDEX ROWID | GEDIS_OFFER_HEADER | 1 | 32 | | 3 (0)|
|* 55 | INDEX RANGE SCAN | GEDIS_ORDER_HEADER_N2 | 1 | | | 2 (0)|
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("FH"."ID"=:B1)
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
10 - access("W1"."ID"="FH"."WORKGROUP_ID")
11 - filter("FTH"."FAX_ID"(+)="FH"."ID")
12 - filter("CHANGED_DATE"=MIN("CHANGED_DATE"))
17 - access("FAX_ID"="FAX_ID")
18 - access("FAX_ID"=:B1)
19 - access("FAX_ID"=:B1)
20 - access("WTR"."ID"="TRANSITION_REASON_ID")
21 - access("WT"."ID"="WTR"."WORKGROUP_TRANSITION_ID")
22 - access("W"."ID"="WT"."CURRENT_WORKGROUP_ID")
23 - access("RR"."ID"(+)="FH"."APPLIED_ROUTING_RULE")
29 - access("FAX_ID"=:B1)
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
30 - access("WTR"."ID"="TRANSITION_REASON_ID")
31 - access("WT"."ID"="WTR"."WORKGROUP_TRANSITION_ID")
32 - access("WT"."CURRENT_WORKGROUP_ID"="FROMW"."ID")
33 - access("WT"."NEXT_WORKGROUP_ID"="TOW"."ID")
36 - access("OD"."FAX_ID"=:B1)
37 - filter("RN">=1 AND "RN"<=20)
38 - filter(ROW_NUMBER() OVER ( ORDER BY "FH"."ID")<=20)
39 - filter(TRUNC(TO_NUMBER(:DATE_FROM))<=TRUNC(TO_NUMBER(:DATE_TO)))
42 - access("FS"."ID"(+)="FH"."FAX_SOURCE")
46 - filter("WG"."BUID"=3535)
47 - access("WG"."ID"=245)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
48 - filter("GBM"."BU_ID"=3535)
50 - filter("FH"."WORKGROUP_ID"=245)
51 - access(TRUNC(INTERNAL_FUNCTION("DATE_RECEIVED"))>=TRUNC(TO_NUMBER(:DATE_FROM)) AND
TRUNC(INTERNAL_FUNCTION("DATE_RECEIVED"))<=TRUNC(TO_NUMBER(:DATE_TO)))
53 - access("OD"."FAX_ID"(+)="FH"."ID")
filter("OD"."FAX_ID"(+) IS NOT NULL)
55 - access("OH"."ORDER_NO"(+)="OD"."ORDER_NUMBER")
filter("OH"."ORDER_NO"(+)<>0)
[Updated on: Wed, 30 October 2013 08:04] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Please help me on tune this query [message #572510 is a reply to message #572489] |
Wed, 12 December 2012 11:43 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
I believe either your stats are insufficient (not collected, or out of date, or no histogram when there is skew). I say this because given the rowcount estimates of the plan this query should go very fast.
OR
The rowcount estimates in the plan are way off, in which case you should be doing a full table scan and hash joins in most places and not nested loops join and index lookups.
You need to investigate the rowcount estimates of the query plan to see if they are accurate or not.
Decompose your query into its smaller parts to see where your time is going and where rowcounts do not match what the plan says. For exmampe:
set timing on
create table temp1
nologging
as
select
FH.IS_LOCKED,
FH.CUSTOMER_NUMBER,
FH.CUSTOMER_OMEGA_NUMBER,
FH.BU_FILE_LOCATION,
FH.CUSTOMER_NAME,
FH.GENIFAX_RECIPIENT_ID,
FH.ACCOUNT_TYPE,
FH.PAYMENT_TYPE,
FH.WORKGROUP_ID
from FROM ORL.FAX_HEADER FH
WHERE FH.WORKGROUP_ID = 245
/
create table temp2
nologging
as
select
FH.IS_LOCKED,
FH.CUSTOMER_NUMBER,
FH.CUSTOMER_OMEGA_NUMBER,
FH.BU_FILE_LOCATION,
FH.CUSTOMER_NAME,
FH.GENIFAX_RECIPIENT_ID,
FH.ACCOUNT_TYPE,
FH.PAYMENT_TYPE,
FH.WORKGROUP_ID
,FROM_TZ ( (SELECT MAX (CHANGED_DATE)
FROM ORL.FAX_TRANSITION_V OFT
WHERE OFT.FAX_ID = FH.ID),
'UTC')
AT TIME ZONE GBM.ORACLE_TZ_NAME
AS CHANGED_DATE
,(SELECT SUM (VALUE)
FROM ORL.FAX_OFFER_DETAIL OD
WHERE OD.FAX_ID = FH.ID)
AS ORDER_VALUE
FROM ORL.FAX_HEADER FH
WHERE FH.WORKGROUP_ID = 245
/
create table temp3
nologging
as
SELECT
FH.ID AS FAX_ID,
WG.NAME AS STATUS,
FH.IS_LOCKED,
FH.CUSTOMER_NUMBER,
FH.CUSTOMER_OMEGA_NUMBER,
FH.BU_FILE_LOCATION,
FH.CUSTOMER_NAME,
FH.GENIFAX_RECIPIENT_ID,
FROM_TZ (FH.DATE_RECEIVED, 'UTC')
AT TIME ZONE GBM.ORACLE_TZ_NAME
AS DATE_RECEIVED,
FH.ACCOUNT_TYPE,
FH.PAYMENT_TYPE,
FH.WORKGROUP_ID,
FROM_TZ ( (SELECT MAX (CHANGED_DATE)
FROM ORL.FAX_TRANSITION_V OFT
WHERE OFT.FAX_ID = FH.ID),
'UTC')
AT TIME ZONE GBM.ORACLE_TZ_NAME
AS CHANGED_DATE,
NULL AS DATA_VALUE,
(SELECT SUM (VALUE)
FROM ORL.FAX_OFFER_DETAIL OD
WHERE OD.FAX_ID = FH.ID)
AS ORDER_VALUE
FROM ORL.FAX_HEADER FH
INNER JOIN ORL.WORKGROUP WG ON (WG.ID = FH.WORKGROUP_ID)
/
And so on. Break down the query to small independent parts. Then run these parts for timings and counts and plans and add parts back together one at a time to see their affect. Done correctly this will show you where rowcounts are off and where time starts adding up.
Good luck, Kevin
|
|
|
|
| Re: Please help me on tune this query [message #572544 is a reply to message #572510] |
Thu, 13 December 2012 01:37 |
ajaykumarkona
Messages: 399
Registered: August 2010
|
Senior Member |
|
|
If I comment ROW_NUMBER() and WHERE RN BETWEEN 1 AND 20.
Then I am getting the output in 2 minutes and it is returning 700000 records.
Please help me.
--SELECT FAX_LIST2.*
-- FROM (SELECT ROW_NUMBER () OVER (ORDER BY FAX_LIST.FAX_ID) RN, FAX_LIST.*
-- FROM (
-- )
--
-- FAX_LIST) FAX_LIST2
-- WHERE RN BETWEEN 1 AND 20;
-- |
|
|
|
| Re: Please help me on tune this query [message #572571 is a reply to message #572544] |
Thu, 13 December 2012 05:32 |
ajaykumarkona
Messages: 399
Registered: August 2010
|
Senior Member |
|
|
I have used FIRST_ROWS hint ,I got the output with in seconds only.
Could you please confirm to me is there any problem in using FIRST_ROWS hint in terms of data.
Please help me.
WITH OD AS (SELECT /*+ INLINE */ OD.FAX_ID,
OD.ORDER_NUMBER AS ORDER_NUMBER,
OD.PURCHASE_ORDER_NUMBER,
OD.ID,
OD.OFFER_NUMBER,
OD.VERSION_NO,
OD.SOURCE_OFFER_NO,
OD.OMEGA_ORDER_NUMBER,
OD.VALUE,
OD.IS_EMC_ORDER,
OD.CREATION_DATE,
OD.UPDATED_DATE,
OD.CREATED_BY,
OD.UPDATED_BY
FROM ORL.FAX_HEADER FHH
INNER JOIN ORL.FAX_OFFER_DETAIL OD
ON (OD.FAX_ID = FHH.ID))
SELECT /*+ FIRST_ROWS */ FAX_LIST2.*
FROM (SELECT RANK () OVER (ORDER BY FAX_LIST.FAX_ID) RN, FAX_LIST.*
FROM (
SELECT /*+ INDEX_ASC(FH PK_FAX_HEADER) NOPARALLEL_INDEX(FH PK_FAX_HEADER) */ FH.ID AS FAX_ID,
FS.ACCOUNT_TYPE_CODE,
FS.PRIORITY_CODE,
FS.FAX_NUMBER AS Fax_Dest_Num,
FS.DESCRIPTION,
OD.ORDER_NUMBER AS ORDER_NUMBER,
OD.PURCHASE_ORDER_NUMBER,
OD.ID AS OD_ID,
OD.OFFER_NUMBER,
OD.VERSION_NO,
OD.SOURCE_OFFER_NO,
OD.OMEGA_ORDER_NUMBER,
OD.VALUE,
OD.IS_EMC_ORDER,
OD.CREATION_DATE,
OD.UPDATED_DATE,
OD.CREATED_BY,
OD.UPDATED_BY,
WG.NAME AS STATUS,
OH.ORDER_TYPE,
OH.ORDER_TYPE_ID,
OH.ORDER_DATE AS DATEORDERENTERED,
FH.IS_LOCKED,
FH.CUSTOMER_NUMBER,
FH.CUSTOMER_OMEGA_NUMBER,
FH.BU_FILE_LOCATION,
FH.CUSTOMER_NAME,
FH.GENIFAX_RECIPIENT_ID,
FROM_TZ (FH.DATE_RECEIVED, 'UTC')
AT TIME ZONE GBM.ORACLE_TZ_NAME
AS DATE_RECEIVED,
FH.ACCOUNT_TYPE,
FH.PAYMENT_TYPE,
FH.WORKGROUP_ID,
GBM.COUNTRY_NAME,
FROM_TZ ( (SELECT MAX (CHANGED_DATE)
FROM ORL.FAX_TRANSITION_V OFT
WHERE OFT.FAX_ID = FH.ID),
'UTC')
AT TIME ZONE GBM.ORACLE_TZ_NAME
AS CHANGED_DATE,
NULL AS DATA_VALUE,
(SELECT SUM (VALUE)
FROM ORL.FAX_OFFER_DETAIL OD
WHERE OD.FAX_ID = FH.ID)
AS ORDER_VALUE
FROM ORL.FAX_HEADER FH
INNER JOIN ORL.WORKGROUP WG
ON (WG.ID = FH.WORKGROUP_ID)
INNER JOIN APPS_GLOBAL.GLOBAL_BU_MAPPING GBM
ON (GBM.BU_ID = WG.BUID AND WG.BUID = 3535)
LEFT JOIN
OD
ON (OD.FAX_ID = FH.ID)
LEFT JOIN
APPS_JP.GEDIS_OFFER_HEADER OH
ON ( OH.ORDER_NO = OD.ORDER_NUMBER
AND OH.ORDER_NO !=0)
LEFT JOIN ORL.FAX_SOURCE FS ON (FS.ID = FH.FAX_SOURCE)
WHERE FH.WORKGROUP_ID =245
AND TRUNC (FH.DATE_RECEIVED) >='01-JAN-2010'
AND TRUNC (FH.DATE_RECEIVED) <='04-DEC-2011'
) FAX_LIST) FAX_LIST2
WHERE RN BETWEEN 1 AND 20;
|
|
|
|
| Re: Please help me on tune this query [message #572578 is a reply to message #572510] |
Thu, 13 December 2012 06:09 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Kevin Meade wrote on Wed, 12 December 2012 17:43
Decompose your query into its smaller parts to see where your time is going and where rowcounts do not match what the plan says
If it is on 11g with the right options you can massively cheat take a significant shortcut here with the sql monitoring features.
Just for mentioning interest as I'm a massive fan of query decomposition, and it saved me a boatload of time.
It works from the cmd line with a fair bit of faffing and config, but there's also a great bit of grid/EM you can use for it if you don't want to script it up.
|
|
|
|
|
|
| Re: Please help me on tune this query [message #572592 is a reply to message #572590] |
Thu, 13 December 2012 08:01 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
The "right options" was to do with licencing requirements (I imagine it's the tuning pack, but check!). We've got just about everything and it's dog in here, but I know not all sites do.
I'll get something scooped out and fired off 
Edit: All done.
The short version is that it'll show you each step of the execution, what it expected and what it actually got. You can also see where it spends its time, proportions, PX details and so forth.
If you're not picking up the query you want, you can add the hint /*+ MONITOR */ and it'll push it through - although in my experience the ones you're looking for run long enough to be snapped up anyway.
It's not a silver bullet, but with all the information it has condensed into pretty much a single page, it can certainly point you in the right area very quickly.
As mentioned, cmd line is a little more work, but works everywhere
[Updated on: Thu, 13 December 2012 08:19] Report message to a moderator |
|
|
|
|
|
| Re: Please help me on tune this query [message #599936 is a reply to message #572596] |
Wed, 30 October 2013 05:24 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Reported message
Reported By: ajaykumarkona On: Wed, 30 October 2013 11:20 In: RDBMS Server » Performance Tuning » Please help me on tune this query
Reason: Please delete this message from this forum. Since i GOT THE ANSWER. Please do it immediately. Thanks.
Just in case someone of fellow moderators sees the request: I sent a private message to the OP, explaining that the whole topic won't be removed (according to OraFAQ Forum Guide (see 13)) and asking him to mark messages and information he finds confidential, so that we could mask it.
He chose to send a report once again, without doing what I asked. Therefore, no action has been performed yet.
[Updated on: Wed, 30 October 2013 05:25] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Mar 06 07:11:13 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
