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:[EMAIL PROTECTED]]
> 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: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Reply via email to