Oracle Database Cache Concepts and Administration Guide Release 1.0.2 Part Number A86617-01 |
Library | Service | Contents | Index

# A
Oracle Database Cache Views

Oracle Database Cache provides the following views, which let you use SQL to obtain information, such as statistics and objects cached, from the cache:

- DBA_ICACHE_AGGREGATE_STATS: Contains information about the number of queries that were hits, misses, or other.

- DBA_ICACHE_OBJECTS: Contains information about the tables and PL/SQL objects that are cached.

- DBA_ICACHE_QUERY_STATS: Contains information, such as status, hit and miss count, and SQL text, about each query executed.

## DBA_ICACHE_AGGREGATE_STATS View

The view contains statistics about the queries executed by clients. It contains a single row that represents the aggregate statistics for the entire cache.

The following table shows the columns in the view:

Column Name | Data Type | Description
--- | --- | ---
HIT_COUNT | NUMBER(8) | The number of queries that were satisfied by the cache.
MISS_COUNT | NUMBER(8) | The number of queries that were not satisfied by the cache and that were routed to the origin database.
OTHER_COUNT | NUMBER(8) | The number of requests that were requests other than queries, such as updates, inserts, or deletes.
TOTAL_COUNT | NUMBER(8) | The total number of queries executed.

The following example shows a SQL query that retrieves information from this view:

    SQL> select * from dba_icache_aggregate_stats;

     HIT_COUNT MISS_COUNT OTHER_COUNT TOTAL_COUNT
    ---------- ---------- ----------- -----------
       3670945     196282        2075     3869302

## DBA_ICACHE_OBJECTS View

This view contains information about the objects in the cache. It contains one row for each object in the cache.

The following table shows the columns in the view:

Column Name | Data Type | Description
--- | --- | ---
OBJECT_NAME | VARCHAR2(128) | The name of the cached object.
OBJECT_TYPE | VARCHAR2(128) | The type of cached object. Valid values are: TABLE PACKAGE FUNCTION PROCEDURE
OWNER | VARCHAR2(128) | The owner of the cached object.
OBJECT_SIZE | NUMBER | The size of the cached table, in kilobytes (KB).
SYNC_METHOD | VARCHAR2(128) | The type of synchronization method for tables. Valid values are: INCREMENTAL COMPLETE Because you cannot set a synchronization method for PL/SQL objects, this column is empty for rows describing PL/SQL objects.
AUTO_REFRESH | CHAR(1) | Whether or not the synchronization is scheduled to execute automatically at specified intervals. Valid values are: Y: Yes, the synchronization is scheduled. N: No, the synchronization is not scheduled. Because you cannot schedule synchronization for PL/SQL objects, this column is empty for rows describing PL/SQL objects.
INTERVAL | NUMBER | The interval, expressed in seconds, between scheduled synchronizations. Because you cannot set a synchronization interval for PL/SQL objects, this column is empty for rows describing PL/SQL objects.
START_DATE | DATE | The date and time for the synchronization to begin. Because you cannot schedule synchronization for PL/SQL objects, this column is empty for rows describing PL/SQL objects.

The following example shows a query that returns information about the cached table EMP:

    SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
    Session altered.

    SQL> select object_name, sync_method, start_date, interval from dba_icache_
    objects where object_name='EMP';

    OBJECT_NAME
    --------------------------------------------------------------------------------
    SYNC_METHOD
    --------------------------------------------------------------------------------
    START_DATE
    --------------------------------------------------------------------------------
      INTERVAL
    ----------
    EMP
    COMPLETE
    05-NOV-2000 01:01:00
         86400

## DBA_ICACHE_QUERY_STATS View

This view contains information, such as status, hit and miss count, and SQL text, about each query executed against the cache. It contains one row for each query.

The following table shows the columns in the view:

Column Name | Data Type | Description
--- | --- | ---
STATUS | VARCHAR2(7) | The last known status of this query. Valid values are: 'HIT' 'MISS' 'UNKNOWN'
HIT_COUNT | NUMBER(8) | The number of times the query was satisfied by the cache.
MISS_COUNT | NUMBER(8) | The number of times the query was not satisfied by the cache and was routed to the origin database.
TOTAL_COUNT | NUMBER | The total number of times the query was executed.
USERNAME | VARCHAR2(20) | The user who executed this query.
SQL_TEXT | VARCHAR2(150) | The SQL text of the query.

The following example shows the number of queries executed against the EMP table and the total number of times they were executed:

    SQL> select count(*), sum(TOTAL_COUNT) from DBA_ICACHE_QUERY_STATS
    where upper(SQL_TEXT) like '%SCOTT.EMP%';

      COUNT(*)   SUM(TOTAL_COUNT)
    ---------- ----------
            23    1300

| Copyright © 2000 Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index