Thanks for the response. The below suggestion to make an SP call worked out.
Another update on programmatic registering the pool to jmx - It worked out and here is how I did it MBeanServer mBeanServer = ManagementFactory.getPlatformMBeanServer(); try { tomcatDataSource.createPool(); mBeanServer.registerMBean(tomcatDataSource.getPool().getJmxPool(), new ObjectName("tomcat.jdbc:type=ConnectionPool")); } catch (Exception e) { throw new RuntimeException("Error registering the tomcat pool data source", e); } Thanks for the quick support on my queries. On Tue, Feb 14, 2012 at 3:42 AM, Filip Hanik - Dev Lists <devli...@hanik.com > wrote: > 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<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-unsubscribe@tomcat.**apache.org<users-unsubscr...@tomcat.apache.org> >>> For additional commands, e-mail: users-h...@tomcat.apache.org >>> >>> >> > > ------------------------------**------------------------------**--------- > To unsubscribe, e-mail: > users-unsubscribe@tomcat.**apache.org<users-unsubscr...@tomcat.apache.org> > For additional commands, e-mail: users-h...@tomcat.apache.org > >