Re: Delay in connections (II)
Friday, November 16, 2001 09:47 AM
One possible diagnostic would include using the setLogWriter() of the DataSource or ConnectionCache to produce a very detailed and verbose output stream of what is happening during the operation of these objects. (Be aware there can be a lot of information dumped by these objects).
My first inclination is to think that some factors related to the host may be contributing to the problem for instance Host VM handling ( disk swaps,paging, or user resource limits ) which could certainly produce some of the symptoms you have describe and allow them to materialize in a seemingly random pattern.
I have run a test creating more than 2000 concurrent connections to preform a set of relatively trival database queries and could not produce anything more than bottlenecking issues which certainly manifest themselves as delays in creating a connections, host memory system swapping/paging, increased rollback activity in db and a sigificant increase of time to preform queries. In my test each thread was infact preforming exactly the same set of operations, the delay between thread starts was minimal, they all started within a single second, some finished in about 2 seconds while some took as long as 5 minutes to complete the same operation. (the task would normally complete in about 2 seconds under normal cirrcumstances) With the LoginTimeout() value set high enough they all completed without failure. The system's strain and the dbs strain where also obviously reflected in monitoring tools showing that a significant IO was causing some thrashing of disk and user memory. Adjustments in SGA size of DB, number of processes and sessions influenced this behavior by reducing the high end of the over all completion time. The size of the SGA defined for the DB seemed to have the greatest impact on this, the high-end was reduced to about 2 minutes in this scenerio, but the SGA setting when from 32M to 256M which was all that was available in the testing enviroment.
Monitoring of some of the db instance attributes like rollback segment growth and shrinkage ( used by read queries for managing time consistant data images), redo log activity ( is db in archive mode, if so how often are arch-logs created ,what is time requirement for this operation and the impact on other operation pending on db ), db memory statistics during normal operation under a load may help to shed light on what to look into as the cause of the problem and possible remedies.