SEO

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

If your apps create a new connection for every query - then you
already know your biggest performance and scalability issue.

Shared server works very well for transaction processing type
connections. Remember the connection pool is built at the time the
session is created. As long as no connection in the pool actively
uses the connection for any lengthy duration, every thing works
well.

What you probably discovered in your earlier implementation effort is
that shared server does not work at all well if any one connection in
the pool is active for any duration of more than 1 second. All the
other connections in the pool are effectively shut off from database
resources while they wait for the long running transaction to
complete. Batch processes (including any reports that run more than
1-2 seconds) should use dedicated connections.

You can use the sqlnet.ora file to set the shared or dedicated
connection mode, or you can define multiple service names for the
database to allow most connections to use shared server and your batch
and reporting processes to run on dedicated connections.

In my experience, a good shared server implementation (where all the
long running processes are correctly running on dedicated connections)
has better overall performance than a dedicated server setup that is
nearly at the hardware and OS limits.

BTW - you don't state how many connections are driving the shared
server consideration. Many DBAs do not even consider shared server
until at least 1,000 concurrent connections are reached - assuming
adequate RAM is available on a dedicated database server. You won't
find as many articles on shared server as you could find on the former
MTS (multi threaded server). Cheaper RAM, high OS process limits, and
application server connection pooling have made this option much less
needed than it was when 8i was the latest and greatest.

MTNorman wrote:
> If your apps create a new connection for every query - then you
> already know your biggest performance and scalability issue.

Yes. That's why we went to mod-perl with apache in order to cache
connections. This allows us to re-use a connection for the lifetime of
the apache child process - many pages without having to create a connection.

> Shared server works very well for transaction processing type
> connections. Remember the connection pool is built at the time the
> session is created. As long as no connection in the pool actively
> uses the connection for any lengthy duration, every thing works
> well.
>
> What you probably discovered in your earlier implementation effort is
> that shared server does not work at all well if any one connection in
> the pool is active for any duration of more than 1 second. All the
> other connections in the pool are effectively shut off from database
> resources while they wait for the long running transaction to
> complete. Batch processes (including any reports that run more than
> 1-2 seconds) should use dedicated connections.

Yes, I found references to this in my reading. I intend to discuss this
with our dba.

> You can use the sqlnet.ora file to set the shared or dedicated
> connection mode, or you can define multiple service names for the
> database to allow most connections to use shared server and your batch
> and reporting processes to run on dedicated connections.
>
> In my experience, a good shared server implementation (where all the
> long running processes are correctly running on dedicated connections)
> has better overall performance than a dedicated server setup that is
> nearly at the hardware and OS limits.
>
> BTW - you don't state how many connections are driving the shared
> server consideration. Many DBAs do not even consider shared server
> until at least 1,000 concurrent connections are reached - assuming
> adequate RAM is available on a dedicated database server. You won't
> find as many articles on shared server as you could find on the former
> MTS (multi threaded server). Cheaper RAM, high OS process limits, and
> application server connection pooling have made this option much less
> needed than it was when 8i was the latest and greatest.

We've upped the max connections to somewhere around 1500. We only have
a problem when something causes the database to respond slowly
(conflicts, bad indices, etc.) As requests to the database back up, the
web server processes have to wait, which causes more web server
processes to be spawned to handle page requests, which leads to more
database requests and eventually, things go to hell :-)

Thanks for your comments. They are quite useful to help me understand
what I'm dealing with.

Jim

On Feb 27, 11:06 pm, Jim Hayter <see.reply...@nowhere.invalid> wrote:
> MTNorman wrote:

First note is that the comments that MTNorman already provided are
exactly on target.

> > If your apps create a new connection for every query - then you
> > already know your biggest performance and scalability issue.
>
> Yes. That's why we went to mod-perl with apache in order to cache
> connections. This allows us to re-use a connection for the lifetime of
> the apache child process - many pages without having to create a connection.
>
> > Shared server works very well for transaction processing type
> > connections. Remember the connection pool is built at the time the
> > session is created. As long as no connection in the pool actively
> > uses the connection for any lengthy duration, every thing works
> > well.
>
> > What you probably discovered in your earlier implementation effort is
> > that shared server does not work at all well if any one connection in
> > the pool is active for any duration of more than 1 second. All the
> > other connections in the pool are effectively shut off from database
> > resources while they wait for the long running transaction to
> > complete. Batch processes (including any reports that run more than
> > 1-2 seconds) should use dedicated connections.
>
> Yes, I found references to this in my reading. I intend to discuss this
> with our dba.

It is very common to have a special config for batch dedicated server
sessions. If the batch is running directly on the database server you
will want to make sure it is using bequeath type connections. Often
this is done by exporting the ORACLE_SID value to specify a connection
string ... whether that results in bequeath connections varies
somewhat by database release level and operating system.

>
> > You can use the sqlnet.ora file to set the shared or dedicated
> > connection mode, or you can define multiple service names for the
> > database to allow most connections to use shared server and your batch
> > and reporting processes to run on dedicated connections.
>
> > In my experience, a good shared server implementation (where all the
> > long running processes are correctly running on dedicated connections)
> > has better overall performance than a dedicated server setup that is
> > nearly at the hardware and OS limits.

Perhaps you have seen a good shared server implementation. Possible
yes ... likely ... maybe not.

>
> > BTW - you don't state how many connections are driving the shared
> > server consideration. Many DBAs do not even consider shared server
> > until at least 1,000 concurrent connections are reached - assuming
> > adequate RAM is available on a dedicated database server. You won't
> > find as many articles on shared server as you could find on the former
> > MTS (multi threaded server). Cheaper RAM, high OS process limits, and
> > application server connection pooling have made this option much less
> > needed than it was when 8i was the latest and greatest.
>
> We've upped the max connections to somewhere around 1500. We only have
> a problem when something causes the database to respond slowly
> (conflicts, bad indices, etc.) As requests to the database back up, the
> web server processes have to wait, which causes more web server
> processes to be spawned to handle page requests, which leads to more
> database requests and eventually, things go to hell :-)

Depending on hardware and memory config 1500 connections is still
relatively modest. I personally don't find much here so far to argue
for shared server.

"Only have a problem when something causes the database to respond
slowly " ...

Is there enough cpu capacity on the machine? Multiple things running
simultaneously for a while demands multiple cpu's for efficient
process ... that's one thing to check out. How many exactly ...
varies by application load ... etc.

Conflicts sounds like locking problems? That's in the application
design area and if you are having problems limiting scalability there
that can be more of a problem than the type of connection into the
database.

>
> Thanks for your comments. They are quite useful to help me understand
> what I'm dealing with.
>
> Jim- Hide quoted text -
>
> - Show quoted text -