首页 » ORACLE » 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.

打赏

对不起,这篇文章暂时关闭评论。