SmartConnector for Oracle DB connection persistence / multiple idle connection issue

[adinnyes]

Hi,

After we've upgraded the SmartConnector to version 7.1.2, Oracle DBAs reported that there are 5 concurrent connections per connector from which one is active and four seems to be idle. This is making performance impact on DB side.

Based on the information we received from support, from connector version 7.1.2 :

"Arcsight leaves the optimizations and the management of the connection pool/sessions mostly to Apache Tomcat and does not try to control it too much. There is no single parameter that can control closing inactive or sniped session. However they can be controlled programmatically (through api) by controlling multiple parameters/apis with questionable/little benefit."

Did anyone face this issue and is there any possibility for having only one active DB session instead of several ?

Thanks and regards,

Andras

[Shaun]

Check agent.properties for:

agents[0].dbcpmaxconn=5

Try modifying the value to have a single connection.

[adinnyes]

Thanks Shaun,

I will check this.

However if there is a parameter for this, I don't understand the statement I copied before about ArcSight is not managing the connections..

Thanks,

Andras

[Marijo Mandic]

Hello Andras,

More details about Database SmartConnector Parameters (so you can see what each setting does and test):

See below for details on the use of each parameter:

initretrysleeptime	If/when database connection fails, this parameter controls the time the SmartConnector will wait before trying to reconnect.
jdbcquerytimeout	An internal parameter which controls the SQL query timeout. It is disabled (with a value of -1) by default.
jdbctimeout	Controls the timeout period of JDBC connection.
loopingenabled	Used as a performance testing utility, set to false by default. It has no impact on the behavior of the agent.
persistenceinterval	This is a Boolean flag, disabled by default, which tells the SmartConnector to store the last processed event or byte offset in the log files. If enabled, the SmartConnector will continue where it left off. If it is disabled, the SmartConnector starts with the first log.
preservestate	If set to true, The SmartConnector should keep track of the last event processed.
preservedstatecount	If preservestate is set to true, this is the count of events to process before writing the preserve state.
preservedstateinterval	If preservestate is set to true, time interval in ms before writing the preserve state.
useconnectionpool	If false, a connection will be opened every time a query is made on the database. Use of this parameter makes the SmartConnector process more resource intensive.

Parameters below are used for JDBC Connection Pools setting:

dbcpcachestatements	Option to enable/disable caching of statements.
dbcpcheckouttimeout	Maximum number of seconds a Thread can checkout a connection before it is closed and returned to the pool.
dbcpidletimeout	Maximum number of seconds a connection can remain idle before it is closed.
dbcpmaxcheckout	Number of times a connection may be checked out before it is closed.
dbcpmaxconn	Maximum number of connections to open.
dbcpreap	How many seconds to wait between reaping connections in the pool.
dbcprowprefetch	Use this value for row pre-fetch.

Best regards,

Marijo Mandic

[adinnyes]

Thanks Marijo, very useful!

May I ask from which documentation have you copied this? I'd be curious about the same for all types of smartconnectors.

Thanks,

Andras

[Marijo Mandic]

You are welcome.

It is not from classical documentation, like PDFs for each SmartConnector, but from one knowledge base article

Document ID : KM1272123

Regards,

Marijo

[adinnyes]

Hi Shaun and All,

Getting back to this. What if I set this parameter to 1 and it goes like that but the current connection cannot be reused for any reason ?

What is the recommendation here ? Should I go with 2 instead of 1 ?

Thanks,

Andras

[molotoff]

Hello Marijo! Thanks for the info.

Could you plz share a link to this knowledge base article? Unfortunately I couldn't find it in the KB by the Document ID >.<

Best regards,

Sergey

[Marijo Mandic]

Hi Segey,

here it the link:

https://softwaresupport.hpe.com/group/softwaresupport/search-result/-/facetsearch/document/KM1272123?lang=en&cc=us&hpapp…

Regards,

Marijo Mandic

[molotoff]

Hello Marijo!

Thank you for the direct link - the document was very helpful.

Best regards,

Sergey

[corey.cousar@fi]

Hi Adinnyes,

Were you able to resolve this problem?

[skrc851]

Hello ​, I did come across a similar scenario today. Referring to "What if I set this parameter to 1 and it goes like that but the current connection cannot be reused for any reason ?"  - Can you let me know why do you want the current connection to be reused. Is this something related to the 'query frequency' that might occur before the previous query timesout?