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;
SNAP_ID BEGIN_INTERVAL_TIME CURR_UTIL MAX_UTIL INI_ALLOC %UsedProcesses
---------- ------------------------------ ---------- -------- --------- --------------
52052 15-MAR-23 09.00.07.338 AM 157 258 650 39.69%
52053 15-MAR-23 10.00.25.855 AM 162 268 650 41.23%
52054 15-MAR-23 11.00.43.312 AM 170 258 650 39.69%
52055 15-MAR-23 12.00.12.070 PM 163 258 650 39.69%
52056 15-MAR-23 01.00.13.464 PM 155 258 650 39.69%
52057 15-MAR-23 02.00.28.177 PM 169 252 650 38.77%
52058 15-MAR-23 03.00.16.798 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.com. Follow the link to get Today‘s Special, only $13.99 CAD !
