Full table scans being performed against the so_job_history table causing slow GUI response

Products

• CA Automic Applications Manager (AM)

Issue/Introduction

Full table scans being performed against the so_job_history table causing slow GUI response

Environment

Release: AAMOS499000-8.0-Automic Applications Manager-OS400 Agent
Component:

Resolution

Synopsis

By default Oracle (10g+) is performing full table scans against the so_job_history table when there are more than 1,000,000 rows of data.  These full table scans can affect your Applications Manager v8 installation by causing a slow down in response time from the GUI.  If the fetched rows are smaller than that Oracle uses indexes so it does not cause an issue.

---

Description

If this problem is affecting your Applications Manager installation, you may experience a very slow response from the GUI.  With debugging enabled you may find in the RmiServer debug log that the aw_inc_update procedure is taking a long time to run.  The entry in the log will look similar to this:

03:02:29.972 rf: .DBAccess: getFunctionData(): sID-303010669 APPWORX aw_web_api.aw_inc_update elapsed ms 141777 rows 68

In the above example the aw_inc_update, which normally executes in seconds, took over 2 minutes to execute.  You can also execute a query to determine if the aw_inc_update procedure is taking an excessive amount of time to run.  See KB0178859 for additional details.

---

Resolution

Stop all Applications Manager processes and run the following SQL statement from the AM Oracle userid:

INSERT into aw_master_stmts values ('alter session set optimizer_index_cost_adj =20',1);

commit;

For more Oracle 10g/11g tuning tips for Application Manager see KB0178055.