Home » RDBMS Server » Performance Tuning » Tuning Merge Statetment (Oracle 10.2.0.4 Enterprise Edition)
| Tuning Merge Statetment [message #507997] |
Wed, 18 May 2011 16:47  |
getzeeshan
Messages: 65
Registered: July 2008
|
Member |
|
|
Hi Team
One of our developers is running the following query
=========================================
alter session enable parallel dml;
merge /*+ parallel(t_cust_email,8) append */ into hh_cdw.t_cust_email c
using (select distinct cdw.customer_id,c.code_channel_type,c.ind_prefer,c.channel_addr as email_addr,c.status,c.audit_time,c.purge_date
from hh_cdw.v_mem_channel c, t_hhonors_mem cdw
where c.channel_addr is not null
and c.code_channel = 'EMAIL'
and c.id_member = cdw.id_member
and c.status = 'A'
and c.audit_time >= '23-APR-2011'
and c.audit_time = (select max(audit_time)
from v_mem_channel m
where m.channel_addr = c.channel_addr
-- and m.id_member = c.id_member
and m.audit_time >= '23-APR-2011'
and m.code_channel = 'EMAIL'
and m.status = 'A'
and m.code_channel_type = c.code_channel_type)) s
on (c.customer_id = s.customer_id
and c.email_addr = s.email_addr
and c.email_type = s.code_channel_type )
when matched then update set c.src_update_date = greatest(s.audit_time,s.purge_date),
c.update_date = systimestamp
where c.update_source = 'HHMEMBASE'
when not matched then insert (cust_email_id,email_addr,email_scrubbed,email_status,row_id,preferred_flag,email_type,src_create_date,src_update_date, create_date,create_user,update_date,update_user,status,customer_id,update_source,deliverable_flag) values (cust_email_id.nextval, s.email_addr,s.email_addr,s.status,null,s.ind_prefer,s.code_channel_type, s.audit_time,greatest(s.audit_time,s.purge_date),sysdate,user,sysdate,user,'A',s.customer_id,'HHMEMBASE','Y');
======================
Can anyone help me tune this. This is now running over 9 hours (still going on)
Any idea?
--Zee
* < code tags > added by BlackSwan
[Updated on: Wed, 18 May 2011 17:13] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Tuning Merge Statetment [message #508130 is a reply to message #508004] |
Thu, 19 May 2011 09:28  |
getzeeshan
Messages: 65
Registered: July 2008
|
Member |
|
|
As per my SQLPLUS Session it does not let me take an explain plan - it throws sequence errors, henceforth that was the reason I could not follow the posting guidelines of ORAFAQ..therefore the only way was that I copied the SQL text from OEM and also the uploaded files with Explain plan and the stats from the OEM.
Please let me know if this does not work?
--Zee
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Tuning Merge Statetment [message #508175 is a reply to message #508172] |
Thu, 19 May 2011 13:12 |
getzeeshan
Messages: 65
Registered: July 2008
|
Member |
|
|
The error I get when I run the explain plan is
""ORA-02289: sequence does not exist""
this is for the last part of the query i.e. its asking for seq number for CUST_EMAIL_ID
when not matched then insert (cust_email_id,email_addr,email_scrubbed,email_status,row_id,preferred_flag,email_type,src_create_date,src_update_date, create_date,create_user,update_date,update_user,status,customer_id,update_source,deliverable_flag) values (cust_email_id.nextval, s.email_addr,s.email_addr,s.status,null,s.ind_prefer,s.code_channel_type, s.audit_time,greatest(s.audit_time,s.purge_date),sysdate,user,sysdate,user,'A',s.customer_id,'HHMEMBASE','Y');
|
|
|
|
|
|
|
|
| Re: Tuning Merge Statetment [message #508359 is a reply to message #508198] |
Fri, 20 May 2011 10:36 |
getzeeshan
Messages: 65
Registered: July 2008
|
Member |
|
|
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- ------------
Plan hash value: 2811015489
------------------------------------------------------------------------------------------------------------------------------------- ----------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------- ----------
| 0 | MERGE STATEMENT | | 26M| 7200M| | 4951K (4)| 12:35:42 | | | |
| 1 | MERGE | T_CUST_EMAIL | | | | | | | | |
| 2 | VIEW | | | | | | | | | |
| 3 | SEQUENCE | CUST_EMAIL_ID | | | | | | | | |
|* 4 | HASH JOIN RIGHT OUTER | | 26M| 5124M| 147M| 4951K (4)| 12:35:42 | | | |
|* 5 | TABLE ACCESS FULL | T_CUST_EMAIL | 18M| 2134M| | 93811 (7)| 00:14:20 | | | |
| 6 | VIEW | | 26M| 2101M| | 4851K (4)| 12:20:24 | | | |
| 7 | SORT UNIQUE | | 26M| 2793M| 386G| 4851K (4)| 12:20:24 | | | |
|* 8 | FILTER | | | | | | | | | |
| 9 | PX COORDINATOR | | | | | | | | | |
| 10 | PX SEND QC (RANDOM) | :TQ10002 | 58M| 6079M| | 1158K (5)| 02:56:52 | Q1,02 | P->S | QC (RAND) |
| 11 | MERGE JOIN | | 58M| 6079M| | 1158K (5)| 02:56:52 | Q1,02 | PCWP | |
| 12 | BUFFER SORT | | | | | | | Q1,02 | PCWC | |
| 13 | PX RECEIVE | | 58M| 5298M| | 1146K (5)| 02:55:01 | Q1,02 | PCWP | |
| 14 | PX SEND HASH | :TQ10000 | 58M| 5298M| | 1146K (5)| 02:55:01 | | S->P | HASH |
|* 15 | VIEW | | 58M| 5298M| | 1146K (5)| 02:55:01 | | | |
| 16 | WINDOW SORT | | 58M| 5131M| 12G| 1146K (5)| 02:55:01 | | | |
| 17 | VIEW | | 58M| 5131M| | 466K (8)| 01:11:10 | | | |
| 18 | UNION-ALL | | | | | | | | | |
| 19 | MAT_VIEW ACCESS FULL | T_MEM_CHANNEL | 44M| 1585M| | 54938 (11)| 00:08:24 | | | |
|* 20 | HASH JOIN | | 13M| 1202M| 724M| 411K (8)| 01:02:47 | | | |
|* 21 | VIEW | | 13M| 569M| | 176K (9)| 00:26:57 | | | |
| 22 | WINDOW SORT | | 13M| 375M| 1142M| 176K (9)| 00:26:57 | | | |
|* 23 | MAT_VIEW ACCESS FULL| A_MEM_CHANNEL | 13M| 375M| | 115K (10)| 00:17:40 | | | |
| 24 | MAT_VIEW ACCESS FULL | A_MEM_CHANNEL | 81M| 3802M| | 115K (10)| 00:17:38 | | | |
|* 25 | SORT JOIN | | 28M| 385M| 1324M| 12114 (4)| 00:01:51 | Q1,02 | PCWP | |
| 26 | PX RECEIVE | | 28M| 385M| | 6444 (1)| 00:01:00 | Q1,02 | PCWP | |
| 27 | PX SEND HASH | :TQ10001 | 28M| 385M| | 6444 (1)| 00:01:00 | Q1,01 | P->P | HASH |
| 28 | PX BLOCK ITERATOR | | 28M| 385M| | 6444 (1)| 00:01:00 | Q1,01 | PCWC | |
| 29 | TABLE ACCESS FULL | T_HHONORS_MEM | 28M| 385M| | 6444 (1)| 00:01:00 | Q1,01 | PCWP | |
| 30 | SORT AGGREGATE | | 1 | 70 | | | | | | |
|* 31 | VIEW | | 58M| 3904M| | 1119K (5)| 02:50:53 | | | |
| 32 | WINDOW SORT | | 58M| 4963M| 12G| 1119K (5)| 02:50:53 | | | |
| 33 | VIEW | | 58M| 4963M| | 462K (8)| 01:10:40 | | | |
| 34 | UNION-ALL | | | | | | | | | |
| 35 | MAT_VIEW ACCESS FULL | T_MEM_CHANNEL | 44M| 1499M| | 54938 (11)| 00:08:24 | | | |
|* 36 | HASH JOIN | | 13M| 1177M| 724M| 408K (8)| 01:02:17 | | | |
|* 37 | VIEW | | 13M| 569M| | 176K (9)| 00:26:57 | | | |
| 38 | WINDOW SORT | | 13M| 375M| 1142M| 176K (9)| 00:26:57 | | | |
|* 39 | MAT_VIEW ACCESS FULL | A_MEM_CHANNEL | 13M| 375M| | 115K (10)| 00:17:40 | | | |
| 40 | MAT_VIEW ACCESS FULL | A_MEM_CHANNEL | 81M| 3647M| | 115K (10)| 00:17:38 | | | |
------------------------------------------------------------------------------------------------------------------------------------- ----------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("C"."EMAIL_TYPE"(+)="S"."CODE_CHANNEL_TYPE" AND "C"."EMAIL_ADDR"(+)="S"."EMAIL_ADDR" AND
"C"."CUSTOMER_ID"(+)="S"."CUSTOMER_ID")
5 - filter("C"."EMAIL_TYPE"(+) IS NOT NULL)
8 - filter("AUDIT_TIME"= (SELECT MAX("AUDIT_TIME") FROM (SELECT "DGROUP" "DGROUP","ID_MEM_CHANNEL" "ID_MEM_CHANNEL","CODE_CHANNEL"
"CODE_CHANNEL","CODE_CHANNEL_TYPE" "CODE_CHANNEL_TYPE","CHANNEL_STATUS" "CHANNEL_STATUS","IND_PREFER" "IND_PREFER","STATUS"
"STATUS","ID_MEMBER" "ID_MEMBER","BEG_DATE" "BEG_DATE","END_DATE" "END_DATE","CHANNEL_ADDR" "CHANNEL_ADDR","TITLE"
"TITLE","COMPANY_NAME" "COMPANY_NAME","ADDR1" "ADDR1","ADDR2" "ADDR2","ADDR3" "ADDR3","CITY" "CITY","CODE_STATE" "CODE_STATE","ZIP"
"ZIP","CODE_POSTAL" "CODE_POSTAL","CODE_COUNTRY" "CODE_COUNTRY","USERID" "USERID","AUDIT_TIME" "AUDIT_TIME","PURGE_DATE"
"PURGE_DATE",FIRST_VALUE("DGROUP") OVER ( PARTITION BY "A"."ID_MEMBER" ORDER BY INTERNAL_FUNCTION("PURGE_DATE") DESC RANGE BETWEE
N
UNBOUNDED PRECEDING AND CURRENT ROW ) "FIRST" FROM ( (SELECT 1 "DGROUP","ID_MEM_CHANNEL" "ID_MEM_CHANNEL","CODE_CHANNEL"
"CODE_CHANNEL","CODE_CHANNEL_TYPE" "CODE_CHANNEL_TYPE","CHANNEL_STATUS" "CHANNEL_STATUS","IND_PREFER" "IND_PREFER","STATUS"
"STATUS","ID_MEMBER" "ID_MEMBER","BEG_DATE" "BEG_DATE","END_DATE" "END_DATE","CHANNEL_ADDR" "CHANNEL_ADDR","TITLE"
"TITLE","COMPANY_NAME" "COMPANY_NAME","ADDR1" "ADDR1","ADDR2" "ADDR2","ADDR3" "ADDR3","CITY" "CITY","CODE_STATE" "CODE_STATE","ZIP"
"ZIP","CODE_POSTAL" "CODE_POSTAL","CODE_COUNTRY" "CODE_COUNTRY","USERID" "USERID","AUDIT_TIME" "AUDIT_TIME",NULL "PURGE_DATE" FROM
"HH"."T_MEM_CHANNEL" "T_MEM_CHANNEL") UNION ALL (SELECT 2 "DGROUP","A"."ID_MEM_CHANNEL" "ID_MEM_CHANNEL","A"."CODE_CHANNEL"
"CODE_CHANNEL","A"."CODE_CHANNEL_TYPE" "CODE_CHANNEL_TYPE","A"."CHANNEL_STATUS" "CHANNEL_STATUS","A"."IND_PREFER"
"IND_PREFER","A"."STATUS" "STATUS","A"."ID_MEMBER" "ID_MEMBER","A"."BEG_DATE" "BEG_DATE","A"."END_DATE" "END_DATE","A"."CHANNEL_ADD
R"
"CHANNEL_ADDR","A"."TITLE" "TITLE","A"."COMPANY_NAME" "COMPANY_NAME","A"."ADDR1" "ADDR1","A"."ADDR2" "ADDR2","A"."ADDR3"
"ADDR3","A"."CITY" "CITY","A"."CODE_STATE" "CODE_STATE","A"."ZIP" "ZIP","A"."CODE_POSTAL" "CODE_POSTAL","A"."CODE_COUNTRY"
"CODE_COUNTRY","A"."USERID" "USERID","A"."AUDIT_TIME" "AUDIT_TIME","A"."PURGE_DATE" "PURGE_DATE" FROM (SELECT "ID_MEMBER"
"ID_MEMBER","PURGE_DATE" "PURGE_DATE","ID_MEM_CHANNEL" "ID_MEM_CHANNEL",FIRST_VALUE("PURGE_DATE") OVER ( PARTITION BY "ID_MEMBER"
ORDER BY INTERNAL_FUNCTION("PURGE_DATE") DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) "MAX_PURGE_DATE" FROM
"HHD"."A_MEM_CHANNEL" "A" WHERE "USERID"='PURGE') "from$_subquery$_042","HHD"."A_MEM_CHANNEL" "A" WHERE
"A"."ID_MEM_CHANNEL"="ID_MEM_CHANNEL" AND "A"."PURGE_DATE"="PURGE_DATE" AND "A"."ID_MEMBER"="ID_MEMBER" AND
"PURGE_DATE"="MAX_PURGE_DATE")) "A") "from$_subquery$_037" WHERE "CHANNEL_ADDR"=:B1 AND "AUDIT_TIME">=TO_DATE(' 2011-04-23 00:00:00
',
'syyyy-mm-dd hh24:mi:ss') AND "CODE_CHANNEL"='EMAIL' AND "STATUS"='A' AND "CODE_CHANNEL_TYPE"=:B2 AND "DGROUP"="FIRST"))
15 - filter("CHANNEL_ADDR" IS NOT NULL AND "CODE_CHANNEL"='EMAIL' AND "STATUS"='A' AND "AUDIT_TIME">=TO_DATE(' 2011-04-23 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "DGROUP"="FIRST")
20 - access("A"."ID_MEMBER"="ID_MEMBER" AND "A"."PURGE_DATE"="PURGE_DATE" AND "A"."ID_MEM_CHANNEL"="ID_MEM_CHANNEL")
21 - filter("PURGE_DATE"="MAX_PURGE_DATE")
23 - filter("USERID"='PURGE')
25 - access("ID_MEMBER"="CDW"."ID_MEMBER")
filter("ID_MEMBER"="CDW"."ID_MEMBER")
31 - filter("CHANNEL_ADDR"=:B1 AND "AUDIT_TIME">=TO_DATE(' 2011-04-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"CODE_CHANNEL"='EMAIL' AND "STATUS"='A' AND "CODE_CHANNEL_TYPE"=:B2 AND "DGROUP"="FIRST")
36 - access("A"."ID_MEMBER"="ID_MEMBER" AND "A"."PURGE_DATE"="PURGE_DATE" AND "A"."ID_MEM_CHANNEL"="ID_MEM_CHANNEL")
37 - filter("PURGE_DATE"="MAX_PURGE_DATE")
39 - filter("USERID"='PURGE')
89 rows selected.
Elapsed: 00:00:00.06
CM: added [code] tags, please do so yourself next time.
[Updated on: Fri, 20 May 2011 11:18] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Tuning Merge Statetment [message #508396 is a reply to message #508395] |
Fri, 20 May 2011 16:55 |
getzeeshan
Messages: 65
Registered: July 2008
|
Member |
|
|
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- ------------
Plan hash value: 2811015489
------------------------------------------------------------------------------------------------------------------------------------- ----------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------- ----------
| 0 | MERGE STATEMENT | | 26M| 7200M| | 4951K (4)| 12:35:42 | | | |
| 1 | MERGE | T_CUST_EMAIL | | | | | | | | |
| 2 | VIEW | | | | | | | | | |
| 3 | SEQUENCE | CUST_EMAIL_ID | | | | | | | | |
|* 4 | HASH JOIN RIGHT OUTER | | 26M| 5124M| 147M| 4951K (4)| 12:35:42 | | | |
|* 5 | TABLE ACCESS FULL | T_CUST_EMAIL | 18M| 2134M| | 93811 (7)| 00:14:20 | | | |
| 6 | VIEW | | 26M| 2101M| | 4851K (4)| 12:20:24 | | | |
| 7 | SORT UNIQUE | | 26M| 2793M| 386G| 4851K (4)| 12:20:24 | | | |
|* 8 | FILTER | | | | | | | | | |
| 9 | PX COORDINATOR | | | | | | | | | |
| 10 | PX SEND QC (RANDOM) | :TQ10002 | 58M| 6079M| | 1158K (5)| 02:56:52 | Q1,02 | P->S | QC (RAND) |
| 11 | MERGE JOIN | | 58M| 6079M| | 1158K (5)| 02:56:52 | Q1,02 | PCWP | |
| 12 | BUFFER SORT | | | | | | | Q1,02 | PCWC | |
| 13 | PX RECEIVE | | 58M| 5298M| | 1146K (5)| 02:55:01 | Q1,02 | PCWP | |
| 14 | PX SEND HASH | :TQ10000 | 58M| 5298M| | 1146K (5)| 02:55:01 | | S->P | HASH |
|* 15 | VIEW | | 58M| 5298M| | 1146K (5)| 02:55:01 | | | |
| 16 | WINDOW SORT | | 58M| 5131M| 12G| 1146K (5)| 02:55:01 | | | |
| 17 | VIEW | | 58M| 5131M| | 466K (8)| 01:11:10 | | | |
| 18 | UNION-ALL | | | | | | | | | |
| 19 | MAT_VIEW ACCESS FULL | T_MEM_CHANNEL | 44M| 1585M| | 54938 (11)| 00:08:24 | | | |
|* 20 | HASH JOIN | | 13M| 1202M| 724M| 411K (8)| 01:02:47 | | | |
|* 21 | VIEW | | 13M| 569M| | 176K (9)| 00:26:57 | | | |
| 22 | WINDOW SORT | | 13M| 375M| 1142M| 176K (9)| 00:26:57 | | | |
|* 23 | MAT_VIEW ACCESS FULL| A_MEM_CHANNEL | 13M| 375M| | 115K (10)| 00:17:40 | | | |
| 24 | MAT_VIEW ACCESS FULL | A_MEM_CHANNEL | 81M| 3802M| | 115K (10)| 00:17:38 | | | |
|* 25 | SORT JOIN | | 28M| 385M| 1324M| 12114 (4)| 00:01:51 | Q1,02 | PCWP | |
| 26 | PX RECEIVE | | 28M| 385M| | 6444 (1)| 00:01:00 | Q1,02 | PCWP | |
| 27 | PX SEND HASH | :TQ10001 | 28M| 385M| | 6444 (1)| 00:01:00 | Q1,01 | P->P | HASH |
| 28 | PX BLOCK ITERATOR | | 28M| 385M| | 6444 (1)| 00:01:00 | Q1,01 | PCWC | |
| 29 | TABLE ACCESS FULL | T_HHONORS_MEM | 28M| 385M| | 6444 (1)| 00:01:00 | Q1,01 | PCWP | |
| 30 | SORT AGGREGATE | | 1 | 70 | | | | | | |
|* 31 | VIEW | | 58M| 3904M| | 1119K (5)| 02:50:53 | | | |
| 32 | WINDOW SORT | | 58M| 4963M| 12G| 1119K (5)| 02:50:53 | | | |
| 33 | VIEW | | 58M| 4963M| | 462K (8)| 01:10:40 | | | |
| 34 | UNION-ALL | | | | | | | | | |
| 35 | MAT_VIEW ACCESS FULL | T_MEM_CHANNEL | 44M| 1499M| | 54938 (11)| 00:08:24 | | | |
|* 36 | HASH JOIN | | 13M| 1177M| 724M| 408K (8)| 01:02:17 | | | |
|* 37 | VIEW | | 13M| 569M| | 176K (9)| 00:26:57 | | | |
| 38 | WINDOW SORT | | 13M| 375M| 1142M| 176K (9)| 00:26:57 | | | |
|* 39 | MAT_VIEW ACCESS FULL | A_MEM_CHANNEL | 13M| 375M| | 115K (10)| 00:17:40 | | | |
| 40 | MAT_VIEW ACCESS FULL | A_MEM_CHANNEL | 81M| 3647M| | 115K (10)| 00:17:38 | | | |
------------------------------------------------------------------------------------------------------------------------------------- ----------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("C"."EMAIL_TYPE"(+)="S"."CODE_CHANNEL_TYPE" AND "C"."EMAIL_ADDR"(+)="S"."EMAIL_ADDR" AND
"C"."CUSTOMER_ID"(+)="S"."CUSTOMER_ID")
5 - filter("C"."EMAIL_TYPE"(+) IS NOT NULL)
8 - filter("AUDIT_TIME"= (SELECT MAX("AUDIT_TIME") FROM (SELECT "DGROUP" "DGROUP","ID_MEM_CHANNEL" "ID_MEM_CHANNEL","CODE_CHANNEL"
"CODE_CHANNEL","CODE_CHANNEL_TYPE" "CODE_CHANNEL_TYPE","CHANNEL_STATUS" "CHANNEL_STATUS","IND_PREFER" "IND_PREFER","STATUS"
"STATUS","ID_MEMBER" "ID_MEMBER","BEG_DATE" "BEG_DATE","END_DATE" "END_DATE","CHANNEL_ADDR" "CHANNEL_ADDR","TITLE"
"TITLE","COMPANY_NAME" "COMPANY_NAME","ADDR1" "ADDR1","ADDR2" "ADDR2","ADDR3" "ADDR3","CITY" "CITY","CODE_STATE" "CODE_STATE","ZIP"
"ZIP","CODE_POSTAL" "CODE_POSTAL","CODE_COUNTRY" "CODE_COUNTRY","USERID" "USERID","AUDIT_TIME" "AUDIT_TIME","PURGE_DATE"
"PURGE_DATE",FIRST_VALUE("DGROUP") OVER ( PARTITION BY "A"."ID_MEMBER" ORDER BY INTERNAL_FUNCTION("PURGE_DATE") DESC RANGE BETWEE
N
UNBOUNDED PRECEDING AND CURRENT ROW ) "FIRST" FROM ( (SELECT 1 "DGROUP","ID_MEM_CHANNEL" "ID_MEM_CHANNEL","CODE_CHANNEL"
"CODE_CHANNEL","CODE_CHANNEL_TYPE" "CODE_CHANNEL_TYPE","CHANNEL_STATUS" "CHANNEL_STATUS","IND_PREFER" "IND_PREFER","STATUS"
"STATUS","ID_MEMBER" "ID_MEMBER","BEG_DATE" "BEG_DATE","END_DATE" "END_DATE","CHANNEL_ADDR" "CHANNEL_ADDR","TITLE"
"TITLE","COMPANY_NAME" "COMPANY_NAME","ADDR1" "ADDR1","ADDR2" "ADDR2","ADDR3" "ADDR3","CITY" "CITY","CODE_STATE" "CODE_STATE","ZIP"
"ZIP","CODE_POSTAL" "CODE_POSTAL","CODE_COUNTRY" "CODE_COUNTRY","USERID" "USERID","AUDIT_TIME" "AUDIT_TIME",NULL "PURGE_DATE" FROM
"HH"."T_MEM_CHANNEL" "T_MEM_CHANNEL") UNION ALL (SELECT 2 "DGROUP","A"."ID_MEM_CHANNEL" "ID_MEM_CHANNEL","A"."CODE_CHANNEL"
"CODE_CHANNEL","A"."CODE_CHANNEL_TYPE" "CODE_CHANNEL_TYPE","A"."CHANNEL_STATUS" "CHANNEL_STATUS","A"."IND_PREFER"
"IND_PREFER","A"."STATUS" "STATUS","A"."ID_MEMBER" "ID_MEMBER","A"."BEG_DATE" "BEG_DATE","A"."END_DATE" "END_DATE","A"."CHANNEL_ADD
R"
"CHANNEL_ADDR","A"."TITLE" "TITLE","A"."COMPANY_NAME" "COMPANY_NAME","A"."ADDR1" "ADDR1","A"."ADDR2" "ADDR2","A"."ADDR3"
"ADDR3","A"."CITY" "CITY","A"."CODE_STATE" "CODE_STATE","A"."ZIP" "ZIP","A"."CODE_POSTAL" "CODE_POSTAL","A"."CODE_COUNTRY"
"CODE_COUNTRY","A"."USERID" "USERID","A"."AUDIT_TIME" "AUDIT_TIME","A"."PURGE_DATE" "PURGE_DATE" FROM (SELECT "ID_MEMBER"
"ID_MEMBER","PURGE_DATE" "PURGE_DATE","ID_MEM_CHANNEL" "ID_MEM_CHANNEL",FIRST_VALUE("PURGE_DATE") OVER ( PARTITION BY "ID_MEMBER"
ORDER BY INTERNAL_FUNCTION("PURGE_DATE") DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) "MAX_PURGE_DATE" FROM
"HHD"."A_MEM_CHANNEL" "A" WHERE "USERID"='PURGE') "from$_subquery$_042","HHD"."A_MEM_CHANNEL" "A" WHERE
"A"."ID_MEM_CHANNEL"="ID_MEM_CHANNEL" AND "A"."PURGE_DATE"="PURGE_DATE" AND "A"."ID_MEMBER"="ID_MEMBER" AND
"PURGE_DATE"="MAX_PURGE_DATE")) "A") "from$_subquery$_037" WHERE "CHANNEL_ADDR"=:B1 AND "AUDIT_TIME">=TO_DATE(' 2011-04-23 00:00:00
',
'syyyy-mm-dd hh24:mi:ss') AND "CODE_CHANNEL"='EMAIL' AND "STATUS"='A' AND "CODE_CHANNEL_TYPE"=:B2 AND "DGROUP"="FIRST"))
15 - filter("CHANNEL_ADDR" IS NOT NULL AND "CODE_CHANNEL"='EMAIL' AND "STATUS"='A' AND "AUDIT_TIME">=TO_DATE(' 2011-04-23 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "DGROUP"="FIRST")
20 - access("A"."ID_MEMBER"="ID_MEMBER" AND "A"."PURGE_DATE"="PURGE_DATE" AND "A"."ID_MEM_CHANNEL"="ID_MEM_CHANNEL")
21 - filter("PURGE_DATE"="MAX_PURGE_DATE")
23 - filter("USERID"='PURGE')
25 - access("ID_MEMBER"="CDW"."ID_MEMBER")
filter("ID_MEMBER"="CDW"."ID_MEMBER")
31 - filter("CHANNEL_ADDR"=:B1 AND "AUDIT_TIME">=TO_DATE(' 2011-04-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"CODE_CHANNEL"='EMAIL' AND "STATUS"='A' AND "CODE_CHANNEL_TYPE"=:B2 AND "DGROUP"="FIRST")
36 - access("A"."ID_MEMBER"="ID_MEMBER" AND "A"."PURGE_DATE"="PURGE_DATE" AND "A"."ID_MEM_CHANNEL"="ID_MEM_CHANNEL")
37 - filter("PURGE_DATE"="MAX_PURGE_DATE")
39 - filter("USERID"='PURGE')
89 rows selected.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Tuning Merge Statetment [message #508405 is a reply to message #508400] |
Fri, 20 May 2011 17:31 |
getzeeshan
Messages: 65
Registered: July 2008
|
Member |
|
|
Im shocked myself to see this... I have attached the script I ran for explain plan too.. I have no idea why its running parallely.
Im sorry I do not want to waste your time in any way. If I waste your time I do the same off my time too.
--Zee
|
|
|
|
|
|
|
|
| Re: Tuning Merge Statetment [message #509461 is a reply to message #508666] |
Sat, 28 May 2011 01:19 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
v_mem_channel appears to be a view, and worse - the view seems to contain a UNION ALL and a self-join on tables of 81M rows and 44M rows. But far worse than that: the view contains an analytic query that is causing the WINDOW SORTs in the plan.
Note the FILTER as STEP 8 - it has TWO child steps: the outer query and the sub-query. This means that it executes the sub-query once for EVERY row returned by the outer query. ie. The sub-query is probably executed millions of times.
Because of the UNION ALL and the possibly the analytics in the view, Oracle cannot use an index on the correlating predicate in the sub-query (m.channel_addr = c.channel_addr AND m.code_channel_type = c.code_channel_type). This results in FULL scans on those huge tables every time the sub-query is executed (i.e. Millions of full scans).
The first thing you need to do is to get rid of that subquery. Something like the following using an aggregate function to get the MAX(audit_date) rather than the sub-query should work (warning: untested SQL).
SELECT cdw.customer_id
, c.channel_addr as email_addr
, c.code_channel_type
, MAX(c.ind_prefer) AS ind_prefer
, MAX(c.status) AS status
, MAX(c.audit_time) AS audit_time
, MAX(c.purge_date) AS purge_date
FROM (
SELECT
channel_addr
, MAX(code_channel_type) KEEP (DENSE_RANK LAST ORDER BY audit_time) as code_channel_type
, MAX(ind_prefer) KEEP (DENSE_RANK LAST ORDER BY audit_time) ind_prefer
, MAX(status) KEEP (DENSE_RANK LAST ORDER BY audit_time) as status
, MAX(audit_time) AS audit_time
, MAX(purge_date) KEEP (DENSE_RANK LAST ORDER BY audit_time) AS purge_date
, MAX(id_member) KEEP (DENSE_RANK LAST ORDER BY audit_time) AS id_member
FROM h_cdw.v_mem_channel
where channel_addr is not null
and code_channel = 'EMAIL'
and id_member = cdw.id_member
and status = 'A'
and audit_time >= '23-APR-2011'
) AS c
, t_hhonors_mem cdw
WHERE c.id_member = cdw.id_member
AND m.code_channel_type = c.code_channel_type)
GROUP BY cdw.customer_id
, c.channel_addr
, c.code_channel_type
If you can also work out whether you can remove any of the complexity of the view and access the underlying tables directly, that would probably help.
Ross Leishman
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Jan 10 14:23:22 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
