OraFAQ Forum: Performance Tuning » Merge statement tuning for 100M records in table

Home » RDBMS Server » Performance Tuning » Merge statement tuning for 100M records in table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Merge statement tuning for 100M records in table [message #529349]

Mon, 31 October 2011 04:42

sachi09
Messages: 22
Registered: October 2011
Location: India

Junior Member

Hi,
I have two tables with 113M records in DWH_BILL_DET & 103M in prd_rerate_chg_que and Im running following merge query, which is running for 13 hrs to update records, which is quiet longer time. How to improve performance of this query, any suggesstions

SQL> explain plan for MERGE /*+ parallel (rq, 16) */
INTO DWH_BILL_DET rq
USING (SELECT rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,
rerate_adj_inv_code
FROM prd_rerate_chg_que PARTITION(SYS_P62532)
WHERE rerate_adj_inv_code >= 0
AND rerate_sel_key NOT IN
(SELECT rerate_sel_key
FROM prd_rerate_sel
WHERE status = 'Cancelled'
AND rerate_batch_key = 24)) rr
ON (rq.rowid = rr.rated_que_rowid and trunc(rq.call_start_dttm) between '01-JAN-11' and '31-JAN-11')
WHEN MATCHED THEN
UPDATE
SET rerate_flag_code = rr.detail_rerate_flag_code,
rerate_sel_key = rr.rerate_sel_key,
rerate_adj_inv_code = rr.rerate_adj_inv_code
WHEN NOT MATCHED THEN
INSERT (file_key) VALUES (NULL);

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529353 is a reply to message #529349]

Mon, 31 October 2011 05:11

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Try please the following query:

MERGE /*+ hash(rq) parallel(rq 8) */
INTO DWH_BILL_DET rq
USING (SELECT /*+ use_hash(prd_rerate_chg_que) parallel(prd_rerate_chg_que 8) */ rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,
rerate_adj_inv_code
FROM prd_rerate_chg_que PARTITION(SYS_P62532)
WHERE rerate_adj_inv_code >= 0
AND rerate_sel_key NOT IN
(SELECT /*+ unnest use_hash(prd_rerate_sel) parallel(prd_rerate_sel 8) */ rerate_sel_key
FROM prd_rerate_sel
WHERE status = 'Cancelled'
AND rerate_batch_key = 24)) rr
ON (rq.rowid = rr.rated_que_rowid and trunc(rq.call_start_dttm) between '01-JAN-11' and '31-JAN-11')
WHEN MATCHED THEN
UPDATE
SET rerate_flag_code = rr.detail_rerate_flag_code,
rerate_sel_key = rr.rerate_sel_key,
rerate_adj_inv_code = rr.rerate_adj_inv_code
WHEN NOT MATCHED THEN
INSERT (file_key) VALUES (NULL);

send the FORMATTED explain plan of the above sql.

Then I'll inform you about the further actions.

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529361 is a reply to message #529353]

Mon, 31 October 2011 05:32

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Do the following:

explain plan set statement_id='TTT' for
MERGE /*+ hash(rq) parallel(rq 8) */
INTO DWH_BILL_DET rq
USING (SELECT /*+ use_hash(prd_rerate_chg_que) parallel(prd_rerate_chg_que 8) */ rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,
rerate_adj_inv_code
FROM prd_rerate_chg_que PARTITION(SYS_P62532)
WHERE rerate_adj_inv_code >= 0
AND rerate_sel_key NOT IN
(SELECT /*+ unnest use_hash(prd_rerate_sel) parallel(prd_rerate_sel 8) */ rerate_sel_key
FROM prd_rerate_sel
WHERE status = 'Cancelled'
AND rerate_batch_key = 24)) rr
ON (rq.rowid = rr.rated_que_rowid and trunc(rq.call_start_dttm) between '01-JAN-11' and '31-JAN-11')
WHEN MATCHED THEN
UPDATE
SET rerate_flag_code = rr.detail_rerate_flag_code,
rerate_sel_key = rr.rerate_sel_key,
rerate_adj_inv_code = rr.rerate_adj_inv_code
WHEN NOT MATCHED THEN
INSERT (file_key) VALUES (NULL);

set linesize 1000
set trimspool on

select * from table(sys.dbms_xplan.display('','TTT','ALL'));

rollback;

[Updated on: Mon, 31 October 2011 05:33]

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529362 is a reply to message #529353]

Mon, 31 October 2011 05:41

sachi09
Messages: 22
Registered: October 2011
Location: India

Junior Member

Thanks for quick reply & help, I have attached txt file with details of explain plan before & after as suggested.

Attachment: Merge_tuning.txt
(Size: 9.37KB, Downloaded 1839 times)

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529363 is a reply to message #529362]

Mon, 31 October 2011 06:01

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Your output isn't formatted. Please use "Add code" Button for code.

Do the following please

explain plan set statement_id='TTT' for
MERGE /*+ hash(rq) parallel(rq 8) full (rq) */
INTO DWH_BILL_DET rq
USING (SELECT /*+ use_hash(prd_rerate_chg_que) parallel(prd_rerate_chg_que 8) */ rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,
rerate_adj_inv_code
FROM prd_rerate_chg_que PARTITION(SYS_P62532)
WHERE rerate_adj_inv_code >= 0
AND rerate_sel_key NOT IN
(SELECT /*+ unnest use_hash(prd_rerate_sel) parallel(prd_rerate_sel 8) */ rerate_sel_key
FROM prd_rerate_sel
WHERE status = 'Cancelled'
AND rerate_batch_key = 24)) rr
ON (rq.rowid = rr.rated_que_rowid and trunc(rq.call_start_dttm) between '01-JAN-11' and '31-JAN-11')
WHEN MATCHED THEN
UPDATE
SET rerate_flag_code = rr.detail_rerate_flag_code,
rerate_sel_key = rr.rerate_sel_key,
rerate_adj_inv_code = rr.rerate_adj_inv_code
WHEN NOT MATCHED THEN
INSERT (file_key) VALUES (NULL);

set linesize 1000
set pagesize 1000

set trimspool on

select * from table(sys.dbms_xplan.display('','TTT','ALL'));

rollback;

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529369 is a reply to message #529363]

Mon, 31 October 2011 06:58

sachi09
Messages: 22
Registered: October 2011
Location: India

Junior Member

Im attaching output of latest query & details, Formatted in npp. btw Where I can get this add code button.Im using putty for executing these queries.

Attachment: Merge_tuning1.txt
(Size: 26.28KB, Downloaded 1865 times)

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529370 is a reply to message #529369]

Mon, 31 October 2011 07:02

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

It is a button from the upper menue of orafaq. You should see this menue and the button there as {...} at writing the answer.

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529374 is a reply to message #529369]

Mon, 31 October 2011 07:07

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Sorry, it was my mistake. Do please the following:

explain plan set statement_id='TTT' for
MERGE /*+ use_hash(rq) parallel(rq 8) full (rq) */
INTO DWH_BILL_DET rq
USING (SELECT /*+ use_hash(prd_rerate_chg_que) parallel(prd_rerate_chg_que 8) */ rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,
rerate_adj_inv_code
FROM prd_rerate_chg_que PARTITION(SYS_P62532)
WHERE rerate_adj_inv_code >= 0
AND rerate_sel_key NOT IN
(SELECT /*+ unnest use_hash(prd_rerate_sel) parallel(prd_rerate_sel 8) */ rerate_sel_key
FROM prd_rerate_sel
WHERE status = 'Cancelled'
AND rerate_batch_key = 24)) rr
ON (rq.rowid = rr.rated_que_rowid and trunc(rq.call_start_dttm) between '01-JAN-11' and '31-JAN-11')
WHEN MATCHED THEN
UPDATE
SET rerate_flag_code = rr.detail_rerate_flag_code,
rerate_sel_key = rr.rerate_sel_key,
rerate_adj_inv_code = rr.rerate_adj_inv_code
WHEN NOT MATCHED THEN
INSERT (file_key) VALUES (NULL);

set linesize 1000
set pagesize 1000

set trimspool on

select * from table(sys.dbms_xplan.display('','TTT','ALL'));

rollback;

What is with the optimizer statistics? Are they up-to-date?

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529377 is a reply to message #529370]

Mon, 31 October 2011 07:14

sachi09
Messages: 22
Registered: October 2011
Location: India

Junior Member

Yes stats are updated,

SQL> select * from table(sys.dbms_xplan.display('','TTT','ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2333175250

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT            |                    |     1 |   481 | 19251  (11)| 00:03:52 |       |       |        |      |            |
|   1 |  MERGE                     | DWH_BILL_DET       |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR           |                    |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)     | :TQ10003           |     1 |   487 | 19251  (11)| 00:03:52 |       |       |  Q1,03 | P->S | QC (RAND)  |
|   4 |     VIEW                   |                    |       |       |            |          |       |       |  Q1,03 | PCWP |            |
|*  5 |      HASH JOIN ANTI SNA    |                    |     1 |   487 | 19251  (11)| 00:03:52 |       |       |  Q1,03 | PCWP |            |
|*  6 |       HASH JOIN OUTER      |                    |     1 |   471 | 19245  (11)| 00:03:51 |       |       |  Q1,03 | PCWP |            |
|   7 |        PX RECEIVE          |                    |     1 |    29 |  3526   (1)| 00:00:43 |       |       |  Q1,03 | PCWP |            |
|   8 |         PX SEND HASH       | :TQ10001           |     1 |    29 |  3526   (1)| 00:00:43 |       |       |  Q1,01 | P->P | HASH       |
|   9 |          PX BLOCK ITERATOR |                    |     1 |    29 |  3526   (1)| 00:00:43 |     1 |     1 |  Q1,01 | PCWC |            |
|* 10 |           TABLE ACCESS FULL| PRD_RERATE_CHG_QUE |     1 |    29 |  3526   (1)| 00:00:43 |     1 |     1 |  Q1,01 | PCWP |            |
|  11 |        PX RECEIVE          |                    |    54M|    22G| 15687  (13)| 00:03:09 |       |       |  Q1,03 | PCWP |            |
|  12 |         PX SEND HASH       | :TQ10002           |    54M|    22G| 15687  (13)| 00:03:09 |       |       |  Q1,02 | P->P | HASH       |
|  13 |          PX BLOCK ITERATOR |                    |    54M|    22G| 15687  (13)| 00:03:09 |     1 |     8 |  Q1,02 | PCWC |            |
|* 14 |           TABLE ACCESS FULL| DWH_BILL_DET       |    54M|    22G| 15687  (13)| 00:03:09 |     1 |  2928 |  Q1,02 | PCWP |            |
|  15 |       BUFFER SORT          |                    |       |       |            |          |       |       |  Q1,03 | PCWC |            |
|  16 |        PX RECEIVE          |                    |     1 |    16 |     5   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  17 |         PX SEND BROADCAST  | :TQ10000           |     1 |    16 |     5   (0)| 00:00:01 |       |       |        | S->P | BROADCAST  |
|* 18 |          TABLE ACCESS FULL | PRD_RERATE_SEL     |     1 |    16 |     5   (0)| 00:00:01 |       |       |        |      |            |
----------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - MRG$1
   5 - SEL$658B16C2
  10 - SEL$658B16C2 / PRD_RERATE_CHG_QUE@SEL$3
  14 - SEL$658B16C2 / RQ@SEL$2
  18 - SEL$658B16C2 / PRD_RERATE_SEL@SEL$4

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("RERATE_SEL_KEY"="RERATE_SEL_KEY")
   6 - access("RQ".ROWID(+)="RATED_QUE_ROWID")
  10 - filter("RERATE_ADJ_INV_CODE">=0)
  14 - filter(TRUNC(INTERNAL_FUNCTION("RQ"."CALL_START_DTTM"(+)))<='31-JAN-11' AND
              TRUNC(INTERNAL_FUNCTION("RQ"."CALL_START_DTTM"(+)))>='01-JAN-11')
  18 - filter("RERATE_BATCH_KEY"=24 AND "STATUS"='Cancelled')

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529379 is a reply to message #529377]

Mon, 31 October 2011 07:31

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Very well! If your Oracle version < 11, then you need the parameter setting parallel_execution_message_size=16384. You have to reboot your instance for that. If it is RAC, then you should set this parameter for all the instances.

Additionally you have to execute

alter session enable parallel dml;

After that you can run your sql. Give a feedback please.

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529385 is a reply to message #529379]

Mon, 31 October 2011 08:17

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Could you please send one more explain plan to me:

explain plan set statement_id='TTT' for
MERGE /*+ use_hash(rq) parallel(rq 8) full (rq) */
INTO DWH_BILL_DET rq
USING (SELECT /*+ use_hash(prd_rerate_chg_que) parallel(prd_rerate_chg_que 8) */ rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,
rerate_adj_inv_code
FROM prd_rerate_chg_que PARTITION(SYS_P62532)
WHERE rerate_adj_inv_code >= 0
AND rerate_sel_key NOT IN
(SELECT /*+ unnest use_hash(prd_rerate_sel) parallel(prd_rerate_sel 8) pq_distribute(prd_rerate_sel hash, hash) */ rerate_sel_key
FROM prd_rerate_sel
WHERE status = 'Cancelled'
AND rerate_batch_key = 24)) rr
ON (rq.rowid = rr.rated_que_rowid and trunc(rq.call_start_dttm) between '01-JAN-11' and '31-JAN-11')
WHEN MATCHED THEN
UPDATE
SET rerate_flag_code = rr.detail_rerate_flag_code,
rerate_sel_key = rr.rerate_sel_key,
rerate_adj_inv_code = rr.rerate_adj_inv_code
WHEN NOT MATCHED THEN
INSERT (file_key) VALUES (NULL);

set linesize 1000
set pagesize 1000

set trimspool on

select * from table(sys.dbms_xplan.display('','TTT','ALL'));

rollback;

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529395 is a reply to message #529385]

Mon, 31 October 2011 10:21

sachi09
Messages: 22
Registered: October 2011
Location: India

Junior Member

Thanks for help, Actually I started running that sql, will update on completion.

Okay will run explain plan for this.

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529396 is a reply to message #529395]

Mon, 31 October 2011 10:27

sachi09
Messages: 22
Registered: October 2011
Location: India

Junior Member

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2333175250

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT            |                    |     1 |   481 | 19251  (11)| 00:03:52 |       |       |        |      |            |
|   1 |  MERGE                     | DWH_BILL_DET       |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR           |                    |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)     | :TQ10003           |     1 |   487 | 19251  (11)| 00:03:52 |       |       |  Q1,03 | P->S | QC (RAND)  |
|   4 |     VIEW                   |                    |       |       |            |          |       |       |  Q1,03 | PCWP |            |
|*  5 |      HASH JOIN ANTI SNA    |                    |     1 |   487 | 19251  (11)| 00:03:52 |       |       |  Q1,03 | PCWP |            |
|*  6 |       HASH JOIN OUTER      |                    |     1 |   471 | 19245  (11)| 00:03:51 |       |       |  Q1,03 | PCWP |            |
|   7 |        PX RECEIVE          |                    |     1 |    29 |  3526   (1)| 00:00:43 |       |       |  Q1,03 | PCWP |            |
|   8 |         PX SEND HASH       | :TQ10001           |     1 |    29 |  3526   (1)| 00:00:43 |       |       |  Q1,01 | P->P | HASH       |
|   9 |          PX BLOCK ITERATOR |                    |     1 |    29 |  3526   (1)| 00:00:43 |     1 |     1 |  Q1,01 | PCWC |            |
|* 10 |           TABLE ACCESS FULL| PRD_RERATE_CHG_QUE |     1 |    29 |  3526   (1)| 00:00:43 |     1 |     1 |  Q1,01 | PCWP |            |
|  11 |        PX RECEIVE          |                    |    54M|    22G| 15687  (13)| 00:03:09 |       |       |  Q1,03 | PCWP |            |
|  12 |         PX SEND HASH       | :TQ10002           |    54M|    22G| 15687  (13)| 00:03:09 |       |       |  Q1,02 | P->P | HASH       |
|  13 |          PX BLOCK ITERATOR |                    |    54M|    22G| 15687  (13)| 00:03:09 |     1 |     8 |  Q1,02 | PCWC |            |
|* 14 |           TABLE ACCESS FULL| DWH_BILL_DET       |    54M|    22G| 15687  (13)| 00:03:09 |     1 |  2928 |  Q1,02 | PCWP |            |
|  15 |       BUFFER SORT          |                    |       |       |            |          |       |       |  Q1,03 | PCWC |            |
|  16 |        PX RECEIVE          |                    |     1 |    16 |     5   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  17 |         PX SEND BROADCAST  | :TQ10000           |     1 |    16 |     5   (0)| 00:00:01 |       |       |        | S->P | BROADCAST  |
|* 18 |          TABLE ACCESS FULL | PRD_RERATE_SEL     |     1 |    16 |     5   (0)| 00:00:01 |       |       |        |      |            |
----------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - MRG$1
   5 - SEL$658B16C2
  10 - SEL$658B16C2 / PRD_RERATE_CHG_QUE@SEL$3
  14 - SEL$658B16C2 / RQ@SEL$2
  18 - SEL$658B16C2 / PRD_RERATE_SEL@SEL$4

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("RERATE_SEL_KEY"="RERATE_SEL_KEY")
   6 - access("RQ".ROWID(+)="RATED_QUE_ROWID")
  10 - filter("RERATE_ADJ_INV_CODE">=0)
  14 - filter(TRUNC(INTERNAL_FUNCTION("RQ"."CALL_START_DTTM"(+)))<='31-JAN-11' AND
              TRUNC(INTERNAL_FUNCTION("RQ"."CALL_START_DTTM"(+)))>='01-JAN-11')
  18 - filter("RERATE_BATCH_KEY"=24 AND "STATUS"='Cancelled')

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529397 is a reply to message #529396]

Mon, 31 October 2011 10:50

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Is your "not in" subquery selective? If yes, we should try to change the order in the join.

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529398 is a reply to message #529397]

Mon, 31 October 2011 10:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>trunc(rq.call_start_dttm) between '01-JAN-11' and '31-JAN-11')
prevents use of INDEX on CALL_START_DTTM

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529399 is a reply to message #529398]

Mon, 31 October 2011 10:59

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

we don't want index scan!

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529403 is a reply to message #529399]

Mon, 31 October 2011 11:59

sachi09
Messages: 22
Registered: October 2011
Location: India

Junior Member

Actually table DWH_BILL_DET is partitioned daily, so date range is useful
PARTITION BY RANGE ("CALL_START_DTTM")
SUBPARTITION BY HASH ("REC_KEY")

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529404 is a reply to message #529403]

Mon, 31 October 2011 12:01

sachi09
Messages: 22
Registered: October 2011
Location: India

Junior Member

can we avoid full table scan of DWH_BILL_DET by any chance

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529405 is a reply to message #529404]

Mon, 31 October 2011 12:19

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

We can try with partition pruning, if you rewrite your where clause:

explain plan set statement_id='TTT' for
MERGE /*+ use_hash(rq) parallel(rq 8) full (rq) */
INTO DWH_BILL_DET rq
USING (SELECT /*+ use_hash(prd_rerate_chg_que) parallel(prd_rerate_chg_que 8) */ rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,
rerate_adj_inv_code
FROM prd_rerate_chg_que PARTITION(SYS_P62532)
WHERE rerate_adj_inv_code >= 0
AND rerate_sel_key NOT IN
(SELECT /*+ unnest use_hash(prd_rerate_sel) parallel(prd_rerate_sel 8) */ rerate_sel_key
FROM prd_rerate_sel
WHERE status = 'Cancelled'
AND rerate_batch_key = 24)) rr
ON (rq.rowid = rr.rated_que_rowid and rq.call_start_dttm between to_date('01-JAN-11','dd-mm-yy') and to_date('31-JAN-11 23:59:59','dd-mm-yy hh24:mi:ss'))
WHEN MATCHED THEN
UPDATE
SET rerate_flag_code = rr.detail_rerate_flag_code,
rerate_sel_key = rr.rerate_sel_key,
rerate_adj_inv_code = rr.rerate_adj_inv_code
WHEN NOT MATCHED THEN
INSERT (file_key) VALUES (NULL);

set linesize 1000
set pagesize 1000

set trimspool on

select * from table(sys.dbms_xplan.display('','TTT','ALL'));

rollback;

I'm not sure, that an index scan is better. How many rows do you expect to be inserted and how many to be updated? What is about the selectivity of the subquery?

[Updated on: Mon, 31 October 2011 12:21]

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529406 is a reply to message #529405]

Mon, 31 October 2011 12:55

sachi09
Messages: 22
Registered: October 2011
Location: India

Junior Member

Most of the time this query updates records, i.e. less no of inserts, and update all rows from prd_rerate_chg_que - 103M

. regarding subquery selectivity, distinct statuses 2/50.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2333175250

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT            |                    |     1 |   481 | 43618  (10)| 00:08:44 |       |       |        |      |            |
|   1 |  MERGE                     | DWH_BILL_DET       |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR           |                    |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)     | :TQ10003           |     1 |   487 | 43618  (10)| 00:08:44 |       |       |  Q1,03 | P->S | QC (RAND)  |
|   4 |     VIEW                   |                    |       |       |            |          |       |       |  Q1,03 | PCWP |            |
|*  5 |      HASH JOIN ANTI SNA    |                    |     1 |   487 | 43618  (10)| 00:08:44 |       |       |  Q1,03 | PCWP |            |
|*  6 |       HASH JOIN OUTER      |                    |     1 |   471 | 43612  (10)| 00:08:44 |       |       |  Q1,03 | PCWP |            |
|   7 |        PX RECEIVE          |                    |     1 |    29 |  3526   (1)| 00:00:43 |       |       |  Q1,03 | PCWP |            |
|   8 |         PX SEND HASH       | :TQ10001           |     1 |    29 |  3526   (1)| 00:00:43 |       |       |  Q1,01 | P->P | HASH       |
|   9 |          PX BLOCK ITERATOR |                    |     1 |    29 |  3526   (1)| 00:00:43 |     1 |     1 |  Q1,01 | PCWC |            |
|* 10 |           TABLE ACCESS FULL| PRD_RERATE_CHG_QUE |     1 |    29 |  3526   (1)| 00:00:43 |     1 |     1 |  Q1,01 | PCWP |            |
|  11 |        PX RECEIVE          |                    |   102M|    42G| 40027  (11)| 00:08:01 |       |       |  Q1,03 | PCWP |            |
|  12 |         PX SEND HASH       | :TQ10002           |   102M|    42G| 40027  (11)| 00:08:01 |       |       |  Q1,02 | P->P | HASH       |
|  13 |          PX BLOCK ITERATOR |                    |   102M|    42G| 40027  (11)| 00:08:01 |     1 |     8 |  Q1,02 | PCWC |            |
|* 14 |           TABLE ACCESS FULL| DWH_BILL_DET       |   102M|    42G| 40027  (11)| 00:08:01 |   KEY |   KEY |  Q1,02 | PCWP |            |
|  15 |       BUFFER SORT          |                    |       |       |            |          |       |       |  Q1,03 | PCWC |            |
|  16 |        PX RECEIVE          |                    |     1 |    16 |     5   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  17 |         PX SEND BROADCAST  | :TQ10000           |     1 |    16 |     5   (0)| 00:00:01 |       |       |        | S->P | BROADCAST  |
|* 18 |          TABLE ACCESS FULL | PRD_RERATE_SEL     |     1 |    16 |     5   (0)| 00:00:01 |       |       |        |      |            |
----------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - MRG$1
   5 - SEL$658B16C2
  10 - SEL$658B16C2 / PRD_RERATE_CHG_QUE@SEL$3
  14 - SEL$658B16C2 / RQ@SEL$2
  18 - SEL$658B16C2 / PRD_RERATE_SEL@SEL$4

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("RERATE_SEL_KEY"="RERATE_SEL_KEY")
   6 - access("RQ".ROWID(+)="RATED_QUE_ROWID")
  10 - filter("RERATE_ADJ_INV_CODE">=0)
  14 - filter("RQ"."CALL_START_DTTM"(+)<=TO_DATE('31-JAN-11 23:59:59','dd-mm-yy hh24:mi:ss') AND
              "RQ"."CALL_START_DTTM"(+)>=TO_DATE('01-JAN-11','dd-mm-yy'))
  18 - filter("RERATE_BATCH_KEY"=24 AND "STATUS"='Cancelled')

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529408 is a reply to message #529406]

Mon, 31 October 2011 13:11

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Normally it is vice versa. How many partitions has DWH_BILL_DET? I think, we need to change the order in the join, to get a partition pruning. So I want to know, how much we can win by partition pruning.

Regarding selectivity of subquery. I wanted to know, how many rows from PRD_RERATE_CHG_QUE are "not in (subquery)"? But if you write, "update all rows from prd_rerate_chg_que - 103M", it should be not selective.

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529410 is a reply to message #529408]

Mon, 31 October 2011 13:16

sachi09
Messages: 22
Registered: October 2011
Location: India

Junior Member

around 370 partitions in DWH_BILL_DET & 64 partitions in PRD_RERATE_CHG_QUE

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529411 is a reply to message #529410]

Mon, 31 October 2011 13:28

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

370 partitions for approximately one year? So we cannot win a lot by partition pruning.

I suggest, we let the sql run further. Could you send the execution plan of it:


select sql_id, child_number, sql_text from v$sql where force_matching_signature=8256480873996085452 and upper(sql_text) not like 'EXPLAIN%';

Then run the following sql with the found sql_id and child_number:  

set pagesize 1000
set linesize 1000

select plan_table_output from table ( sys.dbms_xplan.display_cursor( '<sql_id>', <child_number>, 'ADVANCED' ));

[Updated on: Mon, 31 October 2011 13:30]

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529412 is a reply to message #529411]

Mon, 31 October 2011 13:46

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Sorry, I thought, we select one year. So we can win by partition pruning.

Try, please:

explain plan set statement_id='TTT' for
MERGE /*+ use_hash(rq) parallel(rq 8) full (rq) */
INTO DWH_BILL_DET rq
USING (SELECT /*+ use_hash(prd_rerate_chg_que) parallel(prd_rerate_chg_que 8) push_subq */ rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,
rerate_adj_inv_code
FROM prd_rerate_chg_que PARTITION(SYS_P62532)
WHERE rerate_adj_inv_code >= 0
AND rerate_sel_key NOT IN
(SELECT  rerate_sel_key
FROM prd_rerate_sel
WHERE status = 'Cancelled'
AND rerate_batch_key = 24)) rr
ON (rq.rowid = rr.rated_que_rowid and rq.call_start_dttm between to_date('01-JAN-11','dd-mm-yy') and to_date('31-JAN-11 23:59:59','dd-mm-yy hh24:mi:ss'))
WHEN MATCHED THEN
UPDATE
SET rerate_flag_code = rr.detail_rerate_flag_code,
rerate_sel_key = rr.rerate_sel_key,
rerate_adj_inv_code = rr.rerate_adj_inv_code
WHEN NOT MATCHED THEN
INSERT (file_key) VALUES (NULL);

set linesize 1000
set pagesize 1000

set trimspool on

select * from table(sys.dbms_xplan.display('','TTT','ALL'));

rollback;

[Updated on: Mon, 31 October 2011 13:52]

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529416 is a reply to message #529412]

Mon, 31 October 2011 14:35

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

I have one more question. Why do you use PARTITION(SYS_P62532)? Are you going to merge partition for partition? Why not immediately the complete table?

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529417 is a reply to message #529411]

Mon, 31 October 2011 14:55

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

I think, the reason of no partition pruning is the predicate "rq.rowid = rr.rated_que_rowid".

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529418 is a reply to message #529412]

Mon, 31 October 2011 15:02

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

You don't need to test this anymore. Partition pruning isn't possible here.

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529419 is a reply to message #529417]

Mon, 31 October 2011 15:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

   6 - access("RQ".ROWID(+)="RATED_QUE_ROWID")

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529430 is a reply to message #529419]

Mon, 31 October 2011 17:48

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Today isn't my day Sad

Sad

. I overlooked partition pruning, which was already used in the explain plan:

|* 14 | TABLE ACCESS FULL| DWH_BILL_DET | 102M| 42G| 40027 (11)| 00:08:01 | KEY | KEY | Q1,02 | PCWP | |

So the following sql should be ok:

MERGE /*+ use_hash(rq) parallel(rq 8) full (rq) */
INTO DWH_BILL_DET rq
USING (SELECT /*+ use_hash(prd_rerate_chg_que) parallel(prd_rerate_chg_que 8) */ rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,
rerate_adj_inv_code
FROM prd_rerate_chg_que PARTITION(SYS_P62532)
WHERE rerate_adj_inv_code >= 0
AND rerate_sel_key NOT IN
(SELECT /*+ unnest use_hash(prd_rerate_sel) */ rerate_sel_key
FROM prd_rerate_sel
WHERE status = 'Cancelled'
AND rerate_batch_key = 24)) rr
ON (rq.rowid = rr.rated_que_rowid and rq.call_start_dttm between to_date('01-JAN-11','dd-mm-yy') and to_date('31-JAN-11 23:59:59','dd-mm-yy hh24:mi:ss'))
WHEN MATCHED THEN
UPDATE
SET rerate_flag_code = rr.detail_rerate_flag_code,
rerate_sel_key = rr.rerate_sel_key,
rerate_adj_inv_code = rr.rerate_adj_inv_code
WHEN NOT MATCHED THEN
INSERT (file_key) VALUES (NULL);

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529577 is a reply to message #529430]

Wed, 02 November 2011 01:00

sachi09
Messages: 22
Registered: October 2011
Location: India

Junior Member

Thanks, I really got good results using below mentioned query. Time taken has reduced from 11Hr 38Min to 8Hr 37Min almost 25% reduction.
but observed one drawback also, when I run same query for less no records/rows as well as no rows in prd_rerate_chg_que in table. this query has taken more time when compared to last one.

Can you please explain, what might be the reason?

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529613 is a reply to message #529577]

Wed, 02 November 2011 03:22

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

What sql exactly did you try?

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529615 is a reply to message #529613]

Wed, 02 November 2011 03:29

sachi09
Messages: 22
Registered: October 2011
Location: India

Junior Member

MERGE /*+ use_hash(rq) parallel (rq, 16) full (rq) */
INTO DWH_BILL_DET rqUSING (SELECT /*+ use_hash(prd_rerate_chg_que) parallel(prd_rerate_chg_que, 16) */
                            rated_que_rowid,
                            detail_rerate_flag_code,
                            rerate_sel_key,
                            rerate_adj_inv_code
                             FROM prd_rerate_chg_que PARTITION(SYS_P62672)
                            WHERE rerate_adj_inv_code >= 0
                              AND rerate_sel_key NOT IN
                                  (SELECT /*+ unnest use_hash(prd_rerate_sel) parallel(prd_rerate_sel, 16) */
                                    rerate_sel_key
                                     FROM prd_rerate_sel
                                    WHERE status = 'Cancelled'
                                      AND rerate_batch_key = 49)) rr
ON (rq.rowid = rr.rated_que_rowid)
WHEN MATCHED THEN
  UPDATE
     SET rerate_flag_code    = rr.detail_rerate_flag_code,
         rerate_sel_key      = rr.rerate_sel_key,
         rerate_adj_inv_code = rr.rerate_adj_inv_code
WHEN NOT MATCHED THEN
  INSERT (file_key) VALUES (NULL)

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529623 is a reply to message #529615]

Wed, 02 November 2011 03:51

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Ok, you processed the whole table DWH_BILL_DET not only data for Januar. Did you set parallel_execution_message_size=16384? Did you enable parallel DML's (alter session enable parallel dml)? How many cpu's has your machine? Were you alone on the machine as you tested?

Send please the explain plan of this sql to me.

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529625 is a reply to message #529623]

Wed, 02 November 2011 04:03

sachi09
Messages: 22
Registered: October 2011
Location: India

Junior Member

Yes, session was enabled parallel DML, Actually im on 11g, do I really need to set it? I have 64 CPU m/c, No other load was running on system.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2333175250

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT            |                    |     1 |   481 |  8019  (15)| 00:01:37 |       |       |        |      |            |
|   1 |  MERGE                     | DWH_BILL_DET       |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR           |                    |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)     | :TQ10003           |     1 |   509 |  8019  (15)| 00:01:37 |       |       |  Q1,03 | P->S | QC (RAND)  |
|   4 |     VIEW                   |                    |       |       |            |          |       |       |  Q1,03 | PCWP |            |
|*  5 |      HASH JOIN ANTI SNA    |                    |     1 |   509 |  8019  (15)| 00:01:37 |       |       |  Q1,03 | PCWP |            |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  6 |       HASH JOIN OUTER      |                    |     1 |   493 |  8013  (15)| 00:01:37 |       |       |  Q1,03 | PCWP |            |
|   7 |        PX RECEIVE          |                    |     1 |    51 |     2   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|   8 |         PX SEND HASH       | :TQ10001           |     1 |    51 |     2   (0)| 00:00:01 |       |       |  Q1,01 | P->P | HASH       |
|   9 |          PX BLOCK ITERATOR |                    |     1 |    51 |     2   (0)| 00:00:01 |     1 |     1 |  Q1,01 | PCWC |            |
|* 10 |           TABLE ACCESS FULL| PRD_RERATE_CHG_QUE |     1 |    51 |     2   (0)| 00:00:01 |     1 |     1 |  Q1,01 | PCWP |            |
|  11 |        PX RECEIVE          |                    |   113M|    46G|  7978  (15)| 00:01:36 |       |       |  Q1,03 | PCWP |            |
|  12 |         PX SEND HASH       | :TQ10002           |   113M|    46G|  7978  (15)| 00:01:36 |       |       |  Q1,02 | P->P | HASH       |
|  13 |          PX BLOCK ITERATOR |                    |   113M|    46G|  7978  (15)| 00:01:36 |     1 |     8 |  Q1,02 | PCWC |            |
|  14 |           TABLE ACCESS FULL| DWH_BILL_DET       |   113M|    46G|  7978  (15)| 00:01:36 |     1 |  2928 |  Q1,02 | PCWP |            |
|  15 |       BUFFER SORT          |                    |       |       |            |          |       |       |  Q1,03 | PCWC |            |
|  16 |        PX RECEIVE          |                    |     1 |    16 |     5   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  17 |         PX SEND BROADCAST  | :TQ10000           |     1 |    16 |     5   (0)| 00:00:01 |       |       |        | S->P | BROADCAST  |
|* 18 |          TABLE ACCESS FULL | PRD_RERATE_SEL     |     1 |    16 |     5   (0)| 00:00:01 |       |       |        |      |            |
----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("RERATE_SEL_KEY"="RERATE_SEL_KEY")
   6 - access("RQ".ROWID(+)="RATED_QUE_ROWID")
  10 - filter("RERATE_ADJ_INV_CODE">=0)
  18 - filter("RERATE_BATCH_KEY"=49 AND "STATUS"='Cancelled')

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=5)

37 rows selected.

SQL>

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529631 is a reply to message #529625]

Wed, 02 November 2011 04:23

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

16384 is default in 11g. So you don't need to set it.

Just for my understanding.

- the partition SYS_P62672 of prd_rerate_chg_que has 1609859 rows, so your merge updated 1609859 in DWH_BILL_DET. You wrote about 100M rows. Are you going to repeate your merge for all the partitions of prd_rerate_chg_que?

- was the condition "rq.call_start_dttm between to_date('01-JAN-11','dd-mm-yy') and to_date('31-JAN-11 23:59:59','dd-mm-yy hh24:mi:ss')" only for test purposes? Why did you remove it?

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529636 is a reply to message #529631]

Wed, 02 November 2011 04:33

sachi09
Messages: 22
Registered: October 2011
Location: India

Junior Member

yes, Im going to repeat for all partions of prd_rerate_chg_que and regarding the condition, Im checking how it was missed in code. Need to change code for this.

Im planning change code & do one more test. Will update result of the same.

Thanks once again. Smile

Smile

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529639 is a reply to message #529636]

Wed, 02 November 2011 04:41

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

If you use this condition, you reduce the runtime appr. to 1 hour or even less. You don't need

- the hint parallel(prd_rerate_sel, 16),
- parallel degree of 16 for only one partition of prd_rerate_chg_que, because it is relative small.

What is about update of 100M rows at once? You need a lot of undo space for this, but it should be faster.

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529650 is a reply to message #529639]

Wed, 02 November 2011 04:57

sachi09
Messages: 22
Registered: October 2011
Location: India

Junior Member

Sorry Im not clear on above comment,
What i understans is there is no help form using parallel hint on prd_rerate_sel_key & prd_rerate_chg que, right?

Also Im not sure if I should go for update 100M records at once, as I have 120GB undo_ts and table size is around 46GB for 100M records, Please suggest.

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529659 is a reply to message #529650]

Wed, 02 November 2011 06:29

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

You don't need parallelism for prd_rerate_sel_key. You can decrease degree for ONE partition of prd_rerate_chg_que.

You have the following alternative

- either to include the condition like "rq.call_start_dttm between to_date('01-JAN-11','dd-mm-yy') and to_date('31-JAN-11 23:59:59','dd-mm-yy hh24:mi:ss')" into your code and to execute merge 64 times for each partition of prd_rerate_chg_que

or

- to update 100M rows at once. In this case you need the degree of 16 for the whole table prd_rerate_chg_que. I suppose, 120GB undo should be enough for this update, because you update only 3 columns of DWH_BILL_DET.

For the first case you need 40-60 hours totally (with the included condition!). The second one should be faster.

Report message to a moderator

Re: Merge statement tuning for 100M records in table [message #529661 is a reply to message #529659]

Wed, 02 November 2011 06:48

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Could you please send an awr report for the last runtime (8 hours) of your merge.

Report message to a moderator

Pages (2): [1 2 › »]

Previous Topic:	Need help (2 Merged)
Next Topic:	index monitoring

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 13:52:26 CST 2025