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
4 select 'session utilization from v$resource_limit', current_utilization
5 from v$resource_limit where resource_name = 'sessions';
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?
>> which is correct?
The v$session views shows current sessions (which change rapidly),
while the v$resource_limit shows the maximum resource utilization, like a
>> 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_history and
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 docs
The 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:
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. If this allocation is
exceeded, additional resources are allocated up to the value indicated
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
avoid the contention for space. For most resources, the value for
INITIAL_ALLOCATION is the same as the
LIMIT_VALUE results in an error.
|Name of the
(resources, locks, or processes) currently being used
consumption of this resource since the last instance
allocation. This will be equal to the value specified for
the resource in the initialization parameter file (
for infinite allocation).
resources and locks. This can be greater than the initial
allocation value (
UNLIMITED for infinite
Also, you can use v$resource_limit to see if you need more locks or enqueue_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 the v$resource_limit view:
' UNLIMITED', 0, current_utilization /
LTRIM(limit_value) != '0'
LTRIM(initial_allocation) != '0'
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:
resource_name in ( 'sessions', 'processes');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------- ------------------- --------------- -----------
processes 423 423 600
sessions 426 426 800