IRIS/Caché SQL优化经验分享 - Tune Table

 

TuneTable(调整表)收集数据库中表的统计信息，用来为SQL引擎制定最优的执行计划。在其他数据库产品里，这个动作被称为“gather stats job"或者类似的名字，相比较TuneTable不是那么直白，但作用是一样的。
 

TuneTable是否要人工执行

一定要。

在IRIS 2023版本， 第一次加入了TuneTable的自动执行功能，在此之前的所有IRIS/Caché版本， 如果没有人工执行TuneTable, SQL引擎无法保证给出最好的查询计划。 即使是IRIS2023有了自动执行功能，也还需要人工执行TuneTable的操作，后面解释。

 

怎么知道有没有执行过TuneTable 

到“管理门户>SQL"页面， 打开一个表， 看“目录详情”，如下图， 如果其中的“选择性”，"离群值选择性“， ”离群值“，“平均字段大小”这些字段有数据， 说明这个表至少做过了一次TuneTable. 

 

除了“字段”按钮页，在“表信息”还有统计项 “ExtentSize"， “索引”页，包含每个索引的统计信息项。

关于这些统计项的想象解释， 我会在下个帖子里介绍。 

 

 

什么时候执行TuneTable

简单的说：对于查询所用的表，SQL引擎要有以上有统计信息，而且足够准确。

解释以下“足够准确”。 举个例子，当一个系统上线后，我们立即做了个TuneTable, 这时计算了两个表的Extent Size, 也就是表的记录数量。

• 病人就诊： Extent Size = 1000
• 某字典表： Extent Size = 10000

这时SQL优化器会根据这个记录来为这两个表的关联配置查询计划， 很显然，会从小的表，也就是就诊表查起。 这时候是没问题的。而当就诊越来越多，就诊表的数据量很快就超过了字典表的数据量，而SQL引擎保有的Extent Size信息并不变化，因此还是使用最初的查询计划，SQL性能就会非常糟糕。

相反，如果您执行TuneTable后， 上述两个值的统计为：

• 病人就诊： Extent Size = 100,000
• 某字典表： Extent Size = 10000

从这时候起，那么哪怕病人表的数据增加到10,000,000， 查询计划并不受影响。因此再执行TuneTable从理论上就是不必要的。

所以，总结如下：

是否要执行TuneTable就可以简单的回答为：

• 你知道数据PATTERN发生了变化，这个变化会影响上述的表信息统计，或者
• 你发现某个查询执行计划不太对头

实际工作中，有以下两个选择

1. 定期做， 业务上线初期多做几次，比如第一周做一次， 第一个月做一次，以后每两个月做一次。(这不是厂家推荐!!!)
2. 发现某个查询的执行计划不太对头，检查一下表的统计值是否合理，或者干脆做一个TuneTable看能不能解决问题。

 

要执行多长时间

客户的一个1T的数据库的schema, 在IRIS上执行TuneTable不到10分钟。(注意不要在ECP服务器执行TuneTable, 会非常非常慢。)

 

生产环境执行有风险吗？

因为是做统计，而且是采样的统计，因此理论上资源的开销不大。 除了建议在维护时间窗口操作外， 我没有听说过有过因为TuneTable发生过任何事故。 

 

执行TuneTable的操作

 

在管理门户页面(SMP)执行

可以选择对单个表，或者单个Schema执行

Caché 2010不支持SMP上执行TuneTable Cache

2016可以在SMP上对单表执行TuneTable, 但不能自选设置

下图所示是执行前的情况， 这个表从来没有执行过TuneTable, “离群选择性”等几个栏目还是干干净净的， TuneTable后您会看到填入了计算的结果。

 

要不要选中“保持类更新“

在早期的Caché系统中， SQL表的上述统计值是写在表的类定义的Storage代码块中。比如下面这个是User.Patient类定义的Storage部分，当在执行TuneTable时如果选中了“保持类更新”， 那么计算后的值会写入Storage代码块。 注意， Storage块中的统计值只对开发者有用，对于SQL查询没有实际功效，也就是说: 做TuneTable时不选中"保持类更新"，统计的结果不会写入类的代码块，不影响您在管理门户的SQL页面上查看这些值。

我的建议是： 别选。

 

 

在Terminal执行命令

 

IRIS : 执行 $SYSTEM.SQL.Stats.Table.GatherTableStats()，可以对一个表, 或者本命名空间的所有表执行。

# turn table "IscPerson"
Do $SYSTEM.SQL.Stats.Table.GatherTableStats("IscPerson")
# log to "Tune.log" file
Do $SYSTEM.SQL.Stats.Table.GatherTableStats("MedLab.Patient","Tune.log")

Caché 2016:

DO $SYSTEM.SQL.TuneTable("Sample.Person",1, 1)
Do $SYSTEM.SQL.TuneTable(<Table>,1,1,.msg,1)

 

冻结执行计划(Frozen Query Plan)

如果您是一个悲观的人， 担心执行了TuneTable后SQL的查询计划没有变好，相反可能会变差，那您可以考虑“冻结执行计划”。 有关这部分内容， 请参考在线文档： Configure Frozen Plans

 

最后的注意项

• 当对一个表做TuneTable时， 请确保对和该表有父子关系，一对多关系的相关关系表也一起做TuneTable，这样才能保证SQL优化器在涉及“关系表”的查询时正确工作。
• 另外， 如果要在实验环境中清除统计信息，您可以执行以下命令，只适用IRIS 2023。

set tsc = $SYSTEM.SQL.Stats.Table.ClearTableStats("SQLUser.Patient")