RE: Slow connection to Oracle 9i
On Mon, 2004-10-18 at 10:16, BAXTER, LINCOLN A wrote: Most people with experience with Oracle know that opening oracle connections is SLOW! Really? I've never noticed. Unless I'm connecting to a database over a slow WAN. Here's a connection time to a database on a laptop through an 811g network, from a linux server: time sqlplus system/[EMAIL PROTECTED] @s.sql 0.38s real 0.04s user 0.02s system The actual connection time is much faster, this login has to query some DD tables. The point is, if the connections are slow, it can be corrected, provided the bandwidth is there. This is not the correct forum to determine that. Try Oracle-L at freelists.org. Jared
Re: Slow connection to Oracle 9i
On 2004-10-21 12:01:11 -0600, Reidy, Ron wrote: The statement was Call commit (even if you have done only selects) This was what I was referring to. Ah, ok. I missed that remark in parentheses - sorry. hp -- _ | Peter J. Holzer | Shooting the users in the foot is bad. |_|_) | Sysadmin WSR / LUGA | Giving them a gun isn't. | | | [EMAIL PROTECTED]| -- Gordon Schumacher, __/ | http://www.hjp.at/ | mozilla bug #84128 pgpafUyHfAH7Y.pgp Description: PGP signature
Re: Slow connection to Oracle 9i
[Rearranged quotes for better readability] On 2004-10-19 08:23:53 -0600, Reidy, Ron wrote: BAXTER, LINCOLN A [mailto:[EMAIL PROTECTED]: Ok, then if you use Apache and mod_perl this should be easy... just open connection if you don't have it (or you get and error on it), and keep it around. Call commit (even if you have done only selects), at the end of each event instead of close. This is an **extremely** bad idea. A commit() should be issues only when necessary - the cost in the database of a commit is large and doing so in this random fashion is an invitation to other performance problems. I don't know what lincoln means with event, but if it is a single http request, then that is not a random fashion. An http request is often naturally a transaction - the user has clicked on a button or link and expects that to either work or not work, but not sort-of-work. Also, in many environments (and I believe, mod_perl is one of them) you have absolutely no guarantee that two consecutive requests from the same session will get the same database connection at the server - if you want any changes to be visible from one request to the next you have to commit at the end of the request. hp -- _ | Peter J. Holzer | Shooting the users in the foot is bad. |_|_) | Sysadmin WSR / LUGA | Giving them a gun isn't. | | | [EMAIL PROTECTED]| -- Gordon Schumacher, __/ | http://www.hjp.at/ | mozilla bug #84128 pgp1W919bE0I3.pgp Description: PGP signature
RE: Slow connection to Oracle 9i
My comments are below ... - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: Peter J. Holzer [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 20, 2004 9:10 AM To: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i [Rearranged quotes for better readability] On 2004-10-19 08:23:53 -0600, Reidy, Ron wrote: BAXTER, LINCOLN A [mailto:[EMAIL PROTECTED]: Ok, then if you use Apache and mod_perl this should be easy... just open connection if you don't have it (or you get and error on it), and keep it around. Call commit (even if you have done only selects), at the end of each event instead of close. This is an **extremely** bad idea. A commit() should be issues only when necessary - the cost in the database of a commit is large and doing so in this random fashion is an invitation to other performance problems. I don't know what lincoln means with event, but if it is a single http request, then that is not a random fashion. Maybe I misstated or was misunderstood. To clarify: Issuing commits in the manner described is (IMHO) random fashion. Commit/rollback is for transactional use; a select statement by itself is not a transaction. If it were, then when one uses SQL*Plus or iSQL*Plus, you would need to issue commits all the time. As pointed out in the link I supplied to Steve Adam's website, random commits are a bad idea. Someone else on this list (I forget who) correctly stated this may(?) only apply to high insert situations. However, there are a lot of things that go on under the hood when a commit is issued. In the case of the OP and the traffic he describes, the policy of issuing commits at the end of every script where not needed, could lead to other performance issues. I invite all to go to Steve Adam's website and read up on this. There are others in the Oracle community (Jonathon Lewis, Connor McDonald to name a couple) who also rigorously test Oracle features. If someone else has the empirical evidence that commit is cheaper than rollback and the impacts to the system are minimal, I invite you to post your test results and your data. In this way, others here can benefit from this knowledge by testing the theory and validating the results. An http request is often naturally a transaction - the user has clicked on a button or link and expects that to either work or not work, but not sort-of-work. Also, in many environments (and I believe, mod_perl is one of them) you have absolutely no guarantee that two consecutive requests from the same session will get the same database connection at the server - if you want any changes to be visible from one request to the next you have to commit at the end of the request. hp -- _ | Peter J. Holzer | Shooting the users in the foot is bad. |_|_) | Sysadmin WSR / LUGA | Giving them a gun isn't. | | | [EMAIL PROTECTED]| -- Gordon Schumacher, __/ | http://www.hjp.at/ | mozilla bug #84128 This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
Re: Slow connection to Oracle 9i
On 2004-10-21 08:51:04 -0600, Reidy, Ron wrote: My comments are below ... - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: Peter J. Holzer [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 20, 2004 9:10 AM To: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i [Rearranged quotes for better readability] On 2004-10-19 08:23:53 -0600, Reidy, Ron wrote: BAXTER, LINCOLN A [mailto:[EMAIL PROTECTED]: Ok, then if you use Apache and mod_perl this should be easy... just open connection if you don't have it (or you get and error on it), and keep it around. Call commit (even if you have done only selects), at the end of each event instead of close. This is an **extremely** bad idea. A commit() should be issues only when necessary - the cost in the database of a commit is large and doing so in this random fashion is an invitation to other performance problems. I don't know what lincoln means with event, but if it is a single http request, then that is not a random fashion. Maybe I misstated or was misunderstood. To clarify: Issuing commits in the manner described is (IMHO) random fashion. Commit/rollback is for transactional use; a select statement by itself is not a transaction. If it were, then when one uses SQL*Plus or iSQL*Plus, you would need to issue commits all the time. Where did I (or anyone else in this thread) talk about a select statement by itself? The original question was about a web application, and I believe we are still talking about web applications. If this thread took a turn I missed, I'll shut up and apologize. A web application is driven by HTTP requests. The browser sends either GET or POST requests to the web application, and the application answers these requests. By convention (and I know that there are many applications which ignore this convention) GET requests are used to retrieve information and POST requests are used to send information. Thus to process a GET request, the application may issue a number of SELECT commands against a database, process the resultset in some arbitrary manner (usually converting it into HTML, an image, or whatever) and send that to the client. Since no data was changed, no commit is necessary. (There may be side effects - for example, a counter may be incremented - which may necessitate a commit, but this is a side effect, not a direct cause of the request). To process a POST request, the application usually has to issue at least one INSERT or UPDATE command. It may also have to issue a number of SELECT commands. Again, a result is sent back to the browser. The important thing is that when the user sees the result, he expects the effect of the POST request to be permanent. He has clicked on the submit button of a form, and the result should be either the information has been processed or an error occured. It should not be I got your information, but I may choose to throw it away at some arbitrary point in the future. Thus from the application-logic of a web application, a POST request is a transaction. It may issue 50 selects, 20 inserts and 30 updates, but before it sends back the result to the user, it has to either commit or rollback. There are some cases where a logical transaction spans several HTTP requests. You may want to start a transaction, then select some data, present that to the user, get some input from the user, and finally ask the user to commit or roll back. The straightforward way would be to implement it in this fashion. Unfortunately, in many web application environments, you can't. For example, if you use Apache mod_perl, each HTTP request will be routed randomly to one of the running Apache processes (and also new processes will be created if necessary, and old processes will die if unused for some time). Thus each session to the database will get completely unrelated requests; and requests from the same user session will use different database sessions. So, as an application programmer writing for this environment, you will still have to commit after every POST request because otherwise 1) the next request simply won't see the result in the database and 2) an error in a completely unrelated request may cause a rollback and delete data. As pointed out in the link I supplied to Steve Adam's website, random commits are a bad idea. I agree with this. But a commit at the end of each HTTP request (which changed any data) isn't random. It is pretty much required by the structure of web applications. hp -- _ | Peter J. Holzer | Shooting the users in the foot is bad. |_|_) | Sysadmin WSR / LUGA | Giving them a gun isn't. | | | [EMAIL PROTECTED]| -- Gordon Schumacher, __/ | http://www.hjp.at/ | mozilla bug #84128 pgpZiU3r6UgrZ.pgp Description: PGP signature
RE: Slow connection to Oracle 9i
The statement was Call commit (even if you have done only selects) This was what I was referring to. - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: Peter J. Holzer [mailto:[EMAIL PROTECTED] Sent: Thursday, October 21, 2004 10:20 AM To: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On 2004-10-21 08:51:04 -0600, Reidy, Ron wrote: My comments are below ... - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: Peter J. Holzer [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 20, 2004 9:10 AM To: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i [Rearranged quotes for better readability] On 2004-10-19 08:23:53 -0600, Reidy, Ron wrote: BAXTER, LINCOLN A [mailto:[EMAIL PROTECTED]: Ok, then if you use Apache and mod_perl this should be easy... just open connection if you don't have it (or you get and error on it), and keep it around. Call commit (even if you have done only selects), at the end of each event instead of close. This is an **extremely** bad idea. A commit() should be issues only when necessary - the cost in the database of a commit is large and doing so in this random fashion is an invitation to other performance problems. I don't know what lincoln means with event, but if it is a single http request, then that is not a random fashion. Maybe I misstated or was misunderstood. To clarify: Issuing commits in the manner described is (IMHO) random fashion. Commit/rollback is for transactional use; a select statement by itself is not a transaction. If it were, then when one uses SQL*Plus or iSQL*Plus, you would need to issue commits all the time. Where did I (or anyone else in this thread) talk about a select statement by itself? The original question was about a web application, and I believe we are still talking about web applications. If this thread took a turn I missed, I'll shut up and apologize. A web application is driven by HTTP requests. The browser sends either GET or POST requests to the web application, and the application answers these requests. By convention (and I know that there are many applications which ignore this convention) GET requests are used to retrieve information and POST requests are used to send information. Thus to process a GET request, the application may issue a number of SELECT commands against a database, process the resultset in some arbitrary manner (usually converting it into HTML, an image, or whatever) and send that to the client. Since no data was changed, no commit is necessary. (There may be side effects - for example, a counter may be incremented - which may necessitate a commit, but this is a side effect, not a direct cause of the request). To process a POST request, the application usually has to issue at least one INSERT or UPDATE command. It may also have to issue a number of SELECT commands. Again, a result is sent back to the browser. The important thing is that when the user sees the result, he expects the effect of the POST request to be permanent. He has clicked on the submit button of a form, and the result should be either the information has been processed or an error occured. It should not be I got your information, but I may choose to throw it away at some arbitrary point in the future. Thus from the application-logic of a web application, a POST request is a transaction. It may issue 50 selects, 20 inserts and 30 updates, but before it sends back the result to the user, it has to either commit or rollback. There are some cases where a logical transaction spans several HTTP requests. You may want to start a transaction, then select some data, present that to the user, get some input from the user, and finally ask the user to commit or roll back. The straightforward way would be to implement it in this fashion. Unfortunately, in many web application environments, you can't. For example, if you use Apache mod_perl, each HTTP request will be routed randomly to one of the running Apache processes (and also new processes will be created if necessary, and old processes will die if unused for some time). Thus each session to the database will get completely unrelated requests; and requests from the same user session will use different database sessions. So, as an application programmer writing for this environment, you will still have to commit after every POST request because otherwise 1) the next request simply won't see the result in the database and 2) an error in a completely unrelated request may cause a rollback and delete data. As pointed out in the link I supplied to Steve Adam's website, random commits are a bad idea. I agree with this. But a commit at the end of each HTTP request (which changed any data) isn't random. It is pretty much required by the structure of web applications. hp -- _ | Peter J. Holzer | Shooting the users
Re: Slow connection to Oracle 9i
It's time to end this thread now - or take it to an Oracle specific mailing list like oracle-l. Thanks. Tim.
Re: Slow connection to Oracle 9i
Thank you to all. Your responsiveness was amazing and the responses very helpful. If anyone has any other comments or ideas, please email them to me directly. If I come across any new developments or solutions, I will forward them to Tim, unless he prefers otherwise. I may post to the Oracle-L mailing list on the www.oraclefaq.com site, that he mentions below. You can find many other Oracle lists by searching for oracle on www.freelists.org. But all of you probably knew that. Thanks. Paul It's time to end this thread now - or take it to an Oracle specific mailing list like oracle-l. Thanks. Tim. -- Sincerely, Paul Appleby
Re: Slow connection to Oracle 9i
Paul Appleby wrote:
Try measuring the time it takes to connect with SqlPlus by using
You mean DBI? (I already sent the result for sqlplus.)
Time::HiRes with the variables $time1 and $time2 both set to
gettimeofday() and the the length of time set to $time2-$time1, as follows:
$time1=gettimeofday();
your code runs here;
$time2=gettimeofday();
print $time2-$time1;
O.k., for the record:
Platform:
x86 1193 MHz, 500 MB Mem
W2K Workstation
Oracle 8.1.7.2.1
Perl 5.6.1.
CGI script:
use DBI();
use Time::HiRes();
print Content-type: text/html\n\npre;
$time1 = Time::HiRes::gettimeofday;
my $dbh = DBI-connect;
print Time::HiRes::gettimeofday - $time1, \n;
my $sth = $dbh-prepare('select * from dual');
$sth-execute;
$sth-dump_results;
Result:
0.09375
'X'
1 rows
Both - Perl/DBI and sqlplus - connect in about 1/10 sec.
Could you run the sqlplus test, please? I guess we can
exclude Perl/DBI from causing your delay.
It may help if you say more about your platform/environment.
Steffen
RE: Slow connection to Oracle 9i
Ok, then if you use Apache and mod_perl this should be easy... just open connection if you don't have it (or you get and error on it), and keep it around. Call commit (even if you have done only selects), at the end of each event instead of close. The commit is the most efficient way to ensure that you do not get a transaction too long error (can't remember exactly what its called... yes I do: Snapshot too old). Commit resets the transaction state -- for transaction consistency -- the C of ACID (Read about how oracle implements ACID) -- no it's not a recreational drug. Lincoln -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 4:54 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i No. Each user will be using the same application that logs in to the same schema. But the users are all anonymous visitors to a web site. Paul Are you logging in each user to a unique oracle schemna? If so, no hope... (other than oracle tuning per Tim's message -- pre-spawned listeners on the database can make a BIG difference ... talk to your dba's) Lincoln -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 4:39 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. -- Sincerely, Paul Appleby -- Sincerely, Paul Appleby
RE: Slow connection to Oracle 9i
Are you logging in each user to a unique oracle schemna? If so, no hope... (other than oracle tuning per Tim's message -- pre-spawned listeners on the database can make a BIG difference ... talk to your dba's) Lincoln -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 4:39 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. -- Sincerely, Paul Appleby
RE: Slow connection to Oracle 9i
OH... Col! -Original Message- From: Henri Asseily [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 5:12 PM To: '[EMAIL PROTECTED]'; Jesse, Rich Subject: Re: Slow connection to Oracle 9i I have a DBIx::HA (High Availability) module on CPAN, but I'm readying a next version real soon now that will take care of this for you. If you combine Apache+mod_perl+Apache::DBI+DBIx::HA, you'll get what you need. I'm debugging one last instance of a dbh having an ActiveKid handle when it shouldn't, and then I'll release it. It will require DBI 1.44+ (needs swap_inner_handle()). On Oct 18, 2004, at 2:05 PM, Jesse, Rich wrote: H...one thing I thought of is to check the validity of the handle, in case the Oracle instance bounces (i.e. The Oracle instance is available, but the persistent connection no longer exists). Does that automagically happen in the connect or should there be code to check for a specific error either on the connect or maybe the statement handle? Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] QuadTech, Sussex, WI USA -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:51 PM To: Paul Appleby; BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i Perhaps this'll help: http://perl.apache.org/docs/1.0/guide/ performance.html#Persistent_DB_Connections There's a link on that site to Tim Bunce's Advanced DBI talk, but it returns a 500. Tim??? Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] QuadTech, Sussex, WI USA -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:39 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Paul
RE: Slow connection to Oracle 9i
I would wrap all DB calls in a eval, and implement exception handling. If an error occurs try to close the handle (in a eval), clear it, and then the next time around, reopen it. -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 5:06 PM To: Paul Appleby; BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i H...one thing I thought of is to check the validity of the handle, in case the Oracle instance bounces (i.e. The Oracle instance is available, but the persistent connection no longer exists). Does that automagically happen in the connect or should there be code to check for a specific error either on the connect or maybe the statement handle? Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] QuadTech, Sussex, WI USA -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:51 PM To: Paul Appleby; BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i Perhaps this'll help: http://perl.apache.org/docs/1.0/guide/performance.html#Persistent_DB_Connect ions There's a link on that site to Tim Bunce's Advanced DBI talk, but it returns a 500. Tim??? Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] QuadTech, Sussex, WI USA -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:39 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Paul
Re: Slow connection to Oracle 9i
On 2004-10-18 19:57:07 -0400, Paul Appleby wrote: My CGI application will be called by different visitors to the web site at different times, but they all connect to the same database using the same connection variables, i.e. the same user/password. Are you saying the first visitor to the web site's connection can be left open for all the other visitors? How? Not for CGI, since CGI per definition invokes a new process for every request. It does work with Apache's mod_perl (as others have already noted) and fastcgi, and should also work with PPerl, SpeedyCGI, etc. These keep your process running for several requests (typically for a maximum number of requests or until a timeout), so you only have to open a new connection every once in a while. Which one is appropriate for your application depends on your application and your environment (If you aren't using Apache, you can't use mod_perl, for example). hp -- _ | Peter J. Holzer | Shooting the users in the foot is bad. |_|_) | Sysadmin WSR / LUGA | Giving them a gun isn't. | | | [EMAIL PROTECTED]| -- Gordon Schumacher, __/ | http://www.hjp.at/ | mozilla bug #84128 pgpKJOnAAORcV.pgp Description: PGP signature
Re: Slow connection to Oracle 9i
On 2004-10-18 11:48:11 -0600, Reidy, Ron wrote: Maybe Oracle does not think it is a problem, but I (and lots of others) would disagree. These issues are correctable. In the case of slow logins, it depends on what is being done when the connection is made. When making connections that are dedicated server (not MTS) a process is created. If your server is memory and/or CPU bound, this could take a long time. On a lightly loaded server, OTOH, it doesn't seem to make much difference: I just tested it on two oracle instances on identical hardware. Connecting to one with MTS took 0.059 seconds, connecting to the one without took 0.064 seconds (of course there are other differences between those databases as well, so this may not indicate anything). Invoking the script for the first time takes a lot longer (about 0.5 seconds), so I guess loading DBD::Oracle and the oracle shared libraries from disk just takes some time. Other causes could be the any after-logon triggers that may be firing. Other delays could be in finding the database and authenticating the user. We only use tnsnames.ora and plain database users here, which is probably the fastest method. If the client has to ask the oracle name server where the database is, and the database has to ask an LDAP server to authenticate the user, this will cause additional delays, which may be noticable. hp -- _ | Peter J. Holzer | Shooting the users in the foot is bad. |_|_) | Sysadmin WSR / LUGA | Giving them a gun isn't. | | | [EMAIL PROTECTED]| -- Gordon Schumacher, __/ | http://www.hjp.at/ | mozilla bug #84128 pgpu4gtxuTBRo.pgp Description: PGP signature
RE: Slow connection to Oracle 9i
This is an **extremely** bad idea. A commit() should be issues only when necessary - the cost in the database of a commit is large and doing so in this random fashion is an invitation to other performance problems. - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: BAXTER, LINCOLN A [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:05 PM To: 'Paul Appleby' Cc: '[EMAIL PROTECTED]' Subject: RE: Slow connection to Oracle 9i Ok, then if you use Apache and mod_perl this should be easy... just open connection if you don't have it (or you get and error on it), and keep it around. Call commit (even if you have done only selects), at the end of each event instead of close. The commit is the most efficient way to ensure that you do not get a transaction too long error (can't remember exactly what its called... yes I do: Snapshot too old). Commit resets the transaction state -- for transaction consistency -- the C of ACID (Read about how oracle implements ACID) -- no it's not a recreational drug. Lincoln -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 4:54 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i No. Each user will be using the same application that logs in to the same schema. But the users are all anonymous visitors to a web site. Paul Are you logging in each user to a unique oracle schemna? If so, no hope... (other than oracle tuning per Tim's message -- pre-spawned listeners on the database can make a BIG difference ... talk to your dba's) Lincoln -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 4:39 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. -- Sincerely, Paul Appleby -- Sincerely, Paul Appleby This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
RE: Slow connection to Oracle 9i
Hmm... I strongly disagree. To quote from Steve Adam's website (http://www.ixora.com.au/newsletter/2001_09.htm)... Over committed Many applications commit more frequently than necessary, and their performance suffers as a result. In isolation a commit is not a very expensive operation, but lots of unnecessary commits can nevertheless cause severe performance problems. While a few extra commits may not be noticed, the cumulative effect of thousands of extra commits is very noticeable. Try this test. Insert 1,000 rows into a test table -- first as a single transaction, and then committing after every row. Your mileage may vary, but these results on an otherwise idle system show a performance blowout of more than 100% when committing after every row. Read it and test the results for yourself. Scale it to something that fits your application's profile. I have and my conclusions agree with Steve's findings (Oracle 8.1.7.4.0, 9.2.5.0 and 10.1 all using dictionary managed TBS on UFS disks). - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: BAXTER, LINCOLN A [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 19, 2004 8:27 AM To: Reidy, Ron Subject: RE: Slow connection to Oracle 9i Actually it is CHEAP compared to a rollback, you have to reset your transaction state. Commit is the cheapest thing you can do in Oracle. The MOST expensive thing to do is Rollback. Believe me we know, from empirical experience. The reason is that oracle ASSUMES the transaction is going to be committed, and stores rollback information which must be replayed, if you rollback. -Original Message- From: Reidy, Ron [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 19, 2004 10:24 AM To: BAXTER, LINCOLN A; Paul Appleby Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i This is an **extremely** bad idea. A commit() should be issues only when necessary - the cost in the database of a commit is large and doing so in this random fashion is an invitation to other performance problems. - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: BAXTER, LINCOLN A [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:05 PM To: 'Paul Appleby' Cc: '[EMAIL PROTECTED]' Subject: RE: Slow connection to Oracle 9i Ok, then if you use Apache and mod_perl this should be easy... just open connection if you don't have it (or you get and error on it), and keep it around. Call commit (even if you have done only selects), at the end of each event instead of close. The commit is the most efficient way to ensure that you do not get a transaction too long error (can't remember exactly what its called... yes I do: Snapshot too old). Commit resets the transaction state -- for transaction consistency -- the C of ACID (Read about how oracle implements ACID) -- no it's not a recreational drug. Lincoln -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 4:54 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i No. Each user will be using the same application that logs in to the same schema. But the users are all anonymous visitors to a web site. Paul Are you logging in each user to a unique oracle schemna? If so, no hope... (other than oracle tuning per Tim's message -- pre-spawned listeners on the database can make a BIG difference ... talk to your dba's) Lincoln -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 4:39 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time
RE: Slow connection to Oracle 9i
-Original Message- From: BAXTER, LINCOLN A Sent: Tuesday, October 19, 2004 10:27 AM To: 'Reidy, Ron' Subject: RE: Slow connection to Oracle 9i Actually it is CHEAP compared to a rollback, you have to reset your transaction state. Commit is the cheapest thing you can do in Oracle. The MOST expensive thing to do is Rollback. Believe me we know, from empirical experience. The reason is that oracle ASSUMES the transaction is going to be committed, and stores rollback information which must be replayed, if you rollback. -Original Message- From: Reidy, Ron [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 19, 2004 10:24 AM To: BAXTER, LINCOLN A; Paul Appleby Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i This is an **extremely** bad idea. A commit() should be issues only when necessary - the cost in the database of a commit is large and doing so in this random fashion is an invitation to other performance problems. - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: BAXTER, LINCOLN A [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:05 PM To: 'Paul Appleby' Cc: '[EMAIL PROTECTED]' Subject: RE: Slow connection to Oracle 9i Ok, then if you use Apache and mod_perl this should be easy... just open connection if you don't have it (or you get and error on it), and keep it around. Call commit (even if you have done only selects), at the end of each event instead of close. The commit is the most efficient way to ensure that you do not get a transaction too long error (can't remember exactly what its called... yes I do: Snapshot too old). Commit resets the transaction state -- for transaction consistency -- the C of ACID (Read about how oracle implements ACID) -- no it's not a recreational drug. Lincoln -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 4:54 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i No. Each user will be using the same application that logs in to the same schema. But the users are all anonymous visitors to a web site. Paul Are you logging in each user to a unique oracle schemna? If so, no hope... (other than oracle tuning per Tim's message -- pre-spawned listeners on the database can make a BIG difference ... talk to your dba's) Lincoln -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 4:39 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. -- Sincerely, Paul Appleby -- Sincerely, Paul Appleby This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
RE: Slow connection to Oracle 9i
THis assumes a insert heavy application. I would agree with this. When we LOAD data, we commit every 1000 or every 1 rows. That is real different from a mostly read application with only occasional inserts and updates. -Original Message- From: Reidy, Ron [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 19, 2004 10:39 AM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i Hmm... I strongly disagree. To quote from Steve Adam's website (http://www.ixora.com.au/newsletter/2001_09.htm)... Over committed Many applications commit more frequently than necessary, and their performance suffers as a result. In isolation a commit is not a very expensive operation, but lots of unnecessary commits can nevertheless cause severe performance problems. While a few extra commits may not be noticed, the cumulative effect of thousands of extra commits is very noticeable. Try this test. Insert 1,000 rows into a test table -- first as a single transaction, and then committing after every row. Your mileage may vary, but these results on an otherwise idle system show a performance blowout of more than 100% when committing after every row. Read it and test the results for yourself. Scale it to something that fits your application's profile. I have and my conclusions agree with Steve's findings (Oracle 8.1.7.4.0, 9.2.5.0 and 10.1 all using dictionary managed TBS on UFS disks). - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: BAXTER, LINCOLN A [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 19, 2004 8:27 AM To: Reidy, Ron Subject: RE: Slow connection to Oracle 9i Actually it is CHEAP compared to a rollback, you have to reset your transaction state. Commit is the cheapest thing you can do in Oracle. The MOST expensive thing to do is Rollback. Believe me we know, from empirical experience. The reason is that oracle ASSUMES the transaction is going to be committed, and stores rollback information which must be replayed, if you rollback. -Original Message- From: Reidy, Ron [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 19, 2004 10:24 AM To: BAXTER, LINCOLN A; Paul Appleby Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i This is an **extremely** bad idea. A commit() should be issues only when necessary - the cost in the database of a commit is large and doing so in this random fashion is an invitation to other performance problems. - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: BAXTER, LINCOLN A [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:05 PM To: 'Paul Appleby' Cc: '[EMAIL PROTECTED]' Subject: RE: Slow connection to Oracle 9i Ok, then if you use Apache and mod_perl this should be easy... just open connection if you don't have it (or you get and error on it), and keep it around. Call commit (even if you have done only selects), at the end of each event instead of close. The commit is the most efficient way to ensure that you do not get a transaction too long error (can't remember exactly what its called... yes I do: Snapshot too old). Commit resets the transaction state -- for transaction consistency -- the C of ACID (Read about how oracle implements ACID) -- no it's not a recreational drug. Lincoln -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 4:54 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i No. Each user will be using the same application that logs in to the same schema. But the users are all anonymous visitors to a web site. Paul Are you logging in each user to a unique oracle schemna? If so, no hope... (other than oracle tuning per Tim's message -- pre-spawned listeners on the database can make a BIG difference ... talk to your dba's) Lincoln -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 4:39 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38
Re: Slow connection to Oracle 9i
Ok, I released to CPAN version 0.91 of DBIx::HA. It needs DBI 1.44 or above, and Sys::SigAction. It hasn't yet been fully indexed and propagated on CPAN, but you can get it from: http://www.cpan.org/modules/by-authors/id/H/HA/HASSEILY/DBIx-HA -0.91.tar.gz From the README: DBIx::HA is a High Availability module for DBI. It is implemented by overloading the DBI connect, prepare and execute methods and can be seamlessly used without code modification except for initialization. DBIx::HA also works seamlessly with Apache::DBI when available, and ensures that cached database handles in the Apache::DBI module are properly released when failing over. Features of DBIx::HA are: - multiple failovers Should a datasource become unavailable, queries are automatically sent to the next available datasource in a user-configured datasource stack. All subsequent queries continue to hit the failover server until reinitialized. This ensures that a failed datasource can be properly brought back online before it is put back in service. - timeouts Database calls are wrapped in user-configurable timeouts. Connect and execute timeouts are handled independently. - configurable retries Queries can be retried n times before a datasource is considered failed. - callback function A user-defined callback function can be called upon abnormal failure and disconnection from a datasource in order to perform housekeeping tasks for the user's application. - inter-process automatic failover under mod_perl Failover can be triggered for a single process or a set of processes at the application level. Specifically designed for Apache's multi-process model, if one mod_perl process triggers a failover, it is propagated to all other mod_perl processes using the same database handle. DBIx::HA was designed primarily for reliability and speed. Functionality that would compromise speed was not considered. This latest revision has not yet been fully production-tested. Use at your own risk! On Oct 18, 2004, at 2:15 PM, BAXTER, LINCOLN A wrote: OH... Col! -Original Message- From: Henri Asseily [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 5:12 PM To: '[EMAIL PROTECTED]'; Jesse, Rich Subject: Re: Slow connection to Oracle 9i I have a DBIx::HA (High Availability) module on CPAN, but I'm readying a next version real soon now that will take care of this for you. If you combine Apache+mod_perl+Apache::DBI+DBIx::HA, you'll get what you need. I'm debugging one last instance of a dbh having an ActiveKid handle when it shouldn't, and then I'll release it. It will require DBI 1.44+ (needs swap_inner_handle()). On Oct 18, 2004, at 2:05 PM, Jesse, Rich wrote: H...one thing I thought of is to check the validity of the handle, in case the Oracle instance bounces (i.e. The Oracle instance is available, but the persistent connection no longer exists). Does that automagically happen in the connect or should there be code to check for a specific error either on the connect or maybe the statement handle? Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] QuadTech, Sussex, WI USA -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:51 PM To: Paul Appleby; BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i Perhaps this'll help: http://perl.apache.org/docs/1.0/guide/ performance.html#Persistent_DB_Connections There's a link on that site to Tim Bunce's Advanced DBI talk, but it returns a 500. Tim??? Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] QuadTech, Sussex, WI USA -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:39 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Paul
RE: Slow connection to Oracle 9i
Commit is not to improve the performance of a single thread. It is to complete a transaction (i.e. set of SQL either is comitted or rolled back, no partial). Doing so also minimizes contention with other threads. Bong Tumanut DBA --- Reidy, Ron [EMAIL PROTECTED] wrote: Hmm... I strongly disagree. To quote from Steve Adam's website (http://www.ixora.com.au/newsletter/2001_09.htm)... Over committed Many applications commit more frequently than necessary, and their performance suffers as a result. In isolation a commit is not a very expensive operation, but lots of unnecessary commits can nevertheless cause severe performance problems. While a few extra commits may not be noticed, the cumulative effect of thousands of extra commits is very noticeable. Try this test. Insert 1,000 rows into a test table -- first as a single transaction, and then committing after every row. Your mileage may vary, but these results on an otherwise idle system show a performance blowout of more than 100% when committing after every row. Read it and test the results for yourself. Scale it to something that fits your application's profile. I have and my conclusions agree with Steve's findings (Oracle 8.1.7.4.0, 9.2.5.0 and 10.1 all using dictionary managed TBS on UFS disks). - Ron Reidy Lead DBA Array BioPharma, Inc. = Bong Tumanut
Re: Slow connection to Oracle 9i
On Sun, Oct 17, 2004 at 09:06:56PM -0400, Paul Appleby wrote: I have a simple Perl 5.6 test script that uses DBI and DBD::Oracle to connect to a local Oracle 9i database table and retrieve the data in the three small fields of its only two records. Why is the connection time so long and how can I shorten it? It is 3 to 4 times longer than retrieving data from a MySQL database. Only 3 to 4 times longer than retrieving data from a MySQL? You're lucky, it's often longer! :) This is the connection string: $dbh = DBI-connect(dbi:Oracle:$dbname, $user, $passwd) or die(OOPS: $DBI::errstr); It takes 2.9342188835144 seconds to connect to the database. It takes 0.0100140571594238 seconds to retrieve the data and print it. It takes almost 1 second longer using this connection string: $dbh = DBI-connect(dbi:Oracle:host=$location;sid=$dbname, $user, $passwd); I'd guess the difference is that the second is forcing a network connection (even if host is localhost). I also used d:DProf and ran the test script, and then dprofpp -u to analyze the resulting tmon.out file: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. Oracle is widely known to be slow to connect to. The usual approach is to try to stay connected rather than keep reconnecting. Oracle MTS may help but has its own set of problems. Tim.
Re: Slow connection to Oracle 9i
Paul Appleby wrote: I have a simple Perl 5.6 test script that uses DBI and DBD::Oracle to connect to a local Oracle 9i database [...] Why is the connection time so long and how can I shorten it? [...] It takes 2.9342188835144 seconds to connect to the database. How long does it take with sqlplus? E.g. on our W2K 1193 MHz server (Oracle 8.1.7.2.1): timethis echo exit | sqlplus -s / TimeThis : Command Line : echo exit | sqlplus -s / TimeThis :Start Time : Mon Oct 18 10:17:15 2004 TimeThis : Command Line : echo exit | sqlplus -s / TimeThis :Start Time : Mon Oct 18 10:17:15 2004 TimeThis : End Time : Mon Oct 18 10:17:15 2004 TimeThis : Elapsed Time : 00:00:00.109 Steffen
Re: Slow connection to Oracle 9i
Well, do you know what cause that delay? Between an Oracle client and Database Server? - Original Message - From: Steffen Goeldner [EMAIL PROTECTED] To: Paul Appleby [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, October 18, 2004 11:34 AM Subject: Re: Slow connection to Oracle 9i Paul Appleby wrote: I have a simple Perl 5.6 test script that uses DBI and DBD::Oracle to connect to a local Oracle 9i database [...] Why is the connection time so long and how can I shorten it? [...] It takes 2.9342188835144 seconds to connect to the database. How long does it take with sqlplus? E.g. on our W2K 1193 MHz server (Oracle 8.1.7.2.1): timethis echo exit | sqlplus -s / TimeThis : Command Line : echo exit | sqlplus -s / TimeThis :Start Time : Mon Oct 18 10:17:15 2004 TimeThis : Command Line : echo exit | sqlplus -s / TimeThis :Start Time : Mon Oct 18 10:17:15 2004 TimeThis : End Time : Mon Oct 18 10:17:15 2004 TimeThis : Elapsed Time : 00:00:00.109 Steffen
Re: Slow connection to Oracle 9i
Tim, It is 3 to 4 times longer than retrieving data from a MySQL database. Only 3 to 4 times longer than retrieving data from a MySQL? You're lucky, it's often longer! :) That was 3 to 4 times longer using the data from d:DProf. But it's 20 times longer using Time::HiRes to measure the time it takes to connect to the database. I guess I'm not so lucky after all. It takes 2.9342188835144 seconds to connect to the database. It takes 0.0100140571594238 seconds to retrieve the data and print it. It takes almost 1 second longer using this connection string: $dbh = DBI-connect(dbi:Oracle:host=$location;sid=$dbname, $user, $passwd); I'd guess the difference is that the second is forcing a network connection (even if host is localhost). DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. -- Sincerely, Paul Appleby
Re: Slow connection to Oracle 9i
On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim.
RE: Slow connection to Oracle 9i
Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim.
Re: Slow connection to Oracle 9i
Steffan, Try measuring the time it takes to connect with SqlPlus by using Time::HiRes with the variables $time1 and $time2 both set to gettimeofday() and the the length of time set to $time2-$time1, as follows: $time1=gettimeofday(); your code runs here; $time2=gettimeofday(); print $time2-$time1; Paul Paul Appleby wrote: I have a simple Perl 5.6 test script that uses DBI and DBD::Oracle to connect to a local Oracle 9i database [...] Why is the connection time so long and how can I shorten it? [...] It takes 2.9342188835144 seconds to connect to the database. How long does it take with sqlplus? E.g. on our W2K 1193 MHz server (Oracle 8.1.7.2.1): timethis echo exit | sqlplus -s / TimeThis : Command Line : echo exit | sqlplus -s / TimeThis :Start Time : Mon Oct 18 10:17:15 2004 TimeThis : Command Line : echo exit | sqlplus -s / TimeThis :Start Time : Mon Oct 18 10:17:15 2004 TimeThis : End Time : Mon Oct 18 10:17:15 2004 TimeThis : Elapsed Time : 00:00:00.109 Steffen -- Sincerely, Paul Appleby
RE: Slow connection to Oracle 9i
Maybe Oracle does not think it is a problem, but I (and lots of others) would disagree. These issues are correctable. In the case of slow logins, it depends on what is being done when the connection is made. When making connections that are dedicated server (not MTS) a process is created. If your server is memory and/or CPU bound, this could take a long time. Other causes could be the any after-logon triggers that may be firing. I would suggest: 1. Read the DBD::Oracle docs. There is a section (albeit, dated) that describes how one might make connection times faster. 2. Read the Net Services Admin Guide (http://download-west.oracle.com/docs/cd/B10501_01/network.920/a96580.pdf) Investigate the use of the PRE_SPAWNED and QUEUESIZE SQL*Net parameters. I have found these very useful in several environments I have worked in. 3. If still you are still not satisfied, open a TAR with Oracle support. - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: BAXTER, LINCOLN A [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 11:17 AM To: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
Re: Slow connection to Oracle 9i
On Mon, Oct 18, 2004 at 11:48:11AM -0600, Reidy, Ron wrote: 1. Read the DBD::Oracle docs. There is a section (albeit, dated) that describes how one might make connection times faster. 2. Read the Net Services Admin Guide (http://download-west.oracle.com/docs/cd/B10501_01/network.920/a96580.pdf) Investigate the use of the PRE_SPAWNED and QUEUESIZE SQL*Net parameters. I have found these very useful in several environments I have worked in. Patches welcome! A new DBD::Oracle release is close. (Honest!) Tim. 3. If still you are still not satisfied, open a TAR with Oracle support. - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: BAXTER, LINCOLN A [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 11:17 AM To: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
RE: Slow connection to Oracle 9i
My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. -- Sincerely, Paul Appleby
RE: Slow connection to Oracle 9i
Perhaps this'll help: http://perl.apache.org/docs/1.0/guide/performance.html#Persistent_DB_Connections There's a link on that site to Tim Bunce's Advanced DBI talk, but it returns a 500. Tim??? Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] QuadTech, Sussex, WI USA -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:39 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Paul
RE: Slow connection to Oracle 9i
No. Each user will be using the same application that logs in to the same schema. But the users are all anonymous visitors to a web site. Paul Are you logging in each user to a unique oracle schemna? If so, no hope... (other than oracle tuning per Tim's message -- pre-spawned listeners on the database can make a BIG difference ... talk to your dba's) Lincoln -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 4:39 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. -- Sincerely, Paul Appleby -- Sincerely, Paul Appleby
RE: Slow connection to Oracle 9i
Look at using Apache::DBI for persistent connections. - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 2:39 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. -- Sincerely, Paul Appleby This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
RE: Slow connection to Oracle 9i
H...one thing I thought of is to check the validity of the handle, in case the Oracle instance bounces (i.e. The Oracle instance is available, but the persistent connection no longer exists). Does that automagically happen in the connect or should there be code to check for a specific error either on the connect or maybe the statement handle? Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] QuadTech, Sussex, WI USA -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:51 PM To: Paul Appleby; BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i Perhaps this'll help: http://perl.apache.org/docs/1.0/guide/performance.html#Persistent_DB_Connections There's a link on that site to Tim Bunce's Advanced DBI talk, but it returns a 500. Tim??? Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] QuadTech, Sussex, WI USA -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:39 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Paul
Re: Slow connection to Oracle 9i
I have a DBIx::HA (High Availability) module on CPAN, but I'm readying a next version real soon now that will take care of this for you. If you combine Apache+mod_perl+Apache::DBI+DBIx::HA, you'll get what you need. I'm debugging one last instance of a dbh having an ActiveKid handle when it shouldn't, and then I'll release it. It will require DBI 1.44+ (needs swap_inner_handle()). On Oct 18, 2004, at 2:05 PM, Jesse, Rich wrote: H...one thing I thought of is to check the validity of the handle, in case the Oracle instance bounces (i.e. The Oracle instance is available, but the persistent connection no longer exists). Does that automagically happen in the connect or should there be code to check for a specific error either on the connect or maybe the statement handle? Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] QuadTech, Sussex, WI USA -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:51 PM To: Paul Appleby; BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i Perhaps this'll help: http://perl.apache.org/docs/1.0/guide/ performance.html#Persistent_DB_Connections There's a link on that site to Tim Bunce's Advanced DBI talk, but it returns a 500. Tim??? Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] QuadTech, Sussex, WI USA -Original Message- From: Paul Appleby [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 3:39 PM To: BAXTER, LINCOLN A Cc: [EMAIL PROTECTED] Subject: RE: Slow connection to Oracle 9i My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Paul
Re: Slow connection to Oracle 9i
On Mon, Oct 18, 2004 at 04:39:25PM -0400, Paul Appleby wrote: My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Apart from the other (good) advice here, which you should follow first, it is possible, with DBD::Oracle, to 'reauthenticate' a $dbh for a different user. See the reauthenticate method in the docs. Tim. Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. -- Sincerely, Paul Appleby
Re: Slow connection to Oracle 9i
No reauthenticate() method in DBD:Oracle v 1.12 or in the ActiveState Perl 5.6 docs. Is there a Windows IIS equivalent for the Apache::DBI for persistent connections? Is there a way to configure or set Oracle form its Enterprise Management Console to establish a persistent connection to a particular table in a particular schema for a particular user? Paul On Mon, Oct 18, 2004 at 04:39:25PM -0400, Paul Appleby wrote: My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Apart from the other (good) advice here, which you should follow first, it is possible, with DBD::Oracle, to 'reauthenticate' a $dbh for a different user. See the reauthenticate method in the docs. Tim. Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. -- Sincerely, Paul Appleby -- Sincerely, Paul Appleby
Re: Slow connection to Oracle 9i
I realize I may not have been totally clear in my earlier post, which should have said: My CGI application will be called by different visitors to the web site at different times, but they all connect to the same database using the same connection variables, i.e. the same user/password. Are you saying the first visitor to the web site's connection can be left open for all the other visitors? How? Perhaps, though you understand it as such without this clarification. Paul On Mon, Oct 18, 2004 at 04:39:25PM -0400, Paul Appleby wrote: My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Apart from the other (good) advice here, which you should follow first, it is possible, with DBD::Oracle, to 'reauthenticate' a $dbh for a different user. See the reauthenticate method in the docs. Tim. Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. -- Sincerely, Paul Appleby -- Sincerely, Paul Appleby
Re: Slow connection to Oracle 9i
Paul, Without knowing much about Oracle (although I can talk your ear off about Sybase), you want to cache connections to the server and set a limit (maybe its 1!) and use this pool of connections to connect to your server. Major advantage: No overhead for creating the connection on startup. Major disadvantage: Need to goto java afaik. Apache has some modules written for it to do something like this, hit google. There maybe a dbi module that I am unaware of that can provide this capability to your webserver via cgi/bin perl. Hit CPAN search. Paul Appleby wrote on 10/18/2004, 7:57 PM: I realize I may not have been totally clear in my earlier post, which should have said: My CGI application will be called by different visitors to the web site at different times, but they all connect to the same database using the same connection variables, i.e. the same user/password. Are you saying the first visitor to the web site's connection can be left open for all the other visitors? How? Perhaps, though you understand it as such without this clarification. Paul On Mon, Oct 18, 2004 at 04:39:25PM -0400, Paul Appleby wrote: My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Apart from the other (good) advice here, which you should follow first, it is possible, with DBD::Oracle, to 'reauthenticate' a $dbh for a different user. See the reauthenticate method in the docs. Tim. Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. -- Sincerely, Paul Appleby -- Sincerely, Paul Appleby -- Your Friendly Neighborhood DBA, Chuck
Re: Slow connection to Oracle 9i
Please read the documentation for Apache::DBI. In Apache 1.3 and mod_perl 1.xx, when using Apache::DBI in conjunction with DBI you get the following: Each Apache process runs one perl instance. Each perl instance loads up one instance of Apache::DBI. Each instance of Apache::DBI automatically 'caches' all DBI connections using the connect params as the key. So for example, let's say that you set up Apache to have a maximum of 50 clients, with an average of 30 clients active at any one time. Suppose also that you will be connecting to 2 databases, with the following params: DBI-connect(dbi:Oracle:database=db_A;server=serv_A,'user1','pass1', \%params1); DBI-connect(dbi:Oracle:database=db_B;server=serv_B,'user1','pass1', \%params2); Then you will get a maximum of 50 open connections on server A, and 50 open connections on server B. Your average connections will be 30 on each server. Each Apache child will connect once to each database server, so you have 2 db connections per child. Each connection will stay open for a very long time, and only die when the Apache child dies. If you don't use Apache::DBI, then you will open and close connects every time a script or handler fires, and that's not fun. For Apache 2.0 and mod_perl 2.xx, things are more complicated and I only know how it was supposed to work (i.e. I don't know the current state of things). On Oct 18, 2004, at 5:02 PM, Chuck Fox wrote: Paul, Without knowing much about Oracle (although I can talk your ear off about Sybase), you want to cache connections to the server and set a limit (maybe its 1!) and use this pool of connections to connect to your server. Major advantage: No overhead for creating the connection on startup. Major disadvantage: Need to goto java afaik. Apache has some modules written for it to do something like this, hit google. There maybe a dbi module that I am unaware of that can provide this capability to your webserver via cgi/bin perl. Hit CPAN search. Paul Appleby wrote on 10/18/2004, 7:57 PM: I realize I may not have been totally clear in my earlier post, which should have said: My CGI application will be called by different visitors to the web site at different times, but they all connect to the same database using the same connection variables, i.e. the same user/password. Are you saying the first visitor to the web site's connection can be left open for all the other visitors? How? Perhaps, though you understand it as such without this clarification. Paul On Mon, Oct 18, 2004 at 04:39:25PM -0400, Paul Appleby wrote: My CGI application will be called by different users at different times. Are you saying the first user's connection can be left open for all the other users? How? Apart from the other (good) advice here, which you should follow first, it is possible, with DBD::Oracle, to 'reauthenticate' a $dbh for a different user. See the reauthenticate method in the docs. Tim. Paul Most people with experience with Oracle know that opening oracle connections is SLOW! Oracle does not appear to consider that a problem, just like they do not consider slow performance for doing DDL a problem Applications that require near real time (OLTP) response times open connections once, and hold open oracle connections across transactions. This is true regardless of the language on the client side. That is why, for instance, Websphere caches pooled connections in the java world. Lincoln -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 12:06 PM To: Paul Appleby Cc: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i On Mon, Oct 18, 2004 at 10:38:23AM -0400, Paul Appleby wrote: DBD::Oracle::dr::load_dbnames is only called by data_sources() so don't call data_sources() unless you really need to. I really do need to call data_sources() but the time it takes to retrieve data, as shown above, using Time::HiRes is only 0.0100140571594238 seconds. So that's not the issue. dprofpp showed it to take approx the same time as login: %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN Anyway, I think there's little you can do from DBI to make Oracle connections faster than you already have. Look to changes on the Oracle side - for which other mailing lists (such as oracle-l) are more suitable. Tim. -- Sincerely, Paul Appleby -- Sincerely, Paul Appleby -- Your Friendly Neighborhood DBA, Chuck
Slow connection to Oracle 9i
I have a simple Perl 5.6 test script that uses DBI and DBD::Oracle to connect to a local Oracle 9i database table and retrieve the data in the three small fields of its only two records. Why is the connection time so long and how can I shorten it? It is 3 to 4 times longer than retrieving data from a MySQL database. Using the Time::HiRes module, I am able to show how long it takes to connect to the database, and how long it takes to return the two records. This is the connection string: $dbh = DBI-connect(dbi:Oracle:$dbname, $user, $passwd) or die(OOPS: $DBI::errstr); It takes 2.9342188835144 seconds to connect to the database. It takes 0.0100140571594238 seconds to retrieve the data and print it. It takes almost 1 second longer using this connection string: $dbh = DBI-connect(dbi:Oracle:host=$location;sid=$dbname, $user, $passwd); I also used d:DProf and ran the test script, and then dprofpp -u to analyze the resulting tmon.out file: Total Elapsed Time = -0.00189 Seconds User Time = 0.416244 Seconds Exclusive Times %Time ExclSec CumulS #Calls sec/call Csec/c Name 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN 12.0 0.050 0.050 11 0.0045 0.0045 Exporter::import 4.80 0.020 0.050 10 0.0020 0.0050 DBI::BEGIN 2.40 0.010 0.010116 0.0001 0.0001 DBI::_install_method 2.40 0.010 0.010 3 0.0033 0.0033 Carp::longmess_heavy 2.40 0.010 0.010 2 0.0050 0.0050 Exporter::export_ok_tags 2.40 0.010 0.010 10 0.0010 0.0010 strict::import 2.40 0.010 0.010 1 0.0100 0.0100 vars::BEGIN 2.40 0.010 0.020 2 0.0050 0.0100 DynaLoader::BEGIN 2.40 0.010 0.010 18 0.0006 0.0005 Win32::TieRegistry::BEGIN 0.00 0.000 -0.000 1 0. - warnings::BEGIN 0.00 0.000 -0.000 2 0. - warnings::register::import 0.00 0.000 -0.000 4 0. - warnings::register::mkMask Sincerely, Paul Appleby
