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

[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.