Re: bind variables

You probably already are. You have to go to a lot of trouble to avoid using bind variables in PL/SQL. (I'm too lazy^H^H^H^H busy to find the doc link right now.)

• Philip
  • Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, September 06, 2002 11:38 AM

Despite the importance of using bind variables, the Oracle documentation seems to make very little reference to how to use them(for example the PL/SQL manual)

Can anyone point me at any decent documentation on the subject of using bind
variables in PL/SQL?

John

> -----Original Message-----
> From: Nicoll, Iain (Calanais) [SMTP:iain.nicoll_at_calanais.com]
> Sent: 06 September 2002 15:23
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Must Read for Every Developer and DBA
>
> I thought that bind variables were faster but you always have to ensure
> that
> if you're accessing by data which may be heavily skewed and histograms
> would
> usually help you may not want to use bind variables as they will disable
> the
> use of histograms.
>
> In saying that it doesn't look as though that would be the case here.
>
> Iain Nicoll
>
> -----Original Message-----
> Sent: Friday, September 06, 2002 2:33 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hello Vikas,
>
> As You said We should always make use of bind variables as it executes
> faster as compare to the statements where we do not
> make use of bind variables.
>
> Q1) Can you please take a more specific example as how a statement can
be
> altered to make use of bind variable.
>
> Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA WHERE ROWNUM < 5 to get
> few
> samples for you
>
> These are as follows
>
> UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A101675'
> ,DATE_QUEUED = sysdate WHERE AWB_PREFIX
> = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' '
> AND
> PROCESS = 1 AND USER_ID = 'A101675'
>
> UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675'
> ,DATE_QUEUED = sysdate WHERE AWB_PREFIX
> = '125' AND AWB_NUMBER = 68221156 AND AWB_SUFFIX = ' '
> AND
> PROCESS = 1 AND USER_ID = 'A101675'
>
> UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID ='A105722'
> ,DATE_QUEUED = sysdate WHERE AWB_PREFIX
> = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = 'A '
> AND
> PROCESS = 1 AND USER_ID = 'A105722'
>
> UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A105722'
> ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = '125' AND AWB_NUMBER
=
> 67557416 AND AWB_SUFFIX = ' ' AND PROCESS = 1 AND
> USER_ID
> = 'A105722
>
> How can I Introduce bind variables in these statements ?
>
> I may be sending a wrong SAMPLE as I feel I should apply your remove
> constant function and then send few SQL statements
>
> Warm Regards,
> Om
>
> In your case -- you are NOT using bind variables.
>
> Taking your update statement here:
>
> UPDATE CNST_QUEUE SET PROCESS = -1 ,USER_ID = 'A101675'
> ,DATE_QUEUED = sysdate WHERE AWB_PREFIX
> = '125' AND AWB_NUMBER = 67557405 AND AWB_SUFFIX = ' '
> AND
> PROCESS = 1 AND USER_ID = 'A101675'
>
> that SHOULD BE recoded in the application to become :
>
> update cnst_queue set process = :b1, user_id = :b2, date_queued =
sysdate,
> where awb_prefix = :b3
> and awb_number = :b4
> and awb_suffix = :b5
> and awb_process = :b6
> and user_id = :b7;
>
> and bind in those values before you execute this statement. There are
ways
> in which it could be done and vary from language to language and
> environment
> to environment but they ALL support it. You MUST do this. In this
> case,the
> first time you execute this statement you need to parse this statement
> (HARD
> PARSING) and once the execution plan gets into the SHARED POOL
> (V$libraryCache) the other users can use this to great effect. They
would
> not reparse this statement again and again and but does do the soft
> parsing
> of it. So One Parse may lead to MANY executions instead of 1 Parsing <->
1
> Execution.
>
> At least 90% of your database execution time is spent PARSING and
> OPTIMIZING
> that update -- 10% is spent actually DOING it. If you use bind
variables
> --
> very little time will be spent parsing (you can get that statement to
> execute in 1/10 of the time). Not only that -- but the concurrency and
> scalability of your database will go WAY up.
>
> This is the root cause of your issues, this must be fixed -- no
questions
> about it.
>
> Vikas Khanna
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Vikas Khanna
> INET: vkhanna_at_quark.co.in
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Nicoll, Iain \(Calanais\)
> INET: iain.nicoll_at_calanais.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Dunn
  INET: john.dunn_at_sefas.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Philip Douglass
  INET: philipd_at_sirs.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).