“V$RESOURCE_LIMIT” Reference Note
Oracle9i Information
- 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.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_ALLOCATIONof resources, but if this allocation is exceeded, additional resources are allocated up to the value indicated byLIMIT_VALUE. TheCURRENT_UTILIZATIONcolumn 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 ofINITIAL_ALLOCATIONwill avoid the contention for space. For most resources, the value forINITIAL_ALLOCATIONis the same as theLIMIT_VALUE. ExceedingLIMIT_VALUEresults in an error.
Table 3-2 Values for RESOURCE_NAME column
Oracle10g Information
| 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” |
Support and Historical Notes for “V$RESOURCE_LIMIT”
View Definition:
Use the following SQL to see the view definition of the related GV$ view:
SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$RESOURCE_LIMIT';
Bug 3896119 CURRENT_UTILIZATION of V$RESOURCE_LIMIT may be too high
Affects:
Product (Component) Oracle Server (Rdbms) Range of versions believed to be affected Versions < 10.2 Versions confirmed as being affected Platforms affected Generic (all / most platforms affected)
Fixed:
This issue is fixed in
Symptoms: |
Related To: |
|
Description
CURRENT_UTILIZATION of processes in V$RESOURCE_LIMIT may be too high.
Hdr: 3896119 9.2.0.5 RDBMS 9.2.0.5 VOS PRODID-5 PORTID-197
Abstract: CURRENT_UTILIZATION OF V$RESOURCE_LIMIT IS UNUSUAL BIGGER THAN PROCESSES
PROBLEM:
——–
JTAKABUC has already filed Bug#3893908 for TNS-12516 problem.
The cause of TNS-12516 seems to be the service handler is
in a “blocked” state (this relation is indicated in Note:240710.1).
This bug is filed for investigation of CurrentUtilization of
V$RESOURCE_LIMIT issue.
We checked CURRENT_UTILIZATION of V$RESOURCE_LIMIT.
The value of it seems to be unusual.
The number of Oracle processes was not increased, but
CURRENT_UTILIZATION of V$RESOURCE_LIMIT increased as time passes.
SYSDATE v$resource_limit v$process
CurrentUtilization count(*)
20040915 02:11:09 366 361
20040915 02:39:48 351 346
20040915 03:04:52 358 344
20040915 03:34:52 366 349
20040915 04:04:52 368 351
20040915 04:30:57 376 352
20040915 05:00:57 384 352
20040915 05:30:57 405 372
…
20040916 14:02:00 751 458
20040916 14:32:01 752 459
20040916 15:02:01 754 460
20040916 15:32:02 756 462
20040916 16:02:02 766 462
20040916 16:32:03 772 462
20040916 17:02:04 771 461
20040916 17:32:04 774 464
20040916 18:02:05 781 464
20040916 18:32:05 783 466
The result of ps command was the same as the count of V$PROCESS.
It seems that some problem occurred in v$resource_limit.
DIAGNOSTIC ANALYSIS:
——————–
CURRENT_UTILIZATION of V$RESOURCE_LIMIT seems to be
increased/decreased when Oracle process was created/deleted.
So the value is CURRENT_UTILIZATION of V$RESOURCE_LIMIT
is almost same as the value of count(*) of V$PROCESS.
But it was not so.
Incorrect (always increasing) values showed in v$resource_limit for the transactions field
Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.2
This problem can occur on any platform.
Symptoms
Values in v$resource_limit go way too high for the transactions value.
The value is continuously increasing, while it’s clear the actual number of transactions is not that big:
select * from v$resource_limitwhile:
select count(*) from v$transaction————
67
Cause
This is caused by:
bug: <<5768464>> V$RESOURCE_LIMIT TRANSACTIONS VALUE KEEPS INCREASING ALL THE TIME
probably a duplicate of:
Bug: <<4489041>> CURRENT_UTILIZATION IN V$RESOURCE_LIMIT BECOMES LARGER THAN CURRENT TRANSACTIONS
Solution
To implement the solution, please execute the following steps:
1. use the number of records in v$transaction instead of the value in v$resource_limit view.
2. monitor the evolution of:
Bug: <<4489041>> CURRENT_UTILIZATION IN V$RESOURCE_LIMIT BECOMES LARGER THAN CURRENT TRANSACTIONS
and
Bug: <<5768464>> V$RESOURCE_LIMIT TRANSACTIONS VALUE KEEPS INCREASING ALL THE TIME
on Metalink.
Leave a Reply