slowness issue while executing autorep command or any other long running operations

book

Article ID: 211332

calendar_today

Updated On:

Products

CA Workload Automation AE

Issue/Introduction

Performance problem encountered when running the autorep command or any other long running operations

Errors like this would show up in as_server.$AUTOSERV  or event_demon.$AUTOSERV.

[07/14/2021 03:01:38]      CAUAJM_E_18416 Event Server: <oracle.world>  Failed Query: <select status, count(*) from ujo_proc_event where event = 101 and event_time_gmt between 1626246000 and 1626249599 group by status>
[07/14/2021 03:01:38]      CAUAJM_E_18412 The database client has been interrupted while query execution is in progress.
[07/14/2021 03:01:38]      CAUAJM_E_18400 An error has occurred while interfacing with ORACLE.
[07/14/2021 03:01:38]      CAUAJM_W_10900 The database monitoring system has detected a potential problem with the database.
[07/14/2021 03:01:38]      CAUAJM_E_18401 Function <doExecute> invoked from <execute> failed <908>
[07/14/2021 03:01:38]      CAUAJM_I_10901 The database monitoring system is beginning validation of database connections.

 

[08/12/2020 13:32:54]      CAUAJM_E_18416 Event Server: <oracle.world>  Failed Query: <SELECT j.joid,j.job_name,j.job_type,s.status,s.last_start,s.last_end,s.run_num,s.ntry,s.exit_code,run_priority,i.timezone,j.as_applic,j.as_group, (case when s.pid like :I_S_1 THEN s.pid else :I_S_2 end) FROM ujo_job j JOIN ujo_job_status s ON j.joid = s.joid JOIN ujo_sched_info i ON j.joid = i.joid AND j.job_ver = i.job_ver AND j.over_num = i.over_num LEFT OUTER JOIN ujo_job z ON (z.joid=j.box_joid AND z.is_active=:I_I_3 AND z.is_currver=:I_I_4) AND (z.joid>:I_I_5) AND (z.job_name LIKE :I_S_6 ESCAPE :I_S_7) WHERE (j.job_name like :I_S_8 escape :I_S_9 AND j.is_active=:I_I_10 AND j.is_currver=:I_I_11) AND (z.joid IS NULL)  ORDER BY j.joid ASC NULLS FIRST  <<'NOEXEC_%','$',1,1,0,'abcd!_%','!','abcd!_%','!',1,1>>>
[08/12/2020 13:32:54]      CAUAJM_E_18412 The database client has been interrupted while query execution is in progress.
[08/12/2020 13:32:54]      CAUAJM_E_18400 An error has occurred while interfacing with ORACLE.
[08/12/2020 13:32:54]      CAUAJM_E_18401 Function <doExecute> invoked from <execute> failed <908>
[08/12/2020 13:32:55]      CAUAJM_I_10902 The database monitoring system has completed validation of database connections.
[08/12/2020 13:32:55]      CAUAJM_I_10903 The database monitoring system will resume monitoring for database problems.
[08/12/2020 13:32:55]      CAUAJM_W_10900 The database monitoring system has detected a potential problem with the database.
[08/12/2020 13:32:55]      CAUAJM_I_10901 The database monitoring system is beginning validation of database connections. 

 

Cause

Oracle AWR reports show high wait times on network, where the Oracle client APIs that autosys

Environment

Release : 11.3.x and 12.x

Component : WORKLOAD AUTOMATION AE 

Resolution

Network through put between Oracle client -and Oracle server is slow.  Some tweaks to the Oracle client side, autosys server's $TNS_ADMIN/sqlnet.ora file resolved the issue and made Oracle client to Oracle server performance faster when autosys was issuing longer queries.

 

DEFAULT_SDU_SIZE=32767

SEND_BUF_SIZE=65536

RECV_BUF_SIZE=65536

SQLNET.RECV_TIMEOUT=120

SQLNET.OUTBOUND_CONNECT_TIMEOUT=120

SQLNET.INBOUND_CONNECT_TIMEOUT=120

 

Restart of the as_server/event_demon after the above change is made

 

Attachments