v$resource_limit 与 v$license

 

v$resource_limit 与 v$license

 

From: http://hi.baidu.com/edeed/blog/item/88e9cbef8d318133acafd5e7.html

 

2008-06-25 17:40

SQL> select count(*) from v$session; COUNT(*) ----------        284 得到当前的session总数（但变化很快）。 SQL> select * from v$resource_limit where resource_name = 'sessions'; RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU ------------------------------ ------------------- --------------- ---------- ---------- sessions                                       286             380        555        555 CURRENT_UTILIZATION 得到当前被用的session总数。 MAX_UTILIZATION是各项资源没有达到的最大值。 SQL> select * from v$license; SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT SESSIONS_HIGHWATER USERS_MAX ------------ ---------------- ---------------- ------------------ ----------            0                0              276                363          0 可以得道自从实例启动以来，连接数据库的用户session的曾经达到的最大值（SESSIONS_HIGHWATER）和当前值（SESSIONS_CURRENT，该值并不等于 v$session 中查询出来的数量，似乎并不包括 Oracle 本身连接的 SESSION，但似乎还是有点出入，具体是怎样的对应关系，我还没有搞明白） 以下来自Oracle Docs： 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:: SQL> SELECT resource_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'; RESOURCE_NAME USAGE ------------------------------ ---------- processes 85.8 sessions 51.5315315 enqueue_locks .604719764 enqueue_resources 1.80451128 gcs_resources 0 gcs_shadows 0 dml_locks .245901639 temporary_table_locks 0 transactions 3375.90164 branches 0 cmtcallbk 0 sort_segment_locks 0 max_rollback_segments 17.8861789 max_shared_servers 5 parallel_max_servers 0 15 rows selected. --End--