Monday, June 11, 2012

Performance Tab, Memory Advisor slow or hanging in Enterprise Manager

When you're responsible for the Oracle Enterprise Manager, you'll likely hear "it's slow!" a lot. And most of the time, users mean the Performance Tab or anything related to that.

Had that today again when someone tried to access the Memory Advisor of a database but clicking the link just resulted in an eternity of page loading and finally an error message as well as a warning alert for the "/secFarm_GCDomain/GCDomain/EMGC_OMS1" target along the lines
"Errors in directory: /u00/app/oracle/product/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/adr/diag/ofm/GCDomain/EMGC_OMS1/incident/incdir_n (incident=n):~null"
- generic as can be, also the info inside the incident directory.

Checking the database host, I noticed a process (remote connecting to the database in question) taking lots of CPU. The respective DB session that process represented was executing for minutes already:

select component, final_size, end_time
from v$sga_resize_ops
where initial_size <> final_size order by end_time;

The Enterprise Manager queries that when building the page for the memory advisor.

Problem here was that the fixed object statistics and the dictionary statistics were not collected after a migration to 11g so all the internal views - where EM gets its live information from for those functionalities, it's not all stored in the repository - performed badly.

Everything was just fine after making up for that:

exec dbms_stats.gather_fixed_objects_stats();
exec dbms_stats.gather_dictionary_stats();
Lessons learned:
  1. "Enterprise Manager is slow" does not always mean "Enterprise Manager is slow"
  2. Have your dictionary and fixed objects statisics up to date
  3. Failing to do so can generate some serious load on your system - just by trying to use EM

No comments:

Post a Comment