Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Bulk update in oracle
Sravani
post Mar 28 2012, 04:14 AM
Post #1


Newbie
*

Group: Members
Posts: 3
Joined: 28-March 12
Member No.: 46,991



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.
Go to the top of the page
 
+Quote Post
merrillcd_mem
post Mar 28 2012, 07:39 AM
Post #2


Member
**

Group: Members
Posts: 29
Joined: 30-June 11
From: Memphis TN
Member No.: 45,469



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

Go to the top of the page
 
+Quote Post
Sravani
post Mar 28 2012, 08:12 AM
Post #3


Newbie
*

Group: Members
Posts: 3
Joined: 28-March 12
Member No.: 46,991



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.
Go to the top of the page
 
+Quote Post
burleson
post Mar 28 2012, 08:55 AM
Post #4


Advanced Member
***

Group: Members
Posts: 11,617
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Sravani
post Mar 29 2012, 12:33 PM
Post #5


Newbie
*

Group: Members
Posts: 3
Joined: 28-March 12
Member No.: 46,991



QUOTE (burleson @ Mar 28 2012, 09:55 AM) *
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 smile.gif


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

>> 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 sad.gif

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.
Go to the top of the page
 
+Quote Post
burleson
post Mar 30 2012, 01:32 AM
Post #6


Advanced Member
***

Group: Members
Posts: 11,617
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd October 2014 - 01:31 PM