V$RESOURCE_LIMIT

Question:  I am investigating some session issues, and I am wondering why when I query the v$session and v$resource_limit views, I get different values back for the number of sessions.

SQL> select 'session count from v$session', count(*) 
2 from v$session
3 union 
4 select 'session utilization from v$resource_limit', current_utilization 
5 from v$resource_limit where resource_name = 'sessions';

'SESSIONCOUNTFROMV$SESSION' COUNT(*)
----------------------------------------- ----------
session count from v$session 77
session utilization from v$resource_limit 101

My question is, which is correct?

Also, how can I see real time the session volatility?
Answers:
>> which is correct?
The v$session views shows current sessions (which change rapidly), while thev$resource_limit shows the maximum resource utilization, like a high-water mark.
**********************************************
>> how can I see real time the session volatility?
Unlike the old-fashioned v$session and v$session_wait views (where you could only see waits at the exact instant when they occurred), the new v$session_wait_historyand v$sys_time_model views allow Oracle10g to capture system waits details in a time-series mode. See my notes on v$session_wait_history:

The v$resource_limit view provides information about current and maximum global resource utilization for some system resources. 

From the Oracle docsThe Oracle docs note that v$resource_limit 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. Here are the column values for the resource_name column in v$resource_limit:

DML_LOCKS
ENQUEUE_LOCKS
GES_LOCKS
GES_PROCS
GES_RESS
MAX_SHARED_SERVERS
PARALLEL_MAX_SERVERS
PROCESSES
SESSIONS
SORT_SEGMENT_LOCKS
TEMPORARY_LOCKS
TRANSACTIONS

Some resources, those used by DLM, for example, have an initial allocation (soft limit) and the hard limit, which is theoretically infinite (although in practice it is limited by SGA size). During SGA reservation/initialization, a place is reserved in SGA for theINITIAL_ALLOCATION of resources.  If this allocation is exceeded, additional resources are allocated up to the value indicated by LIMIT_VALUE. TheCURRENT_UTILIZATION column indicates whether the initial allocation has been exceeded. When the initial allocation value is exceeded, the additional required resources are allocated from the shared pool, where they must compete for space with other resources.
A good choice for the value of INITIAL_ALLOCATION will avoid the contention for space.  For most resources, the value for INITIAL_ALLOCATION is the same as theLIMIT_VALUE.  Exceeding LIMIT_VALUE results in an error.

Column	Description
RESOURCE_NAME	Name of the resource
CURRENT_UTILIZATION	Number of (resources, locks, or processes) currently being used
MAX_UTILIZATION	Maximum consumption of this resource since the last instance start-up
INITIAL_ALLOCATION	Initial allocation. This will be equal to the value specified for the resource in the initialization parameter file (UNLIMITEDfor infinite allocation).
LIMIT_VALUE	Unlimited for resources and locks. This can be greater than the initial allocation value (UNLIMITED for infinite limit).

 

Also, you can use v$resource_limit to see if you need more locks orenqueue_resources.
The high water mark of connected Oracle sessions can be determined in several ways. One popular method uses Oracle login and logoff system-level triggers to record sessions in a statistics table. Another method uses Oracle STATSPACK to display the values from the stats$sysstat table or the v$resource_limit view.  Prior to release 8.1.7, the most difficult part of Oracle RAM optimization in any environment was accurately predicting the high water mark of dedicated, connected user sessions once the instance was started. This was because of a bug in the v$resource_limit view. After release 8.1.7, you can use v$resource_limit to see the high water mark of connected sessions since startup time.
Steve Adams notes that "That transaction takes up a slot in the SGA transaction table (X$KTCXB) but does not yet use a slot in one of the rollback segment header block transaction tables. V$TRANSACTION will not show you this transaction because the KTCXBFLG value is 1, but V$RESOURCE_LIMIT will show you that the slot is in use."
You can display the initial allocation and current utilization for any resource with thev$resource_limit view:

SELECT 
   resource_name name, 
   100*DECODE(initial_allocation, /
   ' UNLIMITED', 0, current_utilization / 
   initial_allocation) usage 
FROM 
   v$resource_limit 
WHERE 
   LTRIM(limit_value) != '0' 
AND 
   LTRIM(initial_allocation) != '0' 
AND 
   resource_name = 'xxx';

Oracle allows you to run this script to see the current vs. maximum values. In this example we select the current and maximum values for Oracle sessions and processes:

 select 
  resource_name, 
  current_utilization, 
  max_utilization, 
  limit_value
from 
  v$resource_limit
where 
  resource_name in ( 'sessions', 'processes');


RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------- ------------------- --------------- -----------
processes   423   423  600
sessions    426   426  800

========================================================================

V$RESOURCE_LIMIT

V$RESOURCE_LIMIT 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. Many of the resources correspond to initialization parameters listed in Table 8-5.

Some resources, those used by DLM for example, have an initial allocation (soft limit), and the hard limit, which is theoretically infinite (although in practice it is limited by SGA size). During SGA reservation/initialization, a place is reserved in SGA for the INITIAL_ALLOCATION of resources, but if this allocation is exceeded, additional resources are allocated up to the value indicated by LIMIT_VALUE. The CURRENT_UTILIZATION column indicates whether the initial allocation has been exceeded. When the initial allocation value is exceeded, the additional required resources are allocated from the shared pool, where they must compete for space with other resources.

A good choice for the value of INITIAL_ALLOCATION will avoid the contention for space. For most resources, the value for INITIAL_ALLOCATION is the same as the LIMIT_VALUE. Exceeding LIMIT_VALUE results in an error.


Column
Datatype
Description

RESOURCE_NAME
VARCHAR2(30)
Name of the resource (see Table 8-5)
CURRENT_UTILIZATION
NUMBER
Number of (resources, locks, or processes) currently being used
MAX_UTILIZATION
NUMBER
Maximum consumption of this resource since the last instance start-up
INITIAL_ALLOCATION
VARCHAR2(10)
Initial allocation. This will be equal to the value specified for the resource in the initialization parameter file (UNLIMITED for infinite allocation).
LIMIT_VALUE
VARCHAR2(10)
Unlimited for resources and locks. This can be greater than the initial allocation value (UNLIMITED for infinite limit).




Table 8-5 Values for the RESOURCE_NAME Column

Resource Name
Corresponds to


DML_LOCKS

See "DML_LOCKS"


ENQUEUE_LOCKS

This value is computed by the Oracle Database. See V$ENQUEUE_LOCK to obtain more information about the enqueue locks.


GES_LOCKS

Global Enqueue Service locks


GES_PROCS

Global Enqueue Service processes


GES_RESS

Global Enqueue Service resources


MAX_SHARED_SERVERS

See "MAX_SHARED_SERVERS"


PARALLEL_MAX_SERVERS

See "PARALLEL_MAX_SERVERS"


PROCESSES

See "PROCESSES"


SESSIONS

See "SESSIONS"


SORT_SEGMENT_LOCKS

This value is computed by the Oracle Database


TEMPORARY_LOCKS

This value is computed by the Oracle Database


TRANSACTIONS

See "TRANSACTIONS"





================================================================================