Re: pros/cons of dedicated vs. MTS connections?

Mark Bole wrote:

> That's the whole point of a JDBC connection pool -- you never close the
> connection.

You're missing the fact that I was talking about JDBC connection pool leaking. I.e. the connection pool continues to grow, with original pooled connections not being used (and the socket handle still open). This means that the number of sessions on the Oracle side keeps on increasing.

I have seen this happen, numerous times. With more than one app server implementation. And not just Java/JDBC based app servers.

Poorly configured app servers? Poorly written app server software? I don't know, nor do I really care. I'm tasked to look after the database - and ensure availability and scalabality and performance as best possible. MTS is part of that answer.

> This is the exact same argument you use for claiming that one should
> never ever run more than one Oracle instance on a machine. Following
> this logic, you must be running all your Unix boxes in single user mode,
> since obviously multi-user mode leads to inefficient use of memory and
> kernel resources and is not scalable.

Sorry Mark, but I've lost you there. Where do I state that multi-user mode form of processing or architecture is a "Bad Thing"?

I said that when dealing with multi-user/multi-processing types of systems, dealing with shared resources are -better-. And that is a fact.

> And you must always ride on a bus
> or train, since a passenger car or bicycle does not scale very well
> either. And then there's your cozy dormitory bed, since a personal
> bedroom does not scale very well.

Poor analogy. If you are in the housing or transport business, do you want a 100 bicycles, a 100 personal bedrooms, or rather 10 double decker busses and 10 dorms, for dealing with your business?

> If you have a scalabity problem, then by all means use shared server
> (it's not called MTS anymore since it never was multi-threaded) to
> address it. If you don't have a scalability problem, then don't worry
> about it

Scalability is only a problem when you don't design and configure for it. I do not live by a rule set that is open-ended and changing continuously, depending on whether or not I, or management, or development think what the data volumes and user base will be. If ABC provides scalability and it is within the means of the project scope (time/resources/etc) to do it, then ABC -will- be done. I'm not going to leave out ABC on some vague idea of "oh, we do not need scalability here".

> (I suspect this suggestion will be lost on you, since in your
> world you appear to always and everywhere have a scalability problem).

Yes.. that must be why the SELECT COUNT on a fact table in a warehouse instance of mine takes 30 fricken seconds to run, before it gives an answer of 800+ million rows. That is a real scalability problem on that fact table.. Of course, the so-called experts here will design it differently enable that SELECT COUNT to run in 10 seconds instead?

> And since you can use either or both at the same time, there is no cost
> to starting one way and then changing later if it makes sense.

If ABC is done at the start to ensure scalability, then you're not going to run into a brick wall at a later stage and then have to scramble to get it ABC implemented.

And my approach to scalability and doing ABC as core to the initial design and configuration, is not just where ABC=MTS (aka Shared Server). ABC also means various others "scalability things".

E.g. Would you allow developers to wrote bindless SQL using literals? Even if they insist that this will only always be a 2 user database instance and there will never be shared pool fragmentation and that the hard parse overhead ito performance is negligble? Or would you insist on them using bind variables and writing shared SQL from the start?

I hate revisiting the foundation after the building is build. Be that with MTS or bind variable SQL or whatever the ABC scaling option may imply for that architecture/development/project.

Scalability is not something that you think of throwing in at a later stage ro prevent the crap from hitting the fan. You design with scalability as core criteria from the start.

Okay, maybe not you. At least that I what I (try) to do...

--
Billy