Performance Analysis tool is performing many full table scans on large tables which can impact many other background processes.
Performance Analysis runs select statements for monitoring the Oracle Applications activity, these statements are run by Performance Analysis as a part of E-Business advisories functionality. From the support bundle, we maybe be able to see in our Oracle Application schema that these tables have indexes, but for some reason, the Oracle optimizer does not use these indexes for select statements.
We can verify if these tables have indexes by running the following:
select index_name,column_name from dba_ind_columns where table_name = 'WF_ITEM_ACTIVITY_STATUSES';
The objects involved in SQL statements that Performance Analysis run in order to obtain the information are required for instance analysis. These are Oracle Applications objects that have the required indexes.
Attached to this solution is the 'QUEST_ADV_EB_PURGE_HINT.PBK' which is the fix for package QUEST_ADV_EB_PURGE.PBK that runs these statements.
Quest added the index hints in order for the Oracle optimizer to work with these indexes.
To apply the package, please do the following:
1. Copy the package to some temporary directory
2. Login to monitored instance with monitored schema owner.
3. Apply the package:
4. Reconnect to the Performance Analysis GUI