Bulk update in oracle Options

[Sravani]

Hi...

I have performance issue with the bulk update. I have 2 tables one with 21 millions of data and the other table with 2 millions of data.here the requirement is to update the table which is having 21 millions with the other tables data(which is having 2 million records) based on some matching criteria.

The procedure is taking 9 hours to update the table(which is having 21 millions data).

Can someone please provide some performance tuning tips for this procedure..
I have created required indexes on table also.But the performance is not good.

Here my procedure:

CREATE OR REPLACE PROCEDURE AHM_GKPR.CLAIMS_WITHOUT_PARTITIONS
IS
TYPE T_PL_CO IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
L_PL_CO T_PL_CO;
TYPE T_PL_CD IS TABLE OF VARCHAR2(9) INDEX BY BINARY_INTEGER;
L_PL_CD T_PL_CD;
TYPE T_PL_NB IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
L_PL_NB T_PL_NB;
TYPE T_SE_CD IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
L_SE_CD T_SE_CD;
TYPE T_BIR_DT IS TABLE OF DATE INDEX BY BINARY_INTEGER;
L_BIR_DT T_BIR_DT;
TYPE T_IS_AG IS TABLE OF VARCHAR2(2) INDEX BY BINARY_INTEGER;
L_IS_AG T_IS_AG;
TYPE T_ISS_DT IS TABLE OF DATE INDEX BY BINARY_INTEGER;
L_ISS_DT T_ISS_DT;
TYPE T_ISS_ST IS TABLE OF VARCHAR2(2) INDEX BY BINARY_INTEGER;
L_ISS_ST T_ISS_ST;
TYPE T_CUR_CODE IS TABLE OF VARCHAR2(7) INDEX BY BINARY_INTEGER;
L_CUR_CODE T_CUR_CODE;
TYPE T_SH_PL_CD IS TABLE OF VARCHAR2(7) INDEX BY BINARY_INTEGER;
L_SH_PL_CD T_SH_PL_CD;

BEGIN

SELECT /*+ APPEND NOLOGGING PARALLEL(T 4)*/ DISTINCT a.PL_CO,a.PL_CD,a.PL_NB,a.SE_CD,a.BIR_DT,
a.IS_AG,a.ISS_DT,a.ISS_ST,a.CUR_CODE,a.SH_PL_CD BULK COLLECT INTO L_PL_CO,L_PL_CD,L_PL_NB,L_SE_CD,L_BIR_DT,L_IS_AG,
L_ISS_DT,L_ISS_ST,L_CUR_CODE,L_SH_PL_CD FROM (SELECT * FROM table2 WHERE BASE_RDR = 'B' AND PL_CO IN ('12','13','14')) a,
(SELECT * FROM table1 WHERE PO_CM_CD IN ('12','13','14')) b
where b.PO_CM_CD = a.PL_CO
AND b.PL_NB = a.PL_NB
AND b.CO_PL = a.PL_CD
AND b.SE_CODE =a.SE_CD
AND NVL(b.BIR_DT,'31-DEC-9999')=NVL(a.BIR_DT,'31-DEC-9999');

FORALL A IN L_PL_CO.FIRST..L_PL_CO.LAST
UPDATE /*+ PARALLEL(T 4) */ table1
SET IS_AG = L_IS_AG(A),
PO_IS_DT = L_ISS_DT(A),
ISS_STATE = L_ISS_ST(A),
BA_PL_CD = L_PL_CD(A),
CUR_CODE = L_CUR_CODE(A),
SH_PL_CD=L_SH_PL_CD(A)
WHERE PO_CM_CD=L_PL_CO(A)
AND PL_NB = L_PL_NB(A)
AND CO_PL = L_PL_CD(A)
AND SE_CODE = L_SE_CD(A)
AND NVL(BIR_DT,'31-DEC-9999')=NVL(L_BIR_DT(A),'31-DEC-9999');
IF SQL%NOTFOUND
THEN
COMMIT;
END IF;
END;
/

Please advice me the steps to tune the above procedure.

[merrillcd_mem]

Is this a one time update? Maybe it would be easier to CTAS?

[Sravani]

Yes..CTAS is better approach.I tried CTAS.It took 30 minutes to do the same task.

But here in my project we are not allowed to use the CTAS approach.

This is not a one time update.The same procedure need to be executed every month.

Can you please suggest any other methods.

Thanks in advance.

[burleson]

Hi Sravani,

Read this carefully:

http://www.dba-oracle.com/t_efficient_upda...ql_dml_tips.htm

>> The procedure is taking 9 hours to update the table(which is having 21 millions data).

If this only runs once per month, are you remocing all indexes and constraints first?

**********************************************
>> FORALL A IN L_PL_CO.FIRST..L_PL_CO.LAST
>> UPDATE /*+ PARALLEL(T 4) */ table1

You need to set your parallelism to cpu_count-1.

I question you have 5 processors on your server!

Read this, general tips for high-speed updating:


http://dba-oracle.com/t_optimize_insert_sql_performance.htm


*************************************************

>> But here in my project we are not allowed to use the CTAS approach.

Can you use a global temporary table instead?

http://www.dba-oracle.com/t_tuning_executi...with_clause.htm

You can simmulate a CTAS using the WITH clause:

http://www.dba-oracle.com/t_with_clause.htm

[Sravani]

Hi..

>> The procedure is taking 9 hours to update the table(which is having 21 millions data).

If this only runs once per month, are you remocing all indexes and constraints first?

--->Yes I am removing and rebuilding the indexes before i do the update on the table.There are no constraints on my target table.

**********************************************
>> FORALL A IN L_PL_CO.FIRST..L_PL_CO.LAST
>> UPDATE /*+ PARALLEL(T 4) */ table1

You need to set your parallelism to cpu_count-1.

I question you have 5 processors on your server!

---> I have only 4 processors in my system so i will change my DOP to 3 as per your suggestion


*************************************************

>> But here in my project we are not allowed to use the CTAS approach.

Can you use a global temporary table instead?

http://www.dba-oracle.com/t_tuning_executi...with_clause.htm

--->We are not allowed any temp tables so i can not even use this global temporary table.But the performance is pretty good with temporary tables.I tried this procedure by creating a global temporary table and it took just 20 minutes..But in my project i should not use this method

You can simmulate a CTAS using the WITH clause:

http://www.dba-oracle.com/t_with_clause.htm

--->I have read this page.But can you please provide me an example for this.

Thanks for your help
--Sravani.

[burleson]

Hi Sravani,


>> But in my project i should not use this method

Your management does not understand.

There is no way the WITH clause can be rejected . . . .

Here is an example of temporary tabkles and equivalent WITH clause:

http://www.dba-oracle.com/t_sql99_with_clause.htm

************************************************
>> http://www.dba-oracle.com/t_with_clause.htm -->I have read this page.But can you please provide me an example for this.


The WITH clause doex EXACTLY the same thing as temporary tables, and it is 100% ANSI compliant:


http://dba-oracle.com/googlesearchsite_pro...p;q=with+clause