O'Reilly and PL/SQl Programming almost ALWAYS means the author is Steven Feuerstein
--- [EMAIL PROTECTED] 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 <[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). > === 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: [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).
