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 Go to next message
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 #507998 is a reply to message #507997] Wed, 18 May 2011 16:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Tuning Merge Statetment [message #508001 is a reply to message #507998] Wed, 18 May 2011 17:14 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
I tried attaching the Explain plan and Stats which I had taken from OEM in a Document , since I could not get the execution plan of this statement. How do I upload it?
Re: Tuning Merge Statetment [message #508002 is a reply to message #508001] Wed, 18 May 2011 17:16 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
in the Query above
V_mem_channel -> is a view on T_MEM_CHANNEL & A_MEM_CHANNEL and it has indexes on foling
Audit_time
id_member columns

Re: Tuning Merge Statetment [message #508003 is a reply to message #508002] Wed, 18 May 2011 18:23 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
I have sent two parts of file one with Explain plan and one with stats.

Re: Tuning Merge Statetment [message #508004 is a reply to message #508003] Wed, 18 May 2011 19:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Tuning Merge Statetment [message #508130 is a reply to message #508004] Thu, 19 May 2011 09:28 Go to previous messageGo to next message
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 #508156 is a reply to message #508130] Thu, 19 May 2011 11:54 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
What errors?
And you haven't uploaded any files.
Re: Tuning Merge Statetment [message #508171 is a reply to message #508156] Thu, 19 May 2011 13:07 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
File 1 attached again
Re: Tuning Merge Statetment [message #508172 is a reply to message #508171] Thu, 19 May 2011 13:08 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
File 2 attached
Re: Tuning Merge Statetment [message #508173 is a reply to message #508172] Thu, 19 May 2011 13:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
11. Plain text file attachments are acceptable, while Excel, *.pdf, *jpg, or similar files are not.
Re: Tuning Merge Statetment [message #508175 is a reply to message #508172] Thu, 19 May 2011 13:12 Go to previous messageGo to next message
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 #508190 is a reply to message #508175] Thu, 19 May 2011 15:50 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
did the attachments help?
Re: Tuning Merge Statetment [message #508198 is a reply to message #508190] Thu, 19 May 2011 17:43 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you've getting an error like that then you're doing the explain plan as the wrong user. Do it as the user the merge is being run as.
Re: Tuning Merge Statetment [message #508359 is a reply to message #508198] Fri, 20 May 2011 10:36 Go to previous messageGo to next message
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 #508360 is a reply to message #508359] Fri, 20 May 2011 10:39 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
I have attached the plan as text file.
Re: Tuning Merge Statetment [message #508393 is a reply to message #508360] Fri, 20 May 2011 16:46 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
any updates on this?
Re: Tuning Merge Statetment [message #508394 is a reply to message #508393] Fri, 20 May 2011 16:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
How does performance change after removing PARALLEL hint?
post EXPLAIN PLAN without PARALLEL hint
Re: Tuning Merge Statetment [message #508395 is a reply to message #508393] Fri, 20 May 2011 16:52 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
it does exactly the same explain plan and same stats for with or without HINT.
Re: Tuning Merge Statetment [message #508396 is a reply to message #508395] Fri, 20 May 2011 16:55 Go to previous messageGo to next message
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 #508397 is a reply to message #508395] Fri, 20 May 2011 16:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I want to see PLAN without any "PX" (parallel) activity.
Re: Tuning Merge Statetment [message #508398 is a reply to message #508396] Fri, 20 May 2011 17:00 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
I have attached a new explain plan
Re: Tuning Merge Statetment [message #508399 is a reply to message #508398] Fri, 20 May 2011 17:01 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
this plan attached is is without the PX activity
Re: Tuning Merge Statetment [message #508400 is a reply to message #508399] Fri, 20 May 2011 17:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>this plan attached is is without the PX activity
REALLY? I disagree.
I'll forgive you for wasting my time, if you forgive me for not responding any more.

|   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     
Re: Tuning Merge Statetment [message #508405 is a reply to message #508400] Fri, 20 May 2011 17:31 Go to previous messageGo to next message
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 #508407 is a reply to message #508405] Fri, 20 May 2011 17:46 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
have a great weekend thx! for helping.
Re: Tuning Merge Statetment [message #508666 is a reply to message #508407] Mon, 23 May 2011 14:47 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
Any updates? or let me know how i can get the explain plan removing PX activity.
Re: Tuning Merge Statetment [message #509461 is a reply to message #508666] Sat, 28 May 2011 01:19 Go to previous messageGo to next message
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
Re: Tuning Merge Statetment [message #510146 is a reply to message #509461] Thu, 02 June 2011 11:05 Go to previous message
getzeeshan
Messages: 65
Registered: July 2008
Member
Makes sense I have informed the Developer about the suggestion they are working on it.

Thx! for the reply and detailed explanation.

--Zee
Previous Topic: Advice on Index Creation
Next Topic: Solution Required for Sql Performance issue in oracle due to Null value check(2 Merged)
Goto Forum:
  


Current Time: Fri Mar 29 02:02:10 CDT 2024