RE: bind variables in VB using OO4O
Hi John , i have mailed a doc . have a look at it . Regards, Prem. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Does anyone have examples of how to use bind variables in VB when using OO4O? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Bind variables
Thanks. I'm running 8i and it worked fine with that statement. Rivaldi -Original Message- Sent: Monday, February 10, 2003 4:19 PM To: Multiple recipients of list ORACLE-L Hi. You would need to wrap the ALTER SESSION into an Execute Immediate statement: EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name content forever, level 4'''; Regards, Mark. PS: Single quotes become double quotes since the whole thing is wrapped in another set of quotes, and the semi-colon is removed from the statement being wrapped up. Bahar, Rivaldi (BBASSI-CHQ) To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] rivaldi.bahar@b cc: bassi.com Subject: RE: Bind variables Sent by: [EMAIL PROTECTED] 11/02/2003 06:53 Please respond to ORACLE-L It worked well from SQLplus but not from procedure. Did I miss something ? SQLcreate or replace procedure testtrace 2 as 3vempno varchar2(3); 4vempno1 varchar2(3); 5begin 6 ALTER Session SET EVENTS '10046 trace name context forever, level 4'; 7 select empno into vempno from test where rownum 2; 8 select empno into vempno1 from test1 where rownum 2; 9 end; 10 / Warning: Procedure created with compilation errors. SQL show error Errors for PROCEDURE TESTTRACE: LINE/COL ERROR - 6/2 PLS-00103: Encountered the symbol ALTER when expecting one of the following: begin declare exit for goto if loop mod null pragma raise return select update while an identifier a double-quoted delimited-identifier a bind variable close current delete fetch lock insert open rollback savepoint set sql execute commit forall a single-quoted SQL string -Original Message- Sent: Monday, February 10, 2003 12:59 PM To: Multiple recipients of list ORACLE-L In order to see the actual value of bind variables, you need to use event 10046 with level 4. from your procedure do: alter session set events '10046 trace name context forever, level 4' Richard -Original Message- Sent: Monday, February 10, 2003 12:29 PM To: Multiple recipients of list ORACLE-L Hi Listers, I set the sql trace true in a procedure, let's say the procedure testproc(var1 IN number, var2 IN varchar2). Is there a way to know the value passed to that procedure (the value of var1 and var2) in the trace file ? And also the value of bind variable of each SQL statement in that procedure ? The procedure called through application, not from SQLplus. Thanks. This e-mail, including any attachments, may include confidential and/or proprietary information, and is intended for use only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient, or his or her authorized agent, the reader is hereby notified that any dissemination, distribution, or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bahar, Rivaldi (BBASSI-CHQ) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
RE: Bind variables
TMTOWTDI ... It's everywhere. All these choices. Why do they have to make life so complicated? I'm just curious: How do you turn if off? level(0)? -Original Message- Hi, in 8i etc, you can use dbms_system.set_ev(sid,serial#,10046,level(1-12),'') too. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Bind variables
alter session set events '10046 trace name context off' or exec sys.dbms_system.set_ev(sid, serial#, 10046, 0, '') Richard Ji -Original Message- Sent: Tuesday, February 11, 2003 9:29 AM To: Multiple recipients of list ORACLE-L TMTOWTDI ... It's everywhere. All these choices. Why do they have to make life so complicated? I'm just curious: How do you turn if off? level(0)? -Original Message- Hi, in 8i etc, you can use dbms_system.set_ev(sid,serial#,10046,level(1-12),'') too. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Bind variables
In order to see the actual value of bind variables, you need to use event 10046 with level 4. from your procedure do: alter session set events '10046 trace name context forever, level 4' Richard -Original Message- Sent: Monday, February 10, 2003 12:29 PM To: Multiple recipients of list ORACLE-L Hi Listers, I set the sql trace true in a procedure, let's say the procedure testproc(var1 IN number, var2 IN varchar2). Is there a way to know the value passed to that procedure (the value of var1 and var2) in the trace file ? And also the value of bind variable of each SQL statement in that procedure ? The procedure called through application, not from SQLplus. Thanks. This e-mail, including any attachments, may include confidential and/or proprietary information, and is intended for use only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient, or his or her authorized agent, the reader is hereby notified that any dissemination, distribution, or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bahar, Rivaldi (BBASSI-CHQ) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Bind variables
It worked well from SQLplus but not from procedure. Did I miss something ? SQLcreate or replace procedure testtrace 2 as 3vempno varchar2(3); 4vempno1 varchar2(3); 5begin 6 ALTER Session SET EVENTS '10046 trace name context forever, level 4'; 7 select empno into vempno from test where rownum 2; 8 select empno into vempno1 from test1 where rownum 2; 9 end; 10 / Warning: Procedure created with compilation errors. SQL show error Errors for PROCEDURE TESTTRACE: LINE/COL ERROR - 6/2 PLS-00103: Encountered the symbol ALTER when expecting one of the following: begin declare exit for goto if loop mod null pragma raise return select update while an identifier a double-quoted delimited-identifier a bind variable close current delete fetch lock insert open rollback savepoint set sql execute commit forall a single-quoted SQL string -Original Message- Sent: Monday, February 10, 2003 12:59 PM To: Multiple recipients of list ORACLE-L In order to see the actual value of bind variables, you need to use event 10046 with level 4. from your procedure do: alter session set events '10046 trace name context forever, level 4' Richard -Original Message- Sent: Monday, February 10, 2003 12:29 PM To: Multiple recipients of list ORACLE-L Hi Listers, I set the sql trace true in a procedure, let's say the procedure testproc(var1 IN number, var2 IN varchar2). Is there a way to know the value passed to that procedure (the value of var1 and var2) in the trace file ? And also the value of bind variable of each SQL statement in that procedure ? The procedure called through application, not from SQLplus. Thanks. This e-mail, including any attachments, may include confidential and/or proprietary information, and is intended for use only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient, or his or her authorized agent, the reader is hereby notified that any dissemination, distribution, or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bahar, Rivaldi (BBASSI-CHQ) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 e-mail, including any attachments, may include confidential and/or proprietary information, and is intended for use only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient, or his or her authorized agent, the reader is hereby notified that any dissemination, distribution, or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bahar, Rivaldi (BBASSI-CHQ) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE
RE: Bind variables
You might try playing around with the DBMS_SUPPORT package which is loaded with the dbmssupp.sql script in the rdbms/admin directory. dbms_support.start_trace_in_session(SID, SERIAL#, waits=false, binds=true) dbms_support.stop_trace_in_session(SID, SERIAL#). -Original Message- It worked well from SQLplus but not from procedure. Did I miss something ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Bind variables
alter session is DDL, you should do: execute immediate 'ALTER Session SET EVENTS ''10046 trace name context forever, level 4'''; Richard -Original Message- Sent: Monday, February 10, 2003 2:54 PM To: Multiple recipients of list ORACLE-L It worked well from SQLplus but not from procedure. Did I miss something ? SQLcreate or replace procedure testtrace 2 as 3vempno varchar2(3); 4vempno1 varchar2(3); 5begin 6 ALTER Session SET EVENTS '10046 trace name context forever, level 4'; 7 select empno into vempno from test where rownum 2; 8 select empno into vempno1 from test1 where rownum 2; 9 end; 10 / Warning: Procedure created with compilation errors. SQL show error Errors for PROCEDURE TESTTRACE: LINE/COL ERROR - 6/2 PLS-00103: Encountered the symbol ALTER when expecting one of the following: begin declare exit for goto if loop mod null pragma raise return select update while an identifier a double-quoted delimited-identifier a bind variable close current delete fetch lock insert open rollback savepoint set sql execute commit forall a single-quoted SQL string -Original Message- Sent: Monday, February 10, 2003 12:59 PM To: Multiple recipients of list ORACLE-L In order to see the actual value of bind variables, you need to use event 10046 with level 4. from your procedure do: alter session set events '10046 trace name context forever, level 4' Richard -Original Message- Sent: Monday, February 10, 2003 12:29 PM To: Multiple recipients of list ORACLE-L Hi Listers, I set the sql trace true in a procedure, let's say the procedure testproc(var1 IN number, var2 IN varchar2). Is there a way to know the value passed to that procedure (the value of var1 and var2) in the trace file ? And also the value of bind variable of each SQL statement in that procedure ? The procedure called through application, not from SQLplus. Thanks. This e-mail, including any attachments, may include confidential and/or proprietary information, and is intended for use only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient, or his or her authorized agent, the reader is hereby notified that any dissemination, distribution, or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bahar, Rivaldi (BBASSI-CHQ) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 e-mail, including any attachments, may include confidential and/or proprietary information, and is intended for use only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient, or his or her authorized agent, the reader is hereby notified that any dissemination, distribution, or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bahar, Rivaldi (BBASSI-CHQ)
RE: Bind variables
Hi. You would need to wrap the ALTER SESSION into an Execute Immediate statement: EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name content forever, level 4'''; Regards, Mark. PS: Single quotes become double quotes since the whole thing is wrapped in another set of quotes, and the semi-colon is removed from the statement being wrapped up. Bahar, Rivaldi (BBASSI-CHQ) To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] rivaldi.bahar@b cc: bassi.com Subject: RE: Bind variables Sent by: [EMAIL PROTECTED] 11/02/2003 06:53 Please respond to ORACLE-L It worked well from SQLplus but not from procedure. Did I miss something ? SQLcreate or replace procedure testtrace 2 as 3vempno varchar2(3); 4vempno1 varchar2(3); 5begin 6 ALTER Session SET EVENTS '10046 trace name context forever, level 4'; 7 select empno into vempno from test where rownum 2; 8 select empno into vempno1 from test1 where rownum 2; 9 end; 10 / Warning: Procedure created with compilation errors. SQL show error Errors for PROCEDURE TESTTRACE: LINE/COL ERROR - 6/2 PLS-00103: Encountered the symbol ALTER when expecting one of the following: begin declare exit for goto if loop mod null pragma raise return select update while an identifier a double-quoted delimited-identifier a bind variable close current delete fetch lock insert open rollback savepoint set sql execute commit forall a single-quoted SQL string -Original Message- Sent: Monday, February 10, 2003 12:59 PM To: Multiple recipients of list ORACLE-L In order to see the actual value of bind variables, you need to use event 10046 with level 4. from your procedure do: alter session set events '10046 trace name context forever, level 4' Richard -Original Message- Sent: Monday, February 10, 2003 12:29 PM To: Multiple recipients of list ORACLE-L Hi Listers, I set the sql trace true in a procedure, let's say the procedure testproc(var1 IN number, var2 IN varchar2). Is there a way to know the value passed to that procedure (the value of var1 and var2) in the trace file ? And also the value of bind variable of each SQL statement in that procedure ? The procedure called through application, not from SQLplus. Thanks. This e-mail, including any attachments, may include confidential and/or proprietary information, and is intended for use only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient, or his or her authorized agent, the reader is hereby notified that any dissemination, distribution, or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bahar, Rivaldi (BBASSI-CHQ) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E
RE: Bind variables
Try exec sql alter session set events . Rajesh -Original Message- Sent: Monday, February 10, 2003 11:54 AM To: Multiple recipients of list ORACLE-L It worked well from SQLplus but not from procedure. Did I miss something ? SQLcreate or replace procedure testtrace 2 as 3vempno varchar2(3); 4vempno1 varchar2(3); 5begin 6 ALTER Session SET EVENTS '10046 trace name context forever, level 4'; 7 select empno into vempno from test where rownum 2; 8 select empno into vempno1 from test1 where rownum 2; 9 end; 10 / Warning: Procedure created with compilation errors. SQL show error Errors for PROCEDURE TESTTRACE: LINE/COL ERROR - 6/2 PLS-00103: Encountered the symbol ALTER when expecting one of the following: begin declare exit for goto if loop mod null pragma raise return select update while an identifier a double-quoted delimited-identifier a bind variable close current delete fetch lock insert open rollback savepoint set sql execute commit forall a single-quoted SQL string -Original Message- Sent: Monday, February 10, 2003 12:59 PM To: Multiple recipients of list ORACLE-L In order to see the actual value of bind variables, you need to use event 10046 with level 4. from your procedure do: alter session set events '10046 trace name context forever, level 4' Richard -Original Message- Sent: Monday, February 10, 2003 12:29 PM To: Multiple recipients of list ORACLE-L Hi Listers, I set the sql trace true in a procedure, let's say the procedure testproc(var1 IN number, var2 IN varchar2). Is there a way to know the value passed to that procedure (the value of var1 and var2) in the trace file ? And also the value of bind variable of each SQL statement in that procedure ? The procedure called through application, not from SQLplus. Thanks. This e-mail, including any attachments, may include confidential and/or proprietary information, and is intended for use only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient, or his or her authorized agent, the reader is hereby notified that any dissemination, distribution, or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bahar, Rivaldi (BBASSI-CHQ) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 e-mail, including any attachments, may include confidential and/or proprietary information, and is intended for use only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient, or his or her authorized agent, the reader is hereby notified that any dissemination, distribution, or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bahar, Rivaldi (BBASSI-CHQ) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
Re: Bind variables
Stephen, What version of oracle are you talking about? I didn't find dbms_support in 9.2. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, February 10, 2003 3:43 PM You might try playing around with the DBMS_SUPPORT package which is loaded with the dbmssupp.sql script in the rdbms/admin directory. dbms_support.start_trace_in_session(SID, SERIAL#, waits=false, binds=true) dbms_support.stop_trace_in_session(SID, SERIAL#). -Original Message- It worked well from SQLplus but not from procedure. Did I miss something ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Bind variables
You have to load it by running the dbmssupp.sql script in the ?/rdbms/admin directory. -Original Message- From: Igor Neyman [mailto:[EMAIL PROTECTED]] Sent: Monday, February 10, 2003 3:44 PM To: Multiple recipients of list ORACLE-L Subject: Re: Bind variables Stephen, What version of oracle are you talking about? I didn't find dbms_support in 9.2. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, February 10, 2003 3:43 PM You might try playing around with the DBMS_SUPPORT package which is loaded with the dbmssupp.sql script in the rdbms/admin directory. dbms_support.start_trace_in_session(SID, SERIAL#, waits=false, binds=true) dbms_support.stop_trace_in_session(SID, SERIAL#). -Original Message- It worked well from SQLplus but not from procedure. Did I miss something ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Bind variables
Hi Bahar, If you run it on form you can find out the values by debugging that 'procedure testproc'. Rgrds, Sony -Original Message- From: Bahar, Rivaldi (BBASSI-CHQ) [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, February 11, 2003 12:29 AM To: Multiple recipients of list ORACLE-L Subject: Bind variables Hi Listers, I set the sql trace true in a procedure, let's say the procedure testproc(var1 IN number, var2 IN varchar2). Is there a way to know the value passed to that procedure (the value of var1 and var2) in the trace file ? And also the value of bind variable of each SQL statement in that procedure ? The procedure called through application, not from SQLplus. Thanks. ** ** This e-mail, including any attachments, may include confidential and/or proprietary information, and is intended for use only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient, or his or her authorized agent, the reader is hereby notified that any dissemination, distribution, or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. ** ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bahar, Rivaldi (BBASSI-CHQ) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Sony kristanto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: RE: Bind variables
Stephen Lee, Hi, in 8i etc, you can use dbms_system.set_ev(sid,serial#,10046,level(1-12),'') too. Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(China Oracle User Group) === 2003-02-10 14:09:00 ,you wrote£º=== You have to load it by running the dbmssupp.sql script in the ?/rdbms/admin directory. -Original Message- From: Igor Neyman [mailto:[EMAIL PROTECTED]] Sent: Monday, February 10, 2003 3:44 PM To: Multiple recipients of list ORACLE-L Subject: Re: Bind variables Stephen, What version of oracle are you talking about? I didn't find dbms_support in 9.2. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, February 10, 2003 3:43 PM You might try playing around with the DBMS_SUPPORT package which is loaded with the dbmssupp.sql script in the rdbms/admin directory. dbms_support.start_trace_in_session(SID, SERIAL#, waits=false, binds=true) dbms_support.stop_trace_in_session(SID, SERIAL#). -Original Message- It worked well from SQLplus but not from procedure. Did I miss something ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: chao_ping INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: bind variables
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 ANDUSER_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
RE: bind variables
Yes. -Original Message- Sent: Friday, September 06, 2002 4:28 PM To: Multiple recipients of list ORACLE-L Kevin, Are you saying then, that by default, any static statement that is executed within PL/SQL will not have be re-parsed eg sp_proc(var in varchar2) as begin select last_name from emp where last_name = var; end; If that's the case, I wont have to change much code. mkb --- Toepke, Kevin M [EMAIL PROTECTED] wrote: Actually its easy. Any variable declared in PL/SQL and referenced in a non-dynamic SQL statement is a bind variable. In the following example (#1), some_var is an output bind-variable and other_var is a input bind variable. PL/SQL does manipulation on the statement and will send something like the following (#2) to the database #1 DECLARE some_var NUMBER(1); other_var NUMBER(1) BEGIN SELECT 1 INTO some_var FROM my_table WHERE my_column = other_var; END; #2 SELECT 1 FROM MY_TABLE WHERE MY_COLUMN = :1 Kevin -Original Message- Sent: Friday, September 06, 2002 1: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 ANDUSER_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
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 George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za 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 [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 ANDUSER_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
RE: bind variables
Actually its easy. Any variable declared in PL/SQL and referenced in a non-dynamic SQL statement is a bind variable. In the following example (#1), some_var is an output bind-variable and other_var is a input bind variable. PL/SQL does manipulation on the statement and will send something like the following (#2) to the database #1 DECLARE some_var NUMBER(1); other_var NUMBER(1) BEGIN SELECT 1 INTO some_var FROM my_table WHERE my_column = other_var; END; #2 SELECT 1 FROM MY_TABLE WHERE MY_COLUMN = :1 Kevin -Original Message- Sent: Friday, September 06, 2002 1: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 ANDUSER_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
Re: bind variables
You probably already are. You have to go to a lot of trouble to avoid using bind variables in PL/SQL. (I'm too lazy^H^H^H^H busy to find the doc link right now.) -- Philip - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 06, 2002 11: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 ANDUSER_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
RE: bind variables
Kevin, Are you saying then, that by default, any static statement that is executed within PL/SQL will not have be re-parsed eg sp_proc(var in varchar2) as begin select last_name from emp where last_name = var; end; If that's the case, I wont have to change much code. mkb --- Toepke, Kevin M [EMAIL PROTECTED] wrote: Actually its easy. Any variable declared in PL/SQL and referenced in a non-dynamic SQL statement is a bind variable. In the following example (#1), some_var is an output bind-variable and other_var is a input bind variable. PL/SQL does manipulation on the statement and will send something like the following (#2) to the database #1 DECLARE some_var NUMBER(1); other_var NUMBER(1) BEGIN SELECT 1 INTO some_var FROM my_table WHERE my_column = other_var; END; #2 SELECT 1 FROM MY_TABLE WHERE MY_COLUMN = :1 Kevin -Original Message- Sent: Friday, September 06, 2002 1: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 ANDUSER_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
RE: Bind Variables in VB
Someone pointed me towards these articles when I asked this question a week or two ago: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/ mdobjparameter.asp http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/ mdmscadoobjmod.asp -Original Message- Sent: Wednesday, July 24, 2002 8:30 AM To: Multiple recipients of list ORACLE-L All, Can anyone provide examples of how to issue an SQL statement in VB using Bind Variables (using DAO or ADO). Many thanks for any responses. Dave Leach Technical Services Claybrook Computing Internal ext * 4992 Phone * 01293 604992 Fax 01293 604029 E-Mail * [EMAIL PROTECTED] The above information is confidential to the addressee and may be privileged. Unauthorised access and use is prohibited. Internet communications are not secure and therefore this Company does not accept legal responsibility for the contents of this message. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. Claybrook Computing Limited is a subsidiary of Claybrook Computing (Holdings) Limited Registered Office: Abbey House. 282 Farnborough Road, Farnborough, Hampshire GU14 7NJ Registered in England and Wales No 1287205 A Hogg Robinson plc company -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dave Leach 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: Miller, Jay 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).
RE: Bind Variables
What percentage are you estimating? Have you played with that? -Original Message- Sent: Saturday, October 20, 2001 10:55 AM To: Multiple recipients of list ORACLE-L I have always used the CBO and statistics are generated fairly regularly. I use the analyze table estimate statistics which should not generate histogram information. I have even used the INDEX, FIRST_ROWS, and ALL_ROWS hints. None made a difference. I regenerated the statistics on all tables of a particular slow query with bind variables and it didn't make a difference either. Our platform is red hat linux 6.2 and oracle 8.1.7. [EMAIL PROTECTED] 10/19/01 18:58 PM I have heard of it. Its a concern. However, with hints you can solve some of your problems. And then you could use Stored Outlines and get it stable. And then life will be groovy. -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, October 19, 2001 3:45 PM To: Multiple recipients of list ORACLE-L Danny, It sounds like when you ...moved to bind variables... you did two things: 1. Switched to using CBO 2. Changed your application to take advantage of the bind variables. I have never heard of switching to bind variables making things slower (and can not imagine when it would make it so -- the CBO vs. RBO is irrelevant in this case). Thus the question: did you collect the statistics on all your application tables prior to switching to CBO? If you did not have any statistics collected or had some tables that did not have statistics collected then Oracle may had been choosing wrong execution path. Thanks, Val Gamerman. [EMAIL PROTECTED] on 10/19/2001 03:07:56 PM Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Val Gamerman/Victoria Financial) Danny, Were you using histograms? Histograms do not work on bind variables, they only work on literals. I'm sure others will have more to say on the topic but that is one tidbit that I do have experience with on bind variables. Cherie Machler Oracle DBA Gelco Information Network Danny Hughes[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] DHUGHES cc: Sent by: [EMAIL PROTECTED]Subject: Bind Variables 10/19/01 01:30 PM Please respond to ORACLE-L --CIVUFTDKZVQDOGJWYCGU Content-Type: multipart/alternative; boundary= HALPYZPZYFFYCGAKLKOY --HALPYZPZYFFYCGAKLKOY Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable I have a question about using bind variables. It is my understanding that = bind variables are to be used to keep the same SQL from being reparsed and = keeping multiple copies in the SGA, thus *speeding* things up. I also = understand that by using bind variables that the CBO will not be able to = optimally use the statistics to determine the most effective path to = retrieve data. Some documentation states to use hints when you are using = bind variables to tell the CBO which path to take. Other documentation = states that bind variables don't like the CBO and to use the RBO. After we looked at the library cache and saw thousands of copies of the = same sql, and hundreds of different sql, we decided to look into converting = certain web pages to bind variables. After we converted to using bind = variables, the pages that once took 5 seconds to load all of the sudden = took 2 minutes to load. The db server was pegged at 100% from 8-5 every = day while this application was being used. We have converted back to = literals and the pages are loading quickly again. While on bind variables, = we used every possible hint except RULE and had no luck. We didn't want = to resort to using the RULE hint because oracle claims not to use the RBO = (because of no further enhancements and possibly phasing it out), and to = start using the CBO for everything. There are so many confusing documents regarding the use of bind variables. = I wanted to see what the oracle community is doing in regards to using = bind variables and what their experiences are. TIA Danny Hughes DBA Knobias.com [EMAIL PROTECTED] www.knobias.com --HALPYZPZYFFYCGAKLKOY Content-Type: multipart/related; boundary=KMQGUMGTJRAMPYVFBAEF --KMQGUMGTJRAMPYVFBAEF Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable I have a question about using bind variables. It is my understanding that = bind variables are to be used to keep the same SQL from being reparsed and = keeping multiple copies in the SGA, thus *speeding* things up. I also = understand that by using bind variables that the CBO will not be able to = optimally use the statistics to determine the most effective path to = retrieve data. Some documentation states to use hints when you
Re: Bind Variables
Danny, I'll bet my morning cheese danish that when you changed the SQL from using literals to bind variables, the execution plan changed. Just pull each SQL statement and run it in SQL*Plus, one version of it with literals and the other with bind variables. The difference in your web page coming up in 2 seconds with literals and 5 minutes with bind variables is huge. Testing like this in SQL*Plus should reveal which SQL statement is taking longer with bind variables. In SQL*Plus use SET AUTOTRACE ON so you can see the execution plans. Once you find the problem you can use hints to force the bind variable version to use the same fast plan that was used by the literal version. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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).
RE: Bind Variables
Danny, Have you tried generating explain plans with and without bind variables and comparing them? That should help you identify if it's truly related to bind variables. What are the wait events for the queries that are running slower? HTH, -- Anita --- Danny Hughes [EMAIL PROTECTED] wrote: I have always used the CBO and statistics are generated fairly regularly. I use the analyze table estimate statistics which should not generate histogram information. I have even used the INDEX, FIRST_ROWS, and ALL_ROWS hints. None made a difference. I regenerated the statistics on all tables of a particular slow query with bind variables and it didn't make a difference either. Our platform is red hat linux 6.2 and oracle 8.1.7. [EMAIL PROTECTED] 10/19/01 18:58 PM I have heard of it. Its a concern. However, with hints you can solve some of your problems. And then you could use Stored Outlines and get it stable. And then life will be groovy. -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, October 19, 2001 3:45 PM To: Multiple recipients of list ORACLE-L Danny, It sounds like when you ...moved to bind variables... you did two things: 1. Switched to using CBO 2. Changed your application to take advantage of the bind variables. I have never heard of switching to bind variables making things slower (and can not imagine when it would make it so -- the CBO vs. RBO is irrelevant in this case). Thus the question: did you collect the statistics on all your application tables prior to switching to CBO? If you did not have any statistics collected or had some tables that did not have statistics collected then Oracle may had been choosing wrong execution path. Thanks, Val Gamerman. [EMAIL PROTECTED] on 10/19/2001 03:07:56 PM Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Val Gamerman/Victoria Financial) Danny, Were you using histograms? Histograms do not work on bind variables, they only work on literals. I'm sure others will have more to say on the topic but that is one tidbit that I do have experience with on bind variables. Cherie Machler Oracle DBA Gelco Information Network Danny Hughes[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] DHUGHES cc: Sent by: [EMAIL PROTECTED] Subject: Bind Variables 10/19/01 01:30 PM Please respond to ORACLE-L --CIVUFTDKZVQDOGJWYCGU Content-Type: multipart/alternative; boundary= HALPYZPZYFFYCGAKLKOY --HALPYZPZYFFYCGAKLKOY Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable I have a question about using bind variables. It is my understanding that = bind variables are to be used to keep the same SQL from being reparsed and = keeping multiple copies in the SGA, thus *speeding* things up. I also = understand that by using bind variables that the CBO will not be able to = optimally use the statistics to determine the most effective path to = retrieve data. Some documentation states to use hints when you are using = bind variables to tell the CBO which path to take. Other documentation = states that bind variables don't like the CBO and to use the RBO. After we looked at the library cache and saw thousands of copies of the = same sql, and hundreds of different sql, we decided to look into converting = certain web pages to bind variables. After we converted to using bind = variables, the pages that once took 5 seconds to load all of the sudden = took 2 minutes to load. The db server was pegged at 100% from 8-5 every = day while this application was being used. We have converted back to = literals and the pages are loading quickly again. While on bind variables, = we used every possible hint except RULE and had no luck. We didn't want = to resort to using the RULE hint because oracle claims not to use the RBO = (because of no further enhancements and possibly phasing it out), and to = start using the CBO for everything. There are so many confusing documents regarding the use of bind variables. = I wanted to see what the oracle community is doing in regards to using = bind variables and what their experiences are. TIA Danny Hughes DBA Knobias.com [EMAIL PROTECTED] www.knobias.com --HALPYZPZYFFYCGAKLKOY Content-Type: multipart/related; boundary=KMQGUMGTJRAMPYVFBAEF --KMQGUMGTJRAMPYVFBAEF Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable I have a question about using bind variables. It is my understanding that = bind variables are to be used to keep the same SQL from being
RE: Bind variables v. cursor_sharing = FORCE
Hi Greg and list, I'm not convinced of John's conclusion that 'cursor_sharing' = FORCE outperforms bind variables. Some months ago I tried to reproduce John's results and was not able to, despite trying several different variations on the tests. Tom Kyte claims that 'cursor_sharing' is about 10% more expensive. My results were between 10% and 30%. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Saturday, 20 October 2001 7:21 To: Multiple recipients of list ORACLE-L An excerpt from PIRANHAS IN THE POOL, SQL PERFORMANCE KILLERS Investigating the effects of literal SQL on Oracle performance John Beresniewicz Precise Software Solutio -- Effect of CURSOR_SHARING A primary purpose of the 8.1.6 testing was to assess the impact the new CURSOR_SHARING system parameter and its potential for helping DBAs manage ill-behaved applications with high parse rates of literal SQL. Comparing Test 1 to Test 3 and also Test 0 to Test 4 serves this goal. Comparing these Test results, CURSOR_SHARING exhibits advantages similar to those obtained using bind variables in Test 2: -- Reduced library cache impact. -- Negligible shared pool activity. -- Reduced CPU demands. In fact, Test 3 produced the best elapsed time of all tests in spite of the fact that all the SQL was literal. Thus, it is clear that CURSOR_SHARING can be used to greatly enhance performance of applications that produce high volume literal SQL and thus is a great advantage for the DBA saddled with such applications. CURSOR_SHARING vs. Bind Variables Comparing Test 2 and Test 3 reveals that CURSOR_SHARING = FORCE showed significantly better performance than bind variables in both elapsed time and reduced library cache latching impact. This surprising result deserves further investigation to produce an adequate explanation. Library cache latch impact was significantly reduced as well as shared pool pins and releases. Parsing CPU time increased some but overall CPU was reduced. Perhaps the additional parsing involved in forced cursor sharing also enables increased sharing of shared pool memory heaps. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams 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).
RE: Bind Variables
I have always used the CBO and statistics are generated fairly regularly. I use the analyze table estimate statistics which should not generate histogram information. I have even used the INDEX, FIRST_ROWS, and ALL_ROWS hints. None made a difference. I regenerated the statistics on all tables of a particular slow query with bind variables and it didn't make a difference either. Our platform is red hat linux 6.2 and oracle 8.1.7. [EMAIL PROTECTED] 10/19/01 18:58 PM I have heard of it. Its a concern. However, with hints you can solve some of your problems. And then you could use Stored Outlines and get it stable. And then life will be groovy. -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, October 19, 2001 3:45 PM To: Multiple recipients of list ORACLE-L Danny, It sounds like when you ...moved to bind variables... you did two things: 1. Switched to using CBO 2. Changed your application to take advantage of the bind variables. I have never heard of switching to bind variables making things slower (and can not imagine when it would make it so -- the CBO vs. RBO is irrelevant in this case). Thus the question: did you collect the statistics on all your application tables prior to switching to CBO? If you did not have any statistics collected or had some tables that did not have statistics collected then Oracle may had been choosing wrong execution path. Thanks, Val Gamerman. [EMAIL PROTECTED] on 10/19/2001 03:07:56 PM Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Val Gamerman/Victoria Financial) Danny, Were you using histograms? Histograms do not work on bind variables, they only work on literals. I'm sure others will have more to say on the topic but that is one tidbit that I do have experience with on bind variables. Cherie Machler Oracle DBA Gelco Information Network Danny Hughes[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] DHUGHES cc: Sent by: [EMAIL PROTECTED]Subject: Bind Variables 10/19/01 01:30 PM Please respond to ORACLE-L --CIVUFTDKZVQDOGJWYCGU Content-Type: multipart/alternative; boundary= HALPYZPZYFFYCGAKLKOY --HALPYZPZYFFYCGAKLKOY Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable I have a question about using bind variables. It is my understanding that = bind variables are to be used to keep the same SQL from being reparsed and = keeping multiple copies in the SGA, thus *speeding* things up. I also = understand that by using bind variables that the CBO will not be able to = optimally use the statistics to determine the most effective path to = retrieve data. Some documentation states to use hints when you are using = bind variables to tell the CBO which path to take. Other documentation = states that bind variables don't like the CBO and to use the RBO. After we looked at the library cache and saw thousands of copies of the = same sql, and hundreds of different sql, we decided to look into converting = certain web pages to bind variables. After we converted to using bind = variables, the pages that once took 5 seconds to load all of the sudden = took 2 minutes to load. The db server was pegged at 100% from 8-5 every = day while this application was being used. We have converted back to = literals and the pages are loading quickly again. While on bind variables, = we used every possible hint except RULE and had no luck. We didn't want = to resort to using the RULE hint because oracle claims not to use the RBO = (because of no further enhancements and possibly phasing it out), and to = start using the CBO for everything. There are so many confusing documents regarding the use of bind variables. = I wanted to see what the oracle community is doing in regards to using = bind variables and what their experiences are. TIA Danny Hughes DBA Knobias.com [EMAIL PROTECTED] www.knobias.com --HALPYZPZYFFYCGAKLKOY Content-Type: multipart/related; boundary=KMQGUMGTJRAMPYVFBAEF --KMQGUMGTJRAMPYVFBAEF Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable I have a question about using bind variables. It is my understanding that = bind variables are to be used to keep the same SQL from being reparsed and = keeping multiple copies in the SGA, thus *speeding* things up. I also = understand that by using bind variables that the CBO will not be able to = optimally use the statistics to determine the most effective path to = retrieve data. Some documentation states to use hints when you are using = bind variables to tell the CBO which path to take. Other documentation = states that bind variables don't like the CBO and to use the RBO. br brAfter we looked
Re: Bind Variables
Danny, Were you using histograms? Histograms do not work on bind variables, they only work on literals. I'm sure others will have more to say on the topic but that is one tidbit that I do have experience with on bind variables. Cherie Machler Oracle DBA Gelco Information Network Danny Hughes[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] DHUGHES cc: Sent by: [EMAIL PROTECTED]Subject: Bind Variables 10/19/01 01:30 PM Please respond to ORACLE-L --CIVUFTDKZVQDOGJWYCGU Content-Type: multipart/alternative; boundary= HALPYZPZYFFYCGAKLKOY --HALPYZPZYFFYCGAKLKOY Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable I have a question about using bind variables. It is my understanding that = bind variables are to be used to keep the same SQL from being reparsed and = keeping multiple copies in the SGA, thus *speeding* things up. I also = understand that by using bind variables that the CBO will not be able to = optimally use the statistics to determine the most effective path to = retrieve data. Some documentation states to use hints when you are using = bind variables to tell the CBO which path to take. Other documentation = states that bind variables don't like the CBO and to use the RBO. After we looked at the library cache and saw thousands of copies of the = same sql, and hundreds of different sql, we decided to look into converting = certain web pages to bind variables. After we converted to using bind = variables, the pages that once took 5 seconds to load all of the sudden = took 2 minutes to load. The db server was pegged at 100% from 8-5 every = day while this application was being used. We have converted back to = literals and the pages are loading quickly again. While on bind variables, = we used every possible hint except RULE and had no luck. We didn't want = to resort to using the RULE hint because oracle claims not to use the RBO = (because of no further enhancements and possibly phasing it out), and to = start using the CBO for everything. There are so many confusing documents regarding the use of bind variables. = I wanted to see what the oracle community is doing in regards to using = bind variables and what their experiences are. TIA Danny Hughes DBA Knobias.com [EMAIL PROTECTED] www.knobias.com --HALPYZPZYFFYCGAKLKOY Content-Type: multipart/related; boundary=KMQGUMGTJRAMPYVFBAEF --KMQGUMGTJRAMPYVFBAEF Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable I have a question about using bind variables. It is my understanding that = bind variables are to be used to keep the same SQL from being reparsed and = keeping multiple copies in the SGA, thus *speeding* things up. I also = understand that by using bind variables that the CBO will not be able to = optimally use the statistics to determine the most effective path to = retrieve data. Some documentation states to use hints when you are using = bind variables to tell the CBO which path to take. Other documentation = states that bind variables don't like the CBO and to use the RBO. br brAfter we looked at the library cache and saw thousands of copies of = the same sql, and hundreds of different sql, we decided to look into = converting certain web pages to bind variables. After we converted to = using bind variables, the pages that once took lt; 5 seconds to load all = of the sudden took gt; 2 minutes to load. The db server was pegged at = 100% from 8-5 every day while this application was being used. We have = converted back to literals and the pages are loading quickly again. While = on bind variables, we used every possible hint except RULE and had no = luck. We didn't want to resort to
Re: Bind Variables
Danny, It sounds like when you ...moved to bind variables... you did two things: 1. Switched to using CBO 2. Changed your application to take advantage of the bind variables. I have never heard of switching to bind variables making things slower (and can not imagine when it would make it so -- the CBO vs. RBO is irrelevant in this case). Thus the question: did you collect the statistics on all your application tables prior to switching to CBO? If you did not have any statistics collected or had some tables that did not have statistics collected then Oracle may had been choosing wrong execution path. Thanks, Val Gamerman. [EMAIL PROTECTED] on 10/19/2001 03:07:56 PM Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Val Gamerman/Victoria Financial) Danny, Were you using histograms? Histograms do not work on bind variables, they only work on literals. I'm sure others will have more to say on the topic but that is one tidbit that I do have experience with on bind variables. Cherie Machler Oracle DBA Gelco Information Network Danny Hughes[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] DHUGHES cc: Sent by: [EMAIL PROTECTED]Subject: Bind Variables 10/19/01 01:30 PM Please respond to ORACLE-L --CIVUFTDKZVQDOGJWYCGU Content-Type: multipart/alternative; boundary= HALPYZPZYFFYCGAKLKOY --HALPYZPZYFFYCGAKLKOY Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable I have a question about using bind variables. It is my understanding that = bind variables are to be used to keep the same SQL from being reparsed and = keeping multiple copies in the SGA, thus *speeding* things up. I also = understand that by using bind variables that the CBO will not be able to = optimally use the statistics to determine the most effective path to = retrieve data. Some documentation states to use hints when you are using = bind variables to tell the CBO which path to take. Other documentation = states that bind variables don't like the CBO and to use the RBO. After we looked at the library cache and saw thousands of copies of the = same sql, and hundreds of different sql, we decided to look into converting = certain web pages to bind variables. After we converted to using bind = variables, the pages that once took 5 seconds to load all of the sudden = took 2 minutes to load. The db server was pegged at 100% from 8-5 every = day while this application was being used. We have converted back to = literals and the pages are loading quickly again. While on bind variables, = we used every possible hint except RULE and had no luck. We didn't want = to resort to using the RULE hint because oracle claims not to use the RBO = (because of no further enhancements and possibly phasing it out), and to = start using the CBO for everything. There are so many confusing documents regarding the use of bind variables. = I wanted to see what the oracle community is doing in regards to using = bind variables and what their experiences are. TIA Danny Hughes DBA Knobias.com [EMAIL PROTECTED] www.knobias.com --HALPYZPZYFFYCGAKLKOY Content-Type: multipart/related; boundary=KMQGUMGTJRAMPYVFBAEF --KMQGUMGTJRAMPYVFBAEF Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable I have a question about using bind variables. It is my understanding that = bind variables are to be used to keep the same SQL from being reparsed and = keeping multiple copies in the SGA, thus *speeding* things up. I also = understand that by using bind variables that the CBO will not be able to = optimally use the statistics to determine the most effective path to = retrieve data. Some documentation states to use hints when you are using = bind variables to tell the CBO which path to take. Other documentation = states that bind variables don't like the CBO and to use the RBO. br brAfter we looked at the library cache and saw thousands of copies of = the same sql, and hundreds of different sql, we decided to look into = converting certain web pages to bind variables. After we converted to = using bind variables, the pages that once took lt; 5 seconds to load all = of the sudden took gt; 2 minutes to load. The db server was pegged at = 100% from 8-5 every day while this application was being used. We have = converted back to literals and the pages are loading quickly again. While = on bind variables, we used every possible hint except RULE and had no = luck. We didn't want to resort to using the RULE hint because oracle = claims not to use the RBO (because of no further enhancements and possibly = phasing it out), and to start using the CBO for everything. br brThere are so many
Re: Bind Variables
Danny, It sounds like when you ...moved to bind variables... you did two things: 1. Switched to using CBO 2. Changed your application to take advantage of the bind variables. I have never heard of switching to bind variables making things slower (and can not imagine when it would make it so -- the CBO vs. RBO is irrelevant in this case). Thus the question: did you collect the statistics on all your application tables prior to switching to CBO? If you did not have any statistics collected or had some tables that did not have statistics collected then Oracle may had been choosing wrong execution path. Thanks, Val Gamerman. [EMAIL PROTECTED] on 10/19/2001 03:07:56 PM Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Val Gamerman/Victoria Financial) Danny, Were you using histograms? Histograms do not work on bind variables, they only work on literals. I'm sure others will have more to say on the topic but that is one tidbit that I do have experience with on bind variables. Cherie Machler Oracle DBA Gelco Information Network Danny Hughes[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] DHUGHES cc: Sent by: [EMAIL PROTECTED]Subject: Bind Variables 10/19/01 01:30 PM Please respond to ORACLE-L --CIVUFTDKZVQDOGJWYCGU Content-Type: multipart/alternative; boundary= HALPYZPZYFFYCGAKLKOY --HALPYZPZYFFYCGAKLKOY Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable I have a question about using bind variables. It is my understanding that = bind variables are to be used to keep the same SQL from being reparsed and = keeping multiple copies in the SGA, thus *speeding* things up. I also = understand that by using bind variables that the CBO will not be able to = optimally use the statistics to determine the most effective path to = retrieve data. Some documentation states to use hints when you are using = bind variables to tell the CBO which path to take. Other documentation = states that bind variables don't like the CBO and to use the RBO. After we looked at the library cache and saw thousands of copies of the = same sql, and hundreds of different sql, we decided to look into converting = certain web pages to bind variables. After we converted to using bind = variables, the pages that once took 5 seconds to load all of the sudden = took 2 minutes to load. The db server was pegged at 100% from 8-5 every = day while this application was being used. We have converted back to = literals and the pages are loading quickly again. While on bind variables, = we used every possible hint except RULE and had no luck. We didn't want = to resort to using the RULE hint because oracle claims not to use the RBO = (because of no further enhancements and possibly phasing it out), and to = start using the CBO for everything. There are so many confusing documents regarding the use of bind variables. = I wanted to see what the oracle community is doing in regards to using = bind variables and what their experiences are. TIA Danny Hughes DBA Knobias.com [EMAIL PROTECTED] www.knobias.com --HALPYZPZYFFYCGAKLKOY Content-Type: multipart/related; boundary=KMQGUMGTJRAMPYVFBAEF --KMQGUMGTJRAMPYVFBAEF Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable I have a question about using bind variables. It is my understanding that = bind variables are to be used to keep the same SQL from being reparsed and = keeping multiple copies in the SGA, thus *speeding* things up. I also = understand that by using bind variables that the CBO will not be able to = optimally use the statistics to determine the most effective path to = retrieve data. Some documentation states to use hints when you are using = bind variables to tell the CBO which path to take. Other documentation = states that bind variables don't like the CBO and to use the RBO. br brAfter we looked at the library cache and saw thousands of copies of = the same sql, and hundreds of different sql, we decided to look into = converting certain web pages to bind variables. After we converted to = using bind variables, the pages that once took lt; 5 seconds to load all = of the sudden took gt; 2 minutes to load. The db server was pegged at = 100% from 8-5 every day while this application was being used. We have = converted back to literals and the pages are loading quickly again. While = on bind variables, we used every possible hint except RULE and had no = luck. We didn't want to resort to using the RULE hint because oracle = claims not to use the RBO (because of no further enhancements and possibly = phasing it out), and to start using the CBO for everything. br brThere are so many
Re: Bind Variables
Danny, It sounds like when you ...moved to bind variables... you did two things: 1. Switched to using CBO 2. Changed your application to take advantage of the bind variables. I have never heard of switching to bind variables making things slower (and can not imagine when it would make it so -- the CBO vs. RBO is irrelevant in this case). Thus the question: did you collect the statistics on all your application tables prior to switching to CBO? If you did not have any statistics collected or had some tables that did not have statistics collected then Oracle may had been choosing wrong execution path. Thanks, Val Gamerman. [EMAIL PROTECTED] on 10/19/2001 03:07:56 PM Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Val Gamerman/Victoria Financial) Danny, Were you using histograms? Histograms do not work on bind variables, they only work on literals. I'm sure others will have more to say on the topic but that is one tidbit that I do have experience with on bind variables. Cherie Machler Oracle DBA Gelco Information Network Danny Hughes[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] DHUGHES cc: Sent by: [EMAIL PROTECTED]Subject: Bind Variables 10/19/01 01:30 PM Please respond to ORACLE-L --CIVUFTDKZVQDOGJWYCGU Content-Type: multipart/alternative; boundary= HALPYZPZYFFYCGAKLKOY --HALPYZPZYFFYCGAKLKOY Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable I have a question about using bind variables. It is my understanding that = bind variables are to be used to keep the same SQL from being reparsed and = keeping multiple copies in the SGA, thus *speeding* things up. I also = understand that by using bind variables that the CBO will not be able to = optimally use the statistics to determine the most effective path to = retrieve data. Some documentation states to use hints when you are using = bind variables to tell the CBO which path to take. Other documentation = states that bind variables don't like the CBO and to use the RBO. After we looked at the library cache and saw thousands of copies of the = same sql, and hundreds of different sql, we decided to look into converting = certain web pages to bind variables. After we converted to using bind = variables, the pages that once took 5 seconds to load all of the sudden = took 2 minutes to load. The db server was pegged at 100% from 8-5 every = day while this application was being used. We have converted back to = literals and the pages are loading quickly again. While on bind variables, = we used every possible hint except RULE and had no luck. We didn't want = to resort to using the RULE hint because oracle claims not to use the RBO = (because of no further enhancements and possibly phasing it out), and to = start using the CBO for everything. There are so many confusing documents regarding the use of bind variables. = I wanted to see what the oracle community is doing in regards to using = bind variables and what their experiences are. TIA Danny Hughes DBA Knobias.com [EMAIL PROTECTED] www.knobias.com --HALPYZPZYFFYCGAKLKOY Content-Type: multipart/related; boundary=KMQGUMGTJRAMPYVFBAEF --KMQGUMGTJRAMPYVFBAEF Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable I have a question about using bind variables. It is my understanding that = bind variables are to be used to keep the same SQL from being reparsed and = keeping multiple copies in the SGA, thus *speeding* things up. I also = understand that by using bind variables that the CBO will not be able to = optimally use the statistics to determine the most effective path to = retrieve data. Some documentation states to use hints when you are using = bind variables to tell the CBO which path to take. Other documentation = states that bind variables don't like the CBO and to use the RBO. br brAfter we looked at the library cache and saw thousands of copies of = the same sql, and hundreds of different sql, we decided to look into = converting certain web pages to bind variables. After we converted to = using bind variables, the pages that once took lt; 5 seconds to load all = of the sudden took gt; 2 minutes to load. The db server was pegged at = 100% from 8-5 every day while this application was being used. We have = converted back to literals and the pages are loading quickly again. While = on bind variables, we used every possible hint except RULE and had no = luck. We didn't want to resort to using the RULE hint because oracle = claims not to use the RBO (because of no further enhancements and possibly = phasing it out), and to start using the CBO for everything. br brThere are so many
RE: Bind Variables
I have heard of it. Its a concern. However, with hints you can solve some of your problems. And then you could use Stored Outlines and get it stable. And then life will be groovy. -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, October 19, 2001 3:45 PM To: Multiple recipients of list ORACLE-L Danny, It sounds like when you ...moved to bind variables... you did two things: 1. Switched to using CBO 2. Changed your application to take advantage of the bind variables. I have never heard of switching to bind variables making things slower (and can not imagine when it would make it so -- the CBO vs. RBO is irrelevant in this case). Thus the question: did you collect the statistics on all your application tables prior to switching to CBO? If you did not have any statistics collected or had some tables that did not have statistics collected then Oracle may had been choosing wrong execution path. Thanks, Val Gamerman. [EMAIL PROTECTED] on 10/19/2001 03:07:56 PM Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Val Gamerman/Victoria Financial) Danny, Were you using histograms? Histograms do not work on bind variables, they only work on literals. I'm sure others will have more to say on the topic but that is one tidbit that I do have experience with on bind variables. Cherie Machler Oracle DBA Gelco Information Network Danny Hughes[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] DHUGHES cc: Sent by: [EMAIL PROTECTED]Subject: Bind Variables 10/19/01 01:30 PM Please respond to ORACLE-L --CIVUFTDKZVQDOGJWYCGU Content-Type: multipart/alternative; boundary= HALPYZPZYFFYCGAKLKOY --HALPYZPZYFFYCGAKLKOY Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable I have a question about using bind variables. It is my understanding that = bind variables are to be used to keep the same SQL from being reparsed and = keeping multiple copies in the SGA, thus *speeding* things up. I also = understand that by using bind variables that the CBO will not be able to = optimally use the statistics to determine the most effective path to = retrieve data. Some documentation states to use hints when you are using = bind variables to tell the CBO which path to take. Other documentation = states that bind variables don't like the CBO and to use the RBO. After we looked at the library cache and saw thousands of copies of the = same sql, and hundreds of different sql, we decided to look into converting = certain web pages to bind variables. After we converted to using bind = variables, the pages that once took 5 seconds to load all of the sudden = took 2 minutes to load. The db server was pegged at 100% from 8-5 every = day while this application was being used. We have converted back to = literals and the pages are loading quickly again. While on bind variables, = we used every possible hint except RULE and had no luck. We didn't want = to resort to using the RULE hint because oracle claims not to use the RBO = (because of no further enhancements and possibly phasing it out), and to = start using the CBO for everything. There are so many confusing documents regarding the use of bind variables. = I wanted to see what the oracle community is doing in regards to using = bind variables and what their experiences are. TIA Danny Hughes DBA Knobias.com [EMAIL PROTECTED] www.knobias.com --HALPYZPZYFFYCGAKLKOY Content-Type: multipart/related; boundary=KMQGUMGTJRAMPYVFBAEF --KMQGUMGTJRAMPYVFBAEF Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable I have a question about using bind variables. It is my understanding that = bind variables are to be used to keep the same SQL from being reparsed and = keeping multiple copies in the SGA, thus *speeding* things up. I also = understand that by using bind variables that the CBO will not be able to = optimally use the statistics to determine the most effective path to = retrieve data. Some documentation states to use hints when you are using = bind variables to tell the CBO which path to take. Other documentation = states that bind variables don't like the CBO and to use the RBO. br brAfter we looked at the library cache and saw thousands of copies of = the same sql, and hundreds of different sql, we decided to look into = converting certain web pages to bind variables. After we converted to = using bind variables, the pages that once took lt; 5 seconds to load all = of the sudden took gt; 2 minutes to load. The db server was pegged at = 100% from 8-5 every day while this application was being used. We have = converted back to literals and the pages are loading quickly
Re: Bind Variables
Danny, Bind variables are nearly always better than literals in your SQL. The one exception I can think of is if you are making using of histograms, but those aren't often used in OLTP type apps. Without sitting down and analyzing what was taking place, all anyone can do is guess. If you have a test box to do this on, then you can analyze the before and after SQL, check v$systemevent, etc. If you don't have a test box, you have a good reason to get one. If your queries are returning in 2 second while parsing SQL, it should be able to go significantly faster with bind variables, particularly under load. Literal SQL doesn't scale very well. Jared Danny Hughes[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] DHUGHES cc: Sent by: [EMAIL PROTECTED]Subject: Bind Variables 10/19/01 11:30 AM Please respond to ORACLE-L --CIVUFTDKZVQDOGJWYCGU Content-Type: multipart/alternative; boundary= HALPYZPZYFFYCGAKLKOY --HALPYZPZYFFYCGAKLKOY Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable I have a question about using bind variables. It is my understanding that = bind variables are to be used to keep the same SQL from being reparsed and = keeping multiple copies in the SGA, thus *speeding* things up. I also = understand that by using bind variables that the CBO will not be able to = optimally use the statistics to determine the most effective path to = retrieve data. Some documentation states to use hints when you are using = bind variables to tell the CBO which path to take. Other documentation = states that bind variables don't like the CBO and to use the RBO. After we looked at the library cache and saw thousands of copies of the = same sql, and hundreds of different sql, we decided to look into converting = certain web pages to bind variables. After we converted to using bind = variables, the pages that once took 5 seconds to load all of the sudden = took 2 minutes to load. The db server was pegged at 100% from 8-5 every = day while this application was being used. We have converted back to = literals and the pages are loading quickly again. While on bind variables, = we used every possible hint except RULE and had no luck. We didn't want = to resort to using the RULE hint because oracle claims not to use the RBO = (because of no further enhancements and possibly phasing it out), and to = start using the CBO for everything. There are so many confusing documents regarding the use of bind variables. = I wanted to see what the oracle community is doing in regards to using = bind variables and what their experiences are. TIA Danny Hughes DBA Knobias.com [EMAIL PROTECTED] www.knobias.com --HALPYZPZYFFYCGAKLKOY Content-Type: multipart/related; boundary=KMQGUMGTJRAMPYVFBAEF --KMQGUMGTJRAMPYVFBAEF Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable I have a question about using bind variables. It is my understanding that = bind variables are to be used to keep the same SQL from being reparsed and = keeping multiple copies in the SGA, thus *speeding* things up. I also = understand that by using bind variables that the CBO will not be able to = optimally use the statistics to determine the most effective path to = retrieve data. Some documentation states to use hints when you are using = bind variables to tell the CBO which path to take. Other documentation = states that bind variables don't like the CBO and to use the RBO. br brAfter we looked at the library cache and saw thousands of copies of = the same sql, and hundreds of different sql, we decided to look into = converting certain web pages to bind variables. After we
