RE: Slow connection to Oracle 9i
From: Lincoln A Baxter (lincoln.a.baxter_at_bankofamerica.com)
Date: 10/19/04
- Next message: Michael Peppler: "Re: Strange problems with DBD::Sybase and a simple query"
- Previous message: Lincoln A Baxter: "RE: Slow connection to Oracle 9i"
- Maybe in reply to: Lincoln A Baxter: "RE: Slow connection to Oracle 9i"
- Next in thread: Ron Reidy: "RE: Slow connection to Oracle 9i"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Tue, 19 Oct 2004 10:51:56 -0400 To: "'Reidy, Ron'" <Ron.Reidy@arraybiopharma.com>, "BAXTER, LINCOLN A" <lincoln.a.baxter@bankofamerica.com>
THis assumes a insert heavy application. I would agree with this.
When we LOAD data, we commit every 1000 or every 10000 rows. That is real
different from a mostly read application with only occasional inserts and
updates.
-----Original Message-----
From: Reidy, Ron [mailto:Ron.Reidy@arraybiopharma.com]
Sent: Tuesday, October 19, 2004 10:39 AM
To: BAXTER, LINCOLN A
Cc: dbi-users@perl.org
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:lincoln.a.baxter@bankofamerica.com]
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:Ron.Reidy@arraybiopharma.com]
Sent: Tuesday, October 19, 2004 10:24 AM
To: BAXTER, LINCOLN A; Paul Appleby
Cc: dbi-users@perl.org
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:lincoln.a.baxter@bankofamerica.com]
Sent: Monday, October 18, 2004 3:05 PM
To: 'Paul Appleby'
Cc: 'dbi-users@perl.org'
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:007@pathcom.com]
Sent: Monday, October 18, 2004 4:54 PM
To: BAXTER, LINCOLN A
Cc: dbi-users@perl.org
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:007@pathcom.com]
>Sent: Monday, October 18, 2004 4:39 PM
>To: BAXTER, LINCOLN A
>Cc: dbi-users@perl.org
>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:Tim.Bunce@pobox.com]
>>Sent: Monday, October 18, 2004 12:06 PM
>>To: Paul Appleby
>>Cc: dbi-users@perl.org
>>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. 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.
- Next message: Michael Peppler: "Re: Strange problems with DBD::Sybase and a simple query"
- Previous message: Lincoln A Baxter: "RE: Slow connection to Oracle 9i"
- Maybe in reply to: Lincoln A Baxter: "RE: Slow connection to Oracle 9i"
- Next in thread: Ron Reidy: "RE: Slow connection to Oracle 9i"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
