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 the v$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_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:
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 the
INITIAL_ALLOCATION of resources. 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.
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:
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
and include the URL for the page.
