http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10752/network.htm#10615
Does V$session* views tell you some thing?
- Binod.
Guillaume Bilodeau wrote:
>Hi, I didn't notice one of your questions. These 480,000 messages are
>happening during peak usage period, so the pool is far from being idle.
>
>Any idea?
>
>Thanks!
>GB
>
>
>Jagadish Prasath Ramu wrote:
>
>
>>On Thu, 2007-01-18 at 03:03 -0800, Guillaume Bilodeau wrote:
>>
>>
>>>Binod.Pg wrote:
>>>
>>>
>>>>Guillaume Bilodeau wrote:
>>>>
>>>>
>>>>>Hi guys,
>>>>>
>>>>>We are investigating (aside from the odd app server crash...) a
>>>>>
>>>>>
>>>huge
>>>
>>>
>>>>>performance problem in our production application. The bottleneck
>>>>>
>>>>>
>>>seems
>>>
>>>
>>>>>to
>>>>>be the database server, with its 4 CPUs constantly running at
>>>>>
>>>>>
>>>100%.
>>>
>>>
>>>>>After
>>>>>running several statspack reports, we have discovered that over a
>>>>>
>>>>>
>>>period
>>>
>>>
>>>>>of
>>>>>15 minutes with around 30 open sessions, a total of ~480,000
>>>>>
>>>>>
>>>SQL*Net
>>>
>>>
>>>>>messages are exchanged between the app server and the database.
>>>>>
>>>>>How is this related to Glassfish? We have configured the
>>>>>
>>>>>
>>>Connection Pool
>>>
>>>
>>>>>(which uses the oracle.jdbc.pool.OracleDataSource driver) to use
>>>>>Connection
>>>>>Validation using the table method. My question is: using these
>>>>>
>>>>>
>>>settings,
>>>
>>>
>>>>>could Glassfish be pinging the database so often?
>>>>>
>>>>>
>>GlassFish would validate the connection (talk to db) for every
>>connection request. Do these 480000 messages happen when your
>>application is idle (i.e., it is not using the pool) ?
>>
>>
>>>>>Thanks a lot for your insight,
>>>>>GB
>>>>>
>>>>>
>>>>>
>>>>>
>>>>Could you share the complete connection pool settings?
>>>>
>>>>- Binod.
>>>>
>>>>
>>>>
>>>>
>>>---------------------------------------------------------------------
>>>
>>>
>>>>To unsubscribe, e-mail: users-unsubscribe_at_glassfish.dev.java.net
>>>>For additional commands, e-mail: users-help_at_glassfish.dev.java.net
>>>>
>>>>
>>>>
>>>>
>>>>
>>>Sure:
>>>
>>>---
>>>
>>>General Settings
>>>Name: jdbc/PromoDS
>>>Datasource Classname: oracle.jdbc.pool.OracleDataSource
>>>Resource Type: javax.sql.DataSource
>>>Description: <empty>
>>>
>>>Pool Settings
>>>Initial and Minimum Pool Size: 10 Connections
>>>Maximum Pool Size: 100 Connections
>>>Pool Resize Quantity: 5 Connections
>>>Idle Timeout: 300 Seconds
>>>Max Wait Time: 60000 Milliseconds
>>>
>>>Connection Validation
>>>Required: Yes
>>>Validation Method: table
>>>Table Name: IP_ADDRESS
>>>On Any Failure: Nothing
>>>Allow Non Component Callers: Disabled
>>>Non Transactional Connections: Disabled
>>>
>>>Transaction Isolation
>>>Transaction Isolation: <empty>
>>>Isolation Level: No (disabled anyway)
>>>
>>>Properties
>>>user - promo
>>>password - promo
>>>url - jdbc:oracle:thin:@1.2.3.4:1521:promo
>>>xa-driver-does-not-support-non-tx-operations - true
>>>
>>>---
>>>
>>>Now that I see this I'm wondering if the resource type should be
>>>changed to
>>>javax.sql.ConnectionPoolDataSource instead?
>>>
>>>
>>>>From GlassFish's functionality wrt validation, changing DataSource type
>>will not make any difference.
>>
>>You may make idle-timeout to higher value (900 seconds) and check
>>whether that has any effect on number of SQL-NET messages.
>>
>>And, which version do you use ? V1x / V2 ?
>>
>>Thanks,
>>-Jagadish
>>
>>
>>>Thanks,
>>>GB
>>>
>>>--
>>>View this message in context:
>>>http://www.nabble.com/Connection-pool-keep-alive-flooding-database--tf3033439.html#a8428742
>>>Sent from the java.net - glassfish users mailing list archive at
>>>Nabble.com.
>>>
>>>---------------------------------------------------------------------
>>>To unsubscribe, e-mail: users-unsubscribe_at_glassfish.dev.java.net
>>>For additional commands, e-mail: users-help_at_glassfish.dev.java.net
>>>
>>>
>>>
>>>
>>---------------------------------------------------------------------
>>To unsubscribe, e-mail: users-unsubscribe_at_glassfish.dev.java.net
>>For additional commands, e-mail: users-help_at_glassfish.dev.java.net
>>
>>
>>
>>
>>
>
>
>