NOTE
You cannot issue CREATE CACHE as an independent statement.
It is a clause allowed only as part of a CREATE DATABASE or
IMPORT statement.
You can also create a row cache using the ADD CACHE clause
of the ALTER DATABASE statement.
Creates a row cache that allows frequently referenced rows
to remain in memory even when the associated page has been
transferred back to disk. This saves in memory usage because
only the more recently referenced rows are cached versus caching
the entire buffer.
See the ALTER DATABASE statement and the CREATE DATABASE
statement for more information regarding the row cache areas.
1 – Environment
You can use the CREATE CACHE clause only within a CREATE DATABASE
or IMPORT statement. You can use the ADD CACHE clause only within
the ALTER DATABASE statement.
2 – Format
(B)0[m[1;4mCREATE[m[1m [1;4mCACHE[m[1m <row-cache-name> qwqwqqqqqqqqqqqqqqqqqqqqqqwqwq> [m
[1m x[m [1mtq> row-cache-params1 qu[m [1mx [m
[1m x[m [1mmq> row-cache-params2 qj[m [1mx [m
[1mmqqqqqqqqqqqq <qqqqqqqqqqqqj[m
(B)0[m[1madd-row-cache-clause = [m [1m [m
[1m [m [1m [m
[1mqqq> [1;4mADD[m[1m [1;4mCACHE[m[1m <row-cache-name> qwqwqqqqqqqqqqqqqqqqqqqqqqwqwq>[m
[1m x tq> row-cache-params1 qu x [m
[1m x mq> row-cache-params2[m [1mqj x [m
[1mmqqqqqqqqqqqqq<qqqqqqqqqqqqj[m
(B)0[m[1mrow-cache-params1 = [m [1m [m
[1m [m [1m [m
[1mqqwq> [1;4mALLOCATION[m[1m IS <n> qwqqwqqqqqqqqqqqwqqqqqqqqqqqqqqqqwq> [m
[1m tq> [1;4mEXTENT[m[1m IS <n> qqqqqj tq> [1;4mBLOCK[m[1m qqu [m [1m x [m
[1m x mq> [1;4mBLOCKS[m[1m qj [m [1m x [m
[1m tq> [1;4mCACHE[m[1m [1;4mSIZE[m[1m IS <n> qwqqqq> [1;4mROW[m[1m qqwqqqqqqqqqqqqqqqqqqu [m
[1m x mqqqq> [1;4mROWS[m[1m qj [m [1m x [m
[1m [m [1mtq>[m [1;4mCHECKPOINT[m[1m qw>[m [1;4mUPDATED[m[1m [1;4mROWS[m[1m TO qw> [1;4mBACKING[m[1m [1;4mFILE[m [1mqwqu[m
[1m [m [1mx[m [1mx[m [1mm>[m [1;4mDATABASE[m [1mqqqqqu[m [1mx[m
[1m [m [1mx[m [1mm>[m [1;4mALL[m[1m [1;4mROWS[m[1m TO [1;4mBACKING[m[1m [1;4mFILE[m[1m qqqqqqqqqj[m [1mx[m
[1m tq> [1;4mLARGE[m[1m [1;4mMEMORY[m[1m IS qqqqwqwq> [1;4mENABLED[m[1m qqwqqqqqqqqqqqqqqu[m [1m [m
[1m tq> [1;4mROW[m[1m [1;4mREPLACEMENT[m[1m IS qj mq> [1;4mDISABLED[m[1m qj[m [1m x[m [1m [m
[1m tq> [1;4mLOCATION[m[1m IS qq> <directory-spec> qqqqqqqqqqqqqqqqqqu[m [1m [m
[1m mq> [1;4mNO[m[1m [1;4mLOCATION[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj[m [1m [m
[1m [m [1m [m
(B)0[m[1mrow-cache-params2 = [m
[1m [m
[1mqqwq> [1;4mNUMBER[m[1m [1;4mOF[m[1m qwq> [1;4mRESERVED[m [1mqwq>[m [1;4mROWS[m[1m IS <n> qqqqqqqqqqqqqqwq>[m
[1mx[m [1mmq> [1;4mSWEEP[m[1m qqqqj[m [1mx[m
[1m tq> [1;4mROW[m[1m [1;4mLENGTH[m[1m IS <n> qwqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqu [m
[1m x tqqqq> [1;4mBYTE[m[1m qqu [m [1m [m [1m x [m
[1m x mqqqq> [1;4mBYTES[m[1m qj [m [1m [m [1m x [m
[1mx[m [1mx[m
[1mtq> [1;4mROW[m[1m [1;4mSNAPSHOT[m[1m IS qqwq> [1;4mENABLED[m[1m rs-opt qwqqqqqqqqqqqqqqqu[m
[1mx [m [1mmq> [1;4mDISABLED[m[1m qqqqqqqqj[m [1mx[m
[1mx[m [1mx[m
[1m mq> [1;4mSHARED[m[1m [1;4mMEMORY[m[1m IS qqwqqqq> [1;4mSYSTEM[m[1m qqqqqqqqqqqqqqqqqqqwqqj [m
[1m mqqqq> [1;4mPROCESS[m[1m qqwqqqqqqqqqqqqqwqj[m [1m [m
[1m [m [1mx[m [1mx[m [1m [m
[1m [m [1mmq> [1;4mRESIDENT[m[1m qj[m [1m [m
(B)0[m[1mrs-opt = [m
qq[1mwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqq>[m
[1mmq> ([1;4mCACHE[m[1m [1;4mSIZE[m[1m IS <n> [1;4mROWS[m[1m) qqqj[m
3 – Arguments
3.1 – ALLOCATION
Syntax options:
ALLOCATION IS n BLOCK | ALLOCATION IS n BLOCKS
Specifies the initial allocation of the row cache file (.rdc) to
which cached rows are written.
If the ALLOCATION clause is not specified, the default allocation
in blocks is approximately 40 percent of the CACHE SIZE for this
cache.
This clause is ignored if the row cache is defined to checkpoint
to the database.
3.2 – CACHE
Creates a row cache.
3.3 – CACHE_SIZE
Syntax options:
CACHE SIZE IS n ROW | CACHE SIZE IS n ROWS
Specifies the number of rows allocated to the row cache. As the
row cache fills, rows more recently referenced are retained in
the row cache while those not referenced recently are discarded.
Adjusting the allocation of the row cache helps to retain
important rows in memory. If not specified, the default is 1000
rows.
3.4 – CHECKPOINT
Syntax options:
CHECKPOINT ALL ROWS TO BACKING FILE
CHECKPOINT UPDATED ROWS TO BACKING FILE
CHECKPOINT UPDATED ROWS TO DATABASE
Specifies the source records and target for checkpoint operations
for the row cache. If ALL ROWS is specified, then the records
written during each checkpoint operation are both the modified
and the unmodified rows in the row cache. If UPDATED ROWS is
specified, then just the modified rows in the row cache are
checkpointed each time.
If the target of the checkpoint operation is BACKING FILE, then
the row cache server (RCS) process writes the row cache entries
to the backing (.rdc) files. The row cache LOCATION, ALLOCATION,
and EXTENT clauses are used to create the backing files. Upon
recovery from a node failure, the database recovery process is
able to repopulate the row caches in memory from the rows found
in the backing files.
If the target is DATABASE, then the updated rows (only UPDATED
ROWS is allowed) are written back to the database. The row
cache LOCATION, ALLOCATION, and EXTENT clauses are ignored. Upon
recovery from a node failure, the database recovery process has
no data on the contents of the row cache. Therefore, it does not
repopulate the row caches in memory.
This CHECKPOINT clause overrides the database-level CHECKPOINT
clause.
3.5 – EXTENT
Syntax options:
EXTENT IS n BLOCK | EXTENT IS n BLOCKS
Specifies the file extent size for the row cache file (.rdc).
If the EXTENT clause is not specified, the default number of
blocks is CACHE SIZE * 127 for this cache.
This clause is ignored if the row cache is defined to checkpoint
to the database.
3.6 – LOCATION
Syntax options:
LOCATION IS directory-spec
Specifies the name of the directory to which row cache
information is written. The database system generates a file
name (row-cache-name.rdc) automatically for each row cache at
checkpoint time. Specify a device name and directory name only,
enclosed within single quotation marks. By default, the location
is the directory of the database root file. These .rdc files are
permanent database files.
This LOCATION clause overrides a previously specified location at
the database level.
This clause is ignored if the row cache is defined to checkpoint
to the database.
3.7 – NO_LOCATION
Removes the location previously specified in a LOCATION IS clause
for the row cache. If you specify NO LOCATION, the row cache
location becomes the directory of the database root file.
This clause is ignored if the row cache is defined to checkpoint
to the database.
3.8 – NUMBER_OF_RESERVED_ROWS
Syntax options:
NUMBER OF RESERVED ROWS IS n
Specifies the maximum number of cache rows that each user can
reserve for insertion into the cache. Processes reserve, or
allocate, entries in a cache to be used when inserting rows into
the cache. To improve efficiency, multiple entries are reserved
at one once. Once a user's reserved list becomes depleted, Oracle
Rdb attempts to reserve another group of entries. The default is
20 rows.
This value is also used when searching for available slots in a
row cache. The entire row cache is not searched on the initial
pass. This value specifies the maximum number of rows that are
searched for a free slot. If at least one free slot is found, the
insert operation can proceed. If no free slots are found in this
initial search, Oracle Rdb continues searching through the cache
until it finds a free slot.
3.9 – NUMBER_OF_SWEEP_ROWS
Syntax options:
NUMBER OF SWEEP ROWS IS n
Specifies the number of modified rows that will be written from
the row cache back to the database by the row cache server (RCS)
process during a sweep operation. When the RCS is notified that
a cache is "full" of modified data, the RCS starts a sweep to
make space available in the cache for subsequent transactions
to be able to insert rows into the cache. Oracle Corporation
recommends that you initially specify the number of sweep rows
to be approximately 5 percent of the total number of rows in
the cache. Then monitor performance and adjust the number of
sweep rows, if necessary. Allowable values must be in the range 2
through 524288. If not specified, the default is 3,000 rows.
3.10 – ROW_LENGTH
Syntax options:
ROW LENGTH IS n BYTES
Specifies the length of each row allocated to the row cache. Rows
are not cached if they are too large for the row cache. area. The
ROW LENGTH is an aligned longword rounded up to the next multiple
of 4 bytes.
The maximum row length in a row cache is 65535 bytes.
When the name of the cache matches the name of an existing
logical area, ADD CACHE will calculate ROW LENGTH from the size
of the table row, or the size of the index node (for SORTED
RANKED, or UNIQUE SORTED indices). This cache is known as a
logical area cache.
3.11 – ROW_REPLACEMENT
Syntax options:
ROW REPLACEMENT IS ENABLED | ROW REPLACEMENT IS DISABLED
Specifies whether or not Oracle Rdb replaces rows in the cache.
When ROW REPLACEMENT IS ENABLED, rows are replaced when the
row cache becomes full. When ROW REPLACEMENT IS DISABLED, rows
are not replaced when the row cache is full. The type of row
replacement policy depends upon the application requirements for
each cache.
The default is ENABLED.
3.12 – ROW_SNAPSHOT_IS_DISABLED
Disables storing snapshot copies of rows within the cache.
3.13 – ROW_SNAPSHOT_IS_ENABLED
The ROW SNAPSHOT IS ENABLED (CACHE SIZE IS n ROWS) option enables
storage of snapshot copies of rows within the cache and specifies
the number of snapshot "slots" to allocate for the cache.
The default for new caches, and existing caches is to have this
feature disabled.
If you do not specify the CACHE SIZE clause for the ROW SNAPSHOT
IS ENABLED option, Oracle Rdb creates a cache that can contain up
to 1000 snapshot rows.
3.14 – SHARED_MEMORY
Syntax options:
SHARED MEMORY IS SYSTEM | SHARED MEMORY IS PROCESS
Determines whether cache global sections are created in system
space or process space. The default is SHARED MEMORY IS PROCESS.
When you use cache global sections created in the process
space, you and other users share physical memory and the OpenVMS
operating system maps a row cache to a private address space for
each user. As a result, all users are limited by the free virtual
address range and each use a percentage of memory in overhead. If
many users are accessing the database, the overhead can be high.
When many users are accessing the database, consider using the
SHARED MEMORY IS SYSTEM clause. This gives users more physical
memory because they share the system space of memory and there is
none of the overhead associated with the process space of memory.
3.15 – SHARED_MEMORY_IS_PROCESS_RESIDENT
The SHARED MEMORY clause determines whether database root global
sections (including global buffers when enabled) or whether the
cache global sections are created in system space or process
space. The RESIDENT option extends the PROCESS option by making
the global section memory resident.
4 – Examples
Example 1: Creating a row cache
This example creates a database, creates a row cache, and assigns
the row cache to a storage area.
SQL> CREATE DATABASE FILENAME test_db
cont> ROW CACHE IS ENABLED
cont> CREATE CACHE test1
cont> CACHE SIZE IS 100 ROWS
cont> CREATE STORAGE AREA area1
cont> CACHE USING test1;
SQL> SHOW CACHE
Cache Objects in database with filename test_db
TEST1
SQL> SHOW CACHE test1
TEST1
Cache Size: 100 rows
Row Length: 256 bytes
Row Replacement: Enabled
Shared Memory: Process
Large Memory: Disabled
Window Count: 100
Reserved Rows: 20
Sweep Rows: 3000
No Sweep Thresholds
Allocation: 100 blocks
Extent: 100 blocks
SQL> SHOW STORAGE AREA area1
AREA1
Access is: Read write
Page Format: Uniform
Page Size: 2 blocks
Area File: SQL_USER1:[DAY.V70]AREA1.RDA;1
Area Allocation: 402 pages
Area Extent Minimum: 99 pages
Area Extent Maximum: 9999 pages
Area Extent Percent: 20 percent
Snapshot File: SQL_USER1:[DAY.V70]AREA1.SNP;1
Snapshot Allocation: 100 pages
Snapshot Extent Minimum: 99 pages
Snapshot Extent Maximum: 9999 pages
Snapshot Extent Percent: 20 percent
Extent : Enabled
Locking is Row Level
Using Cache TEST1
No database objects use Storage Area AREA1
Example 2: Creating and modifying various caches
1 The cache named CUSTOMER_STATUS is created with a row length
of 577 bytes with 88000 cache "slots" for storage of live
database rows and 7000 slots for storage of snapshot copies of
rows. This cache is also configured to be memory- resident.
2 The cache named MACHINE_FLOW_IDX_1 is created with a row
length of 430 bytes with 5000 cache slots for storage of live
database rows and 12000 slots for storage of snapshot copies
of rows. This cache is set to disallow replacement of rows in
the cache.
3 The cache named SALES_CALLS is created with a row length of
160 bytes with 3000 cache "slots" for storage of live database
rows and, using the default because an explicit count was not
specified, 1000 slots for storage of snapshot copies of rows.
4 The cache named CUSTOMER_ORDER does not specify "ROW SNAPSHOT
IS ENABLED" so no snapshot row copies will be stored in this
cache.
5 The cache named "SALES" is modified to disable storage of
snapshot rows in cache.
6 The cache named "CLEARING" is modified to enable storage
of snapshot rows in the cache with a snapshot cache size of
12,345 rows.
SQL> ALTER DATABASE FILENAME HDB_DB
1 ADD CACHE CUSTOMER_STATUS
ROW LENGTH IS 577 BYTES
CACHE SIZE IS 88000 ROWS
ROW SNAPSHOT IS ENABLED (CACHE SIZE IS 7000 ROWS)
SHARED MEMORY IS PROCESS RESIDENT
2 ADD CACHE MACHINE_FLOW_IDX_1
ROW LENGTH IS 430 BYTES
CACHE SIZE IS 5000 ROWS
ROW REPLACEMENT IS DISABLED
ROW SNAPSHOT IS ENABLED (CACHE SIZE IS 12000 ROWS)
3 ADD CACHE SALES_CALLS
ROW LENGTH IS 160 BYTES
CACHE SIZE IS 3000 ROWS
ROW SNAPSHOT IS ENABLED
4 ADD CACHE CUSTOMER_ORDER
ROW LENGTH IS 760 BYTES
CACHE SIZE IS 9000 ROWS
CHECKPOINT UPDATED ROWS TO DATABASE
5 ALTER CACHE SALES
ROW SNAPSHOT IS DISABLED
6 ALTER CACHE CLEARING
ROW SNAPSHOT IS ENABLED (CACHE SIZE IS 12345 ROWS);
SQL> SHOW CACHE CUSTOMER_STATUS
CUSTOMER_STATUS
Cache Size: 88000 rows
Row Length: 580 bytes
Row Replacement: Enabled
Shared Memory: Process Resident
Large Memory: Disabled
Window Count: 100
Working Set Count: 10
Reserved Rows: 20
Allocation: 100 blocks
Extent: 100 blocks
Snapshot in Cache: Enabled
Snapshot Cache Size: 7000 rows
SQL> SHOW CACHE MACHINE_FLOW_IDX_1
MACHINE_FLOW_IDX_1
Cache Size: 5000 rows
Row Length: 432 bytes
Row Replacement: Disabled
Shared Memory: Process
Large Memory: Disabled
Window Count: 100
Working Set Count: 10
Reserved Rows: 20
Allocation: 100 blocks
Extent: 100 blocks
Snapshot in Cache: Enabled
Snapshot Cache Size: 12000 rows
SQL> SHOW CACHE SALES_CALLS
SALES_CALLS
Cache Size: 3000 rows
Row Length: 160 bytes
Row Replacement: Enabled
Shared Memory: Process
Large Memory: Disabled
Window Count: 100
Working Set Count: 10
Reserved Rows: 20
Allocation: 100 blocks
Extent: 100 blocks
Snapshot in Cache: Enabled
Snapshot Cache Size: 1000 rows
SQL> SHOW CACHE CUSTOMER_ORDER
CUSTOMER_ORDER
Cache Size: 9000 rows
Row Length: 760 bytes
Row Replacement: Enabled
Shared Memory: Process
Large Memory: Disabled
Window Count: 100
Working Set Count: 10
Reserved Rows: 20
Allocation: 100 blocks
Extent: 100 blocks
Row cache: checkpoint updated rows to database