V$RESULT_CACHE_OBJECTS

V$RESULT_CACHE_OBJECTS displays all the objects (both cached results and dependencies) and their attributes.

Column Datatype Description
ID NUMBER Identifier for the cache object (also the ID of the first block)
TYPE VARCHAR2(10)

Type of the cache object:

  • Dependency

  • Result

  • Temp
STATUS VARCHAR2(9)

Status of the object:

  • New - Result is still under construction

  • Published - Result is available for use

  • Bypass - Result will be bypassed from use

  • Expired - Result has exceeded expiration time

  • Invalid - Result is no longer available for use

BUCKET_NO NUMBER Internal hash bucket for the object
HASH NUMBER Hash value for the object
NAME VARCHAR2(387) Name (for example, SQL prefix or PL/SQL function name)
NAMESPACE VARCHAR2(10)

Namespace:

  • SQL

  • PLSQL

  • KEY VECTOR
CREATION_TIMESTAMP DATE Time when the object was created
CREATOR_UID NUMBER UID that created the object
DEPEND_COUNT NUMBER Number of dependencies (TYPE = Result) or dependents (TYPE = Dependency)
BLOCK_COUNT NUMBER Total number of blocks in the cached object
SCN NUMBER Build SCN (TYPE = Result) or invalidation SCN (TYPE = Dependency)
COLUMN_COUNTFoot 1 NUMBER Number of columns in the cached result
PIN_COUNTFoot 1 NUMBER Number of active scans on this result
SCAN_COUNTFoot 1 NUMBER Total number of scans initiated on the cached result
ROW_COUNTFoot 1 NUMBER Total number of rows in the cached result
ROW_SIZE_MAXFoot 1 NUMBER Size of the largest row (in bytes)
ROW_SIZE_MINFoot 1 NUMBER Size of the smallest row (in bytes)
ROW_SIZE_AVGFoot 1 NUMBER Average size of a row (in bytes)
BUILD_TIMEFoot 1 NUMBER Amount of time (in hundredths of a second) it took to build the cached result
LRU_NUMBERFoot 1 NUMBER LRU list position (the larger the value, the more recent the usage)
OBJECT_NOFoot 2 NUMBER Dictionary object number of the dependency object
INVALIDATIONSFoot 2 NUMBER Number of times the object has invalidated its dependents
TIME_SAVEDFoot 2 NUMBER Amount of time (in microseconds) saved by reuse
INVALIDATION_COSTFoot 2 NUMBER Amount of time (in microseconds) spent on invalidation
SPACE_OVERHEADFoot 1 NUMBER Overhead (in bytes) for the result
SPACE_UNUSEDFoot 1 NUMBER Unused space (in bytes) for the result
CACHE_ID VARCHAR2(387) CacheId for the result (object name if it’s a dependency)
CACHE_KEY VARCHAR2(387) CacheKey for the result (object name if it’s a dependency)
CHECKSUM NUMBER Checksum for the result object. The checksum is computed over all the blocks in the result cache object minus the object header.
EDITION_ID NUMBER Shows the edition’s object ID that was in use when the result was calculated
DB_LINK VARCHAR2(3)

Possible values:

  • YES: The result cache object references a remote database object

  • NO: The result cache object does not reference a remote database object

GLOBAL VARCHAR2(3)

Possible values:

  • YES: The object was fetched from the result cache of a remote database instance

  • NO: The object was recomputed on this instance, either because it was not available on a remote database instance or because the system deemed it was inefficient to fetch the object from a remote database instance

SUBCACHE_ID NUMBER Subcache ID
CON_ID NUMBER

The ID of the container to which the data pertains. Possible values include:

  • 0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.

  • 1: This value is used for rows containing data that pertain to only the root

  • n: Where n is the applicable container ID for the rows containing data

Footnote 1

This column is valid only for TYPE = Result; otherwise, its value is 0.

Footnote 2

This column is valid only for TYPE = Dependency; otherwise, its value is 0.