Wednesday, April 8, 2009

V$DB_OBJECT_CACHE

V$DB_OBJECT_CACHE displays database objects that are cached in the library cache. Objects include tables, indexes, clusters, synonym definitions, PL/SQL procedures and packages, and triggers.

Column

Datatype

Description

OWNER

VARCHAR2(64)

Owner of the object

NAME

VARCHAR2(1000)

Name of the object

DB_LINK

VARCHAR2(64)

Database link name, if any

NAMESPACE

VARCHAR2(28)

Library cache namespace of the object: TABLE/PROCEDURE, BODY, TRIGGER, INDEX, CLUSTER, OBJECT

TYPE

VARCHAR2(28)

Type of the object: INDEX, TABLE, CLUSTER, VIEW, SET, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, CLASS, OBJECT, USER, DBLINK

SHARABLE_MEM

NUMBER

Amount of sharable memory in the shared pool consumed by the object

LOADS

NUMBER

Number of times the object has been loaded. This count also increases when an object has been invalidated.

EXECUTIONS

NUMBER

Not used

See Also: "V$SQLAREA" to see actual execution counts

LOCKS

NUMBER

Number of users currently locking this object

PINS

NUMBER

Number of users currently pinning this object

KEPT

VARCHAR2(3)

(YES | NO) Depends on whether this object has been "kept" (permanently pinned in memory) with the PL/SQL procedure DBMS_SHARED_POOL.KEEP

CHILD_LATCH

NUMBER

Child latch number that is protecting the object. This column is obsolete and maintained for backward compatibility.

INVALIDATIONS

NUMBER

Total number of times objects in the namespace were marked invalid because a dependent object was modified

Oracle data dictionary views

Oracle dynamic performance views

RESULT_CACHE_REMOTE_EXPIRATION

Property

Description

Parameter type

Integer

Default value

0

Modifiable

ALTER SESSION, ALTER SYSTEM

Range of values

0 to operating system-dependent

Basic

No

RESULT_CACHE_REMOTE_EXPIRATION specifies the number of minutes that a result using a remote object is allowed to remain valid. Setting this parameter to 0 implies that results using remote objects should not be cached. Setting this parameter to a nonzero value can produce stale answers (for example, if the remote table used by a result is modified at the remote database).

Note:

1. The parameter was introduced in 11g.

2. Query for the current value of the parameter

select name, value, isdefault, isses_modifiable, issys_modifiable,

isinstance_modifiable, isdeprecated, description

from v$parameter

where upper(name) = 'RESULT_CACHE_REMOTE_EXPIRATION';

Oracle initializatoin parameters

RESULT_CACHE_MODE


Property
Description
Parameter type
String
Syntax
RESULT_CACHE_MODE = { MANUAL | FORCE }
Default value
MANUAL
Modifiable
ALTER SESSION, ALTER SYSTEM
Basic
No
RESULT_CACHE_MODE specifies when a ResultCache operator is spliced into a query's execution plan.
Values:
  • MANUAL
The ResultCache operator is added only when the query is annotated (that is, hints).
  • FORCE
The ResultCache operator is added to the root of all SELECT statements (provided that it is valid to do so).
For the FORCE setting, if the statement contains a NO_RESULT_CACHE hint, then the hint takes precedence over the parameter setting.
Note:
1. The parameter was introduced in 11g. The Query Result Cache feature is available in Oracle Enterprise Edition only. Need to set a minimum value for shared_pool_size to enable result cache.
2. The parameter is related to an 11g new feature: SQL query Result Cache.
A separate shared memory pool is now used for storing and retrieving cached results. Query retrieval from the query result cache is faster than rerunning the query. The new query result cache enables explicit caching of results in database memory. Subsequent queries using the cached results will experience significant performance improvements.

Enable Result Caching: set the RESULT_CACHE_MODE initialization parameter at the system, session, or table level to the following values:
· MANUAL - The results of a query can only be stored in the result cache by using the result_cache hint, such as: SELECT /*+ result_cache */ deptno, avg(sal) FROM emp GROUP BY deptno;
· FORCE - All results are stored in the result cache. If you do not wish to include the result of the query in the result cache, then you must use the /*+ no_result_cache */ hint in your query: SELECT /*+ no_result_cache */ deptno, avg(sal) FROM emp GROUP BY deptno;
The database automatically invalidates a cached result whenever a transaction modifies the data or metadata of any of the database objects used to construct that cached result.

There is another related new feature introduced in 11g as PL/SQL Function Result Cache.
3. Query for the current value of the parameter
select name, value, isdefault, isses_modifiable, issys_modifiable,
isinstance_modifiable, isdeprecated, description
from v$parameter
where upper(name) = 'RESULT_CACHE_MODE';

Oracle initializatoin parameters


More Oracle DBA tips, please visit Oracle DBA Tips