转载

解决Logical Reads高的方法和实验

之前介绍了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，

解决Logical Reads高的方法和实验

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

解决Logical Reads高的方法和实验

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

解决Logical Reads高的方法和实验

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

解决Logical Reads高的方法和实验

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

解决Logical Reads高的方法和实验

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

解决Logical Reads高的方法和实验

内存消耗之所以下降了，就是因为从数据特征看，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万，

解决Logical Reads高的方法和实验

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

解决Logical Reads高的方法和实验

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

解决Logical Reads高的方法和实验

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

解决Logical Reads高的方法和实验

总结：

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

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