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).
