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

Document ID : KB000090325
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:
Full table scans being performed against the so_job_history table causing slow GUI response
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.