Home » RDBMS Server » Performance Tuning » Bind variables and SGA size (Oracle, Any, Any)
Bind variables and SGA size [message #416051] Thu, 30 July 2009 05:05 Go to next message
lharpf
Messages: 2
Registered: July 2009
Junior Member
First of all, my apologies if I'm asking something self-evident, but I'm a newcomer to Oracle.

Oracle has the System Global Area (SGA), which is a memory area holding several components. Among these components is the Shared pool, a cache for parsed SQL and PL/SQL statements.

My question is: Does using or not using bind variables in SQL queries affect SGA size (in terms of memory used)? Let's suppose that the SGA is set to be allowed to dynamically grow as needed. My thesis is that when not using bind variables, the Shared pool would in time grow excessively and demand the SGA size to be increased.

The reason I'm asking this is that I've heard of DB applications having issues with SGA size growing too much and eventually causing disk swapping, which kills performance. In the app I'm working on there are plenty of queries that do not use bind variables and I'm wondering if I should spend the time to convert them to use bind variables.
Re: Bind variables and SGA size [message #416056 is a reply to message #416051] Thu, 30 July 2009 05:11 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Not using bind variables is the kiss of death for performance.
and yes it'll increase the sga size.
Use binds.
Re: Bind variables and SGA size [message #416159 is a reply to message #416056] Thu, 30 July 2009 22:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That's pretty reasonable advice, but it's not universal.

Firstly, converting literals to bind variables will only have a measurable impact on FREQUENTLY run statements. If you have a data warehouse that runs a SQL to load data once per day, it won't make a spit of difference to use bind variables.

Secondly, when converting from literals to bind variables, you need to take into account the possible impacts of skewed data. When you share cursors, you could get SQLs querying skewed values using a sub-optimal plan.

Thirdly, although I agree that using bind variables is a best-practice, if you already have an application that uses literals, it is probably a better use of your time to tweak the CURSOR_SHARING parameter rather than recoding much of the application.

Ross Leishman
Re: Bind variables and SGA size [message #416164 is a reply to message #416159] Thu, 30 July 2009 23:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if you already have an application that uses literals, it is probably a better use of your time to tweak the CURSOR_SHARING parameter rather than recoding much of the application.

Given the number of bugs on CURSOR_SHARING parameters over the versions (several hundreds), I completly disagree with this.
Note that among these bugs there are database crashes and wrong results. If you can can deal with these, ok go on.

Regards
Michel
Re: Bind variables and SGA size [message #416180 is a reply to message #416159] Fri, 31 July 2009 00:34 Go to previous message
lharpf
Messages: 2
Registered: July 2009
Junior Member
Quote:
Firstly, converting literals to bind variables will only have a measurable impact on FREQUENTLY run statements. If you have a data warehouse that runs a SQL to load data once per day, it won't make a spit of difference to use bind variables.


The application in my case is a system that has multiple simultaneous users performing various actions, all of which invoke several SQL queries. So perhaps not using bind vars could in this case cause the SGA to bloat?

Quote:
Secondly, when converting from literals to bind variables, you need to take into account the possible impacts of skewed data. When you share cursors, you could get SQLs querying skewed values using a sub-optimal plan.


Ouch, that sounds bad! I did a Google and I suppose www.dbspecialists.com/specialists/specialist2003-11.html is what you're talking about? This is a valuable nugget of information, I hadn't previously come across it in the couple of articles on bind variables I've read.
Previous Topic: Erratic explain plan
Next Topic: Recompute plan each time for the sql
Goto Forum:
  


Current Time: Sat Dec 10 12:38:33 CST 2016

Total time taken to generate the page: 0.10772 seconds