Hi !
We are currently implementing webMethods IS / TN with Oracle database are facing performance issues in regards of queries.
I would like to know if it is possible to segregate TN Queries and TN Inserts to the Oracle Database using different JDBC connection. One for each of the event meaning 1 jdbc for queries and one for inserts.
Anyone can help me with this
Thanks
Its always ideal way of using one JDBC connection per DB,and regarding performance connection pooling should be taken care like Min/Max connections and timeouts etc…So what is your current settings and how many select queries are inserts taking place.
Are you seeing any errors in the logs?
HTH,
RMG.
“One for each of the event meaning 1 jdbc for queries and one for inserts”
Regarding above question you can create ConnectionType NO_TRASACTION for selecting queries and another with LOCAL_TRANSACTION for inserts/updates etc…connecting to same DB.
Regards,
IIRC, for TN DB I don’t think we can control which connection is used for which types of operations. For 6.x we get to define a single pool only for TN use.
One of the things that may help considerably is creating index on heavily used columns of specific tables. For 4.6 we had created these indexes:
NativeID on BizDoc
DocTimestamp on BizDoc
UserStatus on BizDoc
StringValue on BizDocAttribute
ServerID on DeliveryJob
TimeCreated on DeliveryJob
TimeUpdated on DeliveryJob
JobStatus on DeliveryJob
These were the columns we typically used in TN Console when looking at activity. The DocTimestamp index on BizDoc in particular had a huge impact on TN Console performance.
I haven’t looked at the indexes provided out of the box for 6.x TN (there might be additional indexes provided than what was there for 4.6) but there may be similar benefits gained from adding indexes on the columns you use heavily for queries.
HTH.
Harinen,
Regarding JDBC pools for TN,we can define only one pool for TN,as Rob mentioned above adding indexes will definetely helps which are heavily used table columns by TN.
HTH,
RMG