在数据库运维的工作中，需要了解参数配置的变化。例如在 SQL 调优中，同样的 SQL 在不同环境，优化器在选择最优计划时可能会发生变化，发生变化的原因有系统升级、统计信息重新收集、优化器相关系统参数变更等。本文介绍 OceanBase 数据库中如何使用脚本展示参数配置的历史变化。
可以通过如下脚本展示参数配置的历史变化。
脚本内容如下，将以下 SQL 保存为一个 sql 文件
find_pata.sql。SELECT gmt_create,value2 "alter_system_change_to_value",value1 execution_result,lag(value2) over (partition by cmd_scope order by gmt_create) old_value FROM( SELECT gmt_create,value2, locate('zone:""',value2), locate('exec_tenant_id:',value2), substr(value2,locate('zone:',value2), locate('exec_tenant_id:',value2)-locate('zone:""',value2)) cmd_scope, value1 FROM __all_rootservice_event_history WHERE event='admin_set_config' -- AND value2 LIKE '%memory%' )ORDER BY gmt_create ASC;将脚本复制到任意一台 OBServer 的
/home/admin/oceanbase/admin目录下。[admin@hostname ~]$ cp find_para.sql /home/admin/oceanbase/admin运行查询脚本。
obclient> source find_para.sql
使用示例
+----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| gmt_create | alter_system_change_to_value | execution_result | old_value |
+----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| 2020-09-07 16:24:11.022476 | {items:[{name:"sql_work_area", value:"1G", comment:"", zone:"", server:"xxx.xxx.xxx.xxx", tenant_name:"", exec_tenant_id:1001, tenant_ids:[1001]}]} | 0 | NULL |
| 2020-09-07 16:24:17.636673 | {items:[{name:"sql_work_area", value:"2G", comment:"", zone:"", server:"xxx.xxx.xxx.xxx", tenant_name:"", exec_tenant_id:1001, tenant_ids:[1001]}]} | 0 | {items:[{name:"sql_work_area", value:"1G", comment:"", zone:"", server:"xxx.xxx.xxx.xxx", tenant_name:"", exec_tenant_id:1001, tenant_ids:[1001]}]} |
| 2020-09-07 16:24:26.701905 | {items:[{name:"sql_work_area", value:"3G", comment:"", zone:"", server:"xxx.xxx.xxx.xxx", tenant_name:"", exec_tenant_id:1, tenant_ids:[1]}]} | 0 | {items:[{name:"sql_work_area", value:"2G", comment:"", zone:"", server:"xxx.xxx.xxx.xxx", tenant_name:"", exec_tenant_id:1001, tenant_ids:[1001]}]} |
| 2020-09-07 17:01:02.014515 | {items:[{name:"sql_work_area", value:"1G", comment:"", zone:"", server:"xxx.xxx.xxx.xxx", tenant_name:"", exec_tenant_id:1, tenant_ids:[1]}]} | 0 | {items:[{name:"sql_work_area", value:"3G", comment:"", zone:"", server:"xxx.xxx.xxx.xxx", tenant_name:"", exec_tenant_id:1, tenant_ids:[1]}]} |
+----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.02 sec)
注意
由于 __all_rootservice_event_history 表中只能记录过去 7 天的信息，因此使用脚本也只能获取 7 天内各配置项的变化信息。
