| Please help me my query performance is bad. [message #601388] |
Wed, 20 November 2013 07:57  |
 |
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
|
Senior Member |
|
|
Hi Experts,
The below query is taking 5 to 6 minutes to execute.
Please help me to improve the performance of the query.
SELECT /*+ GATHER_PLAN_STATISTICS */ SECOND_LIST.*
FROM (SELECT ROW_NUMBER () OVER (ORDER BY FIRST_LIST.NAD_ID) RN,
FIRST_LIST.*
FROM (SELECT
NL .ID AS NAD_ID,
US.JOURNAL_CODE,
US.PERFE,
US.UNIT_NUMBER AS unit_num,
US.COMMENTS,
OD.SYSTEM_NUMBER AS SYSTEM_NUMBER,
OD.BUY_SYSTEM_NUMBER,
OD.ID AS OD_ID,
OD.PROPOSE_NUMBER,
OD.VISION,
OD.RENDER_NO,
OD.SELL_SYSTEM_NUMBER,
OD.WORTH,
OD.MAN_SYSTEM,
OD.MANF_DATE,
OD.DATE_RECENT,
OD.MANF_BY,
OD.RECENT_BY,
WG.NAME AS STATUS,
OH.SYSTEM_TYPE,
OH.SYSTEM_TYPE_ID,
OH.PROPPOSED_DATE,
NL.CONFIRMED,
NL.CLIENT_NUMBER,
NL.CONF_CLIENT_NUMBER,
NL.CLIENT_LOCATION,
NL.CLIENT_NAME,
NL.RECEIPT_ID,
FROM_TZ (NL.DATE_REACHED, 'UTC') AT TIME ZONE WBS.ORACLE_TZ_NAME
AS DATE_REACHED,
NL.JOURNAL_TYPE,
RECEIEVE_REP_ID (
WBS.REG_ID,
NL.REP_PERSON
).REP_NAME
AS REP_PERSON,
NL.AMOUNTPAID_METHOD,
NL.MAINUNIT_ID,
RECEIEVE_REP_ID (WBS.REG_ID,
NL.REP_PERSON).SECTOR_NAME
AS SECTOR_NAME,
RECEIEVE_REP_ID (
WBS.REG_ID,
NL.REP_PERSON
).SECTOR_LOC
AS SECTOR_LOC,
WBS.NATION_NAME,
FROM_TZ ( (SELECT MAX (MODIFIED_DATE)
FROM TRANSACTIONS_VW OFT
WHERE OFT.NAD_ID = NL.ID), 'UTC') AT TIME ZONE WBS.ORACLE_TZ_NAME
AS MODIFIED_DATE,
NULL AS DATA_VALUE,
(SELECT SUM (WORTH)
FROM NAD_DETAILS OD
WHERE OD.NAD_ID = NL.ID
AND id NOT IN
(SELECT ID
FROM NAD_DETAILS
WHERE RENDER_NO IN
( SELECT RENDER_NO
FROM NAD_DETAILS
WHERE NAD_ID = NL.ID
GROUP BY RENDER_NO
HAVING COUNT(*) > 1)
AND SYSTEM_NUMBER IS NULL))
AS ORDER_VALUE
FROM NAD_LISTER NL
INNER JOIN
MAINUNIT WG
ON (WG.ID = NL.MAINUNIT_ID)
INNER JOIN
WEDB_SOURCE WBS
ON (WBS.BU_ID = WG.BUID
AND WG.BUID = MANAGECONFIG.BU_ID)
LEFT JOIN
NAD_DETAILS_ND_VW OD
ON (OD.NAD_ID = NL.ID)
LEFT JOIN
WEDB_MAN_UNIT OH
ON (OH.SYSTEM_NO = OD.SYSTEM_NUMBER
AND OH.SYSTEM_NO > 0)
LEFT JOIN
UNIT_SOURCE US
ON (US.ID = NL.UNIT_SOURCE)
WHERE NL.MAINUNIT_ID = 262
AND NL.DATE_REACHED >= TRUNC (TO_DATE('01-MAR-2012','DD-MON-YYYY'))
AND NL.DATE_REACHED < TRUNC (TO_DATE('31-AUG-2012','DD-MON-YYYY'))+1)
FIRST_LIST) SECOND_LIST
WHERE RN BETWEEN 1 AND 20;
20 rows selected.
Elapsed: 00:05:14.75
Plan hash value: 1828662143
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 20 |00:05:13.35 | 3194K| 94412 | | | |
| 1 | SORT AGGREGATE | | 14 | 1 | 14 |00:00:00.39 | 2874 | 45 | | | |
| 2 | VIEW | TRANSACTIONS_VW | 14 | 2 | 54 |00:00:00.39 | 2874 | 45 | | | |
| 3 | UNION-ALL | | 14 | | 54 |00:00:00.39 | 2874 | 45 | | | |
|* 4 | HASH JOIN OUTER | | 14 | 1 | 14 |00:00:00.39 | 2704 | 45 | 1035K| 1035K| 389K (0)|
| 5 | NESTED LOOPS OUTER | | 14 | 1 | 14 |00:00:00.01 | 1597 | 0 | | | |
| 6 | NESTED LOOPS | | 14 | 1 | 14 |00:00:00.01 | 57 | 0 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | NAD_LISTER | 14 | 1 | 14 |00:00:00.01 | 38 | 0 | | | |
|* 8 | INDEX UNIQUE SCAN | PK_NAD_LISTER | 14 | 1 | 14 |00:00:00.01 | 24 | 0 | | | |
|* 9 | INDEX UNIQUE SCAN | PK_MAINUNIT | 14 | 1388 | 14 |00:00:00.01 | 19 | 0 | | | |
|* 10 | TABLE ACCESS FULL | ROUND_UNIT | 14 | 1 | 13 |00:00:00.01 | 1540 | 0 | | | |
| 11 | VIEW | | 14 | 1 | 14 |00:00:00.38 | 1107 | 45 | | | |
|* 12 | FILTER | | 14 | | 14 |00:00:00.38 | 1107 | 45 | | | |
| 13 | SORT GROUP BY | | 14 | 1 | 40 |00:00:00.38 | 1107 | 45 | 2048 | 2048 | 2048 (0)|
| 14 | NESTED LOOPS | | 14 | 1 | 244 |00:00:00.38 | 1107 | 45 | | | |
| 15 | NESTED LOOPS | | 14 | 1 | 244 |00:00:00.35 | 861 | 43 | | | |
| 16 | NESTED LOOPS | | 14 | 1 | 244 |00:00:00.32 | 358 | 39 | | | |
| 17 | TABLE ACCESS BY INDEX ROWID| TRANSACTION_HISTORY | 14 | 1 | 40 |00:00:00.10 | 75 | 15 | | | |
|* 18 | INDEX RANGE SCAN | TRANSACTION_HISTORY_IDX1 | 14 | 1 | 40 |00:00:00.02 | 35 | 3 | | | |
| 19 | TABLE ACCESS BY INDEX ROWID| TRANSACTION_HISTORY | 40 | 1 | 244 |00:00:00.22 | 283 | 24 | | | |
|* 20 | INDEX RANGE SCAN | TRANSACTION_HISTORY_IDX1 | 40 | 1 | 244 |00:00:00.01 | 38 | 0 | | | |
| 21 | TABLE ACCESS BY INDEX ROWID | MAINUNIT_TRANSITION_REASON | 244 | 1 | 244 |00:00:00.03 | 503 | 4 | | | |
|* 22 | INDEX UNIQUE SCAN | PK_MAINUNIT_TRANSITION_REASON | 244 | 1 | 244 |00:00:00.03 | 259 | 4 | | | |
|* 23 | INDEX UNIQUE SCAN | PK_MAINUNIT_TRANSITION | 244 | 1 | 244 |00:00:00.03 | 246 | 2 | | | |
| 24 | NESTED LOOPS | | 14 | | 40 |00:00:00.01 | 170 | 0 | | | |
| 25 | NESTED LOOPS | | 14 | 1 | 40 |00:00:00.01 | 130 | 0 | | | |
| 26 | TABLE ACCESS BY INDEX ROWID | TRANSACTION_HISTORY | 14 | 1 | 40 |00:00:00.01 | 75 | 0 | | | |
|* 27 | INDEX RANGE SCAN | TRANSACTION_HISTORY_IDX1 | 14 | 1 | 40 |00:00:00.01 | 35 | 0 | | | |
|* 28 | INDEX UNIQUE SCAN | PK_MAINUNIT_TRANSITION_REASON | 40 | 1 | 40 |00:00:00.01 | 55 | 0 | | | |
|* 29 | TABLE ACCESS BY INDEX ROWID | MAINUNIT_TRANSITION_REASON | 40 | 1 | 40 |00:00:00.01 | 40 | 0 | | | |
| 30 | SORT AGGREGATE | | 14 | 1 | 14 |00:00:00.18 | 159 | 30 | | | |
|* 31 | FILTER | | 14 | | 18 |00:00:00.18 | 159 | 30 | | | |
| 32 | TABLE ACCESS BY INDEX ROWID | NAD_DETAILS | 14 | 1 | 20 |00:00:00.11 | 44 | 17 | | | |
|* 33 | INDEX RANGE SCAN | NAD_DETAILS_IDX1 | 14 | 1 | 20 |00:00:00.02 | 25 | 3 | | | |
|* 34 | FILTER | | 20 | | 2 |00:00:00.07 | 115 | 13 | | | |
|* 35 | TABLE ACCESS BY INDEX ROWID | NAD_DETAILS | 20 | 1 | 7 |00:00:00.04 | 80 | 10 | | | |
|* 36 | INDEX UNIQUE SCAN | PK_NAD_DETAILS | 20 | 1 | 20 |00:00:00.04 | 60 | 10 | | | |
|* 37 | FILTER | | 7 | | 2 |00:00:00.02 | 35 | 3 | | | |
| 38 | HASH GROUP BY | | 7 | 1 | 12 |00:00:00.02 | 35 | 3 | 1079K| 1079K| 626K (0)|
| 39 | TABLE ACCESS BY INDEX ROWID | NAD_DETAILS | 7 | 1 | 14 |00:00:00.02 | 35 | 3 | | | |
|* 40 | INDEX RANGE SCAN | NAD_DETAILS_IDX1 | 7 | 1 | 14 |00:00:00.01 | 21 | 0 | | | |
|* 41 | VIEW | | 1 | 90346 | 20 |00:05:13.35 | 3194K| 94412 | | | |
|* 42 | WINDOW SORT PUSHED RANK | | 1 | 90346 | 21 |00:05:12.69 | 3190K| 94333 | 66560 | 66560 |59392 (0)|
| 43 | NESTED LOOPS OUTER | | 1 | 90346 | 10055 |00:05:12.63 | 3190K| 94333 | | | |
|* 44 | HASH JOIN RIGHT OUTER | | 1 | 12537 | 10055 |00:00:38.51 | 76594 | 66549 | 837K| 837K| 1395K (0)|
| 45 | TABLE ACCESS FULL | UNIT_SOURCE | 1 | 2368 | 2368 |00:00:00.01 | 70 | 0 | | | |
|* 46 | HASH JOIN OUTER | | 1 | 12537 | 10055 |00:00:38.42 | 76524 | 66549 | 3267K| 934K| 3813K (0)|
| 47 | MERGE JOIN CARTESIAN | | 1 | 8632 | 6907 |00:00:01.41 | 4734 | 217 | | | |
| 48 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 7 | 0 | | | |
|* 49 | TABLE ACCESS BY INDEX ROWID | MAINUNIT | 1 | 1 | 1 |00:00:00.01 | 5 | 0 | | | |
|* 50 | INDEX UNIQUE SCAN | PK_MAINUNIT | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | | | |
| 51 | TABLE ACCESS BY INDEX ROWID | WEDB_SOURCE | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | | | |
|* 52 | INDEX RANGE SCAN | WEDB_SOURCE_BUID | 1 | 1 | 1 |00:00:00.01 | 1 | 0 | | | |
| 53 | BUFFER SORT | | 1 | 7673 | 6907 |00:00:01.40 | 4727 | 217 | 2391K| 711K| 2125K (0)|
| 54 | TABLE ACCESS BY INDEX ROWID | NAD_LISTER | 1 | 7673 | 6907 |00:00:01.37 | 4727 | 217 | | | |
|* 55 | INDEX RANGE SCAN | IDX_MAINUNIT_ID2 | 1 | 7673 | 6907 |00:00:00.08 | 29 | 27 | | | |
| 56 | VIEW | NAD_DETAILS_ND_VW | 1 | 3210K| 3210K|00:00:29.22 | 71790 | 66332 | | | |
|* 57 | HASH JOIN RIGHT SEMI | | 1 | 3210K| 3210K|00:00:24.07 | 71790 | 66332 | 68M| 7188K| 111M (0)|
| 58 | INDEX FAST FULL SCAN | PK_NAD_LISTER | 1 | 2423K| 2423K|00:00:02.03 | 5453 | 0 | | | |
| 59 | TABLE ACCESS FULL | NAD_DETAILS | 1 | 3210K| 3210K|00:00:04.43 | 66337 | 66332 | | | |
| 60 | TABLE ACCESS BY INDEX ROWID | WEDB_MAN_UNIT | 10055 | 7 | 8481 |00:04:34.07 | 3113K| 27784 | | | |
|* 61 | INDEX RANGE SCAN | WEDB_GMT_UNIT_N2 | 10055 | 1 | 8481 |00:03:41.59 | 3105K| 22456 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("FTH"."NAD_ID"="NL"."ID")
8 - access("NL"."ID"=:B1)
9 - access("W1"."ID"="NL"."MAINUNIT_ID")
10 - filter("RR"."ID"="NL"."APPLIED_ROUND_UNIT")
12 - filter("FHIS"."MODIFIED_DATE"=MIN("MODIFIED_DATE"))
18 - access("NAD_ID"=:B1)
20 - access("FHIS"."NAD_ID"=:B1)
filter("FHIS"."NAD_ID"="NAD_ID")
22 - access("WTR"."ID"="FHIS"."TRANSITION_REASON_ID")
23 - access("WT"."ID"="WTR"."MAINUNIT_TRANSITION_ID")
27 - access("FHIS"."NAD_ID"=:B1)
28 - access("WTR"."ID"="FHIS"."TRANSITION_REASON_ID")
29 - filter("WTR"."MAINUNIT_TRANSITION_ID" IS NOT NULL)
31 - filter( IS NULL)
33 - access("OD"."NAD_ID"=:B1)
34 - filter( IS NOT NULL)
35 - filter("SYSTEM_NUMBER" IS NULL)
36 - access("ID"=:B1)
37 - filter(("RENDER_NO"=:B1 AND COUNT(*)>1))
40 - access("NAD_ID"=:B1)
41 - filter(("RN">=1 AND "RN"<=20))
42 - filter(ROW_NUMBER() OVER ( ORDER BY "NL"."ID")<=20)
44 - access("US"."ID"="NL"."UNIT_SOURCE")
46 - access("OD"."NAD_ID"="NL"."ID")
49 - filter("WG"."BUID"="MANAGECONFIG"."BU_ID"())
50 - access("WG"."ID"=262)
52 - access("WBS"."BU_ID"="MANAGECONFIG"."BU_ID"())
55 - access("NL"."MAINUNIT_ID"=262 AND "NL"."DATE_REACHED">=TIMESTAMP' 2012-03-01 00:00:00' AND "NL"."DATE_REACHED"<TIMESTAMP' 2012-09-01 00:00:00')
57 - access("FHH"."ID"="OD"."NAD_ID")
61 - access("OH"."SYSTEM_NO"="OD"."SYSTEM_NUMBER")
filter("OH"."SYSTEM_NO">0)
Please help me.
Thanks.
|
|
|
|
|
|
| Re: Please help me my query performance is bad. [message #601390 is a reply to message #601388] |
Wed, 20 November 2013 08:22  |
cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Looks like the pain is all here:
| 60 | TABLE ACCESS BY INDEX ROWID | WEDB_MAN_UNIT | 10055 | 7 | 8481 |00:04:34.07 | 3113K| 27784 | | | |
|* 61 | INDEX RANGE SCAN | WEDB_GMT_UNIT_N2 | 10055 | 1 | 8481 |00:03:41.59 | 3105K| 22456 | | | |
Oracle thought the query was going to find 7 rows in WEDB_MAN_UNIT, instead it found 8481.
Are statistics on that table up to date and what columns is WEDB_GMT_UNIT_N2 on?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Please help me my query performance is bad. [message #601553 is a reply to message #601541] |
Fri, 22 November 2013 02:00 |
John Watson
Messages: 8966
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
No promises, but you could try hinting a hash join between WEDB_MAN_UNIT OH and NAD_DETAILS_ND_VW OD. Given the apparent cardinality estimation error,that might help.
I think the problem is with the outer join at operation ID 44. That is forcing 10055 look ups into webdb_man_unit at IDs 60 and 61, all of which retrieve 8481 rows. My first question is always "do you rally need the outer join?" Often, in my experience, developers use an outer join when an inner join would do. Why? Either because they do not understand their data, or they do not have adequate constraints. Ask them why they need it. If they do, and that simple hint doesn't help, I think you have to look at major restructuring of the statement
--update: typo, where I say ID 44, I mean ID 43. Sorry about that.
[Updated on: Fri, 22 November 2013 02:07] Report message to a moderator |
|
|
|
| Re: Please help me my query performance is bad. [message #601555 is a reply to message #601553] |
Fri, 22 November 2013 02:25 |
John Watson
Messages: 8966
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Having had a another quick look (I'm procrastinating: I have to start a job this morning that is going to be really difficult) I think we can say with certainty that the outer joins are not needed. The view you materialize between IDs 41 and 61 consists of 10055 rows, which you then cut down to 20 rows with a row number filter. If you are going to throw away 10035 rows, does it really matter which ones remain? I don't think so. Get rid of the outer joins to NAD_DETAILS_ND_VW OD and WEDB_MAN_UNIT OH and UNIT_SOURCE US, and see what happens.
[Updated on: Fri, 22 November 2013 02:28] Report message to a moderator |
|
|
|
| Re: Please help me my query performance is bad. [message #601561 is a reply to message #601555] |
Fri, 22 November 2013 04:07 |
cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I really don't see anyway you can say that just becuase we're cutting down a load of rows in an ordered list to 20, the outer-joins don't matter.
It looks like a pagination query to get data ordered by NAD_LISTER.ID.
So the only thing that determines if those joins need to be inner or outer is if the data being retrieved from those tables is optional or not.
You can't tell that from the query or the plan.
|
|
|
|
|
|
|
|
|
|
| Re: Please help me my query performance is bad. [message #601671 is a reply to message #601574] |
Sun, 24 November 2013 02:41 |
John Watson
Messages: 8966
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
ramya_162 wrote on Fri, 22 November 2013 11:22Hi,
I have tried using hash join hint WEDB_MAN_UNIT OH and NAD_DETAILS_ND_VW OD
but no improvement.
Please help me .How to proceed.
Thanks. What do you mean, "no improvement"? I've reduced your query time from 5:13.35 to 3:49.62. That's 25% faster already.
You might want to say "thank you". I think I shall say "if you want more help, you can pay for consultancy services"
--
John Watson
Oracle Certified Master DBA
|
|
|
|
| Re: Please help me my query performance is bad. [message #601680 is a reply to message #601671] |
Sun, 24 November 2013 06:51 |
|
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
|
Senior Member |
|
|
Hi,
Actually these three are not constant we are sending as variables front frot end.
WHERE NL.MAINUNIT_ID = 262
AND NL.DATE_REACHED >= TRUNC (TO_DATE('01-MAR-2012','DD-MON-YYYY'))
AND NL.DATE_REACHED < TRUNC (TO_DATE('31-AUG-2012','DD-MON-YYYY'))+1);
WHERE NL.MAINUNIT_ID = :MAINUNIT_ID
AND NL.DATE_REACHED >= TRUNC (:DATE_FROM)
AND NL.DATE_REACHED < TRUNC (:DATE_TO)+1);
Based on the values performance is vary with hint and without hint(/*+ use_hash(OH OD) */).
In some cases without hint is fast and in some cases with hint is fast.
Please help me how to improve the performance of the query for any input values.
Without /*+ use_hash(OH OD) */ hint took less time.
Plan hash value: 2350839375
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:42.51 | 74740 | 72089 | | | |
| 1 | SORT AGGREGATE | | 10 | 1 | 10 |00:00:00.42 | 1212 | 115 | | | |
| 2 | VIEW | TRANSACTIONS_VW | 10 | 2 | 10 |00:00:00.42 | 1212 | 115 | | | |
| 3 | UNION-ALL | | 10 | | 10 |00:00:00.42 | 1212 | 115 | | | |
|* 4 | HASH JOIN OUTER | | 10 | 1 | 10 |00:00:00.42 | 1182 | 115 | 1035K| 1035K| 410K (0)|
| 5 | NESTED LOOPS OUTER | | 10 | 1 | 10 |00:00:00.27 | 1152 | 108 | | | |
| 6 | NESTED LOOPS | | 10 | 1 | 10 |00:00:00.01 | 52 | 0 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | NAD_LISTER | 10 | 1 | 10 |00:00:00.01 | 37 | 0 | | | |
|* 8 | INDEX UNIQUE SCAN | PK_NAD_LISTER | 10 | 1 | 10 |00:00:00.01 | 27 | 0 | | | |
|* 9 | INDEX UNIQUE SCAN | PK_MAINUNIT | 10 | 1388 | 10 |00:00:00.01 | 15 | 0 | | | |
|* 10 | TABLE ACCESS FULL | ROUND_UNIT | 10 | 1 | 0 |00:00:00.27 | 1100 | 108 | | | |
| 11 | VIEW | | 10 | 1 | 0 |00:00:00.15 | 30 | 7 | | | |
|* 12 | FILTER | | 10 | | 0 |00:00:00.15 | 30 | 7 | | | |
| 13 | SORT GROUP BY | | 10 | 1 | 0 |00:00:00.15 | 30 | 7 | 1024 | 1024 | |
| 14 | NESTED LOOPS | | 10 | 1 | 0 |00:00:00.15 | 30 | 7 | | | |
| 15 | NESTED LOOPS | | 10 | 1 | 0 |00:00:00.15 | 30 | 7 | | | |
| 16 | NESTED LOOPS | | 10 | 1 | 0 |00:00:00.15 | 30 | 7 | | | |
| 17 | TABLE ACCESS BY INDEX ROWID| TRANSACTION_HISTORY | 10 | 1 | 0 |00:00:00.15 | 30 | 7 | | | |
|* 18 | INDEX RANGE SCAN | TRANSACTION_HISTORY_IDX1 | 10 | 1 | 0 |00:00:00.15 | 30 | 7 | | | |
| 19 | TABLE ACCESS BY INDEX ROWID| TRANSACTION_HISTORY | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 20 | INDEX RANGE SCAN | TRANSACTION_HISTORY_IDX1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 21 | TABLE ACCESS BY INDEX ROWID | MAINUNIT_TRANSITION_REASON | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 22 | INDEX UNIQUE SCAN | PK_MAINUNIT_TRANSITION_REASON | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 23 | INDEX UNIQUE SCAN | PK_MAINUNIT_TRANSITION | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 24 | NESTED LOOPS | | 10 | | 0 |00:00:00.01 | 30 | 0 | | | |
| 25 | NESTED LOOPS | | 10 | 1 | 0 |00:00:00.01 | 30 | 0 | | | |
| 26 | TABLE ACCESS BY INDEX ROWID | TRANSACTION_HISTORY | 10 | 1 | 0 |00:00:00.01 | 30 | 0 | | | |
|* 27 | INDEX RANGE SCAN | TRANSACTION_HISTORY_IDX1 | 10 | 1 | 0 |00:00:00.01 | 30 | 0 | | | |
|* 28 | INDEX UNIQUE SCAN | PK_MAINUNIT_TRANSITION_REASON | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 29 | TABLE ACCESS BY INDEX ROWID | MAINUNIT_TRANSITION_REASON | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 30 | SORT AGGREGATE | | 10 | 1 | 10 |00:00:00.36 | 71 | 21 | | | |
|* 31 | FILTER | | 10 | | 10 |00:00:00.36 | 71 | 21 | | | |
| 32 | TABLE ACCESS BY INDEX ROWID | NAD_DETAILS | 10 | 1 | 10 |00:00:00.15 | 31 | 14 | | | |
|* 33 | INDEX RANGE SCAN | NAD_DETAILS_IDX1 | 10 | 1 | 10 |00:00:00.06 | 22 | 6 | | | |
|* 34 | FILTER | | 10 | | 0 |00:00:00.21 | 40 | 7 | | | |
|* 35 | TABLE ACCESS BY INDEX ROWID | NAD_DETAILS | 10 | 1 | 0 |00:00:00.21 | 40 | 7 | | | |
|* 36 | INDEX UNIQUE SCAN | PK_NAD_DETAILS | 10 | 1 | 10 |00:00:00.21 | 30 | 7 | | | |
|* 37 | FILTER | | 0 | | 0 |00:00:00.01 | 0 | 0 | | | |
| 38 | HASH GROUP BY | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | 1023K| 1023K| |
| 39 | TABLE ACCESS BY INDEX ROWID | NAD_DETAILS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 40 | INDEX RANGE SCAN | NAD_DETAILS_IDX1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 41 | VIEW | | 1 | 14 | 10 |00:00:42.51 | 74740 | 72089 | | | |
|* 42 | WINDOW SORT PUSHED RANK | | 1 | 14 | 10 |00:00:39.69 | 72164 | 71862 | 9216 | 9216 | 8192 (0)|
| 43 | NESTED LOOPS OUTER | | 1 | 14 | 10 |00:00:39.69 | 72164 | 71862 | | | |
|* 44 | HASH JOIN OUTER | | 1 | 5 | 10 |00:00:39.59 | 72132 | 71855 | 707K| 707K| 1169K (0)|
| 45 | NESTED LOOPS OUTER | | 1 | 3 | 10 |00:00:00.51 | 308 | 48 | | | |
| 46 | MERGE JOIN CARTESIAN | | 1 | 3 | 10 |00:00:00.49 | 289 | 45 | | | |
| 47 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.37 | 276 | 32 | | | |
|* 48 | TABLE ACCESS BY INDEX ROWID | MAINUNIT | 1 | 1 | 1 |00:00:00.35 | 274 | 30 | | | |
|* 49 | INDEX UNIQUE SCAN | PK_MAINUNIT | 1 | 1 | 1 |00:00:00.01 | 2 | 2 | | | |
| 50 | TABLE ACCESS BY INDEX ROWID | WEDB_SOURCE | 1 | 1 | 1 |00:00:00.02 | 2 | 2 | | | |
|* 51 | INDEX RANGE SCAN | WEDB_SOURCE_BUID | 1 | 1 | 1 |00:00:00.01 | 1 | 1 | | | |
| 52 | BUFFER SORT | | 1 | 3 | 10 |00:00:00.12 | 13 | 13 | 2048 | 2048 | 2048 (0)|
| 53 | TABLE ACCESS BY INDEX ROWID | NAD_LISTER | 1 | 3 | 10 |00:00:00.12 | 13 | 13 | | | |
|* 54 | INDEX RANGE SCAN | IDX_MAINUNIT_ID2 | 1 | 3 | 10 |00:00:00.02 | 3 | 3 | | | |
| 55 | TABLE ACCESS BY INDEX ROWID | UNIT_SOURCE | 10 | 1 | 10 |00:00:00.02 | 19 | 3 | | | |
|* 56 | INDEX UNIQUE SCAN | PK_UNIT_SOURCE | 10 | 1 | 10 |00:00:00.01 | 9 | 2 | | | |
| 57 | VIEW | NAD_DETAILS_FH_V | 1 | 3210K| 3210K|00:00:31.26 | 71824 | 71807 | | | |
|* 58 | HASH JOIN RIGHT SEMI | | 1 | 3210K| 3210K|00:00:26.00 | 71824 | 71807 | 68M| 7188K| 111M (0)|
| 59 | INDEX FAST FULL SCAN | PK_NAD_LISTER | 1 | 2423K| 2423K|00:00:03.69 | 5453 | 5440 | | | |
| 60 | TABLE ACCESS FULL | NAD_DETAILS | 1 | 3210K| 3210K|00:00:04.23 | 66371 | 66367 | | | |
|* 61 | INDEX RANGE SCAN | WEDB_MAN_UNIT_N5 | 10 | 3 | 10 |00:00:00.10 | 32 | 7 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("FTH"."NAD_ID"="FH"."ID")
8 - access("FH"."ID"=:B1)
9 - access("W1"."ID"="FH"."MAINUNIT_ID")
10 - filter("RR"."ID"="FH"."APPLIED_ROUND_UNIT")
12 - filter("FHIS"."MODIFIED_DATE"=MIN("MODIFIED_DATE"))
18 - access("NAD_ID"=:B1)
20 - access("FHIS"."NAD_ID"=:B1)
filter("FHIS"."NAD_ID"="NAD_ID")
22 - access("WTR"."ID"="FHIS"."TRANSITION_REASON_ID")
23 - access("WT"."ID"="WTR"."MAINUNIT_TRANSITION_ID")
27 - access("FHIS"."NAD_ID"=:B1)
28 - access("WTR"."ID"="FHIS"."TRANSITION_REASON_ID")
29 - filter("WTR"."MAINUNIT_TRANSITION_ID" IS NOT NULL)
31 - filter( IS NULL)
33 - access("OD"."NAD_ID"=:B1)
34 - filter( IS NOT NULL)
35 - filter("SYSTEM_NUMBER" IS NULL)
36 - access("ID"=:B1)
37 - filter(("RENDER_NO"=:B1 AND COUNT(*)>1))
40 - access("NAD_ID"=:B1)
41 - filter(("RN">=1 AND "RN"<=20))
42 - filter(ROW_NUMBER() OVER ( ORDER BY "FH"."ID")<=20)
44 - access("OD"."NAD_ID"="FH"."ID")
48 - filter("WG"."BUID"="MANAGECONFIG"."BU_ID"())
49 - access("WG"."ID"=262)
51 - access("GBM"."BU_ID"="MANAGECONFIG"."BU_ID"())
54 - access("FH"."MAINUNIT_ID"=262 AND "FH"."DATE_REACHED">=TIMESTAMP' 2012-03-01 00:00:00' AND "FH"."DATE_REACHED"<TIMESTAMP' 2012-09-01 00:00:00')
56 - access("FS"."ID"="FH"."UNIT_SOURCE")
58 - access("FHH"."ID"="OD"."NAD_ID")
61 - access("OH"."SYSTEM_NO"="OD"."SYSTEM_NUMBER")
filter("OH"."SYSTEM_NO">0)
With /*+ use_hash(OH OD) */ hint took more time.
Plan hash value: 182466403
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:01:36.65 | 175K| 172K| | | |
| 1 | SORT AGGREGATE | | 10 | 1 | 10 |00:00:00.28 | 1212 | 115 | | | |
| 2 | VIEW | TRANSACTIONS_VW | 10 | 2 | 10 |00:00:00.28 | 1212 | 115 | | | |
| 3 | UNION-ALL | | 10 | | 10 |00:00:00.28 | 1212 | 115 | | | |
|* 4 | HASH JOIN OUTER | | 10 | 1 | 10 |00:00:00.28 | 1182 | 115 | 1035K| 1035K| 418K (0)|
| 5 | NESTED LOOPS OUTER | | 10 | 1 | 10 |00:00:00.21 | 1152 | 108 | | | |
| 6 | NESTED LOOPS | | 10 | 1 | 10 |00:00:00.01 | 52 | 0 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | NAD_LISTER | 10 | 1 | 10 |00:00:00.01 | 37 | 0 | | | |
|* 8 | INDEX UNIQUE SCAN | PK_NAD_LISTER | 10 | 1 | 10 |00:00:00.01 | 27 | 0 | | | |
|* 9 | INDEX UNIQUE SCAN | PK_MAINUNIT | 10 | 1388 | 10 |00:00:00.01 | 15 | 0 | | | |
|* 10 | TABLE ACCESS FULL | ROUND_UNIT | 10 | 1 | 0 |00:00:00.21 | 1100 | 108 | | | |
| 11 | VIEW | | 10 | 1 | 0 |00:00:00.07 | 30 | 7 | | | |
|* 12 | FILTER | | 10 | | 0 |00:00:00.07 | 30 | 7 | | | |
| 13 | SORT GROUP BY | | 10 | 1 | 0 |00:00:00.07 | 30 | 7 | 1024 | 1024 | |
| 14 | NESTED LOOPS | | 10 | 1 | 0 |00:00:00.07 | 30 | 7 | | | |
| 15 | NESTED LOOPS | | 10 | 1 | 0 |00:00:00.07 | 30 | 7 | | | |
| 16 | NESTED LOOPS | | 10 | 1 | 0 |00:00:00.06 | 30 | 7 | | | |
| 17 | TABLE ACCESS BY INDEX ROWID| TRANSACTION_HISTORY | 10 | 1 | 0 |00:00:00.06 | 30 | 7 | | | |
|* 18 | INDEX RANGE SCAN | TRANSACTION_HISTORY_IDX1 | 10 | 1 | 0 |00:00:00.06 | 30 | 7 | | | |
| 19 | TABLE ACCESS BY INDEX ROWID| TRANSACTION_HISTORY | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 20 | INDEX RANGE SCAN | TRANSACTION_HISTORY_IDX1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 21 | TABLE ACCESS BY INDEX ROWID | MAINUNIT_TRANSITION_REASON | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 22 | INDEX UNIQUE SCAN | PK_MAINUNIT_TRANSITION_REASON | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 23 | INDEX UNIQUE SCAN | PK_MAINUNIT_TRANSITION | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 24 | NESTED LOOPS | | 10 | | 0 |00:00:00.01 | 30 | 0 | | | |
| 25 | NESTED LOOPS | | 10 | 1 | 0 |00:00:00.01 | 30 | 0 | | | |
| 26 | TABLE ACCESS BY INDEX ROWID | TRANSACTION_HISTORY | 10 | 1 | 0 |00:00:00.01 | 30 | 0 | | | |
|* 27 | INDEX RANGE SCAN | TRANSACTION_HISTORY_IDX1 | 10 | 1 | 0 |00:00:00.01 | 30 | 0 | | | |
|* 28 | INDEX UNIQUE SCAN | PK_MAINUNIT_TRANSITION_REASON | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 29 | TABLE ACCESS BY INDEX ROWID | MAINUNIT_TRANSITION_REASON | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 30 | SORT AGGREGATE | | 10 | 1 | 10 |00:00:00.20 | 71 | 21 | | | |
|* 31 | FILTER | | 10 | | 10 |00:00:00.20 | 71 | 21 | | | |
| 32 | TABLE ACCESS BY INDEX ROWID | NAD_DETAILS | 10 | 1 | 10 |00:00:00.12 | 31 | 14 | | | |
|* 33 | INDEX RANGE SCAN | NAD_DETAILS_IDX1 | 10 | 1 | 10 |00:00:00.05 | 22 | 6 | | | |
|* 34 | FILTER | | 10 | | 0 |00:00:00.08 | 40 | 7 | | | |
|* 35 | TABLE ACCESS BY INDEX ROWID | NAD_DETAILS | 10 | 1 | 0 |00:00:00.08 | 40 | 7 | | | |
|* 36 | INDEX UNIQUE SCAN | PK_NAD_DETAILS | 10 | 1 | 10 |00:00:00.08 | 30 | 7 | | | |
|* 37 | FILTER | | 0 | | 0 |00:00:00.01 | 0 | 0 | | | |
| 38 | HASH GROUP BY | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | 1023K| 1023K| |
| 39 | TABLE ACCESS BY INDEX ROWID | NAD_DETAILS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 40 | INDEX RANGE SCAN | NAD_DETAILS_IDX1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 41 | VIEW | | 1 | 14 | 10 |00:01:36.65 | 175K| 172K| | | |
|* 42 | WINDOW SORT PUSHED RANK | | 1 | 14 | 10 |00:01:34.96 | 172K| 172K| 9216 | 9216 | 8192 (0)|
|* 43 | HASH JOIN OUTER | | 1 | 14 | 10 |00:01:34.96 | 172K| 172K| 696K| 696K| 1145K (0)|
|* 44 | HASH JOIN OUTER | | 1 | 5 | 10 |00:00:39.14 | 72132 | 71855 | 707K| 707K| 1160K (0)|
| 45 | NESTED LOOPS OUTER | | 1 | 3 | 10 |00:00:00.57 | 308 | 48 | | | |
| 46 | MERGE JOIN CARTESIAN | | 1 | 3 | 10 |00:00:00.54 | 289 | 45 | | | |
| 47 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.37 | 276 | 32 | | | |
|* 48 | TABLE ACCESS BY INDEX ROWID | MAINUNIT | 1 | 1 | 1 |00:00:00.34 | 274 | 30 | | | |
|* 49 | INDEX UNIQUE SCAN | PK_MAINUNIT | 1 | 1 | 1 |00:00:00.01 | 2 | 2 | | | |
| 50 | TABLE ACCESS BY INDEX ROWID | WEDB_SOURCE | 1 | 1 | 1 |00:00:00.03 | 2 | 2 | | | |
|* 51 | INDEX RANGE SCAN | WEDB_SOURCE_BUID | 1 | 1 | 1 |00:00:00.02 | 1 | 1 | | | |
| 52 | BUFFER SORT | | 1 | 3 | 10 |00:00:00.17 | 13 | 13 | 2048 | 2048 | 2048 (0)|
| 53 | TABLE ACCESS BY INDEX ROWID | NAD_LISTER | 1 | 3 | 10 |00:00:00.17 | 13 | 13 | | | |
|* 54 | INDEX RANGE SCAN | IDX_MAINUNIT_ID2 | 1 | 3 | 10 |00:00:00.02 | 3 | 3 | | | |
| 55 | TABLE ACCESS BY INDEX ROWID | UNIT_SOURCE | 10 | 1 | 10 |00:00:00.03 | 19 | 3 | | | |
|* 56 | INDEX UNIQUE SCAN | PK_UNIT_SOURCE | 10 | 1 | 10 |00:00:00.01 | 9 | 2 | | | |
| 57 | VIEW | NAD_DETAILS_FH_V | 1 | 3210K| 3210K|00:00:30.83 | 71824 | 71807 | | | |
|* 58 | HASH JOIN RIGHT SEMI | | 1 | 3210K| 3210K|00:00:25.60 | 71824 | 71807 | 68M| 7188K| 111M (0)|
| 59 | INDEX FAST FULL SCAN | PK_NAD_LISTER | 1 | 2423K| 2423K|00:00:03.63 | 5453 | 5440 | | | |
| 60 | TABLE ACCESS FULL | NAD_DETAILS | 1 | 3210K| 3210K|00:00:04.07 | 66371 | 66367 | | | |
|* 61 | INDEX FAST FULL SCAN | WEDB_MAN_UNIT_N5 | 1 | 8569K| 8507K|00:00:34.79 | 100K| 100K| | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("FTH"."NAD_ID"="FH"."ID")
8 - access("FH"."ID"=:B1)
9 - access("W1"."ID"="FH"."MAINUNIT_ID")
10 - filter("RR"."ID"="FH"."APPLIED_ROUND_UNIT")
12 - filter("FHIS"."MODIFIED_DATE"=MIN("MODIFIED_DATE"))
18 - access("NAD_ID"=:B1)
20 - access("FHIS"."NAD_ID"=:B1)
filter("FHIS"."NAD_ID"="NAD_ID")
22 - access("WTR"."ID"="FHIS"."TRANSITION_REASON_ID")
23 - access("WT"."ID"="WTR"."MAINUNIT_TRANSITION_ID")
27 - access("FHIS"."NAD_ID"=:B1)
28 - access("WTR"."ID"="FHIS"."TRANSITION_REASON_ID")
29 - filter("WTR"."MAINUNIT_TRANSITION_ID" IS NOT NULL)
31 - filter( IS NULL)
33 - access("OD"."NAD_ID"=:B1)
34 - filter( IS NOT NULL)
35 - filter("SYSTEM_NUMBER" IS NULL)
36 - access("ID"=:B1)
37 - filter(("RENDER_NO"=:B1 AND COUNT(*)>1))
40 - access("NAD_ID"=:B1)
41 - filter(("RN">=1 AND "RN"<=20))
42 - filter(ROW_NUMBER() OVER ( ORDER BY "FH"."ID")<=20)
44 - access("OD"."NAD_ID"="FH"."ID")
48 - filter("WG"."BUID"="MANAGECONFIG"."BU_ID"())
49 - access("WG"."ID"=262)
51 - access("GBM"."BU_ID"="MANAGECONFIG"."BU_ID"())
54 - access("FH"."MAINUNIT_ID"=262 AND "FH"."DATE_REACHED">=TIMESTAMP' 2012-03-01 00:00:00' AND "FH"."DATE_REACHED"<TIMESTAMP' 2012-09-01 00:00:00')
56 - access("FS"."ID"="FH"."UNIT_SOURCE")
58 - access("FHH"."ID"="OD"."NAD_ID")
61 - access("OH"."SYSTEM_NO"="OD"."SYSTEM_NUMBER")
filter("OH"."SYSTEM_NO">0)
Please help me.
Thanks.
[Updated on: Sun, 24 November 2013 07:03] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Please help me my query performance is bad. [message #601706 is a reply to message #601680] |
Sun, 24 November 2013 12:48 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Ramya,
If you have noticed that the plan hash value has changed for the same SQL you used with the hint. It is due to the bind variables you are using in your query through frontend. So, now you have a different challenge to handle the adaptive cursor sharing technique with bind variables. With the skewness of data you would see the execution plan to vary. So, start thinking all over again in a new direction as pointed out to you.
Are the bind vaiables being used only for date fields or any other fields too?
[Updated on: Sun, 24 November 2013 22:41] Report message to a moderator |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
