[20241216]测试切换日志文件出现延迟问题时间.txt

--//当执行日志切换时，oracle并不是马上写检查点到数据文件。而是延迟一段时间，测试看看延迟多少。

1.环境：
SYS@book> @ ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

SYS@book> alter system set log_checkpoints_to_alert=true;
System altered.
--//这样检查点信息会写入alert，建议打开该项设置。

2.测试：
SYS@book> alter system switch logfile;
System altered.

--//alert*.log 记录如下：
2024-12-16T12:20:49.040015+08:00
Thread 1 advanced to log sequence 60 (LGWR switch),  current SCN: 22627928
  Current log# 3 seq# 60 mem# 0: /u01/oradata/BOOK/redo03.log

--//等.....
2024-12-16T12:25:52.209924+08:00
Completed checkpoint up to RBA [0x3c.2.10], SCN: 22627928
--//大约5分钟。

2024-12-16T15:45:22.078912+08:00
Beginning log switch checkpoint up to RBA [0x3f.2.10], SCN: 22646741
2024-12-16T15:45:22.079018+08:00
Thread 1 advanced to log sequence 63 (LGWR switch),  current SCN: 22646741
  Current log# 3 seq# 63 mem# 0: /u01/oradata/BOOK/redo03.log
2024-12-16T15:50:26.711160+08:00
Completed checkpoint up to RBA [0x3f.2.10], SCN: 22646741
-//测试几次基本都是在5分钟多1点Completed checkpoint

SYS@book> @ hidez self
 NUM N_HEX NAME                               DESCRIPTION                                          DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---- ----- ---------------------------------- ---------------------------------------------------- ------------- ------------- ------------ ----- ---------
1940   794 _selftune_checkpoint_write_pct     Percentage of total physical i/os for self-tune ckpt TRUE          3             3            FALSE FALSE
2472   9A8 _disable_selftune_checkpointing    Disable self-tune checkpointing                      TRUE          FALSE         FALSE        FALSE IMMEDIATE
2473   9A9 _selftune_checkpointing_lag        Self-tune checkpointing lag the tail of the redo log TRUE          300           300          FALSE FALSE
4411  113B _optimizer_self_induced_cache_cost account for self-induced caching                     TRUE          FALSE         FALSE        TRUE  IMMEDIATE
4419  1143 _selfjoin_mv_duplicates            control rewrite self-join algorithm                  TRUE          TRUE          TRUE         TRUE  IMMEDIATE
--//很明显_selftune_checkpointing_lag=300，控制延迟时间写检查点。
--//如果_disable_selftune_checkpointing=true，切换时不会写检查点!!(有点特殊)
--//参数 _selftune_checkpoint_write_pct=3，描述为Percentage of total physical i/os for self-tune ckpt ，视乎一定百分比3%
--//，指什么我不是很清楚，会写检查点。也就是当业务很忙的情况下，也许不到300秒就出现检查点。

3.继续测试：

--//设置_selftune_checkpointing_lag=60看看,需要重启才能生效。
SYS@book> alter system set "_selftune_checkpointing_lag"=60 scope=spfile;
System altered.

SYS@book> alter system switch logfile;
System altered.

--//alert*.log 记录如下：
2024-12-16T16:04:52.418587+08:00
Beginning log switch checkpoint up to RBA [0x41.2.10], SCN: 22651667
2024-12-16T16:04:52.418707+08:00
Thread 1 advanced to log sequence 65 (LGWR switch),  current SCN: 22651667
  Current log# 2 seq# 65 mem# 0: /u01/oradata/BOOK/redo02.log
2024-12-16T16:05:57.044536+08:00
Completed checkpoint up to RBA [0x41.2.10], SCN: 22651667
--//1分钟5秒。

--//设置_disable_selftune_checkpointing=true看看,该参数不需要重启即可生效。

SYS@book> alter system set "_disable_selftune_checkpointing"=true scope=memory;
System altered.

SYS@book> alter system switch logfile;
System altered.

--//alert*.log 记录如下：
2024-12-16T16:13:14.917118+08:00
Beginning log switch checkpoint up to RBA [0x42.2.10], SCN: 22653031
2024-12-16T16:13:14.917228+08:00
Thread 1 advanced to log sequence 66 (LGWR switch),  current SCN: 22653031
  Current log# 3 seq# 66 mem# 0: /u01/oradata/BOOK/redo03.log

--//等60秒后：
--//没有出现Completed checkpoint up to RBA之类的信息，也是切换日志后不在写检查点。

4.以前还测试过增量检查点出现的频率：
--//如果业务不是很忙，一般受log_checkpoint_timeout参数控制。

SYS@book> @ hidez log_checkpoint_timeout
 NUM N_HEX NAME                   DESCRIPTION                                          DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---- ----- ---------------------- ---------------------------------------------------- ------------- ------------- ------------ ----- ---------
2271   8DF log_checkpoint_timeout Maximum time interval between checkpoints in seconds TRUE          1800          1800         FALSE IMMEDIATE
--//1800秒=30分钟。

5.收尾：
SYS@book>  alter system reset "_selftune_checkpointing_lag";
System altered.