Home » RDBMS Server » Performance Tuning » Merge statement tuning for 100M records in table
Merge statement tuning for 100M records in table [message #529349] Mon, 31 October 2011 04:42 Go to next message
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);
Re: Merge statement tuning for 100M records in table [message #529353 is a reply to message #529349] Mon, 31 October 2011 05:11 Go to previous messageGo to next message
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.
Re: Merge statement tuning for 100M records in table [message #529361 is a reply to message #529353] Mon, 31 October 2011 05:32 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Merge statement tuning for 100M records in table [message #529363 is a reply to message #529362] Mon, 31 October 2011 06:01 Go to previous messageGo to next message
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;

Re: Merge statement tuning for 100M records in table [message #529369 is a reply to message #529363] Mon, 31 October 2011 06:58 Go to previous messageGo to next message
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.
Re: Merge statement tuning for 100M records in table [message #529370 is a reply to message #529369] Mon, 31 October 2011 07:02 Go to previous messageGo to next message
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.
Re: Merge statement tuning for 100M records in table [message #529374 is a reply to message #529369] Mon, 31 October 2011 07:07 Go to previous messageGo to next message
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?
Re: Merge statement tuning for 100M records in table [message #529377 is a reply to message #529370] Mon, 31 October 2011 07:14 Go to previous messageGo to next message
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')
Re: Merge statement tuning for 100M records in table [message #529379 is a reply to message #529377] Mon, 31 October 2011 07:31 Go to previous messageGo to next message
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.
Re: Merge statement tuning for 100M records in table [message #529385 is a reply to message #529379] Mon, 31 October 2011 08:17 Go to previous messageGo to next message
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;


Re: Merge statement tuning for 100M records in table [message #529395 is a reply to message #529385] Mon, 31 October 2011 10:21 Go to previous messageGo to next message
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.
Re: Merge statement tuning for 100M records in table [message #529396 is a reply to message #529395] Mon, 31 October 2011 10:27 Go to previous messageGo to next message
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')

Re: Merge statement tuning for 100M records in table [message #529397 is a reply to message #529396] Mon, 31 October 2011 10:50 Go to previous messageGo to next message
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.


Re: Merge statement tuning for 100M records in table [message #529398 is a reply to message #529397] Mon, 31 October 2011 10:58 Go to previous messageGo to next message
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
Re: Merge statement tuning for 100M records in table [message #529399 is a reply to message #529398] Mon, 31 October 2011 10:59 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
we don't want index scan!
Re: Merge statement tuning for 100M records in table [message #529403 is a reply to message #529399] Mon, 31 October 2011 11:59 Go to previous messageGo to next message
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")
Re: Merge statement tuning for 100M records in table [message #529404 is a reply to message #529403] Mon, 31 October 2011 12:01 Go to previous messageGo to next message
sachi09
Messages: 22
Registered: October 2011
Location: India
Junior Member
can we avoid full table scan of DWH_BILL_DET by any chance
Re: Merge statement tuning for 100M records in table [message #529405 is a reply to message #529404] Mon, 31 October 2011 12:19 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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')
Re: Merge statement tuning for 100M records in table [message #529408 is a reply to message #529406] Mon, 31 October 2011 13:11 Go to previous messageGo to next message
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.

Re: Merge statement tuning for 100M records in table [message #529410 is a reply to message #529408] Mon, 31 October 2011 13:16 Go to previous messageGo to next message
sachi09
Messages: 22
Registered: October 2011
Location: India
Junior Member
around 370 partitions in DWH_BILL_DET & 64 partitions in PRD_RERATE_CHG_QUE
Re: Merge statement tuning for 100M records in table [message #529411 is a reply to message #529410] Mon, 31 October 2011 13:28 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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?
Re: Merge statement tuning for 100M records in table [message #529417 is a reply to message #529411] Mon, 31 October 2011 14:55 Go to previous messageGo to next message
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".
Re: Merge statement tuning for 100M records in table [message #529418 is a reply to message #529412] Mon, 31 October 2011 15:02 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
You don't need to test this anymore. Partition pruning isn't possible here.
Re: Merge statement tuning for 100M records in table [message #529419 is a reply to message #529417] Mon, 31 October 2011 15:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
   6 - access("RQ".ROWID(+)="RATED_QUE_ROWID")
Re: Merge statement tuning for 100M records in table [message #529430 is a reply to message #529419] Mon, 31 October 2011 17:48 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Today isn't my day 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);
Re: Merge statement tuning for 100M records in table [message #529577 is a reply to message #529430] Wed, 02 November 2011 01:00 Go to previous messageGo to next message
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?
Re: Merge statement tuning for 100M records in table [message #529613 is a reply to message #529577] Wed, 02 November 2011 03:22 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
What sql exactly did you try?
Re: Merge statement tuning for 100M records in table [message #529615 is a reply to message #529613] Wed, 02 November 2011 03:29 Go to previous messageGo to next message
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)
Re: Merge statement tuning for 100M records in table [message #529623 is a reply to message #529615] Wed, 02 November 2011 03:51 Go to previous messageGo to next message
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.
Re: Merge statement tuning for 100M records in table [message #529625 is a reply to message #529623] Wed, 02 November 2011 04:03 Go to previous messageGo to next message
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>
Re: Merge statement tuning for 100M records in table [message #529631 is a reply to message #529625] Wed, 02 November 2011 04:23 Go to previous messageGo to next message
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?

Re: Merge statement tuning for 100M records in table [message #529636 is a reply to message #529631] Wed, 02 November 2011 04:33 Go to previous messageGo to next message
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
Re: Merge statement tuning for 100M records in table [message #529639 is a reply to message #529636] Wed, 02 November 2011 04:41 Go to previous messageGo to next message
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.
Re: Merge statement tuning for 100M records in table [message #529650 is a reply to message #529639] Wed, 02 November 2011 04:57 Go to previous messageGo to next message
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.
Re: Merge statement tuning for 100M records in table [message #529659 is a reply to message #529650] Wed, 02 November 2011 06:29 Go to previous messageGo to next message
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.
Re: Merge statement tuning for 100M records in table [message #529661 is a reply to message #529659] Wed, 02 November 2011 06:48 Go to previous messageGo to previous message
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.
Previous Topic: Need help (2 Merged)
Next Topic: index monitoring
Goto Forum:
  


Current Time: Fri Mar 29 07:59:41 CDT 2024