Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

v$resource_limit tips

Oracle Consulting Tips by Burleson
May 13, 2008

 
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.

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 (UNLIMITED for 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 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



 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational