大家好,由于之前定的主题刚好有人分享了类似的内容,所以今天分享的主题改为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优化集
![f6261e1057c798ec786b3d03cb0db1e1.png](https://i-blog.csdnimg.cn/blog_migrate/65e7d0d4a8c1f9ca9891ee9d3737b7c4.jpeg)
加载优化集
1).从AWR快照中加载(首先需要检查SYSAUX空间是否足够!)a).找出需要的快照
![bd9b392f9dd56aa0ba76faf32b43d5d0.png](https://i-blog.csdnimg.cn/blog_migrate/461324117eedb51ca44d437ede9ed6f3.jpeg)
b).加载2个快照之间的所有查询
![24f25ce59a1c088cc669b297f63625c9.png](https://i-blog.csdnimg.cn/blog_migrate/f973fbc3bd93a5eaa8dc67c766c8f57a.jpeg)
c) 验证创建的SQL优化器
## 查询载入的子句数 等SQLSET的信息
![cbb5401cdaad036d678f19713c7d9f53.png](https://i-blog.csdnimg.cn/blog_migrate/5e10b1a880d9a4111e14dc5a8c018c3f.jpeg)
2).从当前游标缓存中加载(在两个节点分别执行)
![5f67ae670c5192fa4c5051b2291d762a.png](https://i-blog.csdnimg.cn/blog_migrate/d299bf2d9a87947d73df6b9b0ea3f2b2.jpeg)
从 STS 中确认SQL语句已经注册(运行时间很长)
![a897825c09ff8a47bef8c09c29fa3358.png](https://i-blog.csdnimg.cn/blog_migrate/f9b98bedda8b0122db077f696cb9af8f.jpeg)
将10g中的sql优化器移动到11g数据库
![d704119da24ad21d9cabd715b2752100.png](https://i-blog.csdnimg.cn/blog_migrate/4d31a096ff7e0487449a7ea2e4a4b3f4.jpeg)
将10g中的SQL优化器移动至11g数据库,在spa用户下创建 stgtab(SQLSET_TAB)表(不能使用SYS用户,使用新建用户)
![7de006e30c87e9f1399e4ea25cb2bc80.png](https://i-blog.csdnimg.cn/blog_migrate/08de072e3f6c7469fe39d84881bcce91.jpeg)
将MY10GSTS_CRM SQL优化器打包到表SQLSET_TAB(SPA用户运行)
![045dbebb030be2fa81166c4bf876462b.png](https://i-blog.csdnimg.cn/blog_migrate/6e836bc4cfb19152e8ea15b98f7dc55e.jpeg)
(3).使用IMPORT将SQLSET_TAB表导入到11gRAT 资料数据库中
![33652fcd51343e2476e88c6ffefa7b46.png](https://i-blog.csdnimg.cn/blog_migrate/f92a461b8dca92e789db5bd29f44d1b9.jpeg)
注意删除数据!注意删除数据!注意删除数据!不然数据会很"海量"
![14493533665115fbbccfd7e3caf8d5f0.png](https://i-blog.csdnimg.cn/blog_migrate/b57354a17820485ad007d0efc8b870e6.jpeg)
![fbd8a4cb5d29040ea225950fc6fc3e6b.png](https://i-blog.csdnimg.cn/blog_migrate/f1229f6136b8fc2b03ae4d6bfcba69a7.jpeg)
![912e291e309af68f2bb471c433e33452.png](https://i-blog.csdnimg.cn/blog_migrate/c24e916b8ac8e0713c71a0fa2d92579f.jpeg)
![0660586c8e7a610def392280118e9f14.png](https://i-blog.csdnimg.cn/blog_migrate/b2deec15c663a605cdf58275a1adabc1.jpeg)
在11G中创建SPA报告的过程就不再一一叙述,有兴趣的可以看下具体的操作方案,最后贴个SPA报告的内容
执行成功率汇总
![b211ac84ce47f771f1054c2c624c5205.png](https://i-blog.csdnimg.cn/blog_migrate/5e25a61c2d9ac88cc85aee8dcc8c8686.jpeg)
在每个报告里面都有如上内容,可以看到抓取业务SQL 30516条sql,有13413条执行成功,其他语句未执行成功。其中这些语句一部分是因为迁移过来,数据的rowid发生了改变,无法找到rowid。一部分是因为表和试图不存在,因为部分数据是静态的,缺少一些数据。有的是SPA不支持的语句。如一些insert/update/job语句。
基于BUFFER_GETS的对比分析
![b95b046c1550a2c2a0a4d8ad9729dde7.png](https://i-blog.csdnimg.cn/blog_migrate/f0be5bd3d6d6023774fa1293ac8eca8f.jpeg)
从BUFFER GET上来看,没有SQL出现性能退化,有一条SQL出现性能优化。
实际上,大多数SQL迁移后执行效率不会有明显问题,但是通过SPA报告分析,可以挑出个别问题SQL,防范于未燃。
例如某系统升级迁移(11203升11204,异机迁移)后,一条关键业务的SQL没按原来的执行计划走索引索描,而是走了全表。
升级后的SQL执行计划(走全表扫)
SELECT * from basejk.CRM_STOP_OPEN b WHERE b.state='F0C'
![bf730df9c7df211ace1077b91ac8158b.png](https://i-blog.csdnimg.cn/blog_migrate/262f10be95e3b8d5fdac7d09f2e4f521.jpeg)
原来的执行计划(走索引扫描)
SELECT * from basejk.CRM_STOP_OPEN b WHERE b.state='F0';
![0799f0ef70833cb57094d9bd2a28f28f.png](https://i-blog.csdnimg.cn/blog_migrate/26926a735f43f8ae491fd0ed363d2975.jpeg)
在新环境收集统计信息后(依旧走全表扫描)
![09f2c29d10950c67e20eefcb9502a742.png](https://i-blog.csdnimg.cn/blog_migrate/ad16c93187dd9f868e9b926457075ee8.jpeg)
由于涉及表的统计系统相同,其他的一些包括:索引,字段的信息等等也相同,但当时没有针对这个问题做更深入的分析找出根源,而是通过绑定执行计划解决。
总结:
SPA作为一个SQL性能对比的手段,可以帮助对比迁移/升级前后的SQL性能情况,在正式投入生产前发现问题,以便及时解决。
数据库大版本升级,无论采用什么方式都会存在风险和隐患,在时间允许的范围内尽可能采用不同手段去排查隐患,可以提高升级的稳定性和成功率。