Processes Usage In the Database Historically

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:

<html xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:w="urn:schemas-microsoft-com:office:word"
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<meta name=ProgId content=Word.Document>
<meta name=Generator content="Microsoft Word 15">
<meta name=Originator content="Microsoft Word 15">
<link rel=File-List href="set%20lines%20200%20pages%201000_files/filelist.xml">

<link rel=themeData href="set%20lines%20200%20pages%201000_files/themedata.thmx">
<link rel=colorSchemeMapping
href="set%20lines%20200%20pages%201000_files/colorschememapping.xml">

<style>

</style>

</head>

<body lang=EN-IN style='tab-interval:36.0pt'>

<div class=WordSection1>

<table class=MsoTableGrid border=1 cellspacing=0 cellpadding=0
style='border-collapse:collapse;border:none;mso-border-alt:solid windowtext .5pt;
mso-yfti-tbllook:1184;mso-padding-alt:0cm 5.4pt 0cm 5.4pt'>
<tr style='mso-yfti-irow:0;mso-yfti-firstrow:yes;mso-yfti-lastrow:yes'>
<td width=902 valign=top style='width:450.8pt;border:solid windowtext 1.0pt;
mso-border-alt:solid windowtext .5pt;background:#9CC2E5;mso-background-themecolor:
accent1;mso-background-themetint:153;padding:0cm 5.4pt 0cm 5.4pt'>
set lines 200 pages 1000<o:p></o:p>
col begin_interval_time
FOR A30<o:p></o:p>
col "%UsedProcesses" for A15<o:p></o:p>
 <o:p></o:p>
SELECT HIST_SNAPSHOT.snap_id,<o:p></o:p>
       --HIST_SNAPSHOT.instance_number, -- for RAC<o:p></o:p>
       HIST_SNAPSHOT.begin_interval_time,<o:p></o:p>
       HIST_RESOURCE_LIMIT.curr_util,  <o:p></o:p>
       HIST_RESOURCE_LIMIT.max_util,<o:p></o:p>
       HIST_RESOURCE_LIMIT.ini_alloc,<o:p></o:p>
  round((HIST_RESOURCE_LIMIT.max_utilization/HIST_RESOURCE_LIMIT.initial_allocation)*100,2)
<o:p></o:p>
      ||
'%' "%UsedProcesses"<o:p></o:p>
FROM DBA_HIST_RESOURCE_LIMIT HIST_RESOURCE_LIMIT,<o:p></o:p>
     DBA_HIST_SNAPSHOT  
HIST_SNAPSHOT<o:p></o:p>
WHERE HIST_RESOURCE_LIMIT.resource_name='processes'<o:p></o:p>
AND   HIST_RESOURCE_LIMIT.snap_id=HIST_SNAPSHOT.snap_id<o:p></o:p>
--AND
HIST_RESOURCE_LIMIT.instance_number=HIST_SNAPSHOT.instance_number --for RAC<o:p></o:p>
ORDER BY HIST_SNAPSHOT.snap_id;<o:p></o:p>
 <o:p></o:p>
   SNAP_ID  BEGIN_INTERVAL_TIME           
CURR_UTIL MAX_UTIL INI_ALLOC %UsedProcesses<o:p></o:p>
----------
------------------------------ ---------- -------- --------- --------------<o:p></o:p>
     52052
15-MAR-23 09.00.07.338
AM            
157      258       650
39.69%<o:p></o:p>
     52053
15-MAR-23 10.00.25.855
AM            
162      268       650
41.23%<o:p></o:p>
     52054
15-MAR-23 11.00.43.312
AM            
170      258       650
39.69%<o:p></o:p>
     52055
15-MAR-23 12.00.12.070
PM            
163      258       650
39.69%<o:p></o:p>
     52056
15-MAR-23 01.00.13.464
PM            
155      258       650
39.69%<o:p></o:p>
     52057
15-MAR-23 02.00.28.177
PM            
169      252       650
38.77%<o:p></o:p>
     52058
15-MAR-23 03.00.16.798
PM            
154      258       650
39.69%<o:p></o:p>
<o:p> </o:p>
</td>
</tr>
</table>

<o:p> </o:p>

</div>

</body>

</html>

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!

Search This Blog

ORACLE DBA BLOG

Processes Usage In the Database Historically

Comments

Post a Comment

Popular posts from this blog

Cold Backup Cloning of Database .

Find ALert log Location

FRA Usage and Administration