Query originated by Performance Analysis consumes a high amount of CPU (32205)

A query originated by Performance Analysis consumes a high amount of CPU and causes performance problems in the monitored database.

The query updating the quest_adv_segment_stat_obj table (UPDATE /*+ HOOSE */quest_adv_segment_stat_obj …) which is originated by QUEST_ADV_SNAPSHOTER package consumes the high amount of CPU.

The is an Oracle bug where the Oracle SQL engine is distorting the Optimizer hint.It is caused by a bug in Oracle when the query hint in the package is /*+CHOOSE */ and Oracle is replacing the C in the hint with a space. The 9.2 documentation says: The space between the plus sign and the hint is optional. The impact is that this hint is being ignored.

Download the QUEST_ADV_SNAPSHOTER_fix_hint.zip and unzip it.
Apply the package in the following way when logged to monitored instance as a monitored schema owner:
SQL> @ QUEST_ADV_SNAPSHOTER_fix_hint.PBK

If the problem still occurs or any other issues are raised following this package installation, the segments statistics that are causing the issue can be turned off by applying QUEST_ADV_SNAPSHOTER_ignore_segs.PSK package.

Download the package QUEST_ADV_SNAPSHOTER_ignore_segs.zip and unzip it. Apply the package in the following way when logged to monitored instance as a monitored schema owner:
SQL> @ QUEST_ADV_SNAPSHOTER_ignore_segs.PSK

Note: In the event that the change in the hint provided by the QUEST_ADV_SNAPSHOTER_fix_hint.zip solution does not work and QUEST_ADV_SNAPSHOTER_ignore_segs.zip is applied, here is what will be lost as a result:

Applying the QUEST_ADV_SNAPSHOTER_ignore_segs.zip fix, instructs Performance Analysis Oracle NOT to collect Segment related data such as Number of Physical disk reads, Physical Writes and Buffer Waits for a given DB object and then aggregating it for each data file per tablespace.

So, you will be loosing this information related to IO Wait events, but these statistics collection is only for Advisories and the Advisory related to data file I/O will be turned off.

Again, we must emphasize this workaround is due to an Oracle bug in this version of DB 9.2.0, and the problem is not seen in Oracle 10G versions.

To diagnose run the following statement against the monitored database. The first lines are just some format settings to make the output of the statement more readable.

set linesize 120
column first_load_time format A22
column executions format 99999
column cpu_time format 9999999999999999
column sql_text format A60

select first_load_time, executions, cpu_time, substr(sql_text, 1,60) sql_text
from v$sqlarea
where lower(sql_text) like '%quest_adv%'
order by 3 desc;

After running the statement search for the statement "UPDATE /*+ HOOSE */quest_adv_segment_stat_obj o...".