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;
