| Bind variables and SGA size [message #416051] |
Thu, 30 July 2009 05:05  |
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 #416159 is a reply to message #416056] |
Thu, 30 July 2009 22:34  |
rleishman
Messages: 3728
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 #416180 is a reply to message #416159] |
Fri, 31 July 2009 00:34 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
