Home » RDBMS Server » Performance Tuning » performance Tunning
performance Tunning [message #403071] Wed, 13 May 2009 11:00 Go to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member

Hi,

I wrote one package which contains four cursors the first cursor which return more than 1.5 lak
records for each record of the first cursor i define another cursor, like that i define three
cursors for each iteration i have to process to arrive the balance of amount .

My problem is while executing this package it will take more than 2 hours.
Can anyone guide me how can i tune this package i am new one to tunning.

I cant able to provide the package it is large one.
Re: performance Tunning [message #403074 is a reply to message #403071] Wed, 13 May 2009 11:12 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
No Oracle version and no useful information to do any analysis.
We can only give most generic hints, with which you are not going be impressed.
To start with, write better SQL.
Since you know the first cursor will fetch X records, use bulk collect with varray.
If you can do with single sql/cursor, do it.

Gather statistics on tables and indexes.

[Updated on: Wed, 13 May 2009 11:18]

Report message to a moderator

Re: performance Tunning [message #403075 is a reply to message #403071] Wed, 13 May 2009 11:13 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
which have you tried & what were the results

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: performance Tunning [message #403078 is a reply to message #403071] Wed, 13 May 2009 11:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Combine all 4 cursors into a single cursor.
Re: performance Tunning [message #403084 is a reply to message #403071] Wed, 13 May 2009 11:24 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Can anyone guide me how can i tune this package i am new one to tunning.
You do NOT tune PL/SQL.
You tune SQL.
SQL> ALTER SESSION SET SQL_TRACE=TRUE;

run trace file through tkprof to see where the time is actually being spent.
Re: performance Tunning [message #403135 is a reply to message #403084] Wed, 13 May 2009 22:20 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
BlackSwan wrote on Thu, 14 May 2009 02:24

You do NOT tune PL/SQL.
You tune SQL.

I disagree. Much of tuning involes the selection of a better algorithm to achieve the same functionality. In SQL, that algorithm is determined at PARSE time, because that's how SQL works. In PL/SQL, the algorithm is determined at build time - it is hard-coded.

There is scope to have poor algorithms in both SQL and in PL/SQL. In SQL, we encourage the optimiser to make better choices. In PL/SQL, we have to re-code.

The algorithm you are using is bad PL/SQL. See this article for more on that. You should heed @JR's advice and merge it all into a single cursor.

Ross Leishman
Re: performance Tunning [message #403137 is a reply to message #403071] Wed, 13 May 2009 22:29 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Ross Leishman,

From my perspective, SQL is "typically" 100 - 1000 times slower than PL/SQL.
PL/SQL runs at CPU speed, while SQL runs at disk speed.
You'll get much more bang for the buck by tuning SQL before ever looking at PL/SQL.
I have never had PL/SQL complex enough to require any tuning.
Re: performance Tunning [message #403138 is a reply to message #403137] Wed, 13 May 2009 22:44 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I don't think we're talking about the same thing.

I'm talking about the arrangement of SQL statements within the PL/SQL.

If you have a big cursor (say 1M rows) and you nest SQL statements WITHIN the cursor loop, then you are doomed. My reasoning is that regardless of how you tune the nested statement, you still have to execute it 1M times.

Tuning involves restructuring the PL/SQL to remove any nesting. This will almost certainly affect the structure of the SQL, which may then warrant a round of pure SQL tuning.

Ross Leishman
Re: performance Tunning [message #403163 is a reply to message #403138] Thu, 14 May 2009 00:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Ross Leishman wrote:
>>you nest SQL statements WITHIN the cursor loop, then you are doomed
Exactly!.
I wish I can play that in a loud audio around my office.
For the last 2 days I am looking into performance issues
with an application.
Thanks to DBMS_HPROF, I was able to single out the offending statement pretty easily (from a ~2500 line code).
Just a cute little totally unwanted SQL statement being called in a loop.

Re: performance Tunning [message #403381 is a reply to message #403071] Fri, 15 May 2009 04:43 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
For the issue i told here i have created bitmap indexes for the below query and index details are given below.

Anyone help me to tune this query.
This is the second cursor i am using in that procedure.

SELECT COUNT (a.sci_las_sec_id) lmtcnt, a.sci_las_sec_id
FROM cms_limit_security_map a,
(SELECT *
FROM  cms_limit_security_map
WHERE sci_las_lmt_id = recs(i).lmt_id
AND   update_status_ind != 'D') b
WHERE a.sci_las_sec_id =b.sci_las_sec_id 				and
a.cms_collateral_id=b.cms_collateral_id 				and
a.sci_las_llp_id=b.sci_las_llp_id 						and
--a.charge_id = b.charge_id 								and
b.update_status_ind != 'D'
GROUP BY a.sci_las_sec_id
ORDER BY lmtcnt, a.sci_las_sec_id


the count will be :

lmtcnt  sci_las_sec_id
-----   ------------
11	23204574
17	23161738


The lmtcnt 11 and 17 is the count which all sci_las_lmt_id limits
tagged with that security_id means 11 number of recs(i).lmt_id and corresponding amount will be available for that particular sci_las_sec_id (23204574) and this security id have some amount of value.

sci_las_sec_id
----
(S1)(23204574) --- 200000 amount

sci_las_lmt_id
-----
(L1) (20105519) --- 50000 amount


the amount of L1 should be minus with the amount of S1 and like the same for remaining 10 Limits.

Its is not one to one mapping it is many to many mapping.

The next limit amount from that 11 should assign to the security
by after executing the query which is given above.

That limit may mapped with some security and this limit amount should be allocated to minimum lmtcnt security only.

Main process is i have temp table structure is given below.

The above query which i posted is to count the security which is
associated for various sci_las_lmt_ids

for that i have created two more indexes which is given below.

create bitmap index cms_lmt_sec_bmap_indx on cms_limit_security_map (sci_las_sec_id,sci_las_lmt_id,update_status_ind)


create bitmap index cms_lmt_sec_bmap_indx1 on cms_limit_security_map (update_status_ind,sci_las_lmt_id)


I have taken the statistics before and after creating the index.but it shows same one. i am using toad to explain the plan

Can anyone correct where i get lacking and correct me if i am wrong.

if the given things are not understandable i will produce my package also.




[Updated on: Fri, 15 May 2009 05:11]

Report message to a moderator

Re: performance Tunning [message #403429 is a reply to message #403071] Fri, 15 May 2009 09:53 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Can anyone do the needful ...
Re: performance Tunning [message #403517 is a reply to message #403135] Sat, 16 May 2009 04:29 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
rleishman wrote on Thu, 14 May 2009 13:20
The algorithm you are using is bad PL/SQL. See this article for more on that. You should heed @JR's advice and merge it all into a single cursor.

Ross Leishman


Ross Leishman
Re: performance Tunning [message #403831 is a reply to message #403517] Tue, 19 May 2009 01:41 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member

Hi rleishman,


Much more thanks rleishman its really valuable document but i have modified according to that document but still my package struc over four hours and i am not able see any result.

I have provided my package here so please post to resolve this
issue.i sorry to say this i have to resolve this usrgently.

I have created GTT (Global Temporary Table) here as given below.

CREATE GLOBAL TEMPORARY TABLE CMS_SECURITY_MOD
(
  CMS_COLLATERAL_ID    NUMBER(19),
  SCI_SECURITY_DTL_ID  NUMBER(10),
  BAL_CMV              NUMBER(30,2)
)
ON COMMIT PRESERVE ROWS


CREATE OR REPLACE package body pkg_sec_cal_gtt 
IS

procedure pro_sec_cal_gtt 
is 

cursor cur_security_details is
select cms_collateral_id,sci_security_dtl_id,cmv
from  cms_security
where status = 'ACTIVE'  	 					   	   and
decode(req_sec_cov_ind,NULL,'Y',req_sec_cov_ind) = 'Y';


cursor cur_limit_details
is
select  lmt_id, 						 
(NVL (cms_activated_limit, 0) * NVL (cms_req_sec_coverage, 0) / 100) REQ_ABS_VALUE, 
null, cms_act_sec_coverage, 
(NVL (cms_activated_limit, 0) * NVL (cms_req_sec_coverage, 0) / 100) bal_security_value,
sysdate created_date,nvl(cms_activated_limit,0) cms_activated_limit
from  sci_lsp_appr_lmts where
upper(lmt_type_value) <> 'CONTROL' 					
and   upper (cms_limit_status) = 'ACTIVE'
and 	update_status_ind !='D'
order by lmt_id;

cursor cur_sec_det(p_lmt_id sci_lsp_appr_lmts.lmt_id%type) is  
SELECT COUNT (a.sci_las_sec_id) lmtcnt, a.sci_las_sec_id
FROM cms_limit_security_map a,
(SELECT *
FROM cms_limit_security_map
WHERE sci_las_lmt_id = p_lmt_id
AND update_status_ind != 'D') b
WHERE a.sci_las_sec_id =b.sci_las_sec_id and
a.cms_collateral_id=b.cms_collateral_id and
a.sci_las_llp_id=b.sci_las_llp_id and
b.update_status_ind != 'D'
GROUP BY a.sci_las_sec_id
ORDER BY lmtcnt, a.sci_las_sec_id;

TYPE reclist IS TABLE OF cur_security_details%ROWTYPE;
recs reclist;

TYPE lmtdetails IS TABLE OF cur_limit_details%ROWTYPE;
lmtdet lmtdetails;

TYPE secdetails IS TABLE OF cur_sec_det%ROWTYPE;
secdet secdetails;

coll_cnt1		  number ;
coll_cnt2		  number ;


v_cms_collateral_id		 number(19)  ;
v_cmv					 number(30,2);			 
v_status				 varchar2(20);		 
v_req_sec_cov_ind		 varchar2(2) ;
v_sci_security_dtl_id	 number(10) ;
v_cmv_currency			 char(3) ;

spec_lmt_cnt			 number;
temp_bal_security_value	 number(30,2) ;

Begin

    EXECUTE IMMEDIATE 'TRUNCATE TABLE CMS_SECURITY_MOD';
    EXECUTE IMMEDIATE 'TRUNCATE TABLE SEC_SECURITY_EVALUATION_RESULT';
    COMMIT;
	  
    OPEN cur_security_details;
    LOOP
    FETCH cur_security_details BULK COLLECT INTO recs LIMIT 5000;

    FORALL i IN 1..recs.COUNT
    INSERT INTO CMS_SECURITY_MOD VALUES recs(i);

    EXIT WHEN cur_security_details%NOTFOUND;
    END LOOP;
    CLOSE cur_security_details;
	
	
	OPEN cur_limit_details;
    FETCH cur_limit_details BULK COLLECT INTO lmtdet;
	
	FOR j IN 1..lmtdet.COUNT
	Loop
		OPEN cur_sec_det(lmtdet(j).lmt_id);
    	FETCH cur_sec_det BULK COLLECT INTO secdet ;
    	coll_cnt2 := secdet.COUNT;	
		
		FOR k IN 1..secdet.COUNT
		loop
		if ((coll_cnt2 = 1)  and (secdet(k).lmtcnt = 1)) then
		   
		   	 select count(*) into spec_lmt_cnt
			   from cms_security
			   where sci_security_dtl_id = secdet(k).sci_las_sec_id and
			   		 status = 'ACTIVE' 	  						   and
					 decode(req_sec_cov_ind,NULL,'Y',req_sec_cov_ind) = 'Y';
   
   
   			 	   if ( spec_lmt_cnt > 0 ) then
	
  	   		 select cms_collateral_id,cmv,status,req_sec_cov_ind,
	   		  	   	sci_security_dtl_id,cmv_currency
			  into  v_cms_collateral_id,v_cmv,v_status,v_req_sec_cov_ind,
			  		v_sci_security_dtl_id,v_cmv_currency
	    	  from  cms_security
			  where sci_security_dtl_id =  secdet(k).sci_las_sec_id and
			  		status = 'ACTIVE'  	 					   	   and  
         			decode(req_sec_cov_ind,NULL,'Y',req_sec_cov_ind) = 'Y';
					
	     temp_bal_security_value :=  0 ;
		 
		update SEC_SECURITY_EVALUATION_RESULT
       	   set allocated_cmv = v_cmv,
		   	   act_sec_coverage = nvl(v_cmv,0) / decode(lmtdet(j).cms_activated_limit,0,Null,lmtdet(j).cms_activated_limit) * 100,
		   	   bal_security_value = temp_bal_security_value
         where lmt_id = lmtdet(j).lmt_id; 					 
		
		if sql%notfound then
		 
		  insert into SEC_SECURITY_EVALUATION_RESULT 
		  		 values (lmtdet(j).lmt_id,lmtdet(j).req_abs_value,v_cmv,
				 		 nvl(v_cmv,0) / decode(lmtdet(j).cms_activated_limit,0,Null,lmtdet(j).cms_activated_limit) * 100,
						 temp_bal_security_value,sysdate);	
		   
		End if;
		
--		else
		
		end if ;
				end if ;
		End loop;
				close cur_sec_det;	
				End loop;
			commit;  
	close cur_limit_details;		
	
	

End pro_sec_cal_gtt;
End pkg_sec_cal_gtt;
/
Re: performance Tunning [message #403847 is a reply to message #403831] Tue, 19 May 2009 03:26 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You are still opening cursors inside a loop:
FOR j IN 1..lmtdet.COUNT
Loop
    OPEN cur_sec_det(lmtdet(j).lmt_id);
    FETCH cur_sec_det BULK COLLECT INTO secdet ;


You are still executing SQL inside a loop:
FOR k IN 1..secdet.COUNT
loop
    if ((coll_cnt2 = 1)  and (secdet(k).lmtcnt = 1)) then	   
        select count(*) into spec_lmt_cnt
        from cms_security
        ...


Read the article again. It will tell you that you need to merge the nested SQL back into the main query, or - if you cannot design it that way due to two tables with mismatched cardinality - to process concurrent cursors.

Ross Leishman
Previous Topic: Increasing number of Processes
Next Topic: PERFORMANCE ISSUE - QUERY TUNING
Goto Forum:
  


Current Time: Sun Dec 04 20:58:07 CST 2016

Total time taken to generate the page: 0.35706 seconds