Hi Nicoll,
The importance of bind variables, to use in OLTP application is such that if
we use, we survive else one or the other day we would feel uncomfortable
over the performance issues and would repent over the scalability of the
application.
Yes, if we use bind variables, the parser does not know how best to execute
the statement but basis on rough estimates (50% values are such that the
table is holding on basis the WHERE Clause),it generates many execution
plans and holds the one which has lowest cost in the V$library cache because
at that time the optimizer is not sure what values to bind.
But in the case of DSS applications yes I do agree that these bind variables
are problematic as the data is in abundance and we have to use the data
skewness and its other credentials for the optimizer best to use as we are
playing with abundance of data.
Thanks
Vikas Khanna
-----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 <[EMAIL PROTECTED]>
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:[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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
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: [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: 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).
