| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: bind variables
If you want to show anyone the different in seed between using bind variables and not using them Tom Kyte's Expert one on one got a nice example, it is in chap 2 or 3 I think.
George
You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit!
-----Original Message-----
Sent: 06 September 2002 19:59 PM
To: Multiple recipients of list ORACLE-L
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). This message contains information intended solely for the addressee, which is confidential or private in nature and subject to legal privilege. If you are not the intended recipient, you may not peruse, use, disseminate, distribute or copy this message or any file attached to this message. Any such unauthorised use is prohibited and may be unlawful. If you have received this message in error, please notify the sender immediately by e-mail, facsimile or telephone and thereafter delete the original message from your machine. Furthermore, the information contained in this message, and any attachments thereto, is for information purposes only and may contain the personal views and opinions of the author, which are not necessarily the views and opinions of Dimension Data (South Africa) (Proprietary) Limited or is subsidiaries and associated companies ("Dimension Data"). Dimension Data therefore does not accept liability for any claims, loss or damages of whatsoever nature, arising as a result of the reliance on such information by anyone. Whilst all reasonable steps are taken to ensure the accuracy and integrity of information transmitted electronically and to preserve the confidentiality thereof, Dimension Data accepts no liability or responsibility whatsoever if information or data is, for whatsoever reason, incorrect, corrupted or does not reach its intended destination. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: George.Leonard_at_za.didata.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).Received on Mon Sep 09 2002 - 01:13:18 CDT
 |
 |