shared server vs. connection pooling - speed, scalability - some questions

Hi,

I'm looking for some pointers to documentation, etc. that will help us understand our options (experiences/horror stories would also be useful).

I'm a sysadmin/developer and don't have a significant oracle background.   I've been reading the Oracle Database Administrator's Guide 10g, but find it confusing - probably due to my lack of background understanding.

We're running Oracle 10.2 (on Solaris 10) currently using dedicated server with applications that are a mix of:

• Apache 1.3.33 w/mod-perl using database connection caching
• Apache 1.3.33 spawning perl scripts that open a database connection and use it until they complete
• cron entries that handle data conversion that may run for minutes to hours using a database connection

We've added several additional web servers recently and the issue of switching to shared server has come up as the number of connections grows. At any given time, few connections are active, however we've determined that response time for web pages is greatly enhanced if we use cached connections and avoid the overhead of creating a connection for each query/page.

Up to now, we've been bumping the maximum number of sessions as needed, but we know this won't scale. Shared server may be the answer - but I have some questions.

• Is there any way to avoid the connection creation with shared server? Can I still use connection caching?
• For the longer running processes, should I still maintain a dsn that allows dedicated connections?
• My reading implies that shared server will be slower, but it scales. How much slower? What is it based on?

Any suggestions appreciated. Our dba has tried implementing shared server but we ran into problems - the number of active connections grew significantly. I'm sorry I don't have any other details, though it seemed to happen during batch intensive times and I suspect from my reading that it may have been due to the cron jobs *not* using dedicated connections.

Thanks,
Jim          Received on Tue Feb 27 2007 - 11:19:52 CST