Re:bind variables

John,

    You would have to ask while I've got the book at home. But it's an Orielly book on PL/SQL Programming. Sorry off the top of my head I can't remember the author or title.

Dick Goulet

____________________Reply Separator____________________
Author: John Dunn <john.dunn_at_sefas.co.uk>
Date:       9/6/2002 7: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: 
  INET: dgoulet_at_vicr.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).