10:20:54 scott@felix SQL>SELECT ID,TYPE,NAME,BLOCK_COUNT,ROW_COUNTFROM V$RESULT_CACHE_OBJECTS;
IDTYPE NAME BLOCK_COUNT ROW_COUNT
---------- ------------------------------------------------------------ ----------- ----------
0Dependency SCOTT.FELIX 1 0
1Result select /*+result_cache */ count(*) from 1 1
felix
10:21:19 scott@felix SQL>
V$RESULT_CACHE_OBJECTS displays all theobjects (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:
|
| 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(128) |
Name (for example, SQL prefix or PL/SQL function name) |
| NAMESPACE |
VARCHAR2(5) |
Namespace:
|
| 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_COUNT |
NUMBER |
Number of columns in the cached resultFoot 1 |
| PIN_COUNT |
NUMBER |
Number of active scans on this resultFootref 1 |
| SCAN_COUNT |
NUMBER |
Total number of scans initiated on the cached resultFootref 1 |
| ROW_COUNT |
NUMBER |
Total number of rows in the cached resultFootref 1 |
| ROW_SIZE_MAX |
NUMBER |
Size of the largest row (in bytes)Footref 1 |
| ROW_SIZE_MIN |
NUMBER |
Size of the smallest row (in bytes)Footref 1 |
| ROW_SIZE_AVG |
NUMBER |
Average size of a row (in bytes)Footref 1 |
| BUILD_TIME |
NUMBER |
Amount of time (in hundredths of a second) it took to build the cached resultFootref 1 |
| LRU_NUMBER |
NUMBER |
LRU list position (the smaller the value, the more recent the usage)Footref 1 |
| OBJECT_NO |
NUMBER |
Dictionary object number of the dependency objectFoot 2 |
| INVALIDATIONS |
NUMBER |
Number of times the object has invalidated its dependentsFootref 2 |
| SPACE_OVERHEAD |
NUMBER |
Overhead (in bytes) for the resultFootref 1 |
| SPACE_UNUSED |
NUMBER |
Unused space (in bytes) for the resultFootref 1 |
| CACHE_ID |
VARCHAR2(93) |
CacheId for the result (object name if it's a dependency) |
| CACHE_KEY |
VARCHAR2(93) |
CacheKey for the result (object name if it's a dependency) |
| DB_LINKFoot 3 |
VARCHAR2(3) |
Possible values:
- YES: If the result cache object references a remote database object NO: If the result cache object does not reference a remote database object
|
| CHECKSUMFootref 3 |
NUMBER |
Checksum for the result object. The checksum is computed over all the blocks in the result cache object minus the object header. |
Footnote 1 These columns are only valid for TYPE = Result; otherwise, they are NULL.
Footnote 2 These columns are only valid for TYPE = Dependency; otherwise, they are NULL.
Footnote 3 This column is available starting with Oracle Database11g Release 2 (11.2.0.4)
Table 7-6 Views and TablesRelated to the Server and Client Result Caches
| View/Table |
Description |
| V$RESULT_CACHE_STATISTICS |
Lists various serv |