Coding TricksOracle DBWR, LGWR, CKPT, ARCH Trigger Conditions Summary
One . DBWR writes disk data trigger conditions
1. The DBWR process is triggered when a process scans on the secondary LRU list and the main LRU list to find buffer headers that can be overwritten, if the number of buffer headers that have been scanned reaches a certain limit (determined by hidden parameters: _db_block_max_scan_pct). 。 _db_block_max_scan_pct represents the number of header buffers that have been scanned as a percentage of the total number of header buffers on the entire LRU list. At this point, the process searching for the available buffer header hangs, appears in v$session_wait waiting for the "free buffer wait" event, and increases the value of "dirty buffers inspected" in v$sysstat. (can query by x$kvit)
This section also has a note in my blog:
Oracle Buffer Cache principle
http://www.cndba.cn/Dave/article/1398
2. When
DBWR looks for a header buffer on the main LRUW list that has been updated and is waiting to be written to a data file, dbWR if the number of buffer headers found exceeds a certain limit (as determined by the hidden parameter: _db_writer_scan_depth_pct).
Instead of continuing to scan down, go to
the secondary
LRUW
list and write dirty blocks of data on it to the data file.
_db_writer_scan_depth_pct
represents the percentage of dirty blocks that have been scanned as a
percentage of the total number of buffer
headers
on the entire main
LRUW
list.
3.
The
DBWR
process is also triggered if the total number of dirty blocks on the main LRUW list and
the secondary
LRUW
list
exceeds a certain
limit. This limit is
determined by the hidden parameter:
_db_large_dirty_queue.
4.
DBWR
is
triggered when
an
incremental
checkpoint occurs or a
full checkpoint
occurs.
5.
Start DBWR
every three seconds.
6.
DBWR
is triggered when the
table space is
set offline.
7.
Issue a command:
Table alterspace ... start backup,
which triggers
DBWR
when the table space is set to a hot backup state.
8.
DBWR is triggered when the table space is set to read-only.
9.
DbWR is triggered when an object is deleted, such as a
table.
Two . . LGWR writes the online log file trigger condition
1. timeout
When
LGWR
is idle, it relies on
rdbms ipc message
waiting and is dormant until the
3
second timeout timeout.
If
LGWR
finds that there is redo
to
write out,
then
LGWR
will perform a
write-out operation and
log file parallel
write
waiting event will occur.
2.
Threshold reaches
The number of log
buffers
that are already used
is calculated as long as
a process
allocates space in the log
buffer.
If the number of blocks used is greater than or equal to
the
_log_io_size
parameter setting, the
LGWR
write operation
is triggered.
If
the LGWR is not active at this time, the LGWR is notified to perform background writes.
The
default _log_io_size is equal to 1/3 log buffer size, with an upper limit of 1M, and the value of 0 displayed in X$KSPPSV means the default value.
That is,
LGWR
will be
triggered at Min
(1M, 1/3 log size buffer).
Note
that the log buffer
size
here
is measured
in
log block.
This value is typically
512 bytes.
3.
Submit When something is committed, a submission flag is recorded in redo stream.
This
thing is unrecoverable until these redos are written to disk.
Therefore,
you must wait for the
LGWR
write to complete before the transaction returns a success flag to
the
user. The process notifies
LGWR
to write and starts
hibernating
with the log file
sync
event, with a timeout of
1
second.
Oracle's
implied parameter _wait_for_sync parameter can be set to false to avoid waiting for redo file sync, but there is no guarantee of the recovery of the transaction
Note that
commits in
recurring calls,
such
as those in
procedures,
do not need to
synchronize
redo
until a response is required to return to the user.
Therefore, recursive calls only need to be synchronized back to the last time before the user call
The
RBA
of the Commit
operation.
There
is an SGA variable that records the log block number that the redo thread needs to synchronize.
If
multiple commits occur before the LGWR wakes up, the log block number with the highest log number recorded by this variable, before all redo will be written to disk.
This is sometimes
referred to
as group
commit
4.
Before DBWR writes, DBWR will post LGWR if the high RBA of the data to be written by DBWR exceeds the on-Disk RBA of LGWR
Go and write.
Before
Oracle8i, DBWR would wait for the log file sync event.
Starting
with Oracle8i, DBWR puts these Blocks into a defer queue and notifies LGWR to redo write-out, which allows DBWR to continue to write data without waiting.
Three . . The trigger condition for CKPT to send a CHECKPOINT signal
1.log the time of the
time
reached 2.
.
The current
redo
log has been written enough
to log_checkpoint_internavl's
operating system block size
3. redo log switch
4. alteration system checkpoint
5. alter tablespace XXX begin backup
,
end
backup
time
6.
Table alterspace, datafile offline, shutdown immediate, direct read time;
Four . . The ARCH process log archive triggers the condition
1. If automatic archiving mode is set, the ARCH process is triggered by the LGWR process to archive when the log switches. This is the most common way.
2. Can be manually archived. Using the command: System alter archive log current indicates that the ARCH process is started to archive the current log file.
3. If the ARCH process does not receive an LGWR notification after 5 minutes, a timeout occurs and arch is woken up to check for log files that need to be archived. ARCH reads the information in the control file to decide which log files need to be archived and which should be archived. However, the ARCH process does not start during instance recovery or media recovery.
reprinted from: http://space.itpub.net/23071790/viewspace-692142
-------------------------------------------------------------------------------------------------------
QQ:492913789
Email:[email protected]
Blog: http://www.cndba.cn/dave
DBA1 Group: 62697716 (full); DBA2 Group: 62697977 (full) DBA3 group: 62697850 (full).
DBA Supergroup: 63306533 (full); DBA4 Group: 83829929 DBA5 Group: 142216823
DBA6 Group: 158654907 Chat Group: 40132017 Chat Group 2: 69087192
-- The group needs to explain the relationship between oracle table space and data files in the notes, otherwise the request will be rejected