SymptomsYou have an installation of Oracle Fusion Middleware 11g where you have been running a large volume of BPEL processes over time. When accessing the dashboard page displayed by Enterprise Manager for one or more composites, the regions that display list views of composite's instances take a long time to render and during this time Enterprise Manager remains hung and unresponsive for other tasks.At the time when the performance of Enterprise Manager is impacted, you will likely have one or more composites that have a reasonably large number of composite instances defined (over 100,000) and the performance impact may be most noticeable the first time a dashboard page is accessed following a restart of the AdminServer. In severe cases you may be experiencing very long delays (potentially hours) when using Enterprise Manager to accessing the dashboard pages of these composites.
At the database layer the SQL query that Enterprise Manager executes to obtain the data displayed on the dashboard is taking far longer than expected to execute, as a result of a less than optimal execution plan having been determined by the Cost Based Optimizer component of the database. The execution plan identified by the Cost Based Optimizer is, in turn, driven by information about the various tables and column indexes that is maintained by database statistics. To reduce processing overhead while creating statistics, the statistics for large tables (over 100,000 rows) are not collected from all rows of the table but using a sampling technique on a smaller subset (by default, 20%).
As a result of this sampling based approach, over time the statistics gathered against the sample may not be representative for the larger data set as a whole. When such a discrepancy occurs the unrepresentative statistics can lead the Cost Based Optimizer to identify a bad execution plan that is less efficient on data access and takes significantly longer to complete. In such cases, this causes the source of the long running SQL which so significantly impacts the performance of the list views on Enterprise Manager dashboard pages.
My Solution(Worked fine for me)
1. Login to the SOA 11g EM Console
2. Right Click on SOA-INFRA and go to SOA ADMINISTRATION->COMMON PROPERTIES
3. Enable the Disable fetching of instance and fault count metrics as shown below.
4. Set the Duration Details to 24 or 48 hours as shown below.
5. Now log back to EM console and voila, the EM performance has a sea change. its faster
These two properties enable you to perform the following:
Now you can folow us on facebook and post your comments/views and questions for expert advise. Check this out facebook
Find us on facebook here