大家好,由于之前定的主题刚好有人分享了类似的内容,所以今天分享的主题改为SPA的学习分享
最近网维在做批量的数据库版本升级工程,为了保证升级能更加平稳,升级过程中我们采用Oracle 11g SQL 性能分析器(SQL Performance Analyzer,以下简称SPA)功能来预测关键SQL在新系统上性能情况,以便提前发现问题并做相关的优化。
由于SQL 性能分析器是11g的新功能,通过相关的配置可以应用在10g/9i升级到11g的过程中,大致的流程如下:先在10g/9i上通过各种方法生成SQL优化集,然后再将优化集导入到11g上,最后对每条SQL生成10g和11g上的对比报告,从这个报告便能发现SQL性能是否改善,执行计划有没有改变。
SQL 性能分析器可用于预测和防止会影响SQL 执行计划结构的任何数据库环境更改所带来的潜在性能问题。这些更改可以包括(但不限于)以下任何一种更改:数据库升级、实施优化建议、更改方案、收集统计信息、更改数据库参数、更改操作系统和硬件
下面简单看看SPA生成报告的过程,以10g升11g的为例:
新建SPA用户(不要建在SYS用户下),创建sql_test优化集
加载优化集
1).从AWR快照中加载(首先需要检查SYSAUX空间是否足够!)a).找出需要的快照
b).加载2个快照之间的所有查询
c) 验证创建的SQL优化器
## 查询载入的子句数 等SQLSET的信息
2).从当前游标缓存中加载(在两个节点分别执行)
从 STS 中确认SQL语句已经注册(运行时间很长)
将10g中的sql优化器移动到11g数据库
将10g中的SQL优化器移动至11g数据库,在spa用户下创建 stgtab(SQLSET_TAB)表(不能使用SYS用户,使用新建用户)
将MY10GSTS_CRM SQL优化器打包到表SQLSET_TAB(SPA用户运行)
(3).使用IMPORT将SQLSET_TAB表导入到11gRAT 资料数据库中
注意删除数据!注意删除数据!注意删除数据!不然数据会很"海量"
在11G中创建SPA报告的过程就不再一一叙述,有兴趣的可以看下具体的操作方案,最后贴个SPA报告的内容
执行成功率汇总
在每个报告里面都有如上内容,可以看到抓取业务SQL 30516条sql,有13413条执行成功,其他语句未执行成功。其中这些语句一部分是因为迁移过来,数据的rowid发生了改变,无法找到rowid。一部分是因为表和试图不存在,因为部分数据是静态的,缺少一些数据。有的是SPA不支持的语句。如一些insert/update/job语句。
基于BUFFER_GETS的对比分析
从BUFFER GET上来看,没有SQL出现性能退化,有一条SQL出现性能优化。
实际上,大多数SQL迁移后执行效率不会有明显问题,但是通过SPA报告分析,可以挑出个别问题SQL,防范于未燃。
例如某系统升级迁移(11203升11204,异机迁移)后,一条关键业务的SQL没按原来的执行计划走索引索描,而是走了全表。
升级后的SQL执行计划(走全表扫)
SELECT * from basejk.CRM_STOP_OPEN b WHERE b.state='F0C'
原来的执行计划(走索引扫描)
SELECT * from basejk.CRM_STOP_OPEN b WHERE b.state='F0';
在新环境收集统计信息后(依旧走全表扫描)
由于涉及表的统计系统相同,其他的一些包括:索引,字段的信息等等也相同,但当时没有针对这个问题做更深入的分析找出根源,而是通过绑定执行计划解决。
总结:
SPA作为一个SQL性能对比的手段,可以帮助对比迁移/升级前后的SQL性能情况,在正式投入生产前发现问题,以便及时解决。
数据库大版本升级,无论采用什么方式都会存在风险和隐患,在时间允许的范围内尽可能采用不同手段去排查隐患,可以提高升级的稳定性和成功率。