Oracle数据库升级迁移、SPA及统计信息

2014年5月8日，是一个重要的日子。一个省级电信运营商，将核心的CRM系统Oracle数据库进行了升级迁移，成功地从原来运行在IBM 595平台上的双节点10.2.0.5 RAC数据库，迁移并升级到基于x86+vmware平台的双节点11.2.0.3 RAC数据库上。新平台的服务器成本只有原小型机成本的十分之一。我个人认为是这是一个里程碑式的事件，这标志着，不远的将来，以IBM p系列为代表的小型机将退出电信运营商的舞台，而以x86平台为核心的私有云平台将占主导地位。

在这篇文章中，我分享一个小小的主题，那就是关于数据库迁移升级后的统计信息收集策略问题。

在Oracle数据库的世界里，统计信息显得是那么重要，所以在升级系统时，我们考虑的一个重要问题是，在新的系统上采用何种统计信息收集策略，或者至少要保证上线的时候不能因为统计信息问题而导致系统性能异常。

这套CRM系统在2010年之前运行在Oracle 9i之上，那时采用手工手集统计信息的方式，每个月在固定的日期收集2-3次统计信息。在我接触到这套系统之前，系统隔段时间总会遇到收集统计信息后某些SQL性能变差影响业务的问题。后来我与这套系统的DBA商量，对手工要收集统计信息的表进行了梳理，去掉可能会引起问题的直方图，对某些表上的某些列必须收集直方图，最后将dbms_stats.gather_table_stats中的method_opt参数改为for all column size repeat，之后基本上再没出现过统计信息变更导致性能的问题。

2010新数据库随应用的升级同时升级到了10.2.0.5版本。开始仍然是用一张表一张表的方式收集统计信息，再后来，随时时间和应用的演变，统计信息的收集也发生了变化。但仍然是手工收集的方式，而没有开启统计信息自动收集。

此次升级到11.2.0.3，我们将面临两个问题：

1. 在新系统上线之时如何保证统计信息正确，以确保系统上线成功？

2. 新系统上线之后，统计信息的收集策略应该是怎么样的？

其实这两个问题可以合成一个问题，因为第一个问题的答案决定了后一个问题。对于第一个问题，有两种可能的处理办法：

1. 迁移时将原数据库的统计信息迁移过来

2. 在新数据库上自动收集统计信息

或许有的人会毫不犹豫地选择第一种方法，但是我们认为这种方法有很明显的缺点：

1. 11gR2版本在优化器方面与10gR2版本相比，多少有些变化，这不一定能保证此种统计信息的收集方法使得新系统的SQL的执行计划就是合理的。

2. 如果用这种方法，那么在上线后启用自动统计信息的收集将会面临风险，因为没有测试验证过。那么此后只能采用手动统计信息的方式。

基于大量11gR2数据库采用自动统计信息的现状、11gR2在CBO以及自动收集统计信息上的不断完善，我们决定在此次新系统上线时以及上线后，采用“自动收集统计信息”的策略。为了保证这个策略是可行的，SPA（SQL Performance Analyzer）就派上了用场。我们使用原生产库的月初、月中、月末三个不同时间段的shared pool的所有SQL，以及一个月内的AWR中的所有SQL，进行SPA测试。而对于新数据库的统计信息，我们使用了最简单的命令来收集统计信息：dbms_stats.gather_database_stats(options=>’gather auto’); 这条命令与数据库自带的自动收集统计信息的任务是基本一致的，也是”增量“收集统计信息的方式，只收集统计信息缺失或陈旧的表和索引的统计信息，唯一的区别是自动任务是有时间窗口的，而手工执行时没有时间窗口而已。

在用这样的方式收集统计信息后，我们使用SPA测试了数万条SQL语句，再用几十个SQL PROFILE固化了可能存在风险的SQL，这样系统就顺利上线了。

不过这种统计信息收集的方式还有一个关键点，那就是直方图。由于跨平台迁移，对于停机时间要求很严格的系统来说，毫无疑问是使用增量同步数据的方式来迁移数据。如果在上线之前用上述的命令来收集统计信息，那直方图一定是有问题的，或许你会发现，表上几乎没有直方图。这个问题是什么产生的？其实这个问题在文档中已经有提及，那就是自动收集统计信息实际上是参考col_usage$来决定是否要在列上收集直方图。Oracle在硬解析SQL语句时，会记录where后面列的使用，是相等比较，或是范围比较，或者是否有相等连接等等。对于全新的数据库，col_usage$是没有数据或是严重不完整的，所以，用刚才提到的命令或自动收集的统计信息就没有直方图了。解决这个问题的办法就是，在上线之前，使用SPA测试足够多的SQL语句，让col_usage$有足够的数据，这样问题就解决了。

建议继续学习：