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
