11g新动态性能视图V$SQL_MONITOR,V$SQL_PLAN_MONITOR

2010/09/16 by Leave a Comment

11g中引入了新的动态性能视图V$SQL_MONITOR，该视图用以显示Oracle监视的SQL语句信息。SQL监视会对那些并行执行或者消耗5秒以上cpu时间或I/O时间的SQL语句自动启动，同时在V$SQL_MONITOR视图中产生一条记录。当SQL语句正在执行，V$SQL_MONITOR视图中的统计信息将被实时刷新，频率为每秒1次。SQL语句执行完成后，监视信息将不会被立即删除，Oracle会保证相关记录保存一分钟(由参数_sqlmon_recycle_time所控制，默认为60s)，最终这些记录都会被删除并被重用。这一新的SQL性能监视特性仅在CONTROL_MANAGEMENT_PACK_ACCESS为DIAGNOSTIC+TUNING和STATISTICS_LEVEL为ALL|TYPICAL时被启用。
[Read more…]

Filed Under: Oracle, Oracle 11g新特性 Tagged With: , , , , , , ,

V$RESOURCE_LIMIT

2010/09/05 by Leave a Comment

“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_ALLOCATION of resources, but 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_ALLOCATIONis the same as the LIMIT_VALUE. Exceeding LIMIT_VALUE results in an error.
    Column Datatype Description
    RESOURCE_NAME VARCHAR2(30) Name of the resource
    CURRENT_UTILIZATION NUMBER Number of (resources, locks, or processes) currently being used
    MAX_UTILIZATION NUMBER Maximum consumption of this resource since the last instance start-up
    INITIAL_ALLOCATION VARCHAR2(10) Initial allocation. This will be equal to the value specified for the resource in the initialization parameter file (UNLIMITED for infinite allocation).
    LIMIT_VALUE VARCHAR2(10) Unlimited for resources and locks. This can be greater than the initial allocation value (UNLIMITED for infinite limit).

    Table 3-2  Values for RESOURCE_NAME column
    Resource Name Corresponds to this Initialization Parameter
    DISTRIBUTED_TRANSACTIONS See <<Parameter:DISTRIBUTED_TRANSACTIONS>>
    DML_LOCKS See <<Parameter:DML_LOCKS>>
    ENQUEUE_LOCKS This value is computed by Oracle. Use <<View:V$ENQUEUE_LOCK>> to obtain more information about the enqueue locks.
    ENQUEUE_RESOURCES See <<Parameter:ENQUEUE_RESOURCES>>
    LM_PROCESSES Lock manager processes
    LM_LOCKS See <<Parameter:LOCAL_LISTENER>>
    MTS_MAX_SERVERS See <<Parameter:MTS_MAX_SERVERS>>
    PARALLEL_SLAVES See <<Parameter:PARALLEL_MAX_SERVERS>>
    PROCESSES See <<Parameter:PROCESSES>>
    ROLLBACK_SEGMENTS See <<Parameter:MAX_ROLLBACK_SEGMENTS>>
    SESSIONS See <<Parameter:SESSIONS>>
    SORT_SEGMENT_LOCKS This value is computed by Oracle
    TEMPORARY_LOCKS This value is computed by Oracle
    TRANSACTIONS See <<Parameter:TRANSACTIONS>>

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_limittransactions 18593 18595 3965 UNLIMITED

while:

select count(*) from v$transactionCOUNT(*)
————
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.

 

Filed Under: Oracle Tagged With: , ,

沪ICP备14014813号-2

沪公网安备 31010802001379号