首页 » ORACLE 9i-23ai » x$kcbbes checkpoint
x$kcbbes checkpoint
Q: some documentation talks about non-checkpoint based DBWR writes, does anyone know how to produce an example of that?
I’ve only seen writes based on a checkpoint (row in X$ACTIVECKPT) so far.
A:
DBWR has two type writes:
1, physical writes non checkpoint ( LRUW) — DBWR write for Free Request
2, DBWR checkpoint buffers written (CKPTQ checkpoint queue) dirty queue — DBWR write For checkpoint。
1* select name, value sys_value from v$sysstat where lower(name) like lower('%&1%')
SQL> @sys checkpoint
NAME VALUE
---------------------------------------------------------------- --------------------------
physical writes non checkpoint 3003
DBWR checkpoint buffers written 3095
DBWR thread checkpoint buffers written 0
DBWR tablespace checkpoint buffers written 0
DBWR parallel query checkpoint buffers written 0
DBWR checkpoints 0
checkpoint clones created for ADG recovery 0
background checkpoints started 0
background checkpoints completed 0
IM populate checkpoint time (ms) 0
IM repopulate checkpoints 0
IM populate no checkpoint 0
about db writer parameters
SQL> @pd dbw
Show all parameters and session values from x$ksppi/x$ksppcv...
NUM N_HEX NAME VALUE DESCRIPTION
---------- ----- ----------------------------------------------- ---------- -----------------------------------------------------------------------
392 188 dbwr_io_slaves 0 DBWR I/O slaves
1683 693 _db_writer_chunk_writes 0 Number of writes DBWR should wait for
1686 696 _db_writer_histogram_statistics FALSE maintain dbwr histogram statistics in x$kcbbhs
1687 697 _dbwr_scan_interval 300 dbwriter scan interval
1688 698 _db_writer_flush_imu TRUE If FALSE, DBWR will not downgrade IMU txns for AGING
1695 69F _dbwr_stall_write_detection_interval 900 dbwriter stall write detection interval
1696 6A0 _dbwr_flashlock_shrink_limit 0 limit to shrink le to flash lock per dbwr iteration
1697 6A1 _enable_dbwr_auto_tracing 0 enable dbwriter auto-tracing
1698 6A2 _dbwr_nowrite_assert_interval 7200 dbwriter assert interval after no write seconds
1699 6A3 _dbwr_nwp_assert_interval 1800 dbwriter no write permission assert interval after no write seconds
1700 6A4 _remessage_dbwrs 0 DBWR wait and remessage time - in cs
16 rows selected.
SQL> @pd db_write
Show all parameters and session values from x$ksppi/x$ksppcv...
NUM N_HEX NAME VALUE DESCRIPTION
---------- ----- ----------------------------------------------- ---------- -----------------------------------------------------------------------
1678 68E db_writer_processes 1 number of background database writer processes to start
1682 692 _db_writer_max_writes 0 Max number of outstanding DB Writer IOs
1689 699 _db_writer_coalesce_write_limit 131072 Limit on size of coalesced write
1690 69A _db_writer_coalesce_encrypted_buffers TRUE Coalecsing for encrypted buffers
1691 69B _db_writer_coalesce_area_size 2260992 Size of memory allocated to dbwriter for coalescing writes
1692 69C _db_writer_nomemcopy_coalesce FALSE Enable DBWR no-memcopy coalescing
1694 69E _db_writer_verify_writes FALSE Enable lost write detection mechanism
10 rows selected.
SQL> @pd max_scan
Show all parameters and session values from x$ksppi/x$ksppcv...
NUM N_HEX NAME VALUE DESCRIPTION
---------- ----- ------------------------- --------- --------------------------------------------------------
1680 690 _db_block_max_scan_pct 40 Percentage of buffers to inspect when looking for free
SQL> @pd large_dirty
Show all parameters and session values from x$ksppi/x$ksppcv...
NUM N_HEX NAME VALUE DESCRIPTION
---------- ----- ----------------------- ------- ---------------------------------------------------------
1681 691 _db_large_dirty_queue 25 Number of buffers which force dirty queue to be written
SQL> @st x$kvit
ADDR INDX KVITVAL KVITTAG KVITDSC
---------------- ---------- --------- -------------------- ----------------------------------------------------------------
00000000149FC040 0 1 ksbcpurawthrcnt number of raw CPU threads in the system used by Oracle
00000000149FC050 1 1 ksbcpueffthrcnt number of effective CPU threads in the system used by Oracle
00000000149FC060 2 1 ksbcpucore number of physical CPU cores in the system used by Oracle
00000000149FC070 3 1 ksbcpusocket number of physical CPU sockets in the system used by Oracle
00000000149FC080 4 1 ksbcpu_hwm high water mark of number of CPUs used by Oracle
00000000149FC090 5 1 ksbcpucore_hwm high water mark of number of CPU cores on system
00000000149FC0A0 6 1 ksbcpusocket_hwm high water mark of number of CPU sockets on system
00000000149FC0B0 7 1 ksbcpu_actual number of available CPUs in the system
00000000149FC0C0 8 1 ksbcpu_dr CPU dynamic reconfiguration supported
00000000149FC0D0 9 26352 kcbncbh number of buffers in cdb
00000000149FC0E0 10 26352 kcbnbh number of buffers
00000000149FC0F0 11 25 kcbldq large dirty queue if kcbclw reaches this
00000000149FC100 12 40 kcbfsp Max percentage of LRU list foreground can scan for free
00000000149FC110 13 2 kcbcln Initial percentage of LRU list to keep clean
00000000149FC120 14 2000 kcbnbf number buffer objects
00000000149FC130 15 0 kcbwst Flag that indicates recovery or db suspension
00000000149FC140 16 0 kcteln Error Log Number for thread open
00000000149FC150 17 0 kcvgcw SGA: opcode for checkpoint cross-instance call
00000000149FC160 18 0 kcvgcw SGA:opcode for pq checkpoint cross-instance call
a.当前台进程需要将磁盘上的物理数据块读取到数据库高速缓存中(db cache)时，其首先需要在数据库缓存中寻找到一块可用的free(空闲) buffer。扫描都会从LRU链表的尾端开始,所遍历到的脏块将被移动到LRUW链表上.若该前台进程在LRU链表上搜索的范围超过了整个LRU链表长度所规定的阀值时，其搜索操作将自行中止，通知dbwr进程,执行一次大批量的写出操作以使得LRU链表上有干净的clean buffer可用, 当DBWR所扫描的LRU链表长度等于整个LRU链表长度*(_db_writer_scan_depth_pct/100)时，DBWR将停止继续扫描LRU链表。
b.前台进程在遍历LRU链表，顺带将脏块(dirty block)移动到LRUW链表上时，前台进程可能意识到LRUW链表的长度已经达到了某种阀值(_db_large_dirty_queue)。DBWR后台进程将被通知完成批量清理脏块的任务。
c. 增量检查点会引发checkpoint queue(dirty queue)上的脏块递进地被写出，每三秒CKPT后台进程将计算检查点目标RBA(Redo Block Address)，当增量检查点发生时所有在目标RBA相应时间之前被弄脏的buffer块都当被写出(When a checkpoint is initiated, DBWR writes all buffers on the queue until the checkpoint RBA is less than the head of the CKPTQ RBA)。
x$kcbbes Check incremental checkpoints (259586.1)
TanelPoder’s script @kcbbes
SQL> @kcbbes
List background I/O write priorities and reasons from X$KCBBES...
(X$KCBBES = Kerncel Cache Buffers dB writer Event Statistics)
INDX REASON_NAME REASON_BUFFERS REASON% PRIORITY_NAME PRIORITY_BUFFERS PRIO% IO_PROC_STATUS IO_COUNT STATUS%
---------- ---------------------- -------------- ---------- ---------------- ---------------- ---------- ------------------------ ---------- ----------
0 Invalid Reason 0 Invalid Priority 0 Queued For Writing 3982646704 48
1 Ping Write 269635 0 High Priority 254978412 3.1 Deferred (log file sync) 0
2 High Prio Thread Ckpt 0 Medium Priority 4112475022 49.6 Already being written 380103989 4.6
3 Instance Recovery Ckpt 24999170 .3 Low Priority 3926243756 47.3 Buffer not dirty 109738 0
4 Med Prio (incr) Ckpt 4087475852 49.3 0 Buffer is pinned 87632 0
5 Aging Writes 0 0 I/O limit reached 0
6 Media Recovery Ckpt 0 0 Buffer logically flushed 0
7 Low Prio Thread Ckpt 3235829 0 0 No free IO slots 137 0
8 Tablespace Ckpt 1091896 0 0 3930748990 47.4
9 Reuse Object Ckpt 1122729 0 0 0
10 Reuse Block Range Ckpt 164534171 2 0 0
11 Limit Dirty Buff Ckpt 3750179631 45.2 0 0
12 Smart Scan Ckpt 254708777 3.1 0 0
13 0 0 0
14 Direct Path Read Ckpt 6079500 .1 0 0
15 0 0 0
16 0 0 0
17 0 0 0
18 0 0 0
19 rows selected.
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。