The third edition of Steven Feuerstein's PL/SQL book will be released this month. So if your considering a purchase then be patient for a few more weeks.
http://www.amazon.com/exec/obidos/tg/detail/-/0596003811/qid=1031937397/sr=1-2/ref=sr_1_2/102-7450165-2433766?v=glance&s=books
-----Original Message-----
Sent: Friday, September 06, 2002 2:40 PM
To: Multiple recipients of list ORACLE-L
O'Reilly and PL/SQl Programming almost ALWAYS means the author is
Steven Feuerstein
- dgoulet_at_vicr.com wrote:
> 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).
>
=== message truncated ===
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: wisernet100_at_yahoo.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: Grabowy, Chris
INET: cgrabowy_at_fcg.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 Fri Sep 13 2002 - 13:23:25 CDT
