| Tuning Insert [message #503091] |
Tue, 12 April 2011 22:39  |
 |
sunny17
Messages: 2
Registered: April 2011
Location: Bangalore
|
Junior Member |
|
|
Hi All,
My first posting in this forum.
We have insert in the procudure which took 150 mins to execute it has insert which is taking around 140+ mins.
Below is the code
PROCEDURE refresh
IS
l_errm VARCHAR(2000);
--The data is being inserted into a synonym (REINS)
--that points to the underlying table
INSERT /*+ APPEND */ INTO REINS
( rcc,
da_ceded_code
)
SELECT DISTINCT nvl(rtrim(pre_explosion_rcc), nvl(rtrim(rcc),' ')) rcc,
trim(da_ceded_code) da_ceded_code
FROM cl_trans_facts
WHERE acc_period > 197000
AND trim(da_ceded_code) between 1 and 8
UNION
SELECT DISTINCT nvl(rtrim(pre_explosion_rcc), nvl(rtrim(rcc),' ')) rcc,
trim(da_ceded_code) da_ceded_code
FROM prem_trans_facts
WHERE acc_period > 197000
AND trim(da_ceded_code) between 1 and 8
UNION
SELECT DISTINCT nvl(rtrim(pre_explosion_rcc), nvl(rtrim(rcc),' ')) rcc,
trim(da_ceded_code) da_ceded_code
FROM earn_prem_facts
WHERE acc_period > 197000
AND trim(da_ceded_code) between 1 and 8;
COMMIT;
INSERT INTO status
VALUES ( SYSDATE, 'Success insert' );
COMMIT;
EXCEPTION
WHEN OTHERS
THEN l_errm := SUBSTR( SQLERRM, 1, 2000 );
INSERT INTO ard_run_status
VALUES ( SYSDATE, l_errm );
COMMIT;
END refresh;
The tables (cl_trans_facts, prem_trans_facts, earn_prem_facts) are huge tables are around 750 gb and they belong to different schema. They are partitioned on acc_period and have partitioned index on all columns of those tables.
Please find the explain plan for the select statment.
explain plan for
INSERT /*+ APPEND */INTO reinsurance_bitmasks_load
(rcc, direct_assumed_ceded_code)
SELECT DISTINCT NVL (RTRIM (pre_explosion_rcc),
NVL (RTRIM (rcc), ' ')) rcc,
TRIM (direct_assumed_ceded_code) direct_assumed_ceded_code
FROM claim_transaction_facts
WHERE accounting_period > 197000
AND TRIM (direct_assumed_ceded_code) BETWEEN 1 AND 8
UNION
SELECT DISTINCT NVL (RTRIM (pre_explosion_rcc),
NVL (RTRIM (rcc), ' ')) rcc,
TRIM (direct_assumed_ceded_code) direct_assumed_ceded_code
FROM premium_transaction_facts
WHERE accounting_period > 197000
AND TRIM (direct_assumed_ceded_code) BETWEEN 1 AND 8
UNION
SELECT DISTINCT NVL (RTRIM (pre_explosion_rcc),
NVL (RTRIM (rcc), ' ')) rcc,
TRIM (direct_assumed_ceded_code) direct_assumed_ceded_code
FROM earned_premium_facts
WHERE accounting_period > 197000
AND TRIM (direct_assumed_ceded_code) BETWEEN 1 AND 8
PLAN_TABLE_OUTPUT
Plan hash value: 1434373387
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 5832K| 77M| 9811K (92)| 21:44:08 | | | | | |
| 1 | LOAD AS SELECT | | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 5832K| 77M| 9811K (92)| 21:44:08 | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT UNIQUE | | 5832K| 77M| 9811K (92)| 21:44:08 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | | | | | | | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | | | | | | | Q1,00 | P->P | HASH |
| 7 | UNION-ALL | | | | | | | | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 735K| 9M| 869K (2)| 01:55:33 | 1 | 385 | Q1,00 | PCWC | |
|* 9 | TABLE ACCESS FULL| CLAIM_TRANSACTION_FACTS | 735K| 9M| 869K (2)| 01:55:33 | 1 | 385 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 1493K| 19M| 2688K (1)| 05:57:24 | 1 | 385 | Q1,00 | PCWC | |
|* 11 | TABLE ACCESS FULL| PREMIUM_TRANSACTION_FACTS | 1493K| 19M| 2688K (1)| 05:57:24 | 1 | 385 | Q1,00 | PCWP | |
| 12 | PX BLOCK ITERATOR | | 4891K| 65M| 6252K (2)| 13:51:08 | 1 | 385 | Q1,00 | PCWC | |
|* 13 | TABLE ACCESS FULL| EARNED_PREMIUM_FACTS | 4891K| 65M| 6252K (2)| 13:51:08 | 1 | 385 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter(TO_NUMBER(TRIM("DIRECT_ASSUMED_CEDED_CODE"))>=1 AND TO_NUMBER(TRIM("DIRECT_ASSUMED_CEDED_CODE"))<=8 AND
"ACCOUNTING_PERIOD">197000)
11 - filter(TO_NUMBER(TRIM("DIRECT_ASSUMED_CEDED_CODE"))>=1 AND TO_NUMBER(TRIM("DIRECT_ASSUMED_CEDED_CODE"))<=8 AND
"ACCOUNTING_PERIOD">197000)
13 - filter(TO_NUMBER(TRIM("DIRECT_ASSUMED_CEDED_CODE"))>=1 AND TO_NUMBER(TRIM("DIRECT_ASSUMED_CEDED_CODE"))<=8 AND
"ACCOUNTING_PERIOD">197000)
Please suggest area as how we can tune the code.
Regards,
Sunny
|
|
|
|
|
|
|
|
|
|
| Re: Tuning Insert [message #503128 is a reply to message #503099] |
Wed, 13 April 2011 02:35  |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
1) Union does an implicit distinct so you don't need the distinct keyword.
2) How many rows in each table and how many rows do you expect each select to return?
3) How many partitions do expect the data to come from for each table? 1 or more?
4) Have you tried it with parallel switched off?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
