bind variables in VB using OO4O
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: John Dunn 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 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: Cold Fusion and Bind Variables
Jared, Try this link. It still worked for me this morning. http://www.macromedia.com/devnet/server_archive/articles/cfqueryparam_oracle_databases.html Scott [EMAIL PROTECTED] 1/12/04 8:49:25 PM Dear List, We have a developer here that is pretty good with the web stuff, but his strong suit is not databases, or at least not Oracle. He develops primarily in Cold Fusion, which I know little about. I'm trying to teach him the difference between literal and bind variables, and the importance of knowing the difference. While it is easy to demonstrate this in PL/SQL or Perl, I have no idea how to do so in CF. A few minutes of googling didn't really turn up anything useful. I'm looking for some examples of using bind variables in SQL as used in Cold Fusion that connects to Oracle 8i. If you have one, the virtual beer is on me. :) Jared
RE: Cold Fusion and Bind Variables
Hi Jared, Look at QFQUERYPARM: Usage The CFQUERYPARAM is designed to do the following things: Allows the use of SQL bind parameters. Allows long text fields to be updated from an SQL statement. Improves performance. The ColdFusion ODBC, DB2, Informix, Oracle 7 and Oracle 8 driverssupport SQL bind parameters. However, at present, the ColdFusion Sybase 11 driver and Sybase native driver do not support SQL bind parameters. If a database does not support bind parameters, ColdFusion still performs validation and substitutes the validated parameter value back into the string. If validation fails, an error message is returned. Suzy -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of [EMAIL PROTECTED]Sent: Monday, January 12, 2004 6:49 PMTo: Multiple recipients of list ORACLE-LSubject: Cold Fusion and Bind VariablesDear List, We have a developer here that is pretty good with the web stuff, but his strong suit is not databases, or at least not Oracle. He develops primarily in Cold Fusion, which I know little about. I'm trying to teach him the difference between literal and bind variables, and the importance of knowing the difference. While it is easy to demonstrate this in PL/SQL or Perl, I have no idea how to do so in CF. A few minutes of googling didn't really turn up anything useful. I'm looking for some examples of using bind variables in SQL as used in Cold Fusion that connects to Oracle 8i. If you have one, the virtual beer is on me. :) Jared
RE: Cold Fusion and Bind Variables
Thanks Suzy, CFQUERYPARM is what he needs. Jared Vordos, Suzy [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/13/2004 05:34 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Cold Fusion and Bind Variables Hi Jared, Look at QFQUERYPARM: Usage The CFQUERYPARAM is designed to do the following things: Allows the use of SQL bind parameters. Allows long text fields to be updated from an SQL statement. Improves performance. The ColdFusion ODBC, DB2, Informix, Oracle 7 and Oracle 8 drivers support SQL bind parameters. However, at present, the ColdFusion Sybase 11 driver and Sybase native driver do not support SQL bind parameters. If a database does not support bind parameters, ColdFusion still performs validation and substitutes the validated parameter value back into the string. If validation fails, an error message is returned. Suzy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of [EMAIL PROTECTED] Sent: Monday, January 12, 2004 6:49 PM To: Multiple recipients of list ORACLE-L Subject: Cold Fusion and Bind Variables Dear List, We have a developer here that is pretty good with the web stuff, but his strong suit is not databases, or at least not Oracle. He develops primarily in Cold Fusion, which I know little about. I'm trying to teach him the difference between literal and bind variables, and the importance of knowing the difference. While it is easy to demonstrate this in PL/SQL or Perl, I have no idea how to do so in CF. A few minutes of googling didn't really turn up anything useful. I'm looking for some examples of using bind variables in SQL as used in Cold Fusion that connects to Oracle 8i. If you have one, the virtual beer is on me. :) Jared
Cold Fusion and Bind Variables
Dear List, We have a developer here that is pretty good with the web stuff, but his strong suit is not databases, or at least not Oracle. He develops primarily in Cold Fusion, which I know little about. I'm trying to teach him the difference between literal and bind variables, and the importance of knowing the difference. While it is easy to demonstrate this in PL/SQL or Perl, I have no idea how to do so in CF. A few minutes of googling didn't really turn up anything useful. I'm looking for some examples of using bind variables in SQL as used in Cold Fusion that connects to Oracle 8i. If you have one, the virtual beer is on me. :) Jared
Re: wait events that indicate lack of bind variables
Also add the library cache load and pin events to that. Anjo. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 05, 2003 5:19 AM 1. latch free for the shared pool latch (a shared pool latch in 9i). 2. Indirectly, SQL*Net message from client and CPU service time consumption too, because not using bind variables probably means that an application parses more often than it needs to. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Thursday, December 04, 2003 2:24 PM To: Multiple recipients of list ORACLE-L Which wait events are indications that your missing bind variables? btw, if you want to implement bind variables through a c/c++ middle tier its best to use 'prepared statements'. correct? bind arrays can be issued as prepared statements right? This is when you need to do alot of inserts from the middle tier to the database with just one pass to the database. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: Cary Millsap 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: Anjo Kolk 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).
wait events that indicate lack of bind variables
Which wait events are indications that your missing bind variables? btw, if you want to implement bind variables through a c/c++ middle tier its best to use 'prepared statements'. correct? bind arrays can be issued as prepared statements right? This is when you need to do alot of inserts from the middle tier to the database with just one pass to the database. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: wait events that indicate lack of bind variables
latch free waits on library cache shared pool latches. But I'd check parse to execute ratio from v$librarycache instead. If most of your statements close parse count and execution count, you might have a problem. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 04, 2003 10:24 PM Which wait events are indications that your missing bind variables? btw, if you want to implement bind variables through a c/c++ middle tier its best to use 'prepared statements'. correct? bind arrays can be issued as prepared statements right? This is when you need to do alot of inserts from the middle tier to the database with just one pass to the database. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: Tanel Poder 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: wait events that indicate lack of bind variables
1. latch free for the shared pool latch (a shared pool latch in 9i). 2. Indirectly, SQL*Net message from client and CPU service time consumption too, because not using bind variables probably means that an application parses more often than it needs to. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Thursday, December 04, 2003 2:24 PM To: Multiple recipients of list ORACLE-L Which wait events are indications that your missing bind variables? btw, if you want to implement bind variables through a c/c++ middle tier its best to use 'prepared statements'. correct? bind arrays can be issued as prepared statements right? This is when you need to do alot of inserts from the middle tier to the database with just one pass to the database. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: Cary Millsap 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: Multiple parses with bind variables
Thanks for the replies. I will also admit (sheepishly) that until today, I didn't realize that the Misses in library cache during parse: line after each statement in a trace file tells you how many of the parses for a statement were hard parses. --- Cary Millsap [EMAIL PROTECTED] wrote: Exactly! This is the thing that ora_check_sql=0 prevents when you use the Perl DBI. Then there's the problem of doing the prepare() inside a loop. The way to fix that problem is to NEVER PARSE INSIDE A LOOP. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Josh Collier Sent: Wednesday, August 20, 2003 3:45 PM To: Multiple recipients of list ORACLE-L The application that is running this, or the connection thinger (ODBC, JDBC, DBI) is parsing it twice. They often do a describe before a parse, which results in two parses for every execution. 945 * 2 = 1895 Josh -Original Message- Sent: Wednesday, August 20, 2003 1:34 PM To: Multiple recipients of list ORACLE-L Why should a query such as this, with bind variables and no literals, be parsed so many times? Other queries in the trace show, for example, 2 parses and 16 executions. And while I'm at it, why does is the parse count shown equal to the SUM of executes and fetches? SELECT BENEFICIARY_FK, BENEFICIARY_PHONE_PK, PHONE_TYPE_FK, PHONE_NUMBER FROM BENEFICIARY_PHONE WHERE COMPANY_FK = :V1 AND BENEFICIARY_FK IN ( :V2) call count cpuelapsed disk querycurrent --- -- -- -- -- -- Parse 1890 0.21 0.17 0 0 0 Execute945 0.04 0.08 0 0 0 Fetch 945 38.48 43.24 0 620428 0 = Paul Baumgartel Transcentive, Inc. www.transcentive.com __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel 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: Josh Collier 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: Cary Millsap 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). = Paul Baumgartel Transcentive, Inc. www.transcentive.com __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel 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
Multiple parses with bind variables
Why should a query such as this, with bind variables and no literals, be parsed so many times? Other queries in the trace show, for example, 2 parses and 16 executions. And while I'm at it, why does is the parse count shown equal to the SUM of executes and fetches? SELECT BENEFICIARY_FK, BENEFICIARY_PHONE_PK, PHONE_TYPE_FK, PHONE_NUMBER FROM BENEFICIARY_PHONE WHERE COMPANY_FK = :V1 AND BENEFICIARY_FK IN ( :V2) call count cpuelapsed disk querycurrent --- -- -- -- -- -- Parse 1890 0.21 0.17 0 0 0 Execute945 0.04 0.08 0 0 0 Fetch 945 38.48 43.24 0 620428 0 = Paul Baumgartel Transcentive, Inc. www.transcentive.com __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel 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: Multiple parses with bind variables
The application that is running this, or the connection thinger (ODBC, JDBC, DBI) is parsing it twice. They often do a describe before a parse, which results in two parses for every execution. 945 * 2 = 1895 Josh -Original Message- Sent: Wednesday, August 20, 2003 1:34 PM To: Multiple recipients of list ORACLE-L Why should a query such as this, with bind variables and no literals, be parsed so many times? Other queries in the trace show, for example, 2 parses and 16 executions. And while I'm at it, why does is the parse count shown equal to the SUM of executes and fetches? SELECT BENEFICIARY_FK, BENEFICIARY_PHONE_PK, PHONE_TYPE_FK, PHONE_NUMBER FROM BENEFICIARY_PHONE WHERE COMPANY_FK = :V1 AND BENEFICIARY_FK IN ( :V2) call count cpuelapsed disk querycurrent --- -- -- -- -- -- Parse 1890 0.21 0.17 0 0 0 Execute945 0.04 0.08 0 0 0 Fetch 945 38.48 43.24 0 620428 0 = Paul Baumgartel Transcentive, Inc. www.transcentive.com __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel 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: Josh Collier 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).
using bind variables for procedures/packages
Hi,
I know how to do it for regulard query statement but I
wasn't sure how to do it with procedures and packages.
I have this procedure. e.g.
I have this procedure
'begin
run_proc ('foo','blah');
end;
'
the procedure will be called either in a perl script.
How would I change it so that it uses bind variables?
Because it will be executed many times every hour and
every day.
thanks,
benny
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Benny Pei
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: using bind variables for procedures/packages
Consult the Descartes/Bunce Programming the Perl DBI book. See the
section about how to use '?' as a placeholder in your SQL text. You'll
end up with code that looks a little bit like this:
my $sql = begin run_proc(?, ?); end;;
my $sth = $dbh-prepare($sql, {ora_check_sql = 0});
...
# then, inside the loop that runs this thing lots of times...
while (my ($foo, $blah) = whatever()) {
$sth-execute($foo, $blah);
}
This code will result in a single parse call, and however many execute
calls with different bind values you deem appropriate. (By the way,
without the ora_check_sql setting, the prepare() method would cause
Oracle to perform two parse calls instead of just one.)
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Hotsos Clinic 101 in Sydney
- Hotsos Symposium 2004, March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
Benny Pei
Sent: Wednesday, August 20, 2003 8:14 PM
To: Multiple recipients of list ORACLE-L
Hi,
I know how to do it for regulard query statement but I
wasn't sure how to do it with procedures and packages.
I have this procedure. e.g.
I have this procedure
'begin
run_proc ('foo','blah');
end;
'
the procedure will be called either in a perl script.
How would I change it so that it uses bind variables?
Because it will be executed many times every hour and
every day.
thanks,
benny
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Benny Pei
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: Cary Millsap
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: Multiple parses with bind variables
Exactly! This is the thing that ora_check_sql=0 prevents when you use the Perl DBI. Then there's the problem of doing the prepare() inside a loop. The way to fix that problem is to NEVER PARSE INSIDE A LOOP. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Josh Collier Sent: Wednesday, August 20, 2003 3:45 PM To: Multiple recipients of list ORACLE-L The application that is running this, or the connection thinger (ODBC, JDBC, DBI) is parsing it twice. They often do a describe before a parse, which results in two parses for every execution. 945 * 2 = 1895 Josh -Original Message- Sent: Wednesday, August 20, 2003 1:34 PM To: Multiple recipients of list ORACLE-L Why should a query such as this, with bind variables and no literals, be parsed so many times? Other queries in the trace show, for example, 2 parses and 16 executions. And while I'm at it, why does is the parse count shown equal to the SUM of executes and fetches? SELECT BENEFICIARY_FK, BENEFICIARY_PHONE_PK, PHONE_TYPE_FK, PHONE_NUMBER FROM BENEFICIARY_PHONE WHERE COMPANY_FK = :V1 AND BENEFICIARY_FK IN ( :V2) call count cpuelapsed disk querycurrent --- -- -- -- -- -- Parse 1890 0.21 0.17 0 0 0 Execute945 0.04 0.08 0 0 0 Fetch 945 38.48 43.24 0 620428 0 = Paul Baumgartel Transcentive, Inc. www.transcentive.com __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel 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: Josh Collier 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: Cary Millsap 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: ADO and bind variables (was RE: Performance improvement required :-))
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Pardee, Roy E Sent: Friday, June 13, 2003 20:10 To: Multiple recipients of list ORACLE-L Subject: ADO and bind variables (was RE: Performance improvement required :-)) This is interesting--if I use ADO with the ODBC provider (as the code does below), I get the same results. But if I use just ADO (that is, ms' OLE DB provider for oracle (MSDAORA.1)) then I don't get bind vars. (I'm doing INSERTs in my code, not SELECTs). I wonder if oracle's native OLE DB provider works any differently--I would bet that it does... Cheers, -Roy Roy, I tried with the Oracle provider (OraOLEDB.Oracle.1), and the bind variables work. Another reason to stick with the Oracle provider :-) Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen 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).
ADO and bind variables (was RE: Performance improvement required :-))
This is interesting--if I use ADO with the ODBC provider (as the code does below), I get the same results. But if I use just ADO (that is, ms' OLE DB provider for oracle (MSDAORA.1)) then I don't get bind vars. (I'm doing INSERTs in my code, not SELECTs). I wonder if oracle's native OLE DB provider works any differently--I would bet that it does... Cheers, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Friday, June 13, 2003 11:05 AM To: Multiple recipients of list ORACLE-L I'm sure you can. You should see it in an ODBC trace log, or you can use trace events on the database. Here's a really simplistic test I did to verify it. I ran this VB code that executes a really dumb query that could not have come from anywhere else - SELECT DUMMY FROM DUAL WHERE DUMMY = 'X' but passed 'X' as a bind variable Private Sub Form_Load() Dim conn1 As New ADODB.Connection Dim cmd1 As New ADODB.Command Dim rs1 As New ADODB.Recordset Dim STRSQLSTRING As String Dim param1 As New Parameter strConnect = UID=produser;PWD=prodpass;DSN=WAREHOUSE; STRSQLSTRING = SELECT DUMMY FROM DUAL WHERE DUMMY = ? With conn1 .ConnectionTimeout = 0 .CommandTimeout = 0 .CursorLocation = adUseClient .Mode = adModeRead .Open strConnect End With If Err.Number Then MsgBox Err.Number Exit Sub End If With cmd1 .ActiveConnection = conn1 .CommandText = STRSQLSTRING .CommandType = adCmdText Set param1 = .CreateParameter(DummyValue, adChar, adParamInput, 1, X) param1.Value = X .Parameters.Append param1 Set rs1 = .Execute End With MsgBox rs1.Fields(DUMMY) End Sub Afterward, executed this on the database - SQL select sql_text from v$sqlarea where sql_text like 'SELECT DUMMY %'; SQL_TEXT SELECT DUMMY FROM DUAL WHERE DUMMY = :1 It shows the parameter was definitely passed as a bind variable. Check out this document on Metalink - Retrieving Record Set from Stored Proc Using ADO and VB (SCR 782) It appears to have an example of passing a cursor back to a recordset, though I've never tried it. HTH. Beth -Original Message- Sent: Friday, June 13, 2003 1:20 PM To: Multiple recipients of list ORACLE-L Why can't you use bind variables? I thought using .Parameters method (property?) of ADODB.Command would use bind variables. I thought it didn't. Any way of checking (other than to get the developers to try it? What function, and where can't you use it? Stored Procedure type function (i.e. user-written) called from VB. 'Cos it's a Stored Procedure it will use bind variables, but you can't return a result set to VB. Craig -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seefelt, Beth 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: Pardee, Roy E 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: Using bind variables with ADO in VB
I thought the .Prepared property of the command object was what you needed to use bind vars from ADO. Have a look at the following code, adapted from the ADO help entry for .Prepared. It purports to compare response times for prepared unprepared versions of the same SQL command. In the couple of times I've run it, the prepared statement runs just a bit faster, for whatever that's worth. I'm not enough of a DBA to verify that it's using bind vars--but I bet one of you are. The table the code inserts into is a single column table called Numbers. ' --- Public S u b PreparedX() Dim Cnxn As ADODB.Connection Dim cmd1 As ADODB.Command Dim cmd2 As ADODB.Command Dim prm As ADODB.Parameter Dim strCnxn As String Dim strCmd As String Dim sngStart As Single Dim sngEnd As Single Dim sngNotPrepared As Single Dim sngPrepared As Single Dim intLoop As Integer Dim strUserName As String Dim strPwd As String Dim strSID As String strUserName = scott strPwd = tiger strSID = devl ' Open a connection ' strCnxn = Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; strCnxn = Provider=MSDAORA.1; _ User ID= strUserName ; _ Password= strPwd ; _ Data Source= strSID ; _ Persist Security Info=True Set Cnxn = New ADODB.Connection Cnxn.Open strCnxn ' Create two command objects for the same ' command - one prepared and one not prepared ' strCmd = SELECT title, type FROM Titles ORDER BY type ' strCmd = SELECT * FROM emp ORDER BY ename strCmd = INSERT INTO numbers VALUES (?) Set cmd1 = New ADODB.Command Set cmd1.ActiveConnection = Cnxn ' cmd1.CommandText = strCmd Set cmd2 = New ADODB.Command Set cmd2.ActiveConnection = Cnxn cmd2.CommandText = strCmd Set prm = cmd2.CreateParameter With prm .Name = Val .Type = adNumeric .Direction = adParamInput End With cmd2.Parameters.Append prm cmd2.Prepared = True ' Set a timer, then execute the unprepared ' command 20 times sngStart = Timer For intLoop = 40 To 60 cmd1.CommandText = Replace(strCmd, ?, CStr(intLoop)) cmd1.Execute Next intLoop sngEnd = Timer sngNotPrepared = sngEnd - sngStart ' Reset the timer, then execute the prepared ' command 20 times sngStart = Timer For intLoop = 70 To 90 prm.Value = intLoop cmd2.Execute Next intLoop sngEnd = Timer sngPrepared = sngEnd - sngStart ' Display performance results MsgBox Performance Results: vbCr _ Not Prepared: Format(sngNotPrepared, _ ##0.000) seconds vbCr _ Prepared: Format(sngPrepared, _ ##0.000) seconds ' clean up Cnxn.Close Set Cnxn = Nothing End Sub ' --- (I ran this w/ADO 2.5, Net8 v.8.0.5, against an 8.0.6 db). Cheers, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Friday, June 06, 2003 9:00 AM To: Multiple recipients of list ORACLE-L I don't THINK so. To quote further the parameters are not bound except in the case of stored procedures...In the case of normal SQL, ADO replaces the placeholder with Parameter.Value, so there is no binding on the server side. The way I read that, you can't use bind variables in VB using ADO. Craig Healey -Original Message- From: Stefan Jahnke [mailto:[EMAIL PROTECTED] Sent: 06 June 2003 14:55 To: Multiple recipients of list ORACLE-L Subject: AW: Using bind variables with ADO in VB Hi Question: Are you sure that the binding you're referring to (your book excerpt) doesn't mean early bindig versus late binding in VB using ADO ? Stefan -Original Message- Sent: Friday, June 06, 2003 12:35 PM To: Multiple recipients of list ORACLE-L Having read that bind variables will solve all our problems ;-) I've tried to get the developers to use them. There response being: how? I don't know much VB and they don't know what a bind variable is. So I read with interest J Prem's question. Now knowing what to look for, I read further, and found that ADO also does not bind (compile to p-code) parameterized statements in Oracle (VB Oracle 8 by Dov Trietsch) 8-( Is it true? Are bind variables that big a deal in VB code anyway? We have a lot of badly written code, so are we better off re-writing the code without bothering about bind variables at the moment? Am I worrying about nothing? And no, it doesn't scale particularly well! Regards Craig Healey ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed and may contain confidential
RE: example to use bind variables with OO4O/VB
go to your: (B%ORACLE_HOME%\oo4o\VB\SAMPLES (B (Bit has a lot of example which should help (B (BRegis (B (B-Original Message- (BSent: Thursday, June 05, 2003 1:00 PM (BTo: Multiple recipients of list ORACLE-L (B (B (BGuys, (B (Bcan someone give a simple example ( piece of code ) for: (B (BHow to use bind variables with OO40/VB6 to connect to a 8.1.6 database ? (B (B..peeped into metalink too. (B (Bcan u give me any other example / URL for the same ???! (B (BTIA. (B (BJp. (B (B (B (B (B-- (BPlease see the official ORACLE-L FAQ: http://www.orafaq.net (B-- (BAuthor: Prem Khanna J (B INET: [EMAIL PROTECTED] (B (BFat City Network Services-- 858-538-5051 http://www.fatcity.com (BSan Diego, California-- Mailing list and web hosting services (B- (BTo REMOVE yourself from this mailing list, send an E-Mail message (Bto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in (Bthe message BODY, include a line containing: UNSUB ORACLE-L (B(or the name of mailing list you want to be removed from). You may (Balso send the HELP command for other information (like subscribing). (B* (BThis electronic transmission is strictly confidential and intended solely (Bfor the addressee. It may contain information which is covered by legal, (Bprofessional or other privilege. If you are not the intended addressee, (Byou must not disclose, copy or take any action in reliance of this (Btransmission. If you have received this transmission in error, (Bplease notify the sender as soon as possible. (B (BThis footnote also confirms that this message has been swept (Bfor computer viruses. (B** (B (B-- (BPlease see the official ORACLE-L FAQ: http://www.orafaq.net (B-- (BAuthor: Regis Biassala (B INET: [EMAIL PROTECTED] (B (BFat City Network Services-- 858-538-5051 http://www.fatcity.com (BSan Diego, California-- Mailing list and web hosting services (B- (BTo REMOVE yourself from this mailing list, send an E-Mail message (Bto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in (Bthe message BODY, include a line containing: UNSUB ORACLE-L (B(or the name of mailing list you want to be removed from). You may (Balso send the HELP command for other information (like subscribing).
RE: example to use bind variables with OO4O/VB
Check out http://asktom.oracle.com HTH Best Regards, Ganesh R DID : +65-6215-8413 HP : +65-9067-8474 === Live to learn... forget... and learn again. === -Original Message- Khanna J Sent: Thursday, June 05, 2003 8:00 PM To: Multiple recipients of list ORACLE-L Guys, can someone give a simple example ( piece of code ) for: How to use bind variables with OO40/VB6 to connect to a 8.1.6 database ? ..peeped into metalink too. can u give me any other example / URL for the same ???! TIA. Jp. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ganesh Raja 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: example to use bind variables with OO4O/VB
I found this link really helpful during my development projects using oo4o. http://technet.oracle.com/doc/oracle8i_816/nt.816/a8/o4o00259.htm From: Regis Biassala [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: example to use bind variables with OO4O/VB Date: Thu, 05 Jun 2003 05:44:47 -0800 go to your: %ORACLE_HOME%\oo4o\VB\SAMPLES it has a lot of example which should help Regis -Original Message- Sent: Thursday, June 05, 2003 1:00 PM To: Multiple recipients of list ORACLE-L Guys, can someone give a simple example ( piece of code ) for: How to use bind variables with OO40/VB6 to connect to a 8.1.6 database ? ..peeped into metalink too. can u give me any other example / URL for the same ???! TIA. Jp. -- 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). * This electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender as soon as possible. This footnote also confirms that this message has been swept for computer viruses. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Regis Biassala 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). _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade 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: example to use bind variables with OO4O/VB
http://www.doag.de/orafaq/faqoo4o.htm#BINDVART (B (BRoy Pardee (BProgrammer/Analyst/DBA (BSWFPAC Lockheed Martin IT (BExtension 8487 (B (B-Original Message- (BSent: Thursday, June 05, 2003 5:00 AM (BTo: Multiple recipients of list ORACLE-L (B (B (BGuys, (B (Bcan someone give a simple example ( piece of code ) for: (B (BHow to use bind variables with OO40/VB6 to connect to a 8.1.6 database ? (B (B..peeped into metalink too. (B (Bcan u give me any other example / URL for the same ???! (B (BTIA. (B (BJp. (B (B (B (B (B-- (BPlease see the official ORACLE-L FAQ: http://www.orafaq.net (B-- (BAuthor: Prem Khanna J (B INET: [EMAIL PROTECTED] (B (BFat City Network Services-- 858-538-5051 http://www.fatcity.com (BSan Diego, California-- Mailing list and web hosting services (B- (BTo REMOVE yourself from this mailing list, send an E-Mail message (Bto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in (Bthe message BODY, include a line containing: UNSUB ORACLE-L (B(or the name of mailing list you want to be removed from). You may (Balso send the HELP command for other information (like subscribing). (B-- (BPlease see the official ORACLE-L FAQ: http://www.orafaq.net (B-- (BAuthor: Pardee, Roy E (B INET: [EMAIL PROTECTED] (B (BFat City Network Services-- 858-538-5051 http://www.fatcity.com (BSan Diego, California-- Mailing list and web hosting services (B- (BTo REMOVE yourself from this mailing list, send an E-Mail message (Bto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in (Bthe message BODY, include a line containing: UNSUB ORACLE-L (B(or the name of mailing list you want to be removed from). You may (Balso send the HELP command for other information (like subscribing).
Re: example to use bind variables with OO4O/VB
Try a search on support.microsoft.com. There are several articles there detailing to to use Oracle from MS languages. Jared Prem Khanna J [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/05/2003 04:59 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:example to use bind variables with OO4O/VB Guys, can someone give a simple example ( piece of code ) for: How to use bind variables with OO40/VB6 to connect to a 8.1.6 database ? ..peeped into metalink too. can u give me any other example / URL for the same ???! TIA. Jp. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
Using bind variables with ADO in VB
Having read that bind variables will solve all our problems ;-) I've tried to get the developers to use them. There response being: how? I don't know much VB and they don't know what a bind variable is. So I read with interest J Prem's question. Now knowing what to look for, I read further, and found that ADO also does not bind (compile to p-code) parameterized statements in Oracle (VB Oracle 8 by Dov Trietsch) 8-( Is it true? Are bind variables that big a deal in VB code anyway? We have a lot of badly written code, so are we better off re-writing the code without bothering about bind variables at the moment? Am I worrying about nothing? And no, it doesn't scale particularly well! Regards Craig Healey ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. Statements and opinions expressed in this e-mail may not represent those of the company. If you have received this email in error please notify [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses (www.mimesweeper.com) *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Healey 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: Using bind variables with ADO in VB
That's why developping server side code will help as you can bind in PL/SQL...period ! Avoid using thick clients...instead put all your logic in Oracle server side (i.e: write package, procedure and function) Regis -Original Message- Sent: Friday, June 06, 2003 12:35 PM To: Multiple recipients of list ORACLE-L Having read that bind variables will solve all our problems ;-) I've tried to get the developers to use them. There response being: how? I don't know much VB and they don't know what a bind variable is. So I read with interest J Prem's question. Now knowing what to look for, I read further, and found that ADO also does not bind (compile to p-code) parameterized statements in Oracle (VB Oracle 8 by Dov Trietsch) 8-( Is it true? Are bind variables that big a deal in VB code anyway? We have a lot of badly written code, so are we better off re-writing the code without bothering about bind variables at the moment? Am I worrying about nothing? And no, it doesn't scale particularly well! Regards Craig Healey ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. Statements and opinions expressed in this e-mail may not represent those of the company. If you have received this email in error please notify [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses (www.mimesweeper.com) *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Healey 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 electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender as soon as possible. This footnote also confirms that this message has been swept for computer viruses. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Regis Biassala 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).
AW: Using bind variables with ADO in VB
Hi Question: Are you sure that the binding you're referring to (your book excerpt) doesn't mean early bindig versus late binding in VB using ADO ? Stefan -Ursprüngliche Nachricht- Von: Regis Biassala [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 6. Juni 2003 14:50 An: Multiple recipients of list ORACLE-L Betreff: RE: Using bind variables with ADO in VB That's why developping server side code will help as you can bind in PL/SQL...period ! Avoid using thick clients...instead put all your logic in Oracle server side (i.e: write package, procedure and function) Regis -Original Message- Sent: Friday, June 06, 2003 12:35 PM To: Multiple recipients of list ORACLE-L Having read that bind variables will solve all our problems ;-) I've tried to get the developers to use them. There response being: how? I don't know much VB and they don't know what a bind variable is. So I read with interest J Prem's question. Now knowing what to look for, I read further, and found that ADO also does not bind (compile to p-code) parameterized statements in Oracle (VB Oracle 8 by Dov Trietsch) 8-( Is it true? Are bind variables that big a deal in VB code anyway? We have a lot of badly written code, so are we better off re-writing the code without bothering about bind variables at the moment? Am I worrying about nothing? And no, it doesn't scale particularly well! Regards Craig Healey ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. Statements and opinions expressed in this e-mail may not represent those of the company. If you have received this email in error please notify [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses (www.mimesweeper.com) *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Healey 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 electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender as soon as possible. This footnote also confirms that this message has been swept for computer viruses. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Regis Biassala 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: Stefan Jahnke 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: Using bind variables with ADO in VB
I don't THINK so. To quote further the parameters are not bound except in the case of stored procedures...In the case of normal SQL, ADO replaces the placeholder with Parameter.Value, so there is no binding on the server side. The way I read that, you can't use bind variables in VB using ADO. Craig Healey -Original Message- From: Stefan Jahnke [mailto:[EMAIL PROTECTED] Sent: 06 June 2003 14:55 To: Multiple recipients of list ORACLE-L Subject: AW: Using bind variables with ADO in VB Hi Question: Are you sure that the binding you're referring to (your book excerpt) doesn't mean early bindig versus late binding in VB using ADO ? Stefan -Original Message- Sent: Friday, June 06, 2003 12:35 PM To: Multiple recipients of list ORACLE-L Having read that bind variables will solve all our problems ;-) I've tried to get the developers to use them. There response being: how? I don't know much VB and they don't know what a bind variable is. So I read with interest J Prem's question. Now knowing what to look for, I read further, and found that ADO also does not bind (compile to p-code) parameterized statements in Oracle (VB Oracle 8 by Dov Trietsch) 8-( Is it true? Are bind variables that big a deal in VB code anyway? We have a lot of badly written code, so are we better off re-writing the code without bothering about bind variables at the moment? Am I worrying about nothing? And no, it doesn't scale particularly well! Regards Craig Healey ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. Statements and opinions expressed in this e-mail may not represent those of the company. If you have received this email in error please notify [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses (www.mimesweeper.com) *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Healey 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: Using bind variables with ADO in VB
Yet another reason not to use VB or anything like it. http://www.amazon.com/exec/obidos/ASIN/020177061X Craig Healey [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/06/2003 08:59 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Using bind variables with ADO in VB I don't THINK so. To quote further the parameters are not bound except in the case of stored procedures...In the case of normal SQL, ADO replaces the placeholder with Parameter.Value, so there is no binding on the server side. The way I read that, you can't use bind variables in VB using ADO. Craig Healey -Original Message- From: Stefan Jahnke [mailto:[EMAIL PROTECTED] Sent: 06 June 2003 14:55 To: Multiple recipients of list ORACLE-L Subject: AW: Using bind variables with ADO in VB Hi Question: Are you sure that the binding you're referring to (your book excerpt) doesn't mean early bindig versus late binding in VB using ADO ? Stefan -Original Message- Sent: Friday, June 06, 2003 12:35 PM To: Multiple recipients of list ORACLE-L Having read that bind variables will solve all our problems ;-) I've tried to get the developers to use them. There response being: how? I don't know much VB and they don't know what a bind variable is. So I read with interest J Prem's question. Now knowing what to look for, I read further, and found that ADO also does not bind (compile to p-code) parameterized statements in Oracle (VB Oracle 8 by Dov Trietsch) 8-( Is it true? Are bind variables that big a deal in VB code anyway? We have a lot of badly written code, so are we better off re-writing the code without bothering about bind variables at the moment? Am I worrying about nothing? And no, it doesn't scale particularly well! Regards Craig Healey ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. Statements and opinions expressed in this e-mail may not represent those of the company. If you have received this email in error please notify [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses (www.mimesweeper.com) *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Healey 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: 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: Using bind variables with ADO in VB
Hello: You can bind values using the command object. Set the command type to text then add your parameters similar to how you would call a stored procedure. The SQL would looks something like SELECT NAME FROM EMP WHERE EMPID=:PEMPNO Also, if ADO (and ADO.Net) will bind the values for you, this can be seen in your code when monitoring using Quests SQL Monitor. Although it is always better to do it yourself since the execution will be quicker and the values will be bound the first time. From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Using bind variables with ADO in VB Date: Fri, 06 Jun 2003 09:04:00 -0800 Yet another reason not to use VB or anything like it. http://www.amazon.com/exec/obidos/ASIN/020177061X Craig Healey [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/06/2003 08:59 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Using bind variables with ADO in VB I don't THINK so. To quote further the parameters are not bound except in the case of stored procedures...In the case of normal SQL, ADO replaces the placeholder with Parameter.Value, so there is no binding on the server side. The way I read that, you can't use bind variables in VB using ADO. Craig Healey -Original Message- From: Stefan Jahnke [mailto:[EMAIL PROTECTED] Sent: 06 June 2003 14:55 To: Multiple recipients of list ORACLE-L Subject: AW: Using bind variables with ADO in VB Hi Question: Are you sure that the binding you're referring to (your book excerpt) doesn't mean early bindig versus late binding in VB using ADO ? Stefan -Original Message- Sent: Friday, June 06, 2003 12:35 PM To: Multiple recipients of list ORACLE-L Having read that bind variables will solve all our problems ;-) I've tried to get the developers to use them. There response being: how? I don't know much VB and they don't know what a bind variable is. So I read with interest J Prem's question. Now knowing what to look for, I read further, and found that ADO also does not bind (compile to p-code) parameterized statements in Oracle (VB Oracle 8 by Dov Trietsch) 8-( Is it true? Are bind variables that big a deal in VB code anyway? We have a lot of badly written code, so are we better off re-writing the code without bothering about bind variables at the moment? Am I worrying about nothing? And no, it doesn't scale particularly well! Regards Craig Healey ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. Statements and opinions expressed in this e-mail may not represent those of the company. If you have received this email in error please notify [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses (www.mimesweeper.com) *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Healey 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: 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). _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade INET: [EMAIL
example to use bind variables with OO4O/VB
Guys, can someone give a simple example ( piece of code ) for: How to use bind variables with OO40/VB6 to connect to a 8.1.6 database ? ..peeped into metalink too. can u give me any other example / URL for the same ???! TIA. Jp. -- 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).
Thanks -- Re: example to use bind variables with OO4O/VB
Thanx Ganesh,Jared,Jay,Roy and Chajol. Thanks for the info'. Jp. -- 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: Oracle position on hints (talking about bind variables)
My first consideration with regards to bind variables is to consider the cost of parsing the query. After all the benefit of bind variables (or at least a major one) is to reduce the number of parses required. If the query is going to be executed so rarely that the parse cost (a fraction of a second typically) isn't significant then there appears to be little benefit. I think bind variables are most useful in OTLP where the same query gets fired again and again (especially when you have hundreds of client connections). At the other extreme (a batch query executed once per day) they aren't required. Therefore my approach tends to be is the query going to be executed very frequently - if so then look at using bind variables on the values that will change. Regards, Mark. [EMAIL PROTECTED] disys.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Oracle position on hints om 11/03/2003 05:09 Please respond to ORACLE-L Well, I wouldn't say you should *always* use bind variables. Many situations in a database warehouse preclude that. Potentially long running queries may need to have literals to help the CBO make the right choice. Star transforms don't work with bind variables, and histograms can't be used with bind variables. Jared Nicoll, Iain [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/08/2003 06:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Oracle position on hints Given the attitude of most dba's that you should always use bind variables where possible I can't see how you'd cope with skewed data without them. Most developers should know a databases's data better than the optimiser and certainly when building queries it's always worthwhile seeing where data is being most effectively filtered. There are lots of mature systems out there where the data characteristics are unlikely to change much and for most in-house developers you're never going to have to think about portability but always about performance. Iain Nicoll -Original Message- Sent: 07 March 2003 16:04 To: Multiple recipients of list ORACLE-L Hi, Does Oracle have an official position on hints ? Will they go away as the optimiser is becoming bettre or they are there to stay ? TIA Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tél. (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nicoll, Iain 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: 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
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).
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).
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 http
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
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
bind variables
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 to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain \(Calanais\) INET: [EMAIL PROTECTED] Fat City Network
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
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
Bind Variables in VB
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).
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).
AW: How to change VB code to use bind variables?
Hi I did a search on MSDN for sql bind variable. Here is the result page: http://search.microsoft.com/default.asp?qu=sql+bind+variableboolean=ALLnq= NEWso=RECCNTp=1ig=01ig=02ig=03ig=04ig=05ig=06i=00i=01i=02i=03i= 04i=05i=06i=07i=08i=09i=10i=11i=12i=13i=14i=15i=16i=17i=18i=1 9i=20i=21i=22i=23i=24i=25i=26i=27i=28i=29i=30i=31i=32i=33i=34 i=35i=36i=37i=38i=39i=40i=41i=42i=43i=44i=45i=46i=47i=48i=49 i=50i=51siteid=us/dev Looks like some articles might help. Try also these ones 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 Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto:[EMAIL PROTECTED] visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Behalten Sie den Ueberblick - mit dem neuen BasicOverView, unserer Seminaruebersicht fuer das 2. Halbjahr 2002. Sie haben noch kein Exemplar? Schreiben Sie eine E-Mail an mailto:[EMAIL PROTECTED] oder rufen Sie uns an unter 0 18 03 / 73 64 62 73! Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: Miller, Jay [mailto:[EMAIL PROTECTED]] Gesendet: Mittwoch, 17. Juli 2002 00:08 An: Multiple recipients of list ORACLE-L Betreff: RE: How to change VB code to use bind variables? Hi Jared, Thanks for the suggestion. But I went to www.microsoft.com, clicked on support and searched on the words bind variables oracle visual basic After a dozen Internet Explorer Script errors I got a bunch of links with names like 26jbkjakjbmon That one led to a blank page. Others led to articles that didn't even mention bind variables. And I thought Metalink was bad. Does anyone have a link that goes directly to an article? Thanks, Jay Miller -Original Message- Sent: Tuesday, July 16, 2002 4:45 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Check on the MS support site. There are several articles detailing how to do this. Jared Miller, Jay [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/16/2002 02:08 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:How to change VB code to use bind variables? Hi, In checking my v$SQLArea I recently discovered that there was an often executed SQL statement that was not using bind variables. I tracked it down to a VB front end and asked the development group to modify the code. They asked me how to do it. Not being a VB programmer I said I had no idea but would try to find out. Here's an abbreviated version of the code. It's the sAcctId that seems to be causing the multiple executions. Anyone know how this can be rewritten to use bind variables? Can it be done from VB or do we need to transfer the code to PL/SQL on the back end? Private Function GetSQL(ByVal sKey As String, _ ByVal sAcctId As String, _ Optional ByVal sAcctNum As String, _ Optional ByVal sWhereAnd As String) Dim sConnect As String Dim sSql As String Dim oWsiLibData As WSILib.CDisplayData Dim rsCustomer As ADODB.Recordset Dim lCustId As Long Dim sCustId As String Select Case sKey ... ... Case accountinfo: sSql = SELECT a.id, a.account_no, a.type, a.title1, a.title2, a.title3, _ a.inventory_date date_opened, a.last_modified_date last_change, _ rc.description rebatecmsn, _ sSql = sSql account_info.fetch_email( sAcctId ) email sSql = sSql FROM account a, status s, usa_wh_tax u, fund f, _ stock_commission_codes sc, cmsn_rebate_codes rc, account_kind ak sSql = sSql , dual sSql = sSql WHERE a.status_id=s.id and a.usa_tax_code=u.code(+) _ and a.fund_id=f.id and _ a.s_stk_cmsn_code=sc.code(+) and a.cmsn_reb_code=rc.code(+) and a.kind_id=ak.id _ and a.id = sAcctId sConnect = sConnectWSI GetSQL = Array(sConnect, sSql) -- 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
How to change VB code to use bind variables?
Hi, In checking my v$SQLArea I recently discovered that there was an often executed SQL statement that was not using bind variables. I tracked it down to a VB front end and asked the development group to modify the code. They asked me how to do it. Not being a VB programmer I said I had no idea but would try to find out. Here's an abbreviated version of the code. It's the sAcctId that seems to be causing the multiple executions. Anyone know how this can be rewritten to use bind variables? Can it be done from VB or do we need to transfer the code to PL/SQL on the back end? Private Function GetSQL(ByVal sKey As String, _ ByVal sAcctId As String, _ Optional ByVal sAcctNum As String, _ Optional ByVal sWhereAnd As String) Dim sConnect As String Dim sSql As String Dim oWsiLibData As WSILib.CDisplayData Dim rsCustomer As ADODB.Recordset Dim lCustId As Long Dim sCustId As String Select Case sKey ... ... Case accountinfo: sSql = SELECT a.id, a.account_no, a.type, a.title1, a.title2, a.title3, _ a.inventory_date date_opened, a.last_modified_date last_change, _ rc.description rebatecmsn, _ sSql = sSql account_info.fetch_email( sAcctId ) email sSql = sSql FROM account a, status s, usa_wh_tax u, fund f, _ stock_commission_codes sc, cmsn_rebate_codes rc, account_kind ak sSql = sSql , dual sSql = sSql WHERE a.status_id=s.id and a.usa_tax_code=u.code(+) _ and a.fund_id=f.id and _ a.s_stk_cmsn_code=sc.code(+) and a.cmsn_reb_code=rc.code(+) and a.kind_id=ak.id _ and a.id = sAcctId sConnect = sConnectWSI GetSQL = Array(sConnect, sSql) -- 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: How to change VB code to use bind variables?
Check on the MS support site. There are several articles detailing how to do this. Jared Miller, Jay [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/16/2002 02:08 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:How to change VB code to use bind variables? Hi, In checking my v$SQLArea I recently discovered that there was an often executed SQL statement that was not using bind variables. I tracked it down to a VB front end and asked the development group to modify the code. They asked me how to do it. Not being a VB programmer I said I had no idea but would try to find out. Here's an abbreviated version of the code. It's the sAcctId that seems to be causing the multiple executions. Anyone know how this can be rewritten to use bind variables? Can it be done from VB or do we need to transfer the code to PL/SQL on the back end? Private Function GetSQL(ByVal sKey As String, _ ByVal sAcctId As String, _ Optional ByVal sAcctNum As String, _ Optional ByVal sWhereAnd As String) Dim sConnect As String Dim sSql As String Dim oWsiLibData As WSILib.CDisplayData Dim rsCustomer As ADODB.Recordset Dim lCustId As Long Dim sCustId As String Select Case sKey ... ... Case accountinfo: sSql = SELECT a.id, a.account_no, a.type, a.title1, a.title2, a.title3, _ a.inventory_date date_opened, a.last_modified_date last_change, _ rc.description rebatecmsn, _ sSql = sSql account_info.fetch_email( sAcctId ) email sSql = sSql FROM account a, status s, usa_wh_tax u, fund f, _ stock_commission_codes sc, cmsn_rebate_codes rc, account_kind ak sSql = sSql , dual sSql = sSql WHERE a.status_id=s.id and a.usa_tax_code=u.code(+) _ and a.fund_id=f.id and _ a.s_stk_cmsn_code=sc.code(+) and a.cmsn_reb_code=rc.code(+) and a.kind_id=ak.id _ and a.id = sAcctId sConnect = sConnectWSI GetSQL = Array(sConnect, sSql) -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to change VB code to use bind variables?
Hi Jared, Thanks for the suggestion. But I went to www.microsoft.com, clicked on support and searched on the words bind variables oracle visual basic After a dozen Internet Explorer Script errors I got a bunch of links with names like 26jbkjakjbmon That one led to a blank page. Others led to articles that didn't even mention bind variables. And I thought Metalink was bad. Does anyone have a link that goes directly to an article? Thanks, Jay Miller -Original Message- Sent: Tuesday, July 16, 2002 4:45 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Check on the MS support site. There are several articles detailing how to do this. Jared Miller, Jay [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/16/2002 02:08 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:How to change VB code to use bind variables? Hi, In checking my v$SQLArea I recently discovered that there was an often executed SQL statement that was not using bind variables. I tracked it down to a VB front end and asked the development group to modify the code. They asked me how to do it. Not being a VB programmer I said I had no idea but would try to find out. Here's an abbreviated version of the code. It's the sAcctId that seems to be causing the multiple executions. Anyone know how this can be rewritten to use bind variables? Can it be done from VB or do we need to transfer the code to PL/SQL on the back end? Private Function GetSQL(ByVal sKey As String, _ ByVal sAcctId As String, _ Optional ByVal sAcctNum As String, _ Optional ByVal sWhereAnd As String) Dim sConnect As String Dim sSql As String Dim oWsiLibData As WSILib.CDisplayData Dim rsCustomer As ADODB.Recordset Dim lCustId As Long Dim sCustId As String Select Case sKey ... ... Case accountinfo: sSql = SELECT a.id, a.account_no, a.type, a.title1, a.title2, a.title3, _ a.inventory_date date_opened, a.last_modified_date last_change, _ rc.description rebatecmsn, _ sSql = sSql account_info.fetch_email( sAcctId ) email sSql = sSql FROM account a, status s, usa_wh_tax u, fund f, _ stock_commission_codes sc, cmsn_rebate_codes rc, account_kind ak sSql = sSql , dual sSql = sSql WHERE a.status_id=s.id and a.usa_tax_code=u.code(+) _ and a.fund_id=f.id and _ a.s_stk_cmsn_code=sc.code(+) and a.cmsn_reb_code=rc.code(+) and a.kind_id=ak.id _ and a.id = sAcctId sConnect = sConnectWSI GetSQL = Array(sConnect, sSql) -- 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). -- 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: How to change VB code to use bind variables?
did you try google? http://www.google.com/search?hl=enie=UTF-8oe=UTF-8q=bind+variables+oracle+visual+basicbtnG=Google+Search [EMAIL PROTECTED] 07/16/02 06:08PM Hi Jared, Thanks for the suggestion. But I went to www.microsoft.com, clicked on support and searched on the words bind variables oracle visual basic After a dozen Internet Explorer Script errors I got a bunch of links with names like 26jbkjakjbmon That one led to a blank page. Others led to articles that didn't even mention bind variables. And I thought Metalink was bad. Does anyone have a link that goes directly to an article? Thanks, Jay Miller -Original Message- Sent: Tuesday, July 16, 2002 4:45 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Check on the MS support site. There are several articles detailing how to do this. Jared Miller, Jay [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/16/2002 02:08 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:How to change VB code to use bind variables? Hi, In checking my v$SQLArea I recently discovered that there was an often executed SQL statement that was not using bind variables. I tracked it down to a VB front end and asked the development group to modify the code. They asked me how to do it. Not being a VB programmer I said I had no idea but would try to find out. Here's an abbreviated version of the code. It's the sAcctId that seems to be causing the multiple executions. Anyone know how this can be rewritten to use bind variables? Can it be done from VB or do we need to transfer the code to PL/SQL on the back end? Private Function GetSQL(ByVal sKey As String, _ ByVal sAcctId As String, _ Optional ByVal sAcctNum As String, _ Optional ByVal sWhereAnd As String) Dim sConnect As String Dim sSql As String Dim oWsiLibData As WSILib.CDisplayData Dim rsCustomer As ADODB.Recordset Dim lCustId As Long Dim sCustId As String Select Case sKey ... ... Case accountinfo: sSql = SELECT a.id, a.account_no, a.type, a.title1, a.title2, a.title3, _ a.inventory_date date_opened, a.last_modified_date last_change, _ rc.description rebatecmsn, _ sSql = sSql account_info.fetch_email( sAcctId ) email sSql = sSql FROM account a, status s, usa_wh_tax u, fund f, _ stock_commission_codes sc, cmsn_rebate_codes rc, account_kind ak sSql = sSql , dual sSql = sSql WHERE a.status_id=s.id and a.usa_tax_code=u.code(+) _ and a.fund_id=f.id and _ a.s_stk_cmsn_code=sc.code(+) and a.cmsn_reb_code=rc.code(+) and a.kind_id=ak.id _ and a.id = sAcctId sConnect = sConnectWSI GetSQL = Array(sConnect, sSql) -- 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). -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gene Sais 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: Help on using bind variables asp web pages
You may want to check support.microsoft.com. There are some pages there that detail the use of Oracle and ASP. Jared ARUN K C arun_k_c@hotm To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ail.com cc: Sent by: Subject: Help on using bind variables asp web pages [EMAIL PROTECTED] om 12/12/01 02:55 PM Please respond to ORACLE-L Hi, Can anybody give me some examples as to how I can use bind variables in asp pages. Here the developers are using static values for inserting and updating the data due to which the whole shared pool is filled with nothing insert and update statements. They are not able to get any documents on this issue. If anybody knows about this or point some urls,It will be of great help. Thanks in advance _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ARUN K C INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Help on using bind variables asp web pages
Hi, Can anybody give me some examples as to how I can use bind variables in asp pages. Here the developers are using static values for inserting and updating the data due to which the whole shared pool is filled with nothing insert and update statements. They are not able to get any documents on this issue. If anybody knows about this or point some urls,It will be of great help. Thanks in advance _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ARUN K C 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: Value of bind variables in tkprof
Hi, TKPROF is not designed to format the event 10046 level 4 data. It can only format the trace of event 10046 level 1 (Read: SQL Trace). If you want to format the higher level trace file you need to use *itprof* (Check www.unal-bilisim.com ) *sparky* Hotsos profiler (Check www.hotsos.com). The first one is free and Hotsos is free for their customers. Best Regards, K Gopalakrishnan Bangalore, INDIA + (91) 98451 78868 -Original Message- Sent: Wednesday, October 31, 2001 3:55 PM To: Multiple recipients of list ORACLE-L Mike - Haven't used this, so I will be interested in how you come out. If you set trace level to 4 or 12, as in alter system set timed_statistics = true alter session set events '10046 trace name context forever, level 4' Then the trace file that is produced should have your bind values. I'm not sure whether you can see them through tkprof or if you have to just directly view the trace file. Let me know how you come out. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, October 31, 2001 5:20 PM To: Multiple recipients of list ORACLE-L Hello, I am trying to debug the sql of an app that uses oracle as the back end. In order to see what the app is doing I did the following: 1) had the user log on the the application 2) Started a trace for the users session 3) Had the user go through a normal work flow (it's an order entry app) 4) After the user finished I turned the trace off 5) Used tkprof to extract the sql statements from the users session There are no problems with any of the above steps. When I look at the file I see something similar to the following: select col1,col2,col3,col4,col5 from table1 where col3 = :1 and col5 = :2 ; Is there any way to see what the actual values for ':1 and :2' are? Thanks. MIke -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Guerrero 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: DENNIS WILLIAMS 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). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan 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: Value of bind variables in tkprof
Mike - Haven't used this, so I will be interested in how you come out. If you set trace level to 4 or 12, as in alter system set timed_statistics = true alter session set events '10046 trace name context forever, level 4' Then the trace file that is produced should have your bind values. I'm not sure whether you can see them through tkprof or if you have to just directly view the trace file. Let me know how you come out. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, October 31, 2001 5:20 PM To: Multiple recipients of list ORACLE-L Hello, I am trying to debug the sql of an app that uses oracle as the back end. In order to see what the app is doing I did the following: 1) had the user log on the the application 2) Started a trace for the users session 3) Had the user go through a normal work flow (it's an order entry app) 4) After the user finished I turned the trace off 5) Used tkprof to extract the sql statements from the users session There are no problems with any of the above steps. When I look at the file I see something similar to the following: select col1,col2,col3,col4,col5 from table1 where col3 = :1 and col5 = :2 ; Is there any way to see what the actual values for ':1 and :2' are? Thanks. MIke -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Guerrero 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: DENNIS WILLIAMS 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
Bind Variables
--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 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. br brTIA br brDanny Hughes brDBA brKnobias.com br[EMAIL PROTECTED] brwww.knobias.com br --KMQGUMGTJRAMPYVFBAEF-- --HALPYZPZYFFYCGAKLKOY-- --CIVUFTDKZVQDOGJWYCGU Content-Type: application/x-pkcs7-signature; name=smime.p7s Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename=smime.p7s CONTENT-DESCRIPTION: S/MIME Cryptographic Signature MIIKkwYJKoZIhvcNAQcCoIIKhDCCCoACAQExCzAJBgUrDgMCGgUAMAsGCSqGSIb3DQEHAaCCCJ4w ggI8MIIBpaADAgECAgMD6X8wDQYJKoZIhvcNAQEEBQAwgZIxCzAJBgNVBAYTAlpBMRUwEwYDVQQI EwxXZXN0ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEPMA0GA1UEChMGVGhhd3RlMR0wGwYD VQQLExRDZXJ0aWZpY2F0ZSBTZXJ2aWNlczEoMCYGA1UEAxMfUGVyc29uYWwgRnJlZW1haWwgUlNB IDIwMDAuOC4zMDAeFw0wMTAxMDUxNTQ3MzdaFw0wMjAxMDUxNTQ3MzdaMEUxHzAdBgNVBAMTFlRo YXd0ZSBGcmVlbWFpbCBNZW1iZXIxIjAgBgkqhkiG9w0BCQEWE2RodWdoZXNAa25vYmlhcy5jb20w XDANBgkqhkiG9w0BAQEFAANLADBIAkEAn8T4J83YTOXIk3iPl8Hpg58yaB/YQk0hWUHIDe26ZCwJ P7MnmKJkyiTRP3Vl7WJdt3SOBuLYsQa4bewJrG5SHQIDAQABozAwLjAeBgNVHREEFzAVgRNkaHVn aGVzQGtub2JpYXMuY29tMAwGA1UdEwEB/wQCMAAwDQYJKoZIhvcNAQEEBQADgYEAloEUXE3MsxSe oNIMwOdM7l+j6zat3yCav2T1s06uBaus9hRgruTGI0+HzkPdjj0zJ4Q2krHO1QzKnRz24vExupK+ 2GR13ldRWLfoDphFJyvBXuI0x2AhPpC+KY04jBojgtKmwX//BoKquodT89CzcDrZaU4FRU5OG7cM Hc4q
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
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
Re: Tracing a sql statement also to include bind variables
Dear List, I couldn't find this dbms_support.start_trace_in_session(sid,serial#,TRUE,TRUE) Platform WinNT Oracle 8.1.7 Pl let me know venkat -- On Mon, 24 Sep 2001 03:05:19 Christian Trassens wrote: Execute as sys or dba user the package dbms_system.set_ev(sid,serial#,10046,12,''); Or with oradebug: oradebug setospid OSPID of the session and then oradebug event 10046 trace name context forever level 12 Or from the user: alter session set events '10046 trace name context forever, level 12'; Or with the package dbms_support: dbms_support.start_trace_in_session(sid,serial#,TRUE,TRUE) Regards. --- [EMAIL PROTECTED] wrote: Hi All, I have to trace a sql that a user generates. I also need the bind variables. Can somebody please enlighten me??? TIA Jack = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Eng. Christian Trassens Senior DBA Systems Engineer [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : 541149816062 __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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). Make a difference, help support the relief efforts in the U.S. http://clubs.lycos.com/live/events/september11.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: C.S.Venkata Subramanian INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San
RE: Tracing a sql statement also to include bind variables
That is beacuase it is not DBMS_SUPPORT, it is DBMS_SYSTEM: exec sys.dbms_system.set_sql_trace_in_session(sid,serial#,TRUE|FALSE); HTH Mark -Original Message- Subramanian Sent: Wednesday, September 26, 2001 07:45 To: Multiple recipients of list ORACLE-L Dear List, I couldn't find this dbms_support.start_trace_in_session(sid,serial#,TRUE,TRUE) Platform WinNT Oracle 8.1.7 Pl let me know venkat -- On Mon, 24 Sep 2001 03:05:19 Christian Trassens wrote: Execute as sys or dba user the package dbms_system.set_ev(sid,serial#,10046,12,''); Or with oradebug: oradebug setospid OSPID of the session and then oradebug event 10046 trace name context forever level 12 Or from the user: alter session set events '10046 trace name context forever, level 12'; Or with the package dbms_support: dbms_support.start_trace_in_session(sid,serial#,TRUE,TRUE) Regards. --- [EMAIL PROTECTED] wrote: Hi All, I have to trace a sql that a user generates. I also need the bind variables. Can somebody please enlighten me??? TIA Jack = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopiekn te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Eng. Christian Trassens Senior DBA Systems Engineer [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : 541149816062 __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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). Make a difference, help support the relief efforts in the U.S. http
RE: Tracing a sql statement also to include bind variables
WOW!! What a typo!! That should of course be *because*.. LOL.. Mark -Original Message- Sent: Wednesday, September 26, 2001 10:45 To: Multiple recipients of list ORACLE-L That is beacuase it is not DBMS_SUPPORT, it is DBMS_SYSTEM: exec sys.dbms_system.set_sql_trace_in_session(sid,serial#,TRUE|FALSE); HTH Mark -Original Message- Subramanian Sent: Wednesday, September 26, 2001 07:45 To: Multiple recipients of list ORACLE-L Dear List, I couldn't find this dbms_support.start_trace_in_session(sid,serial#,TRUE,TRUE) Platform WinNT Oracle 8.1.7 Pl let me know venkat -- On Mon, 24 Sep 2001 03:05:19 Christian Trassens wrote: Execute as sys or dba user the package dbms_system.set_ev(sid,serial#,10046,12,''); Or with oradebug: oradebug setospid OSPID of the session and then oradebug event 10046 trace name context forever level 12 Or from the user: alter session set events '10046 trace name context forever, level 12'; Or with the package dbms_support: dbms_support.start_trace_in_session(sid,serial#,TRUE,TRUE) Regards. --- [EMAIL PROTECTED] wrote: Hi All, I have to trace a sql that a user generates. I also need the bind variables. Can somebody please enlighten me??? TIA Jack = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopiekn te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Eng. Christian Trassens Senior DBA Systems Engineer [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : 541149816062 __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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
RE: Tracing a sql statement also to include bind variables
I think it comes with 8.0.4. I don't know exactly. However I enclose it one copy. Regards. --- Mark Leith [EMAIL PROTECTED] wrote: WOW!! What a typo!! That should of course be *because*.. LOL.. Mark -Original Message- Sent: Wednesday, September 26, 2001 10:45 To: Multiple recipients of list ORACLE-L That is beacuase it is not DBMS_SUPPORT, it is DBMS_SYSTEM: exec sys.dbms_system.set_sql_trace_in_session(sid,serial#,TRUE|FALSE); HTH Mark -Original Message- Subramanian Sent: Wednesday, September 26, 2001 07:45 To: Multiple recipients of list ORACLE-L Dear List, I couldn't find this dbms_support.start_trace_in_session(sid,serial#,TRUE,TRUE) Platform WinNT Oracle 8.1.7 Pl let me know venkat -- On Mon, 24 Sep 2001 03:05:19 Christian Trassens wrote: Execute as sys or dba user the package dbms_system.set_ev(sid,serial#,10046,12,''); Or with oradebug: oradebug setospid OSPID of the session and then oradebug event 10046 trace name context forever level 12 Or from the user: alter session set events '10046 trace name context forever, level 12'; Or with the package dbms_support: dbms_support.start_trace_in_session(sid,serial#,TRUE,TRUE) Regards. --- [EMAIL PROTECTED] wrote: Hi All, I have to trace a sql that a user generates. I also need the bind variables. Can somebody please enlighten me??? TIA Jack = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopiekn te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Eng. Christian Trassens Senior DBA Systems Engineer [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : 541149816062 __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego
RE: Tracing a sql statement also to include bind variables
You are right - DBMS_SUPPORT is for 7.something 8.0.5. 8i uses DBMS_SYSTEM AFAIK. List, please correct me if I'm wrong.. Mark -Original Message- Trassens Sent: Wednesday, September 26, 2001 13:55 To: Multiple recipients of list ORACLE-L I think it comes with 8.0.4. I don't know exactly. However I enclose it one copy. Regards. --- Mark Leith [EMAIL PROTECTED] wrote: WOW!! What a typo!! That should of course be *because*.. LOL.. Mark -Original Message- Sent: Wednesday, September 26, 2001 10:45 To: Multiple recipients of list ORACLE-L That is beacuase it is not DBMS_SUPPORT, it is DBMS_SYSTEM: exec sys.dbms_system.set_sql_trace_in_session(sid,serial#,TRUE|FALSE); HTH Mark -Original Message- Subramanian Sent: Wednesday, September 26, 2001 07:45 To: Multiple recipients of list ORACLE-L Dear List, I couldn't find this dbms_support.start_trace_in_session(sid,serial#,TRUE,TRUE) Platform WinNT Oracle 8.1.7 Pl let me know venkat -- On Mon, 24 Sep 2001 03:05:19 Christian Trassens wrote: Execute as sys or dba user the package dbms_system.set_ev(sid,serial#,10046,12,''); Or with oradebug: oradebug setospid OSPID of the session and then oradebug event 10046 trace name context forever level 12 Or from the user: alter session set events '10046 trace name context forever, level 12'; Or with the package dbms_support: dbms_support.start_trace_in_session(sid,serial#,TRUE,TRUE) Regards. --- [EMAIL PROTECTED] wrote: Hi All, I have to trace a sql that a user generates. I also need the bind variables. Can somebody please enlighten me??? TIA Jack = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopiekn te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Eng. Christian Trassens Senior DBA Systems Engineer [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : 541149816062 __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo
Tracing a sql statement also to include bind variables
Hi All, I have to trace a sql that a user generates. I also need the bind variables. Can somebody please enlighten me??? TIA Jack = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Tracing a sql statement also to include bind variables
Execute as sys or dba user the package dbms_system.set_ev(sid,serial#,10046,12,''); Or with oradebug: oradebug setospid OSPID of the session and then oradebug event 10046 trace name context forever level 12 Or from the user: alter session set events '10046 trace name context forever, level 12'; Or with the package dbms_support: dbms_support.start_trace_in_session(sid,serial#,TRUE,TRUE) Regards. --- [EMAIL PROTECTED] wrote: Hi All, I have to trace a sql that a user generates. I also need the bind variables. Can somebody please enlighten me??? TIA Jack = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Eng. Christian Trassens Senior DBA Systems Engineer [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : 541149816062 __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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: Tracing a sql statement also to include bind variables/SOLVED
Hi Christian, Thx. I studied the manuals this morning but the list was better once again Jack Christian Trassens [EMAIL PROTECTED]@fatcity.com on 24-09-2001 13:05:18 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Execute as sys or dba user the package dbms_system.set_ev(sid,serial#,10046,12,''); Or with oradebug: oradebug setospid OSPID of the session and then oradebug event 10046 trace name context forever level 12 Or from the user: alter session set events '10046 trace name context forever, level 12'; Or with the package dbms_support: dbms_support.start_trace_in_session(sid,serial#,TRUE,TRUE) Regards. --- [EMAIL PROTECTED] wrote: Hi All, I have to trace a sql that a user generates. I also need the bind variables. Can somebody please enlighten me??? TIA Jack = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Eng. Christian Trassens Senior DBA Systems Engineer [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : 541149816062 __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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). = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde
latch free waits on LIBRARY CACHE latch and bind variables
Can someone please explain how to monitor latch free waits on LIBRARY cache and provide examples of using bind variables. THE PL/SQL reference does not seem to make much reference to bind varaiables John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
