Here is an example
String query = "BEGIN DBMS_LOCK.sleep(seconds => 5.01); END;"
It is pretty obvious that you can't do "SELECT 1; SELECT 1;" is this would
result in TWO result sets.
But you can create a block and execute any number of instructions
The above example executes a stored procedure.
Filip
On 2/9/2012 9:48 AM, Pid wrote:
On 09/02/2012 16:21, Amit wrote:
Any thoughts on the first point about executing multiple SQL queries on
physical connection creation?
I have no idea if it'll work, but I'd try:
SELECT 1; SELECT 1;
If you are controlling the pool (and you are) by passing in
username/password parameters each time, then you could do it as an extra
transaction thereafter.
p
On 09-Feb-2012, at 7:05 PM, Pid<p...@pidster.com> wrote:
On 09/02/2012 12:56, amit shah wrote:
One more comment below about oracle UCP.
<snip>
The pool returns members at random, so how would you know which cached
credentials you were getting?
The credentials which are passed to the getConnection(String username,
String password) method. When we configure the same pool to be used for
multiple schema's the pool will *not *be configured with default
username
password.
OK, so you create a bunch of connections with various credentials, you
want to cache those connections and only return them if the creds match
for the new request?
So you're basically creating an uncontrolled pool per cred pair, inside
the outer pool which is controlled?
Yes right.
So why not create multiple controlled pools& not run into availability
problems?
<snip>
What overhead?
The application server and database server resources (memory, cpu etc) for
keeping the connections open?
That's a total connection count dependent metric.
So the overhead is virtually the same regardless of whether you have 5
pools or 1, if you have the same total number of connections.
For e.g. If we have 5 tenants with 5
pools configured with 10 min pool size, we would have min 50 connections
always open to the database server. This count would be for each
application server. If we had the same pool for all 5 tenants, there
would
be just 10 connections open per application server.
There's a flaw in your logic.
In your example there may be zero connections open for a given tenant
because they use a shared pool.
So you might has well have separate pools with the minimum set to 2 and
still have more connections guaranteed per tenant, and the 10 you were
aiming for.
Worse, if you hit your max with other tenants, a remaining tenant might
not be able to get a connection at all, thus failing to address one of
the key requirements in a multi-tenant system - guaranteed availability.
Probably true when all the tenants are actively used. As I said, there is
always a flexibility in the configuration to use a separate pool for a
particular tenant.
That should be the default IMO. You're asking for trouble otherwise.
Also the application can always provide a configuration flexibility to
allow a tenant to use a separate pool instead of sharing it with other
tenants (like I said above).
This flexibility is provided by the Oracle Universal Connection
Pool<http://docs.oracle.com/cd/E11882_01/java.112/e12265/toc.htm>
So if that's a better fit for your requirement, why not use it?
It provides the feature I mentioned about by has lock contention issues.
Tomcat 7 jdbc pool seems to be better and hence I was trying it out.
!
<snip>
If you are programmatically registering the pool, can you not just
register it with the MBean server yourself?
Ok I will try this and provide an update.
Cool.
p
--
[key:62590808]
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
