Home » RDBMS Server » Performance Tuning » Updaing a 804 million partioned table (for 365 days) from a 67 million table
Updaing a 804 million partioned table (for 365 days) from a 67 million table [message #273648] Thu, 11 October 2007 03:25 Go to next message
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;


Re: Updaing a 804 million partioned table (for 365 days) from a 67 million table [message #273658 is a reply to message #273648] Thu, 11 October 2007 04:12 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Post in the appropriate forum, that is Performances Tuning.
Read How to Identify Performance Problem and Bottleneck .

Regards
Michel
Previous Topic: ora 01555
Next Topic: Large table help
Goto Forum:
  


Current Time: Sat Dec 10 20:27:15 CST 2016

Total time taken to generate the page: 0.09614 seconds