Friday, July 3, 2009

V$CONTEXT

V$CONTEXT displays set attributes in the current session.

Column

Datatype

Description

NAMESPACE

VARCHAR2(31)

Namespace that the attribute is in

ATTRIBUTE

VARCHAR2(31)

Name of the attribute

VALUE

VARCHAR2(4000)

Value of the attribute

Oracle data dictionary views

Oracle dynamic performance views

V$RESOURCE

V$RESOURCE contains resource name and address information.

Column

Datatype

Description

ADDR

RAW(4 | 8)

Address of the resource object

TYPE

VARCHAR2(2)

Resource type; the resource types are listed in the following table

ID1

NUMBER

Resource identifier #1

ID2

NUMBER

Resource identifier #2

Table Values for the TYPE Column: System Types

System Type

Description

System Type

Description

BL

Buffer hash table instance

NA..NZ

Library cache pin instance (A..Z = namespace)

CF

Control file schema global enqueue

PF

Password File

CI

Cross-instance function invocation instance

PI, PS

Parallel operation

CU

Cursor bind

PR

Process startup

DF

datafile instance

QA..QZ

Row cache instance (A..Z = cache)

DL

Direct loader parallel index create

RT

Redo thread global enqueue

DM

Mount/startup db primary/secondary instance

SC

System change number instance

DR

Distributed recovery process

SM

SMON

DX

Distributed transaction entry

SN

Sequence number instance

FS

File set

SQ

Sequence number enqueue

HW

Space management operations on a specific segment

SS

Sort segment

IN

Instance number

ST

Space transaction enqueue

IR

Instance recovery serialization global enqueue

SV

Sequence number value

IS

Instance state

TA

Generic enqueue

IV

Library cache invalidation instance

TS

Temporary segment enqueue (ID2=0)

JQ

Job queue

TS

New block allocation enqueue (ID2=1)

KK

Thread kick

TT

Temporary table enqueue

LA .. LP

Library cache lock instance lock (A..P = namespace)

UN

User name

MM

Mount definition global enqueue

US

Undo segment DDL

MR

Media recovery

WL

Being-written redo log instance

Oracle data dictionary views

Oracle dynamic performance views

V$RESOURCE_LIMIT

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. Many of the resources correspond to initialization parameters listed in the following table:

Resource Name

Corresponds to

DML_LOCKS

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"

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_ALLOCATION is the same as the LIMIT_VALUE. Exceeding LIMIT_VALUE results in an error.

Column

Datatype

Description

RESOURCE_NAME

VARCHAR2(30)

Name of the resource (see the table above)

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).

Oracle data dictionary views

Oracle dynamic performance views