Kill Application Engine (AE)
It amazes me that in the world of these high end databases that simple data handling can go for a crap simply because data was bulk loaded into the system. Usually during big changes to an environment: data conversions, upgrades, archiving or other aggressive changes, data will become out of sync with the statistics that are stored on the tables.
What are Database Statistics? In a nut shell it is data about data or simply metadata. Oracle statistics is metadata about your data. There are several kinds of statistics in Oracle mainly: Object statistics, System statistics and fixed table statistics.
So if you have bad statistics your Application Engine may not be able to execute code correctly. It will simply appear to be hung. I have seen processes run for days and it will never return a result because of bad statistics. After updating the statistics those exact same processes that ran for days will run in seconds, sometimes faster. Okay, so now the catch is you have to “kill” the process in order for the process to be re-ran and pick up the correct statistics. This isn’t the easiest process in the world to do, because if you try to just cancel the job through the process scheduler it may or may not cancel nicely. However, if you “kill” the SQL at the database level that is hung the application engine for go to ERROR, and you can restart the AE from the last committed point that it executed to. This is much cleaner than any other method I have found, especially if you have a huge amount of stuff already done by the application engine prior to running into this issue. Upgrades are notorious for this, which is why they have “update statististics” steps all throughout the upgrade process now.
So in Oracle SQL we need to find the SQL that is causing the issue:
select * from v$session where program like ‘%PSAESRV%’; –See SQL running by Application Engine
select * from v$sql where sql_id = ‘SQL_ID from v$Session’; — Confirm you have the write SQL
alter system kill session ‘{sid},{serial#}’;
It may take a few minutes to find the SQL causing the problems, but don’t worry, it will still be running after you find it. Once it is killed, the AE will go to error, and you can restart it. It will start up at the last commit point, which you can usually see if you query the AE Control table:
select * from ps_aeruncontrol;
Rerun your statistics for the schema or for the tables in question.
exec dbms_stats.gather_schema_stats(‘SYSADM’, cascade=>true);
Good luck out there!