Oracle

August 21, 2010

PLSQL Performance

Filed under: Uncategorized — srivenu @ 10:58 am

Recently i was given a plsql procedure for tuning.
This is the proc (I had changed table & column names in it)

CREATE OR REPLACE PROCEDURE FRAN_CORR_IN
AS

V_TMP_AREA VARCHAR2(10);
V_FRAN VARCHAR2(10);
V_5_AREA VARCHAR2(1);
V_4_AREA VARCHAR2(1);
l_nrows           NUMBER:=0;
l_trows           NUMBER:=0;

BEGIN

DECLARE
CURSOR c1  IS SELECT * FROM XYZ_WALKY_200501
              Where SEG_DIRECT = ‘I’;
BEGIN
FOR r1 IN c1
LOOP
 
BEGIN
V_5_AREA :=’0′;
V_4_AREA :=’0′;
V_TMP_AREA := SUBSTR (r1. BNUMBER , 1 ,5 );

BEGIN

SELECT FRANCHISE  INTO V_FRAN FROM AREA_CODES
WHERE AREA_CODE = V_TMP_AREA;
EXCEPTION WHEN NO_DATA_FOUND THEN
V_5_AREA :=’1′;

END;

IF (V_5_AREA = ‘1’) THEN
V_TMP_AREA := SUBSTR(r1. BNUMBER,1,4);

BEGIN

SELECT FRANCHISE INTO V_FRAN FROM AREA_CODES
WHERE AREA_CODE = V_TMP_AREA;
EXCEPTION WHEN NO_DATA_FOUND THEN
V_4_AREA :=’1′;

END   ;
END IF;
IF (V_4_AREA = ‘1’) THEN
V_TMP_AREA := SUBSTR(r1. BNUMBER,1,3);

BEGIN

SELECT FRANCHISE INTO V_FRAN FROM AREA_CODES
WHERE AREA_CODE = V_TMP_AREA;
EXCEPTION WHEN NO_DATA_FOUND THEN
V_FRAN :=’NA’;

END ;
END IF ;
INSERT  INTO  MODIFIED_XYZ_WALKY_200501  VALUES
(V_FRAN, r1.col1, r1.col2, r1.col3, r1.col4, r1.col5);

      l_nrows:=l_nrows+1;

      IF MOD(l_nrows,5000)=0 THEN

        DBMS_OUTPUT.PUT_LINE(‘Rows Inserted : ‘||l_nrows);

        COMMIT;

        l_nrows:=0;

        l_trows:=l_trows+5000;

      END IF;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Exception caught while inserting record’||SQLERRM);
END;
END LOOP;
END;

COMMIT;
END;
/

The guys told me that initially it was processing around 200/300 rows per second. The processing increased to 400 rows/second when they made the following changes
1) Commit for every 5000 rows instead of 50000
2) Cursor c1 was like this
SELECT * FROM XYZ_WALKY_200501;
They split it into 2 procs one with sql
SELECT * FROM XYZ_WALKY_200501
Where SEG_DIRECT = ‘I’
and one with sql
SELECT * FROM XYZ_WALKY_200501
Where SEG_DIRECT = ‘O’
So that they could run both procs simultaneously.

By first glance i could see that they were doing row by row processing instead of bulk. Before i tried to optimize the code i thought i would try to read the code a bit more thoroughly to understand the logic of what they were trying to do. After 30 minutes i could grasp the essence of what they wanted and replaced the proc with this sql.

INSERT  INTO  MODIFIED_XYZ_WALKY_200501
select 	decode(ac1.FRANCHISE,null,
              decode(ac2.FRANCHISE,null,
               decode(ac3.FRANCHISE,null,'NA',ac3.FRANCHISE),
               ac2.FRANCHISE),
              ac1.FRANCHISE),
	r1.col1, r1.col2, r1.col3, r1.col4, r1.col5
FROM   	XYZ_WALKY_200501	r1,
	AREA_CODES 		ac1,
	AREA_CODES 		ac2,
	AREA_CODES 		ac3
Where	SEG_DIRECT = 'I'
and	AREA_CODE(+) = SUBSTR(r1.BNUMBER, 1, 5)
and	AREA_CODE(+) = SUBSTR(r1.BNUMBER, 1, 4)
and	AREA_CODE(+) = SUBSTR(r1.BNUMBER, 1, 3)
/

The sql processed 16,959,545 rows and completed in 20 mins (14,000 rows/sec).

If you could do it in SQL dont use PLSQL!

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: