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