Oracle数据库统计信息_执行计划_sharedpool等的知识梳理

背景

最近有项目出现了年底业务量增加时卡顿的情况. 
同事多次发现执行SQL缓慢.
但是重新执行统计信息更新后问题就优化的现象.
12月份上半月解决测试环境的SQLServer卡顿时基本上也是这个套路
重建索引, 添加必要索引的方式进行优化. 
产品项目上的问题和优化手段也比较相似. 
这里想总结一下,数据库方面的知识,作为一个学习和提高的机会 

前期总结的执行计划,索引更新的SQL

## Oracle重新获取统计信息:
exec dbms_stats.gather_schema_stats(ownname =>'username',options => 'GATHER',estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size repeat', degree => 4)
## Oracle 还可以这样:
select 'alter index '||index_name|| ' rebuild;' from user_indexes

----------------------------------------------------------------

## SQLSERVER重新新建所有表的索引:
EXEC SP_MSFOREACHTABLE 'dbcc DBreindex("?")'
## SQLSERVER重新收集所有表的统计分析记录.
EXEC SP_UPDATESTATS;

## 注意可以统计一下Oracle的执行统计信息更新的时间:
我这边一个 四路72核心144线程的服务器, 我这边执行的SQL为:
set timing on ;
exec dbms_stats.gather_schema_stats(ownname =>'xxxx',options => 'GATHER',estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size repeat', degree => 72)

执行时间为: 
第一次的执行时间为: Elapsed: 00:28:10.46
直接进行第二次时为: Elapsed: 00:26:29.11
发现统计信息的时间跟 表大小/表多少/CPU/硬盘的情况相关性很大.
但是跟是否统计过的关系反而不是很大. 

注意 我一共有 20000个表, 数据文件大小为: 200G左右. 

CPU使用情况
02:10:01 PM     CPU     %user     %nice   %system   %iowait    %steal     %idle
02:10:01 PM     all      1.89      0.00      3.42      0.04      0.00     94.65
02:20:01 PM     all      2.12      0.00     25.00      0.05      0.00     72.83
02:30:01 PM     all      2.27      0.00     20.92      0.17      0.00     76.64
02:40:01 PM     all      1.90      0.00     13.93      0.08      0.00     84.09

发现大部分时间都是 sys态的时间. 

前提-关于数据库的优化思路

Oracle.SQLServer等的大部分业务表都是堆表.
堆表的特性在插入删除时效率很快,查询时的性能不是很好 

大量进行写更新的表一半都建议使用堆表方式进行存储. 

堆表的性能的生命线其实是 索引. 
索引一方面可以绝对查询效率, 
另外一方面,如果索引可以包含被查询的内容和判断条件,产品是可以不用再range查询时执行 回表的操作, 性能会优化十倍百倍. 

其实不管是何种数据库, 优化思路其实都是:
0. 优化业务SQL, 避免出现不走索引, 全表查询, 胡乱关联,无限制关联, 深分页, 大表驱动小标, 无限循环, 不进行变量绑定, 大量业务单条提交等类型的SQL语句. 
1. 减少重点业务表的大小, 可以使用冷热数据分别存储, 分区表等方式来实现.
2. 增加索引的区分度, 以及有效性, 如果是分区表,所有的查询SQL建议必须第一个Where条件指定分区字段. 少量字段查询, 尽量使用复合索引覆盖查询结果集.
3. 适当进行索引.统计信息的更新, 避免CBO使用错误的查询分析计划, 导致执行效率下降.
4. 适当增加SGA/PGA等内存参数, 优化process/session/cursor等参数, 避免出现内存配置不对导致出现性能瓶颈
5. 优化操作系统参数, 保证操作系统没有其他大型IO影响实际业务使用, 避免非数据库因素影响数据库. 比如numa/透明大页/大页内存参数等.
6. 优化服务器硬件, 比如升级快速的硬盘, 换用更加优秀的Raid卡, 数据库坚决避免使用PM8222这种类型的无缓存的raid卡. 
7. 有条件使用高主频的CPU, 主频对高性能的影响要比核心数更加重要, 尽量选用较新的CPU, 新CPU的IPC更加优秀一些. 
8. 使用高性能的网络/最佳性能配置的BIOS,良好的散热,平稳的电源供电,没有震动的机房条件, 减少认为误操作的可能. 
9. 合理进行运维监控以及巡检, 保证数据库平稳运行. 

数据库的自动更新

Oracle数据库其实是有一个计划任务,定期进行数据库的索引统计信息更新的处理

可以进行如下的操作进行处理: 查看所有表的最后被分析的时间
select table_name, num_rows, last_analyzed from user_tables where num_rows is not null order by LAST_ANALYZED DESC  ;

Oracle数据库的计划任务一般可以使用如下方式查询
select * from dba_autotask_client
select * from dba_autotask_client_history  order by WINDOW_END_TIME DESC 

第一个SQL可以查看计划任务是否开启
第二个SQL可以查看所有任务执行情况. 开始时间,结束时间等. 

如果发现特定表的执行计划不够新.可以采用这个命令进行处理: 
call dbms_stats.gather_table_stats('用户名','表名');

注意事项

虽然有一个计划任务: auto optimizer stats collection
执行统计计划的分析
但是数据库并不是无脑的进行所有表的统计信息更新的. 

他一般是按照数据库的数据/索引变化的量进行评估, 是否进行统计信息的更新
这里如果表很大, 你的变化量虽然不小, 到那时比率如果非常低, 也有可能不会执行统计信息更新
所以就会出现一个很严重的悖论, 导致大表的性能很差, 却得不到应该有的优化. 

可以通过如下SQL进行查询表最近一周的变量话排名. 

SELECT
	obj.owner,
	obj.object_name,
	to_char( sn.BEGIN_INTERVAL_TIME, 'yyyy-mm-dd' ) start_day,
	round( sum( a.db_block_changes_delta * 8 ) / 1024 / 1024, 2 ) incr_GB 
FROM
	dba_hist_seg_stat a,
	dba_hist_snapshot sn,
	dba_objects obj 
WHERE
	sn.snap_id = a.snap_id 
	AND obj.object_id = a.obj# 
	AND obj.object_type = 'TABLE' 
	AND obj.owner NOT IN ( 'SYS', 'SYSTEM' ) 
	AND end_interval_time BETWEEN to_timestamp( SYSDATE - 10 ) 
	AND to_timestamp( SYSDATE ) 
GROUP BY
	obj.owner,
	obj.object_name,
	to_char( sn.BEGIN_INTERVAL_TIME, 'yyyy-mm-dd' ) 
ORDER BY
	INCR_GB DESC

关于统计信息和SQL执行

Oracle为了SQL的高效执行其实做了非常多的工作:

1. 不停地进行统计信息的更新和处理
2. 一直在不停地优化CBO分析器.
3. 一直在进行SQL执行效率的提升, 比如使用并行, 使用最新的类似于AVX512等指令集.
4. 将形成的合理的执行计划存储到 shared_pool 实现执行计划的 复用, 避免解析执行计划浪费CPU和浪费查询时的实际物理时间. 

这里面其实就存在一个比较严重的问题:

数据库存储的执行计划,可能空间不够被弃用,也有可能使用了一开始错误的统计信息, 导致执行效果并不好, 但是还一直存在未进行清理. 
现场遇到的问题, 其实是第二个. 

第一个问题,如果SQL数量居多, 但是shared_pool内存用量较小, 也会出现CPU使用量较高的问题. 

其实这两个问题 我们都可以理解为是内存使用不当 导致CPU利用率出现波动的情况. 

其实非常不建议生产时间进行 统计信息的收集, 和 shared_pool的清理工作. 此类工作应该都放到业务低谷期进行处理. 

应急处理的方法

第一个: 有条件重启的话, 重启数据库.
但是历久弥新的数据库非常不建议执行重启, 如果数据库处于一种很微妙的平衡状态, 
一次非规划内的重启可能会导致数据库宕机. 如果不是自己特别命硬, 不建议动不动就重启数据库. 尤其是高压力的情况下.

第二个: 清理内存
sqlplus / as sysdba <<EOS
alter system flush shared_pool ;
EOS
执行很简单, 但是如果高并发情况下, 重新生成统计信息可能会导致CPU瞬间升高.
当然了如果服务器CPU够猛可以不考虑, 如果是那种跟自己工龄还长的服务器CPU, 不建议挑战物理规律. 

第三个: 使用工具逐个清理
可能存在一些异常情况, 并且需要dba的权限,不是非常建议采用此类方式. 

后续优化思路

如果全表重建比较难, 是否可以再业务高峰之前的早上四点开始. 暂停一下计划任务.
留出大约 2-3个小时左右的时间, 执行一下统计任务的采集. 

这样可以保证第二天可以有比较准确的统计信息实用. 

另外中午如果没有备份恢复后者是其他计划任务的话
可以将业务使用量较大的表执行一下计划统计信息的收集. 
避免早上业务量较大引起统计信息失真. 

需要注意, SQL最好都进行一下限时操作, 避免业务跟业务高峰重合, 影响客户体验.