Article Original Creation Date: 2012-03-28
This morning we have noticed that we had 35281 remaining devices in the queue. After deleting them from database (included "commit"), the remaining policies was around 0 for 20 minutes, then it was increasing again and it keeps growing.
What are your recommendations? A full restart + database cleanup will solve the problem?
Also, ADDM suggested some SQL tune-up.
Not a clear cause was found yet:
Steps taken, run Enterprise manager and use ADDM and Oracle profiles to optimize a few sprt_nc_cpe and sprt_ec_device related TOP 5 queries. No other issues found in the DB
Asked customer to truncate at least the sprt_nc_inform_event table before we take a next step (was 35K).
Last week a new version of a MacLess.ear file was deployed to the application, a few days after that the application started to show performance problems mainly in one Default Configuration Synchronization policy which uses the MacLess.ear as workflow step.
The customer truncated the sprt_nc_inform_event table and restarted in the same process all services including JBoss.
After that the performance is ok (24 hours later still ok) The average count of sprt_nc_inform_vent = < 50 rows and the IN_PROGRESS count of the involved policy is 40-60 instead of 90-100, remaining is 0 instead of a few thousand.
The problem came back after 7 days now a slight increase of remaining devices was shown and the sprt_nc_inform_event table increased slightly to > 200 entries from < 100. Because of another reason the services were restarted, now 5 days ago and the tables were truncated.
-Services meanwhile told me there were only made a few minor changes in the MacLess plugin that cannot influence the performance of the policy.
-Above actions together with running the profiler within Oracle Enterprise manager and optimizing the following query:
SELECT nc_cpe_guid FROM sprt_nc_cpe WHERE nc_cpe_remote_addr = :1 solved this performance issue.