Dear Autosys community,
Autosys r12 SP1 with Oracle DB server 12c (12.1.0.2.0) and Oracle Instant Client 19c.
Has anyone observed any Application server performance issues with wildcarded autorep (ex: autorep -J
%d01_11564%, autorep -J *uw3*ztb_cds1*) commands?
We are severely impacted after r12SP1 upgrade (jumped from 11.3.6 SP6).
We opened a Support Case with Broadcom and also engaged our DBAs and after much investigation from the DB side, it turns out the bind variables (introduced in 11.3.6 SP7) containing wildcard (%) don't go well with Oracle 12c, especially it is in the begining (ex: autorep -J %d01_11564%); wildcards in the middle appears to be a lot quicker. The long-running (actually, never-ending) SQLs took an unusual execution plan (missing all vital Autosys indices).
We have worked around the issue (still see some SQLs bogging down the application server) by disciplining the application users to avoid wildcards in the front/beginning (syd%d01_11564% better than %d01_11564%) and also create a SQL profile with a lot of hints to pretty much force a specific plan, on the DB server to force using hints to optimize. DBAs suggestion was to get the application (Autosys) to use these hints, however Broadcom support/engineering didn't think this is the right approach as they should not be overriding the Oracle's optimizer (debatable?). DBA's opinion about the autorep SQL (so many joins) wasn't very good either:
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
At the moment, we don't really know if it's Oracle or Autosys, but hoping Oracle 19c server may provide some relief.
Has anyone seen this behaviour at all? The behaviour seems very easy to replicate (at least in our environment).
Thanks & Regards,
Chandru V