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!

Index hint in SQL Profile being ignored

Filed under: Uncategorized — srivenu @ 10:10 am

Recently i faced an issue with one sql profile. An index hint in it was being ignored by the CBO. This is just the opposite of the one of the situations mentioned here by Kerry Osborne.

I had a sql like this

UPDATE 	XYZ
SET 	STATUS_FLAG='2'
WHERE 	created_date>=sysdate-1
and 	STATUS_FLAG=1
AND 	ATTRIBUTE9='ABCDE'
and 	CLASS!='CLASS1'
and 	rownum <=500
/

The CBO was using this plan

----------------------------------------------------------------------
| Id  | Operation                            | Name                  |
----------------------------------------------------------------------
|   1 |  UPDATE                              | XYZ                   |
|*  2 |   COUNT STOPKEY                      |                       |
|   3 |    PARTITION RANGE ITERATOR          |                       |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| XYZ                   |
|*  5 |      INDEX RANGE SCAN                | CREATED_DATE          |
----------------------------------------------------------------------

I wanted it to use the composite index (IDX_XYZ_SF_CD_A9) on STATUS_FLAG, CREATED_DATE & ATTRIBUTE9 colums. For legacy reasons they were using CURSOR_SHARING=SIMILAR, so i wanted to fix it using a SQL Profile. (Just a note of caution – Its not the recommended way, thanks for these notes by Jonathan Lewis and Tom Kyte).
I created a SQL Profile like this (thanks to Kerry Osborne for his scripts)

IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘10.2.0.4’)
FIRST_ROWS
OUTLINE_LEAF(@”UPD$1″)
INDEX_RS_ASC(@”UPD$1″ “XYZ”@”UPD$1” “IDX_XYZ_SF_CD_A9″)

But the plan generated by the CBO does not change and  it continues to use the single column index on CREATED_DATE. I scratched my head and verified that i properly flushed the shared pool and that v$sql is showing my profile usage .

Finally i tried modifying the index hint – removed the index name and specified all the column names like this

IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘10.2.0.4’)
FIRST_ROWS
OUTLINE_LEAF(@”UPD$1″)
INDEX_RS_ASC(@”UPD$1” “XYZ”@”UPD$1” (“XYZ”.”STATUS_FLAG” “XYZ”.”CREATED_DATE” “XYZ”.”ATTRIBUTE9″))

Voila! it started working!

August 9, 2010

PLAN_HASH_VALUE Limitations – PQ_DISTRIBUTE & skew in data distribution across parallel slaves

Filed under: Uncategorized — srivenu @ 12:04 pm

Recently I read an article entitled “Is plan_hash_value a final say?” on Riyaj Shamsudeen’s blog. As usual in his lucid style, Riyaj Shamsudeen has highlighted the shortcomings of PLAN_HASH_VALUE and why it shouldnt be relied upon while comparing plans.

I wanted to jot down my own experiences and how i came to realsize this fact. A couple of years back I worked in a BI DBA team managing a decently sized datawarehouse of around 200-300TB. I faced some serious performance issues in parallel queries. Mostly we used to have a fixed set of jobs. And somedays our jobs used to take more time than usual – sometimes even 5 – 6 times more than usual.

The tables involved in the sql were composite partitioned with range partitions on CIRCLE & DATE and hash subpartitioned on PRODUCT_ID, CUSTOMER_ID etc. The data in our tables was highly skewed across CIRCLE’s with some old circles having abnormally huge data compared to the new ones. Lets say table A is range partitioned on CIRCLE & DATE and hash sub partitioned on PRODUCT. Lets say B was hash partitioned on PRODUCT. The sql is something like this

select a.col1, a.col2, b.col2
from a, b
where a.circle=b.circle
and a.product_id = b.product_id;

The plan was something like this

           HASH JOIN               |
            PX RECEIVE             |
             PX SEND HASH          | :TQ10000
              PX BLOCK ITERATOR    |
               TABLE ACCESS FULL   | A
            PX RECEIVE             |
             PX SEND HASH          | :TQ10001
              PX BLOCK ITERATOR    |
               TABLE ACCESS FULL   | B

I had scheduled a job which used to capture a lot of session details for all the sql’s running in the database for more than 15 minutes. The information captured included – sql stats, parallel slaves, workarea stats, plan details etc. On comparing the different runs, I did not find anything unsual, both the good and bad runs used to had the same plan hash values, stats (like disk reads, buffer gets etc). Both the good and bad runs showed the same plan as above.

The clincher came from the V$PQ_TQSTAT output. The bad run clearly showed a skew in the data distribution across the parallel slaves while the good runs had a good equal distribution of data. On close observation of the complete plan (using the display option’s dbms_xplan.display(null,null,’all’,null)) I found the issue in the section “Column Projection Information (identified by operation id):”

The good plans had this column projection info

— for the lines “PX RECEIVE”

“A”.”PRODUCT_ID” [VARCHAR2,50], “A”.”CIRCLE” [VARCHAR2,50],
“B”.”PRODUCT_ID” [VARCHAR2,50], “B”.”CIRCLE” [VARCHAR2,50],

for the lines “PX SEND HASH”

(#keys=2) “A”.”PRODUCT_ID” [VARCHAR2,50], “A”.”CIRCLE” [VARCHAR2,50],

for the line “HASH JOIN”

(#keys=2) “A”.”PRODUCT_ID” [VARCHAR2,50], “A”.”CIRCLE” [VARCHAR2,50],

The bad plans had this column projection info

— for the lines “PX RECEIVE”

“A”.”CIRCLE” [VARCHAR2,50], “A”.”PRODUCT_ID” [VARCHAR2,50],
“B”.”CIRCLE” [VARCHAR2,50],”B”.”PRODUCT_ID” [VARCHAR2,50],

for the lines “PX SEND HASH”

(#keys=2) “A”.”CIRCLE” [VARCHAR2,50], “A”.”PRODUCT_ID” [VARCHAR2,50],

for the line “HASH JOIN”

(#keys=2) “A”.”CIRCLE” [VARCHAR2,50], “A”.”PRODUCT_ID” [VARCHAR2,50],

The distribution of data across the slaves in the order of CIRCLE & PREDICATE has caused only 1 or a few slaves to do most of the work as only 1 or few circles had large amount of data. I raised a TAR and got this fixed. But I became aware of the limitation that the way data is distributed across the parallel slaves in case of a multi column table join, the key order is not reflected in the PLAN_HASH_VALUE.

For the benefit of those who are facing the same issue, there is one workaround i stumbled upon.

I faced the same issue recently on 10.2.0.4 on SUN SPARC SOLARIS. I was able to workaround the issue by changing the order of predicates (i did not use any ORDERED_PREDICATES hint)

I had an issue with the sql written like this

select a.col1, a.col2, b.col2
from a, b
where a.circle=b.circle
and a.product_id = b.product_id.

But when I rewrote the sql like this, I got the right order of keys in the join.

select a.col1, a.col2, b.col2
from a, b
where a.product_id = b.product_id
and a.circle=b.circle;

So looks like the order of predicates does have some relevance ! (even without the ORDERED_PREDICATES hint)

Blog at WordPress.com.