解决Logical Reads高的方法和实验

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/bisal/article/details/76560183

之前介绍了CBC,就是cache buffer chains这个等待事件的影响,《缓解latch: cache buffers chains的案例,解决逻辑读过高的SQL语句,是优化方向。为了更直观地说明这个问题,通过模拟实验,来了解下。


创建测试表,test表三个字段,分别是id1,id2和name,insert入100万行记录,其中id1每个distinct值100次,id2针对每个id1的distinct值,其是唯一的,namedbms_random则是取随机数,


test表总计为127MB,


新建test表id1字段,为非唯一单键值索引,收集表的统计信息,cascade=true,这张表每行平均占用字节108个,


根据id1=1、id2=746,以及name的取值执行SQL,


执行计划中,E-Rows和A-Rows一样,第一步是根据id1索引检索符合条件的rowid键值,根据数据特征,会返回1万条记录,需要回表,根据id2和name字段,过滤检索,返回符合条件的1条记录。相应内存消耗181,


删除原索引,新建id1和id2的复合索引,



执行同一条SQL,E-Rows和A-Rows一样,第一步会根据id1和id2的复合索引,检索出1条记录的rowid,第二步回表检索这个rowid,对应的数据,根据name过滤条件,返回检索。相应内存消耗5,



内存消耗之所以下降了,就是因为从数据特征看,id1、id2和name条件检索结果只会是1条记录,但id1索引会返回1万条数据,在此基础上,做过滤处理,相当于要将1万条数据加载至buffer cache,108bytes(单行平均长度)*10000条=1MB,即一次执行需要1MB的数据内存空间,而id1和id2的复合索引,从索引扫描阶段,就只会返回唯一一条记录,根据name过滤处理,需要的数据空间为108bytes(单行平均长度)*1条=108bytes,即一次执行需要108字节数据内存空间。


我们从AWR看下两种索引的区别,

(1) id1这个单键值非唯一索引,两个session,并发总计执行20万次select操作,基本需要10分钟所有的时间,逻辑读消耗5万次/秒,每次交易消耗逻辑读21万,



SQL ordered by Gets中则显示这条SQL逻辑读消耗,排名第一,每次执行消耗179次逻辑读,执行20万次,总计消耗3千5百万次逻辑读,


(2) id1和id2复合索引,两个session并发执行总计40万次select操作,基本每20万次执行之需要1-2秒,逻辑读消耗1万9千/秒,每次交易消耗逻辑读2万次,


SQL ordered by Gets中则显示这条SQL逻辑读消耗,排名第一,但每次执行仅消耗4次逻辑读,执行40万次,总计消耗160万次逻辑读,执行次数翻了一倍了,但逻辑读消耗仅为原来的1/21,自然支持的TPS就会提升了,


总结:

1. CBC等待事件,结合Load Profile中的逻辑读部分,往往需要关注SQL ordered by Gets中排名靠前的SQL语句,如何使这些SQL语句逻辑读降低,是优化方向。

2. 可能需要分析SQL执行计划,看是否实际执行,加载了本不需要的内存,索引设置是否合理了。

3. AWR、SQL AWR这些工具,均是我们的利器,要充分利用这些。



如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!共同学习,共同进步:)

展开阅读全文

没有更多推荐了,返回首页