Oracle Database Cache Concepts and Administration Guide Release 1.0.2.1 Part Number A88706-01 |
|
Oracle Database Cache provides a cache management API that allows you to manage caches using scripts or a utility such as SQL*Plus. The API consists of a PL/SQL package, DBMS_ICACHE, that is loaded into the cache during installation. This package provides the same functionality as that provided by Cache Manager. In addition, it allows you to:
The package is installed in the SYS schema during the Oracle Database Cache installation.
The following tables provide brief descriptions of each PL/SQL procedure and function. Full descriptions of each procedure and function are provided later in this chapter.
The following table lists the PL/SQL functions and procedures that you can use to pause and restart caching, enable routing of PL/SQL procedures, enable gathering of statistics, control the interval at which statistics are gathered, and reset statistics:
The following table lists the PL/SQL functions and procedures that you can use to add objects to or remove objects from the cache. The objects include tables and PL/SQL packages, procedures, and functions:
To use the PL/SQL package, you connect to the cache as a user with DBA privileges. Some procedures, such as DBMS_ICACHE.ADD_TABLE, require access to the origin database. Before you call those procedures, you must call the DBMS_ICACHE.SET_ORIGIN_CREDENTIALS procedure. See "SET_ORIGIN_CREDENTIALS Procedure" for more information.
The PL/SQL procedures and functions in this package raise the application error ORA-20001, following by the number and text of an Oracle Database Cache error message. For example, if you attempt to remove a table from the cache, but the table is not cached, you receive the following error:
ORA-20001: WTE-03806 Object not cached. ORA-06512: at "SYS.DBMS_ICACHE", line 1227 ORA-06512: at line 1
See Appendix B for a description of the Oracle Database Cache errors.
For information about handling errors returned by PL/SQL procedures, see the PL/SQL User's Guide and Reference.
This procedure adds the named PL/SQL object (package, procedure, or function) to the cache. It also adds the associated object privileges and any synonyms to the cache.
DBMS_ICACHE.ADD_PLSQL_OBJECT ( schema_name IN VARCHAR2, object_name IN VARCHAR2 );
The name of the schema on the origin database that contains the PL/SQL object to be cached.
The name of the PL/SQL object to be cached.
Assume that you have a PL/SQL procedure called GET_TOY_ITEMS in the schema ICPB. Because this procedure contains only queries, it is a good candidate for caching. To cache the procedure, take the following steps:
connect system/manager @mycache.us.oracle.com AS SYSDBA;
execute DBMS_ICACHE.SET_ORIGIN_CREDENTIALS ('system','manager');
execute DBMS_ICACHE.ADD_PLSQL_OBJECT ('ICPB', - 'GET_TOY_ITEMS');
describe ICPB.GET_TOY_ITEMS
This procedure adds the specified table, its data, and its dependent objects to the cache. It also sets the synchronization policy of the table.
DBMS_ICACHE.ADD_TABLE( schema_name IN VARCHAR2, table_name IN VARCHAR2, sync_method IN NUMBER, auto_refresh IN BOOLEAN, start_date IN DATE, interval IN INTEGER, build_indexes IN BOOLEAN, column_list IN VARCHAR2, exclusion_list IN VARCHAR2 );
The name of the schema that contains the table to be cached.
The name of the table to be cached.
The synchronization method to be used for the table. Possible values are the following constants:
The default is SYNC_COMPLETE.
A flag to indicate whether the table should be synchronized automatically on a given schedule or synchronized explicitly. To specify that the synchronization be performed automatically, use the value TRUE. To specify that the synchronization be performed only when it is explicitly initiated, use the value FALSE.
The default is FALSE.
The date and time to start the automatic synchronization. If you specify TRUE for auto_refresh, but you do not specify a start_date, the start_date is set to the value SYSDATE.
The default is NULL.
For information about date and time formats, see the Oracle8i SQL Reference.
The interval, in seconds, between automatic synchronization. If you specify TRUE for auto_refresh, but you do not specify an interval, Oracle Database Cache returns the error "Automatic refresh interval is not provided."
This parameter is currently ignored.
This parameter is currently ignored.
This parameter is currently ignored.
You must execute the SET_ORIGIN_CREDENTIALS procedure before you execute this procedure.
The following example adds the TOY_ITEMS table from the schema STOCK to the cache. It specifies an incremental, scheduled synchronization.
! Change the default date format to specify hours, minutes, seconds. alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'; execute DBMS_ICACHE.ADD_TABLE ('ICPB', - 'TOY_ITEMS', - DBMS_ICACHE.SYNC_INCREMENTAL, - TRUE, - '04-OCT-2000 01:10:00', 3600*24);
This function returns a flag that indicates whether or not the routing of PL/SQL to the cache is enabled or whether or not the collection of statistics is enabled.
DBMS_ICACHE.GET_FLAG ( flag IN NUMBER ) RETURN BOOLEAN;
The name of the flag to be retrieved. Possible values are the following constants:
Returns TRUE if the specified flag is enabled. Returns FALSE if the specified flag is disabled.
The following example uses the GET_FLAG function to check whether or not PL/SQL subprograms are routed to the cache. If they are, it disables routing of PL/SQL.
begin if DBMS_ICACHE.GET_FLAG (DBMS_ICACHE.PLSQL_ROUTING_ENABLED) then DBMS_ICACHE.SET_FLAG(DBMS_ICACHE.PLSQL_ROUTING_ENABLED, FALSE); end if; end;
This function returns a constant that indicates whether the cache is running or paused.
DBMS_ICACHE.GET_ROUTING_STATE RETURN NUMBER;
Returns the constant RUNNING if the cache is running. If the cache is running, queries are routed to the cache. You can perform all operations on the cache, and all statistics are gathered.
Returns the constant PAUSED if the cache is paused. If the cache is paused, the cache is running, but queries are routed directly to the origin database. If a cache is paused, you can view all information about the cache. You can add and remove tables and synchronize a paused cache. Some statistics are gathered while the cache is paused, but the Hit/Miss Percentage and Hit/Miss Count per Second statistics are not gathered.
The following example uses the GET_ROUTING_STATE procedure to check if the cache is paused. If it is, the example calls the procedure SET_ROUTING_STATE to start the paused cache.
begin if DBMS_ICACHE.GET_ROUTING_STATE ( ) = DBMS_ICACHE.PAUSED then DBMS_ICACHE.SET_ROUTING_STATE (DBMS_ICACHE.RUNNING); end if; end;
This function returns the interval, in seconds, at which a specified type of statistics is collected.
DBMS_ICACHE.GET_STATS_INTERVAL ( interval IN NUMBER ) RETURN NUMBER;
The type of statistics collection for which the interval is to be retrieved. Possible values are the following constants:
The interval, in seconds, for the specified type.
The following example returns the interval for QUERY_STATS to the screen in SQL*Plus:
set serveroutput on execute DBMS_OUTPUT.PUT_LINE - (DBMS_ICACHE.GET_STATS_INTERVAL(DBMS_ICACHE.QUERY_STATS));
This function returns the date and time that the specified statistics were last reset.
DBMS_ICACHE.GET_STATS_RESET_TIME ( category IN NUMBER) RETURN DATE;
The category of statistics collection for which you want the last reset time returned. Possible values are the following constants:
These statistics can be viewed on the Hit/Miss Percentages chart and the Hit/Miss Count per Second chart.
This reset time and these statistics can viewed on the Hit/Miss Statistics property page.
The queries can be viewed on the Hit/Miss Statistics property page.
Returns the date and time that the specified type of statistics was reset to zero or deleted.
The following example returns the last reset time for AGGREGATE_COUNTERS to the screen in SQL*Plus:
set serveroutput on execute DBMS_OUTPUT.PUT_LINE - (DBMS_ICACHE.GET_STATS_RESET_TIME (DBMS_ICACHE.AGGREGATE_COUNTERS));
This procedure calculates the amount of disk space required to cache the named table. It calculates the size of the table and its dependent objects. Use this procedure to ensure that sufficient disk space exists before you add a table to the cache.
DBMS_ICACHE.GET_TABLE_SIZE ( schema_name IN VARCHAR2, table_name IN VARCHAR2, table_size OUT NUMBER );
The name of the schema that contains the table to be sized.
The name of the table to be sized.
The parameter to hold the size of the table, in megabytes (MB).
You must execute the SET_ORIGIN_CREDENTIALS procedure before you execute this procedure.
The following example shows how to get the size for the SCOTT.EMP table:
declare tab_size number; begin DBMS_ICACHE.GET_TABLE_SIZE ('scott', 'emp', tab_size); DBMS_OUTPUT.PUT_LINE:('Table size is:' || tab_size); end; / Table size is: .0078125
This procedure removes the named PL/SQL object (package, procedure, or function) from the cache.
DBMS_ICACHE.REMOVE_PLSQL_OBJECT ( schema_name IN VARCHAR2, object_name IN VARCHAR2 );
The name of the schema that contains the PL/SQL object to be removed.
The name of the PL/SQL object to be removed from the cache.
You must execute the SET_ORIGIN_CREDENTIALS procedure before you execute this procedure.
The following example removes the PL/SQL procedure GET_TOY_ITEMS in schema ICPB from the cache:
execute DBMS_ICACHE.REMOVE_PLSQL_OBJECT('ICPB','GET_TOY_ITEMS' );
This procedure removes the named table, its data, and its dependent objects from the cache. If no other objects owned by the named schema are in the cache, Oracle Database Cache performs additional cleanup operations.
DBMS_ICACHE.REMOVE_TABLE ( schema_name IN VARCHAR2, table_name IN VARCHAR2 );
The name of the schema that contains the table to be removed.
The name of the table to be removed from the cache.
You must execute the SET_ORIGIN_CREDENTIALS procedure before you execute this procedure.
The following example removes the table ICPB.TOY_ITEMS from the cache:
execute DBMS_ICACHE.REMOVE_TABLE ('ICPB','TOY_ITEMS' );
This procedure resets the specified category of statistics.
DBMS_ICACHE.RESET_STATS ( category IN NUMBER );
The category of statistics that you want to reset. Possible values are the following constants:
These statistics can be viewed on the Hit/Miss Percentages chart and the Hit/Miss Count per Second chart.
These statistics can viewed on the Hit/Miss Statistics property page.
The queries can be viewed on the Hit/Miss Statistics property page.
The following example resets the AGGREGATE_COUNTERS to zero:
execute DBMS_ICACHE.RESET_STATS (DBMS_ICACHE.AGGREGATE_COUNTERS);
This procedure enables or disables the routing of PL/SQL subprograms (procedures and functions) and anonymous blocks to the cache or it enables or disables the collection of statistics.
DBMS_ICACHE.SET_FLAG ( name IN NUMBER, value IN BOOLEAN );
The name of the flag to be set. Possible values are the following constants:
The value to which to set the flag. To enable a flag, set the value to TRUE. To disable a flag, set the value to FALSE.
The following example enables the collection of statistics:
execute DBMS_ICACHE.SET_FLAG ( - DBMS_ICACHE.STATS_REPORTING_ENABLED, - TRUE );
The following example disables the routing of PL/SQL subprograms and anonymous blocks to the cache:
execute DBMS_ICACHE.SET_FLAG ( - DBMS_ICACHE.PLSQL_ROUTING_ENABLED, - FALSE );
This procedure sets the user name and password to be used to authenticate the user as a DBA on the origin database. You must execute this procedure before you execute procedures that involve the origin database.
DBMS_ICACHE.SET_ORIGIN_CREDENTIALS ( username IN VARCHAR2, password IN VARCHAR2, connect_type IN INTEGER );
The name of a user on the origin database who has DBA privileges.
The password associated with the specified user.
The type of connection to be made to the origin database. Possible values are the following constants:
The default is CONNECT_NORMAL.
You must execute this procedure before you execute the following procedures, which involve the origin database:
The following example shows how to call this procedure from SQL*Plus. You must be connected to the cache as a user with DBA privileges.
! Set the credentials for the origin database. execute DBMS_ICACHE.SET_ORIGIN_CREDENTIALS ('system','manager');
This procedure starts a paused cache or pauses a running cache.
DBMS_ICACHE.SET_ROUTING_STATE ( state IN NUMBER );
The state to which you want to set the cache. To start a paused cache, set the value to the constant RUNNING. To pause a running cache, set the value to the constant PAUSED.
The following example pauses the cache:
execute DBMS_ICACHE.SET_ROUTING_STATE (DBMS_ICACHE.PAUSED );
This procedure sets the interval, in seconds, for the specified type of statistics collection.
DBMS_ICACHE.SET_STATS_INTERVAL ( interval IN NUMBER, seconds IN NUMBER );
The type of statistics collection for which the interval is to be set. Possible values are the following constants:
This interval reflects the rate at which the Hit/Miss Percentages chart and the Hit/Miss Count per Second chart are refreshed. (The charts are on the Performance Monitor property page of Cache Manager.) By default, Oracle Database Cache sets this interval to 5 seconds. If you are viewing the charts in Cache Manager, the rate is lowered. When the charts are closed, the rate reverts to the specified rate.
This interval affects the rate at which the Hit/Miss Statistics property page in the Cache Manager is refreshed. By default, Oracle Database Cache sets the interval to 10 seconds. This interval should be equal to or less than the interval for QUERY_STATS.
The length of the interval, in seconds.
The following example sets the interval for collecting AGGREGATE_STATS to 10 seconds:
execute DBMS_ICACHE.SET_STATS_INTERVAL ( - DBMS_ICACHE.AGGREGATE_STATS, - 10 );
This procedure modifies the synchronization method of a table in the cache.
DBMS_ICACHE.SET_SYNC_METHOD ( schema_name IN VARCHAR2, table_name IN VARCHAR2, sync_method IN NUMBER );
The name of the schema that contains the table to be synchronized.
The name of the table to be synchronized.
The type of synchronization to be used for the named table. Possible values are the following constants:
The default is SYNC_COMPLETE.
The following example changes the synchronization method of ICPB.TOY_ITEMS to complete synchronization.
execute DBMS_ICACHE.SET_SYNC_METHOD ('ICPB', - 'TOY_ITEMS', - DBMS_ICACHE.SYNC_COMPLETE);
This procedure modifies the synchronization schedule for a table in the cache. You can specify whether or not you want to schedule the table for automatic synchronization and, if so, the start time and interval for the synchronization.
DBMS_ICACHE.SET_SYNC_SCHEDULE ( schema_name IN VARCHAR2, table_name IN VARCHAR2, auto_refresh IN BOOLEAN, start_date IN DATE, interval IN INTEGER );
The name of the schema that contains the named table.
The name of the table for which you want to modify the synchronization schedule.
A flag to indicate whether the table should be synchronized automatically on a given schedule or synchronized explicitly. To specify that the synchronization be performed automatically, use the value TRUE. To specify that the synchronization be performed only when it is explicitly initiated, use the value FALSE.
The default is FALSE.
The date and time to start the automatic synchronization. If you specify TRUE for auto_refresh, you must specify a start_date for the synchronization to be performed.
The default is NULL.
For information about date and time formats, see the Oracle8i SQL Reference.
The interval, in seconds, between automatic synchronization of the table. If you specify TRUE for auto_refresh, you should specify an interval. If you do not, the synchronization will be performed only once, at the start_date.
The default is zero.
The following example changes the schedule of the synchronization of the table ICPB.TOY_ITEMS.
execute DBMS_ICACHE.SET_SYNC_SCHEDULE( 'ICPB', - 'TOY_ITEMS', - TRUE, - '05-NOV-2000 02:00:00', - 3600*24);
The following example changes the synchronization of the table ICPB.TOY_ITEMS to unscheduled.
execute DBMS_ICACHE.SET_SYNC_SCHEDULE( 'ICPB', - 'TOY_ITEMS', - FALSE );
This procedure initiates the immediate synchronization of a PL/SQL object. When you call this procedure, it synchronizes the definition (the metadata) of the specified PL/SQL object in the cache with the object in the origin database. Use this procedure when the definition of a PL/SQL object in the origin database is modified.
DBMS_ICACHE.SYNC_PLSQL_OBJECT ( schema_name IN VARCHAR2, object_name IN VARCHAR2 );
The name of the schema that contains the PL/SQL object to be synchronized.
The name of the PL/SQL object to be synchronized.
The following example updates the definition of the PL/SQL procedure GET_MUSIC_ITEMS:
execute DBMS_ICACHE.SYNC_PLSQL_OBJECT ('ICPB', - 'GET_MUSIC_ITEMS');
This procedure initiates the immediate synchronization of a table. When you call this procedure, it synchronizes the specified table in the cache with the table in the origin database. Use this procedure when the synchronization policy of a table is not Scheduled. Even if the synchronization policy is Scheduled, you may decide that you want to initiate the action, rather than wait until the next scheduled synchronization.
DBMS_ICACHE.SYNC_TABLE ( schema_name IN VARCHAR2, table_name IN VARCHAR2 );
The name of the schema that contains the table to be synchronized.
The name of the table to be synchronized.
The following example synchronizes the table ICPB.TOY_ITEMS immediately:
execute DBMS_ICACHE.SYNC_TABLE ( 'ICPB', 'TOY_ITEMS');
This procedure synchronizes the list of users on the cache with the list of users on the origin database. It updates the list of nonsystem users and the attributes of the users on the cache, so that it is consistent with the list of nonsystem users on the origin database.
DBMS_ICACHE.SYNC_USERS ;
None.
The following procedure synchronizes the user list:
execute DBMS_ICACHE.SYNC_USERS ;
|
![]() Copyright © 2001 Oracle Corporation. All Rights Reserved. |
|