Re: Bind Variables vs Literals -
From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Wed, 11 Nov 2009 14:47:17 +0200
Message-ID: <6e49b6d00911110447u32b041cdod72f071ad15db8ea_at_mail.gmail.com>
It could or couldn't cause problems depending on HOW you are using literals. If you are using literals in static SQL something like gender = 'M' or processed = 'Y' then it is OK. Even more - because there is big probability for distribution of such nondynamic values to be quite scattered e.g. many 'Y' values and just a few 'N' values, execution plan could be different for queries using different literal values and it will only benefit your system.
On the other hand if you are dynamically building SQL statements using literal values for columns, which contain many distinct values, then probability of reusing your statements is very low, effectively flooding shared pool with unique non reusable statements.
Date: Wed, 11 Nov 2009 14:47:17 +0200
Message-ID: <6e49b6d00911110447u32b041cdod72f071ad15db8ea_at_mail.gmail.com>
It could or couldn't cause problems depending on HOW you are using literals. If you are using literals in static SQL something like gender = 'M' or processed = 'Y' then it is OK. Even more - because there is big probability for distribution of such nondynamic values to be quite scattered e.g. many 'Y' values and just a few 'N' values, execution plan could be different for queries using different literal values and it will only benefit your system.
On the other hand if you are dynamically building SQL statements using literal values for columns, which contain many distinct values, then probability of reusing your statements is very low, effectively flooding shared pool with unique non reusable statements.
There are plenty of topics about bind variables in http://asktom.oracle.com
Gints Plivna
http://www.gplivna.eu
2009/11/11 Amir Gheibi <gheibia_at_gmail.com>:
> Hi Listers,
> I have two questions.
>
> - I know what using literals in SQL can do to the SGA. My question is
> actually very simple. If literals are used in Stored Procedures, since
> procedures are loaded and compiled once when they are called, would that
> cause problems as well?
> - In the alert log, I noticed that there are lots of these messages: "Heap
> size 2122K exceeds notification threshold (2048K)". What does that indicate?
> Regards,
> ~ Amir Gheibi
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 11 2009 - 06:47:17 CST