(B)0[mchange-database-options2 = qwqwqq>qqqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq> x tqq> [4mSNAPSHOT[m IS qqwq> [4mENABLED[m qwqqqqqqq>qqqqqqwqwqqqqqqqu x x x x tq> [4mIMMEDIATE[m qu x x x x x x mq> [4mDEFERRED[m qqj x x x x x mq> [4mDISABLED[m qqqqqqqq>qqqqqqqqj x x x tqq> [4mSNAPSHOT[m [4mALLOCATION[m IS q> snp-pages qq> PAGES qqqqqqu x x twq> [4mSNAPSHOT[m [4mEXTENT[m IS qwqqwq> extent-pages q> PAGES qwqu x x xmq> [4mEXTENT[m [4mIS[m qqqqqqqqqqj mq> (extension-options) qqqj x x x tqq> [4mCARRY[m [4mOVER[m [4mLOCKS[m ARE qwq> [4mENABLED[m qqwqqqqqqqqqqqqqqqu x x x mq> [4mDISABLED[m qj x x x tqq> [4mLOCK[m [4mTIMEOUT[m [4mINTERVAL[m IS number-seconds SECONDS qqqqu x x tqq> [4mREAD_ONLY[m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x tqq> [4mREAD_WRITE[m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x tqq> [4mDICTIONARY[m IS qqqqqqwq> [4mREQUIRED[m qqqqqqqwqqqqqqqqqqqu x x x mq> [4mNOT[m [4mREQUIRED[m qqqj x x x mqq> journal-fast-commit-clause qqqqqqqqqqqqqqqqqqqqqqqqqj x mqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqj
1 – ENABLED-IMMEDIATE
Specifies that read/write transactions write copies of records to
the snapshot file before those records are modified, regardless
of whether a read-only transaction is active. The default is
SNAPSHOT IS ENABLED IMMEDIATE.
You enable snapshot writing to all snapshot files for all storage
areas when you specify the SNAPSHOT IS ENABLED clause.
2 – ENABLED-DEFERRED
Specifies that read/write transactions not write copies of
records they modify to the snapshot file unless a read-only
transaction is active. Read-only transactions that attempt to
start after an active read/write transaction begins must wait for
all active read/write users to complete their transactions.
You enable snapshot writing to all snapshot files for all storage
areas when you specify the SNAPSHOT IS ENABLED clause.
3 – DISABLED
Disables snapshot transactions.
If snapshots are disabled and a user starts a READ_ONLY
transaction, the user will not receive an error message, but
their transaction will be converted to a READ_WRITE transaction,
but they will not be able to write.
If you use the SNAPSHOT IS DISABLED clause to disable snapshots
on a multifile database, writing to all snapshot files for all
storage areas is disabled.
4 – snp-pages
The number of pages allocated for the snapshot file. The default
is 100 pages.
5 – extent-pages
The number of pages of each extent. The default is 100 pages.
6 – extension-options
Specifies the MIN, MAX, and percent growth of each database file
extent. Enclose the parameter list in parentheses.
(B)0[mextension-options =
qqq> ( qqq> [4mMINIMUM[m OF qq> min-pages qqq> PAGES, qk
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqq> [4mMAXIMUM[m OF qq> max-pages qq> PAGES,qk
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqq> [4mPERCENT[m [4mGROWTH[m IS qqq> growth qqqq> ) qqq>
6.1 – min-pages
The minimum number of pages of each extent. The default is 99
pages.
6.2 – max-pages
The maximum number of pages of each extent. The default is 9,999
pages.
6.3 – growth
The percent growth of each extent. The default is 20 percent
growth.
7 – CARRY_OVER_LOCKS
Enables or disables carry-over lock optimization. Carry-over
locks are enabled by default.
While attached to the database, a process can have some active
locks (locks attached to the database) and some carry-over
locks (locks requested in earlier transactions that have not
been demoted). If a transaction needs a lock it has currently
marked as carry-over, it can reuse the lock by changing it to an
active lock. The same lock can go from active to carry-over to
active multiple times without paying the cost of lock request and
demotion. This results in a substantial reduction in the number
of lock requests if a process accesses the same set of areas
repeatedly.
As part of the carry-over lock optimization, a NOWAIT transaction
requests, acquires, and holds a NOWAIT lock. This signals other
processes accessing the database that a NOWAIT transaction exists
and causes Oracle Rdb to release all carry-over locks. If NOWAIT
transactions are noticeably slow in executing, you can specify
CARRY OVER LOCKS ARE DISABLED with the CHANGE DATABASE or DEFINE
DATABASE statement.
This feature is available as an online database modification.
8 – LOCK_TIMEOUT
Specifies the number of seconds for processes to wait during a
lock conflict before timing out. The number of seconds can be
between one and 65,000.
The lock timeout interval is database-wide: it is used as the
default as well as the upper limit in determining the timeout
interval. For example, if LOCK TIMEOUT INTERVAL IS 25 SECONDS is
specified with the CHANGE DATABASE or DEFINE DATABASE statement,
and a user specifies SQL SET TRANSACTION WAIT 30 or sets the
logical name RDM$BIND_LOCK_TIMEOUT_INTERVAL to 30, RDO would
still use the interval 25 specified with the LOCK TIMEOUT
INTERVAL clause.
9 – READ_ONLY
The READ_ONLY option is used to change a read/write RDB$SYSTEM
storage area (and the Oracle Rdb system relations stored in the
area) to read-only. You might choose the READ_ONLY option if
your database is never or rarely updated. When the RDB$SYSTEM
storage area is changed to read-only, locking conflicts occur
less frequently, and the automatic updating of index and relation
cardinality is inhibited.
No write operation can be done in a read-only storage area except
a cardinality update. See the RMU/ANALYZE/CARDINALITY help topic
in the RMU help file for more information and restrictions on
updating cardinalities.
To change a storage area other than the RDB$SYSTEM storage area
to read-only, or to change a database to read-only, use the READ_
ONLY option of the change-storage-area-clause.
10 – READ_WRITE
The READ_WRITE option is used to change a read-only RDB$SYSTEM
storage area (and the Oracle Rdb system relations stored in the
area) to read/write. Select the READ_WRITE option to change a
read-only RDB$SYSTEM storage area to read/write.
To change a storage area other than the RDB$SYSTEM storage area
to read/write, or to change a database to read/write, use the
READ_WRITE option of the change-storage-area-clause.
11 – DICTIONARY
Determines whether the database must be invoked by path name for
data definition changes to occur. If you specify the DICTIONARY
IS REQUIRED option, the database must be invoked by path name to
change metadata and the data dictionary will be maintained. If
you specify the DICTIONARY IS NOT REQUIRED option, the database
can be invoked by either file name or path name to change
metadata. The default is DICTIONARY IS NOT REQUIRED.
If you specify the DICTIONARY option, you cannot specify any
other options in the same CHANGE DATABASE statement.
12 – journal-fast-commit-clause
(B)0[mjournal-fast-commit-clause=
qq> [4mJOURNAL[m [4mFAST[m [4mCOMMIT[m qwq> [4mENABLED[m qqwqqqk
mq> [4mDISABLED[m qj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>
m> ( qqwqwq> [4mCHECKPOINT[m [4mINTERVAL[m IS n BLOCKS qqqqqwqwq> ) qj
x tq> [4mCHECKPOINT[m [4mTIMED[m EVERY n SECONDS qqqqu x
x tq> [4mCOMMIT[m [4mTO[m JOURNAL OPTIMIZATION qqqqqqu x
x tq> [4mNO[m [4mCOMMIT[m [4mTO[m JOURNAL OPTIMIZATIONqqqqu x
x mq> [4mTRANSACTION[m [4mINTERVAL[m [4mIS[m number-txns qj x
mqqqqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqqj
12.1 – FAST_COMMIT
By default, Oracle Rdb writes updated database pages to disk
each time a transaction executes the COMMIT statement. If a
transaction fails before committing, Oracle Rdb only needs to
rollback (undo) the current failed transaction; it never has
to redo previous successful transactions.
You can change the commit processing method by enabling journal
fast commit processing (fast commit). With fast commit enabled,
Oracle Rdb keeps updated pages in the buffer pool (in memory) and
does not write the pages to disk when a transaction commits. The
updated pages can remain in the buffer pool until the process
meets a condition specified by the database administrator or
applications programmer. At the moment the condition is met,
called the checkpoint, all the pages the process has updated, for
multiple transactions, are written to disk.
You can set a checkpoint for your process when:
o A fixed number of transactions have been committed or aborted
o A specified time interval has elapsed
o The after-image journal (AIJ) file has grown a certain amount
If a transaction fails, Oracle Rdb must undo the current, failed
transaction and redo all the committed transactions since the
last checkpoint. Redoing updates involves reading the AIJ file
and reapplying the changes to the relevant data pages.
The checkpoint interval value is set by the database
administrator and applies to all processes attached to a
database. Users can implement an alternate, process-specific
method of checkpointing by defining the logical name RDM$BIND_
CKPT_TRANS_INTERVAL. The mechanism uses transaction count as
the checkpoint. When fast commit processing is disabled, the
RDM$BIND_CKPT_TRANS_INTERVAL logical name is ignored. For more
information about the RDM$BIND_CKPT_TRANS_INTERVAL logical name,
see the Oracle Rdb Guide to Database Performance and Tuning.
Fast commit processing applies only to data updates, that
is erase, modify, and store operations. Transactions that
include data definition statements, such as DEFINE RELATION and
DEFINE INDEX statements, force a checkpoint at the end of the
transaction.
Note that in order to enable fast commit, you must enable after-
image journaling.
12.2 – CHECKPOINT_INTERVAL
You can limit how many transactions the Database Recovery process
(DBR) must redo by setting a checkpoint interval. Setting a
checkpoint interval instructs Oracle Rdb to periodically flush
updated pages. This shortens recovery time.
The value you assign to the checkpoint interval specifies the
number of blocks the .AIJ file is allowed to grow before updated
pages are flushed. For example, if you set the checkpoint
interval value equal to 100, all processes will flush updated
pages to disk when 100 blocks have been written to the .AIJ file
since the last checkpoint. Thus all processes contribute to .AIJ
growth.
If no checkpoint interval is established and a process completes
1000 transactions but fails during number 1001, the DBR must redo
transactions 1 through 1000 and undo number 1001.
When a process binds to the database, it writes a checkpoint
record to the .AIJ file and notes the virtual block number (VBN)
of the .AIJ file at which the checkpoint record is located. If
the checkpoint is located at VBN 120 and the checkpoint interval
is 100 blocks, the process will checkpoint again when VBN 220 is
reached.
A process will never checkpoint in the middle of a transaction.
Since all processes contribute to .AIJ file growth, a process
may be able to commit many transactions before checkpointing
if update activity by other processes is low. Conversely, if a
process' first transaction is long and if update activity by
other processes is high, the process may be forced to checkpoint
when it commits its first transaction.
When the database checkpoint interval value is reached, Oracle Rdb
executes the following steps:
o Updated pages are written to disk.
o A checkpoint record is written to the .AIJ file.
o The root user process block (RTUPB) for each process is
updated to indicate where the checkpoint record is stored in
the .AIJ file. The RTUPB is a data structure in the database
root file that maintains information on each process accessing
the database. The database recovery process (DBR) uses the
RTUPB checkpoint entry to determine where in the .AIJ file
recovery must start.
12.3 – CHECKPOINT_TIMED
Assigns a value to the checkpoint interval specifying the number
of seconds that can pass before updated pages are written. When
the specified number of seconds elapse, Oracle Rdb executes the
checkpoint steps described in the CHECKPOINT_INTERVAL topic. You
can set both a checkpoint based on time and a checkpoint based on
AIJ file growth: Oracle Rdb will checkpoint at whichever checkpoint
it reaches first.
For example, if you specify TIMED EVERY 100 SECONDS, each process
checkpoints when it completes a transaction after at least 100
seconds have passed since its last checkpoint.
For example, the following statement enables fast commit
processing and specifies checkpoint intervals of 512 blocks and
12 seconds:
RDO> CHANGE DATABASE FILENAME TEST1
cont> JOURNAL FILE IS TEST1_AIJ
cont> JOURNAL FAST COMMIT ENABLED
cont> (CHECKPOINT INTERVAL IS 512 BLOCKS,
cont> CHECKPOINT TIMED EVERY 12 SECONDS).
12.4 – COMMIT_TO_JOURNAL
If you enable COMMIT TO JOURNAL OPTIMIZATION when you enable
fast commit, Oracle Rdb does not write commit information to
the database root file. This option enhances performance in
database environments that are update intensive. Because
of the prerequisites for enabling the journal optimization
option, general use databases or databases that have many read-
only transactions may not benefit from this feature. For more
information see the Oracle Rdb Guide to Database Maintenance.
Note that if you specify COMMIT TO JOURNAL OPTIMIZATION, you must
disable or defer snapshots.
If you change snapshots to enabled immediate, then you must
disable COMMIT TO JOURNAL OPTIMIZATION.
12.5 – TRANSACTION_INTERVAL
The TRANSACTION INTERVAL IS n clause specifies the size of
the TSN range where n equals the number of TSNs (transaction
sequence numbers). Oracle Rdb uses transaction sequence numbers to
ensure database integrity. When you specify NO COMMIT TO JOURNAL
OPTIMIZATION, TSNs are assigned to users one at a time. When the
journal optimization option is enabled, each user is pre-assigned
a range of TSNs. Assigning a range of TSNs avoids the single-
threading problem because commit information need not be written
to the database root for each transaction. Oracle Rdb writes all
transaction information to the .AIJ file except for each user's
allocated TSN range, which it writes to the root.
The transaction interval value (the TSN range) must be a number
between 8 and 1024. The default value is 256. You need to decide
which constraint has precedence on your database: performance or
running out of TSNs.
As a general guideline, if your database has few users or if
all user sessions are long, select a high transaction interval.
If your database has many users or if user sessions are short,
select a smaller transaction interval.
You can specify a transaction interval with JOURNAL FAST COMMIT
IS DISABLED, but the interval will not be used until you enable
the fast commit protocol.