| Updaing a 804 million partioned table (for 365 days) from a 67 million table [message #273648] |
Thu, 11 October 2007 03:25  |
Aju
Messages: 94
Registered: October 2004
|
Member |
|
|
The STATEMENT_FACT table is a daily partitioned table containg data for 1 year(365 days) and for each day the data volume is
around 5.75 million. The index and partition on the
statement_fact is as below
ACCOUNT_KEY (NORMAL)
BILLING_YEAR_MTH (BITMAP)
CURRENT_DUE_FLAG (BITMAP)
EXTERNAL_STATUS (BITMAP)
MTH_CODE (BITMAP)
NBR_MTHS_DUE (BITMAP)
Parition is on BILLING_CYCLE_DATE(RANGE) and CLIENT_ID(List)
I have a table called STMT_FIX which does not have any
partition or index and contain 1 month data (67 million)
I have 2 columns called Gross_sales and Net_of_Gross_Sales from
STMT_FIX which need to be updated into STMT_FACT based on the
following condition
SFACT.ACCOUNT_KEY = SFIX.ACCOUNT_KEY
SFACT.CLIENT_ID = SFIX.CLIENT_ID
SFACT.CURRENT_ACCOUNT_NBR = SFIX.CURRENT_ACCOUNT_NBR
SFACT.BILLING_CYCLE_DATE = SFIX.BILLING_CYCLE_DATE
Attached is the code for the same. But it is getting me the error on the cursor ora-12081 error signalled in paralellel query server P028. Attached is the error message
SELECT /*+ PARALLEL(SFACT, 32) PARALLEL(SFIX,32) */ SFACT.ROWID, SFIX.NET_OF_GROSS_SALES, SFIX.GROSS_SALES
FROM OWNER_CDCI.STATEMENT_FACT SFACT, LOADER_CDCI.STMT_FIX SFIX WHERE
SFACT.ACCOUNT_KEY = SFIX.ACCOUNT_KEY
AND SFACT.CLIENT_ID = SFIX.CLIENT_ID
AND SFACT.CURRENT_ACCOUNT_NBR = SFIX.CURRENT_ACCOUNT_NBR
AND SFACT.BILLING_CYCLE_DATE = SFIX.BILLING_CYCLE_DATE
AND SFACT.BILLING_CYCLE_DATE BETWEEN Para1 AND Para2;
Can somebosy suggest to optimise this qry
Attached is the explain plan.
--------------
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1838 Card=1 Bytes=10
2)
1 0 PX COORDINATOR
2 1 PX SEND* (QC (RANDOM)) OF ':TQ10000' (Cost=1 Card=1 Byte :Q1000
s=58)
3 2 TABLE ACCESS* (BY LOCAL INDEX ROWID) OF 'STATEMENT_FAC :Q1000
T' (TABLE) (Cost=1 Card=1 Bytes=58)
4 3 NESTED LOOPS* (Cost=1838 Card=1 Bytes=102) :Q1000
5 4 PX BLOCK* (ITERATOR) :Q1000
6 5 TABLE ACCESS* (FULL) OF 'STMT_FIX' (TABLE) (Cost :Q1000
=1837 Card=5578540 Bytes=245455760)
7 4 PARTITION RANGE* (SINGLE) (Cost=1 Card=1) :Q1000
8 7 PARTITION LIST* (ITERATOR) (Cost=1 Card=1) :Q1000
9 8 INDEX* (RANGE SCAN) OF 'INDX_SF_ACCT_KEY' (IND :Q1000
EX) (Cost=1 Card=1)
2 PARALLEL_TO_SERIAL
3 PARALLEL_COMBINED_WITH_CHILD
4 PARALLEL_COMBINED_WITH_PARENT
5 PARALLEL_COMBINED_WITH_CHILD
6 PARALLEL_COMBINED_WITH_PARENT
7 PARALLEL_COMBINED_WITH_PARENT
8 PARALLEL_COMBINED_WITH_PARENT
9 PARALLEL_COMBINED_WITH_PARENT
Statistics
----------------------------------------------------------
205 recursive calls
3 db block gets
145527 consistent gets
107677 physical reads
636 redo size
217 bytes sent via SQL*Net to client
213 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
--------------
CREATE OR REPLACE PROCEDURE JCP_UPDATE_STMT_ROWID(
p_in_JOB_NAME VARCHAR2,
p_in_Start_Date DATE,
P_in_End_Date DATE
)
IS
TYPE t_NET_OF_GROSS_SALES IS TABLE OF STMT_FIX.NET_OF_GROSS_SALES%TYPE;
TYPE t_GROSS_SALES IS TABLE OF STMT_FIX.GROSS_SALES %TYPE;
TYPE t_ROWID IS TABLE OF ROWID;
l_NET_OF_GROSS_SALES t_NET_OF_GROSS_SALES;
l_GROSS_SALES t_GROSS_SALES ;
l_ROWID t_ROWID;
CURSOR C1(Para1 DATE, Para2 DATE) IS
SELECT /*+ PARALLEL(SFACT, 32) PARALLEL(SFIX,32) */ SFACT.ROWID, SFIX.NET_OF_GROSS_SALES, SFIX.GROSS_SALES
FROM OWNER_CDCI.STATEMENT_FACT SFACT, LOADER_CDCI.STMT_FIX SFIX WHERE
SFACT.ACCOUNT_KEY = SFIX.ACCOUNT_KEY
AND SFACT.CLIENT_ID = SFIX.CLIENT_ID
AND SFACT.CURRENT_ACCOUNT_NBR = SFIX.CURRENT_ACCOUNT_NBR
AND SFACT.BILLING_CYCLE_DATE = SFIX.BILLING_CYCLE_DATE
AND SFACT.BILLING_CYCLE_DATE BETWEEN Para1 AND Para2;
l_stop_Count NUMBER := 0; -- Sentinel
l_Start_Time JOB_AUDIT_STMT.JOB_START%TYPE := SYSDATE;
l_step JOB_AUDIT_STMT.STEP%TYPE;
l_count NUMBER;
l_limit NUMBER := 1000000;-- 1 Million
l_Update_Cnt NUMBER;
BEGIN
SELECT NVL(MAX(l_step),0) INTO l_step FROM JOB_AUDIT_STMT;
OPEN C1(p_in_Start_Date, p_in_End_Date);
LOOP
FETCH C1 BULK COLLECT INTO
l_ROWID,
l_NET_OF_GROSS_SALES,
l_GROSS_SALES
LIMIT l_limit;
SELECT COUNT(*) INTO l_stop_Count FROM SENTINEL WHERE STATUS = 'STOP'
AND JOB_NAME = 'JCP_UPDATE_STMT';
-- CREATE TABLE SENTINEL (STATUS VARCHAR2(10), JOB_NAME VARCHAR2(20))
exit when l_Rowid.count = 0 OR l_stop_count =1;
l_step := l_step +1;
INSERT INTO JOB_AUDIT_STMT JOB_NAME(JOB_NAME, JOB_STATUS, JOB_START, STEP) VALUES
(p_in_JOB_NAME, 'RUNNING', SYSDATE, l_step);
forall indx in l_RowID.first..l_RowID.last
UPDATE /*+ PARALLEL(A,32) */ OWNER_CDCI.STATEMENT_FACT A
SET NET_OF_GROSS_SALES = l_NET_OF_GROSS_SALES(indx),
GROSS_SALES = l_GROSS_SALES(indx)
WHERE
ROWID = l_ROWID(indx);
UPDATE JOB_AUDIT_STMT SET
JOB_STATUS = 'COMPLETED',
JOB_END = SYSDATE,
RECS_PROCESSED = l_limit
WHERE STEP = l_step;
COMMIT;
END LOOP;
COMMIT;
CLOSE C1;
END JCP_UPDATE_STMT_ROWID;
BEGIN
JCP_UPDATE_STMT_ROWID(p_in_JOB_NAME => 'JCP_UPDATE_STMT_200706',
p_in_Start_Date => '01-JUL-2006',
P_in_End_Date => '31-JUL-2006');
END;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
