Re: Bind Variables vs Literals -
Date: Wed, 11 Nov 2009 11:56:51 +0000
Message-ID: <53258cd50911110356g50b46397kb19f091de2eb0562_at_mail.gmail.com>
Q1: PL/SQL interacts with the SQL engine in pretty much exactly the same way as other client applications. You will still see (soft or hard) parses as necessary at runtime; the SQL statements issued by PL/SQL do not bypass that in any way. So if you see lots of similar statements with hard coded literals, it will have the same effect as in a Java or .Net application.
A particular concern will be when you generate the SQL at runtime (ie, you are using Native Dynamic SQL, or using the DBMS_SQL package). Not that the handling of literals is any different, just that there are far more likely to be multiple (10s, 100s or 1000s) of similar statements (ie similar in the same sense that cursor sharing uses).
Regards Nigel
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 - 05:56:51 CST