How Many Processes In the Database Historically?

How Many Processes In the Database Historically?

March 23, 2023 Off By dianarobete

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

       --HIST_SNAPSHOT.instance_number, -- for RAC
      || '%' "%UsedProcesses"
WHERE HIST_RESOURCE_LIMIT.resource_name='processes'
--AND HIST_RESOURCE_LIMIT.instance_number=HIST_SNAPSHOT.instance_number --for RAC

---------- ------------------------------ ---------- -------- --------- --------------
     52052 15-MAR-23 AM             157      258       650 39.69%
     52053 15-MAR-23 AM             162      268       650 41.23%
     52054 15-MAR-23 AM             170      258       650 39.69%
     52055 15-MAR-23 PM             163      258       650 39.69%
     52056 15-MAR-23 PM             155      258       650 39.69%
     52057 15-MAR-23 PM             169      252       650 38.77%
     52058 15-MAR-23 PM             154      258       650 39.69%

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!

I am curious, did you know about this view? Have you used it before? Let me know in the comments section, I read every comment!

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.comFollow the link to get Today‘s Special, only $13.99 CAD !