How Many Processes In the Database Historically?

 

The other day, I had an interesting challenge to look at. The maximum number of processes in the database instance was reached, and I had to review if this is was an isolated case, or something that occurred often.

There is a data dictionary view that keeps track of the number of processes in the instance for each hourly interval, or however often you take AWR snapshots. Since this view is a DBA_HIST view, and it is part of AWR, you need to have appropriate licensing in place, before you query it. You have been warned!

I personally was not aware of this view! I won’t keep it from you any longer! The view name is: DBA_HIST_RESOURCE_LIMIT.

This view contains snapshots of V$RESOURCE_LIMIT. The current value, the maximum utilization and the init parameter value for the resource are captured, along with other information.

Knowing about this view, makes it easy to track the usage of processes over time, especially if your retention for AWR data is greater than the default of 8 days. Just a side note, for production systems, I like to keep at least 3 months (90 days) of AWR information in the database, space permitting. What is your AWR retention?

Going back to my initial challenge to see if the incident was isolated or not, I ran the following query:

 

set lines 200 pages 1000
col begin_interval_time FOR A30
col "%UsedProcesses" for A15

SELECT HIST_SNAPSHOT.snap_id,
      --HIST_SNAPSHOT.instance_number, -- for RAC
      HIST_SNAPSHOT.begin_interval_time,
      HIST_RESOURCE_LIMIT.curr_util,  
      HIST_RESOURCE_LIMIT.max_util,
      HIST_RESOURCE_LIMIT.ini_alloc,
 round((HIST_RESOURCE_LIMIT.max_utilization/HIST_RESOURCE_LIMIT.initial_allocation)*100,2) 
     || '%' "%UsedProcesses"
FROM DBA_HIST_RESOURCE_LIMIT HIST_RESOURCE_LIMIT,
    DBA_HIST_SNAPSHOT   HIST_SNAPSHOT
WHERE HIST_RESOURCE_LIMIT.resource_name='processes'
AND   HIST_RESOURCE_LIMIT.snap_id=HIST_SNAPSHOT.snap_id
--AND HIST_RESOURCE_LIMIT.instance_number=HIST_SNAPSHOT.instance_number --for RAC
ORDER BY HIST_SNAPSHOT.snap_id;

 

I am not displaying all the 90 days worth of data, but from the output, I can see that the usage for processes in the database is around 38%-41%. Oracle recommends increasing the value of the parameter PROCESSES, if you hit the 75% mark, regularly. In that case, you should increase the value by 20%-30%.

In my situation there was no action taken to increase the PROCESSES parameter, as it was an isolated incident, which was caused by a user. A conversation with the user and corrective action of the code was required.

If you run the query on a RAC database, you need to be aware that you get an entry for each instance, for each interval, and you need to join the two views on the instance_number columns. I included that in the query, but commented it out.

How can you get historical data on the number of processes in the database if you do not have licensing for AWR? In that case you will need to set up your own process or job that collects the historical information! That could be another post on it’s own for the future!

courtesy: DRobete