I need your help in one scenario of performance which we are facing now a days in our application, kindly give your inputs.
We have one very large table (Partition for each day) in our application which holds like 50+ Million data for a day and this table holds data for 3 Months.This is the kind of DWH Table. In one module we need to expire let say 100 million of records via a PL/SQL code in that table by setting one column expired_flag=Y. Once the old data set gets expire we are now free to insert recalculated 100M data in the same table.
Design and Problem Area:
First we select these 100M data from actual table to a temp table then we do some changes in the fetched data like enrichment and before we insert this fresh data in the actual table we need to expire already existing data by updating one column value to 'Y' (Remember in the table data is around 50M*90 means in Billions). Now this update of flag is taking time like anything and running in hours even for 15-20 M. This update is written in a PL/SQL procedure and its call is a regular activity once/twice in week. This table is Partitioned for Date Range and works like a Fact Table. Also while fetching data it is also possible that user does not fetch all the records for a date and put some filter on select via front end so as per my knowledge even exchange partition wont work here because we never know if user has selected only first 10Hrs of data for each day in a Month and trying to enrich a month data. :-(
Any suggestion or idea is highly helpful indeed.
Below is the DML statement and we can see what happens during update in this DML as it updates few column values.
MERGE /*+ parallel (rq, 2) */
INTO DWH_BILL_DET rq
USING (SELECT /*+ parallel(a, 2) */
FROM PRD_RERATE_CHG_QUE a
WHERE rerate_adj_inv_code >= 0
AND rerate_sel_key NOT IN
WHERE status = 'Cancelled'
AND rerate_batch_key = 163)) rr
ON (rq.rowid = rr.rated_que_rowid)
WHEN MATCHED THEN
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)
Also I collected the AWR & noticed that CPU time for this DML is 1,162.73(s) and elapsed time is 14,521.51(s). So can we say there are so much of wait time here that is why DML is so slow?