Friday, February 08, 2013

V$RESOURCE_LIMIT and Bug 3896119

In order to get the number of processes Oracle database uses, DBA can query the V$RESOURCE_LIMIT with RESOURCE_NAME equal PROCESSES. By knowing the max utilization, DBA can plan for the process increase if need since the parameter PROCESSES change need to bounce the database.

This view displays information about global resource use for some of the system resources. Use this view to monitor the consumption of resources so that you can take corrective action, if necessary.

ColumnDatatypeDescription
RESOURCE_NAMEVARCHAR2(30)Name of the resource
CURRENT_UTILIZATIONNUMBERNumber of (resources, locks, or processes) currently being used
MAX_UTILIZATIONNUMBERMaximum consumption of this resource since the last instance start-up
INITIAL_ALLOCATIONVARCHAR2(10)Initial allocation. This will be equal to the value specified for the resource in the initialization parameter file (UNLIMITED for infinite allocation).
LIMIT_VALUEVARCHAR2(10)Unlimited for resources and locks. This can be greater than the initial allocation value (UNLIMITED for infinite limit).

Values for RESOURCE_NAME column
Resource NameCorresponds to this Initialization Parameter
DISTRIBUTED_TRANSACTIONSSee <>
DML_LOCKSSee <>
ENQUEUE_LOCKSThis value is computed by Oracle. Use <> to obtain more information about the enqueue locks.
ENQUEUE_RESOURCESSee <>
LM_PROCESSESLock manager processes
LM_LOCKSSee <>
MTS_MAX_SERVERSSee <>
PARALLEL_SLAVESSee <>
PROCESSESSee <>
ROLLBACK_SEGMENTSSee <>
SESSIONSSee <>
SORT_SEGMENT_LOCKSThis value is computed by Oracle
TEMPORARY_LOCKSThis value is computed by Oracle
TRANSACTIONSSee <>

However, there is a Bug 3896119 -  CURRENT_UTILIZATION of V$RESOURCE_LIMIT may be too high - which affects the Oracle database versions less than 10.2 such as 9.2.0.5 and 10.1.0.3. The bug is fixed since 10.2.0.1 as well patching to 9.2.0.7 and 10.1.0.4.