RE: avoid dynamic SQL

From: Goulet, Richard <Richard.Goulet_at_parexel.com>
Date: Wed, 1 Jul 2009 14:32:41 -0400
Message-ID: <23C4836D8E9C5F4280A66C0C247BC16F2A77AE9C_at_US-BOS-MX011.na.pxl.int>

Alex,  

    While that sounds like a healthy shared pool a little more to 750MB probably wouldn't hurt. Also having the developer put that rollback statement into his code would also be useful to close the cursor thereby allowing reuse.  

Dick Goulet
Senior Oracle DBA
PAREXEL International  

---

From: amonte [mailto:ax.mount_at_gmail.com] Sent: Wednesday, July 01, 2009 5:06 AM
To: Goulet, Richard
Cc: Oracle-L Group
Subject: Re: avoid dynamic SQL

I have 500MB Shared Pool and the instance dies once every 3 to 4 weeks due to 4031

Alex

2009/6/30 Goulet, Richard <Richard.Goulet_at_parexel.com>

        Alex,          

            What is your current shared pool size? Also, is it possible for your developer to close/rollback his efforts so that the space can be reclaimed. Say:                   

	for i in (... cursor ...)
	loop
	select formula
	into vFormula 
	from calculators
	where .......
	
	execute immediate 'BEGIN :EXITCODE:='||vFormula||'; Rollback;
END;';
	end loop;
	
	 

	Dick Goulet 
	Senior Oracle DBA 
	PAREXEL International 

	 



________________________________


	From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of amonte
	Sent: Tuesday, June 30, 2009 2:42 AM
	To: Oracle-L Group
	Subject: avoid dynamic SQL
	
	
	Hi all
	
	
	I have some problem with some dynamic sql generated by a

package, it is causing ORA-4031 and I have to reboot the instance to get rid of the problem.         

        The code does something like         

	for i in (... cursor ...)
	loop
	select formula
	into vFormula 
	from calculators
	where .......
	execute immediate 'BEGIN :EXITCODE:='||vFormula||'; END;';
	end loop;
	
	vFormula contains mathematical formulas
	
	The cursor returns around 30000 rows and this is hammering the

shared pool 30000 times!         

        I dont see how can I avoid this dynamic SQL without doing some major changes (how the formulas are stored for example) and wonder if anyone have some idea.                  

        TIA                           Alex                  

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 01 2009 - 13:32:41 CDT