RichardM

CA WAAE Database Connections

Discussion created by RichardM on Oct 18, 2013
User:
=====

We have concerns about the database connection count. By running the below statement,
the DBA found there were more than 100 connections were established. Is it
normal? Why does it needs so many database connections?

SQL> select username,count(*) from v$session group by username;

USERNAME COUNT(*)
------------------------------ ----------
27
AUTOSYS 113
SYS 1



Explanation:
=============

Depending on your CA WAAE workload, yes it maybe normal. The scheduler and the
application server processes interface directly with the database.

When you start CA WAAE, it continually scans the database for events to process.
The scheduler interprets CA WAAE events and, based on job definitions, initiates
actions through the agent.

The application server executes the request by contacting the event
server. This results in the information either being inserted, updated,
retrieved, or removed from the event server. The responses are returned
to the client as the operation executes or after the operation completes.

DB_CONNECTIONS max 128

The DB_CONNECTIONS parameter value is applied in both the scheduler and the
application server on startup.

AUTOSYS database connections to the Database are persistent, meaning, the
database connections will be open when the scheduler and application
server are started and will remain open after the startup of CA WAAE.

The Event or scheduler process and application server processes
by default, will each open 32 database connections and are presistant.
If they're not used or remain idle during high periods of activity for
example, the application server having those 32 database connections
allows us (CA) to execute 32 concurrent requests and feedback responses
for those 32 requests. If more requests come in- they queue up until one of
those database connections, the API associated with the request, executes
the responses then that thread basically goes back to the pool then the
next request gets initiated and so on down the line... You are going to see
a number of database connections assigned to both the scheduler and
application server. If you're running in HA mode, because you're running 2
schedulers, your going to have 32 additional database connections so you need
to make sure that the database itself can support the large number of database
connections. You can set it in the autosys.sh script, it will then be set for
both the scheduler and application server.

Be careful- if you set DB_CONNECTIONS parameter to the max 128, you need to ensure
the repository is able to support that large number of database connections.
Recommendations would be to start at DB_CONNECTIONS at 32 (default) and if that's not
good enough- try 48 or 64 DB_CONNECTIONS. This should be monitored by your DBA
group.

Reference:
CA Workload Automation AE Administration Guide-
Define the Tuning Parameters for the Scheduler


**The support technicians would like your feedback. Thanks*

Outcomes