|
于远
云和恩墨技术专家
本文来自于本周四云和恩墨大讲堂于远的内容分享：一条让人欲罢不能的 SQL 给我的启发。通过实际生产环境中的 SQL 优化案例的讲解，分享了 SQL 优化的思路流程和对运维工作的反思。
客户的一套核心OLTP生产系统，业务量较大，常见的 TOP5 等待，如下：
可以见到：db file sequential read 事件总是排在第一，关于 Db file sequential read 等待事件，这里介绍一下：
db file sequential read 事件有三个参数:file#,first block#, blockcount, 此等待事件归于 User I/O wait class 下面。 db filesequential read 等待事件是由于执行对index, rollback(orundo) segments, tables(通过 rowid 访问表)，control files 和 data file headers 的 single-block read 操作 SQL 语句引起的，全表扫描时，部分块未缓存到内容中，单独访问这些块也会产生单块读。
提道 sequential read，我们再简单谈谈和 scattered read，direct read 的区别:
· db file sequential read (single block read into one SGA buffer) · db file scattered read (multiblock read into many discontinuous SGA buffers) · direct read (single or multiblock read into the PGA, bypassing theSGA)
到此，我们知道单块读是一种最为常见的物理 IO，当出现 db file sequential read 等待事件时，并不意味着数据库产生系统问题，基至它大量出现都不是一件坏事，不过我们仍然需要对系统中那些物理读总量较高的 TOPSQL，进行分析优化，从而降低整库的 db file sequential read 等待，提升系统响应。
通过提取系统中 TOPSQL，发现 SQLID: grq5hzwqbbg6y 执行次数很多，逻辑读，物理读总量很高，对系统影响较大，该 SQL 19个小时内统计数据如下：
统计报告上看，该 SQL 在19个小时内执行了170多万次，平均每次14.98块的物理读，每次返回29.81行，这条 SQL 查询的表是 INS_PROD_851，同样，查询 INS_PROD_85X(1...9)的SQL，也有类似问题，根据实际生产环境估算，优化前，这9条 SQL 每天大约产生2亿块左右的单块读，优化了这一类 SQL，将有助于降低 db file sequential read 等待。
先看看SQL全文：
select M.SRC_SYSTEM_EXT_CODE, M.COUNTY_CODE, M.PROD_ID, M.REGION_ID, M.PROD_PKG_FLAG, M.EXPIRE_PROCESS_TYPE, M.DONE_CODE, M.EXPIRE_DATE_TYPE, M.DONE_DATE, M.PROD_INST_ID, M.EXPIRE_DATE, M.USER_ID, M.SRC_SYSTEM_TYPE, M.BUSI_TYPE, M.CREATE_DATE, M.PRESENT_OBJ_USER, M.OLD_INST_OFFER_ID, M.PROD_TYPE, M.EFFECTIVE_DATE, M.STATE, M.OFFER_INST_ID, M.EXT_1, M.EFFECTIVE_DATE_TYPE, M.OLD_OFFER_ID, M.ORG_ID, M.BOSS_EFFECTIVE_DATE, M.OP_ID, M.OFFER_USER_RELAT_ID, M.ROWID as MROWID___ from INS_PROD_851 M where M.USER_ID = :1 and sysdate between M.EFFECTIVE_DATE and M.EXPIRE_DATE;
为了更好的分析每一步等待，开启10046事件来分析。
从 trace 文件中我们看到，db file sequential read 等待在索引扫描和回表操作都存在，但是回表的等待是最多，如果可以减少回表，那么对于减少 db file sequential read 等待，非常有利。
如何降低回表查询的成本？
从前面的信息来看，索引的聚族因子较高会增加回表查询读取的块数，那么降低索引的聚族因子有利于减少回表查询时所扫描的块数，另外寻找更优化的执行计划，减少回表查询。
什么是聚族因子？ cluster_factor，这是一个判断表上的数据分布是不是和索引一样有序的一个值，它的取值范围在表的 block 的个数和表的行数之间，越接近 block 的个数说明表的数据分布越有序（和索引一样有序），越是接近行数越说明表上的数据分布是混乱的。
可以这样理解，当我们按照索引上的顺序访问表的时候，每当我们需要的数据不在当前 block 上的时候，我们就要“跳到”其他 block 上进行访问。如果表上的数据排列和索引是完全一样的话，我们跳的次数等于表的 Block 的个数，如果是另一个极端，表的数据分布极其的混乱，我们访问每一行数据都要跳一次，那我们最后跳的次数就等于行数，跳的次数就是 CLUSTER_FACTOR.
CLUSTER_FACTOR 对 Oracle 执行计划会产生重要影响。这个值越高，说明索引的使用效率将会越差。这个值会存在于一个区间内，区间的最小值为表占用的数据块数，最大值为表拥有的数据行数。
假设数据存储如下图： 
则 index1 访问其中7行，需要2个块，而 index2 访问3行就需要3个块。
按照索引列顺序重构表可以降低索引的聚族因子，如下： 重构表，CREATE TABLE AS SELECT * FROM TABLE_NAME ORDER BY 索引的列；
在生产环境中，必须要综合考虑业务需要，选择访问业务查询最频繁列排序来重构表，根据我们对业务理解，当前的 SQL，选择 USER_ID 排序重构表数据，是最佳方案。降低索引的聚族因子后，执行计划如下,可见一致性读和物理读均有部分下降，物理读下降 30%左右。
统计显示，表有1.9亿多行，按照 sysdate> EXPIRE_DATE 条件统计表数据，过期的有0.7亿多行，如果能创建基于（USER_ID, EFFECTIVE_DATE, EXPIRE_DATE）复合索引，无疑是可以减少 36%左右的回表查询。
调整以后，比原来有了进步，这个 SQL 还能再优化吗？ 是的，还能，我们还只是在数据库的层面的优化，还没有关注应用为啥会这样写这条 SQL，基于这条SQL对系统影响度，有必要去关注下这条 SQL 的具体作用。
通过与应用深入沟通，了解到，SQL 是属于信控模块，目的是判断用户是否订购取消节假日免停机，方法 isOrderProductInOffer(long userId, longprodId) ，而 prod_id 传入的参数始终是 212079900009，但是在SQL的书写上没有加入 prod_id 限制条件。
而该 SQL 每日查询超过 200W 次，每次都返回不少数据，会给数据库，网络，应用程序都带来负担。如果在 SQL 上加上条件 prod_id=212079900009，将减少大量不必要的开销。通过沟通，应用方面也确认可以在这个地方业务上做优化，将会大大减少返回的数据量。
1、 修改 SQL，增加产品条件的判断，减少返回的数据量，可大大减少应用判断次数，更减轻网络，数据库的负担。
select M.SRC_SYSTEM_EXT_CODE, M.COUNTY_CODE, M.PROD_ID, M.REGION_ID, M.PROD_PKG_FLAG, M.EXPIRE_PROCESS_TYPE, M.DONE_CODE, M.EXPIRE_DATE_TYPE, M.DONE_DATE, M.PROD_INST_ID, M.EXPIRE_DATE, M.USER_ID, M.SRC_SYSTEM_TYPE, M.BUSI_TYPE, M.CREATE_DATE, M.PRESENT_OBJ_USER, M.OLD_INST_OFFER_ID, M.PROD_TYPE, M.EFFECTIVE_DATE, M.STATE, M.OFFER_INST_ID, M.EXT_1, M.EFFECTIVE_DATE_TYPE, M.OLD_OFFER_ID, M.ORG_ID, M.BOSS_EFFECTIVE_DATE, M.OP_ID, M.OFFER_USER_RELAT_ID, M.ROWID as MROWID___ from INS_PROD_851 M where M.USER_ID= :1 AND M.PROD_ID=212079900009 and sysdate between M.EFFECTIVE_DATE and M.EXPIRE_DATE;
2、 增加索引，减少回表
通过统计和业务分析，订购产品（PROD_ID=212079900009）的用户非常的少，也就是说，如果索引就可以判断没有这样的行，那么就无需回表了，可以极大减少 db file sequential read 等待 CREATE INDEX SO1.IDX_INS_PROD_851_USER_PROD ONSO1.INS_PROD_851 (USER_ID, PROD_ID) TABLESPACE YY_INX 3、 如果业务允许，清理表过期数据。
SQL 改写和创建新索引后的执行计划如下：
再对比下以上三次优化后的效果：
10月27日对类似9条 SQL 进行了优化上线以后，可以看到整库物理读下降很多。
从业务前台的业务办理效果来看（特别说明：这个效果图，包括了应用系统多个环节，多维度，多批次的优化，但是本次 SQL 的优化贡献占了相当一部分），提升简直不敢相信自己的眼睛。下图是某项业务优化前后办理的时长对比：
这个 SQL 优化，并没有特别牛逼的技术，但是因为关注到了应用层面的东西，从应用系统全局来思考，才得到最好的效果。
我记得盖总在 DBA 的七点建议中特别提到，DBA 应该全面深入的了解应用架构，对应用了解不深入的 DBA 算不上 Expert，所以一定要深入了解应用。
通过这个案例，我们看到这个看似简单的 SQL，对系统造成了巨大的影响，最后，通过修改原始代码，重构表数据，新建索引，停机上线才完成修复，如果我们能提前对这些 SQL 进行审核，预判到问题严重性，优化以后再上线，化被动为主动，那么运营成本将会小很多，云和恩墨的 SQL 审核服务，就是为了解决此类问题而来，改善终端体验，为用户创造价值，你值得拥有。 推荐阅读：【云和恩墨业务介绍】之 SQL 审核服务
脚本的作用：查询表对象信息
set echo off set scan on set lines 160 set pages 1000 set verify off set feedback off set termout off column uservar new_value Table_Owner noprint select user uservar from dual; set termout on column TABLE_NAME heading 'Tables owned by &Table_Owner' format a30 undefine table_name undefine owner accept owner prompt 'Please enter Name of Table Owner: ' accept table_name prompt 'Please enter Table Name : ' column TABLE_NAME heading 'Table|Name' format a30 column PARTITION_NAME heading 'Partition|Name' format a14 column NUM_ROWS heading 'Number|of Rows' format 9,999,999,990 column BLOCKS heading 'Blocks' format 999,999,990,99 column EMPTY_BLOCKS heading 'Empty|Blocks' format 999,999,990
column AVG_SPACE heading 'Average|Space' format 9,990 column CHAIN_CNT heading 'Chain|Count' format 999,990 column AVG_ROW_LEN heading 'Average|Row Len' format 9,990 column COLUMN_NAME heading 'Column|Name' format a30 column NULLABLE heading Null|able format a4 column NUM_DISTINCT heading 'Distinct|Values' format 999,999,990 column NUM_NULLS heading 'Number|Nulls' format 999,999,990 column NUM_BUCKETS heading 'Number|Buckets' format 990 column DENSITY heading 'Density' format 9.99999999 column INDEX_NAME heading 'Index|Name' format a30 column BLEV heading 'BLV' format 90 column LEAF_BLOCKS heading 'Leaf|Blks' format 999,999,999 column DISTINCT_KEYS heading 'Distinct|Keys' format 9,999,999,990 column AVG_LEAF_BLOCKS_PER_KEY heading 'AV|LEA' format 99,990 column AVG_DATA_BLOCKS_PER_KEY heading 'Av|Data' format 99,990 column CLUSTERING_FACTOR heading 'Cluster|Factor' format 999,999,990 column COLUMN_POSITION heading 'Col|Pos' format 990 column col heading 'Column|Details' format a24 column COLUMN_LENGTH heading 'Col|Len' format 9,990 column GLOBAL_STATS heading 'Global|Stats' format a6 column USER_STATS heading 'User|Stats' format a6 column SAMPLE_SIZE heading 'Sample|Size' format 9,999,999,990 column to_char(t.last_analyzed,'MM-DD-YYYY') heading 'Date|MM-DD-YYYY' format a10 column HISTOGRAM for a10
prompt prompt ********************************************************** prompt Table Level prompt ********************************************************** prompt select TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, CHAIN_CNT, AVG_ROW_LEN, GLOBAL_STATS, SAMPLE_SIZE, to_char(t.last_analyzed,'MM-DD-YYYY') from all_tables t where owner = upper(nvl('&&Owner',user)) and table_name = upper('&&Table_name') / select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_BUCKETS, NUM_NULLS, SAMPLE_SIZE, to_char(t.last_analyzed,'MM-DD-YYYY') from all_tab_columns t where table_name = upper('&Table_name') and owner = upper(nvl('&Owner',user)) /
select INDEX_NAME, BLEVEL BLev, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, CLUSTERING_FACTOR, to_char(t.last_analyzed,'MM-DD-YYYY') from all_indexes t where table_name = upper('&Table_name') and table_owner = upper(nvl('&Owner',user)) / break on index_name select /*+ first_rows use_nl(i,t)*/ i.INDEX_NAME, i.COLUMN_NAME, i.COLUMN_POSITION, decode(t.DATA_TYPE, 'NUMBER',t.DATA_TYPE||'('|| decode(t.DATA_PRECISION, null,t.DATA_LENGTH||')', t.DATA_PRECISION||','||t.DATA_SCALE||')'), 'DATE',t.DATA_TYPE, 'LONG',t.DATA_TYPE, 'LONG RAW',t.DATA_TYPE, 'ROWID',t.DATA_TYPE, 'MLSLABEL',t.DATA_TYPE, t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '|| decode(t.nullable, 'N','NOT NULL', 'n','NOT NULL', NULL) col from all_ind_columns i, all_tab_columns t where i.index_owner=t.owner and i.table_name = upper('&Table_name') and i.index_owner = upper(nvl('&Owner',user)) and i.table_name = t.table_name and i.column_name = t.column_name order by index_name,column_position /
prompt prompt ********************************************************** prompt Partition Level prompt **********************************************************
select PARTITION_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, GLOBAL_STATS, to_char(t.last_analyzed,'MM-DD-YYYY') from all_tab_partitions t where table_owner = upper(nvl('&&Owner',user)) and table_name = upper('&&Table_name') order by partition_position /
break on partition_name select PARTITION_NAME, COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_BUCKETS, NUM_NULLS, SAMPLE_SIZE, to_char(t.last_analyzed,'MM-DD-YYYY') from all_PART_COL_STATISTICS t where table_name = upper('&Table_name') and owner = upper(nvl('&Owner',user)) /
break on INDEX_NAME select t.INDEX_NAME, t.PARTITION_NAME, t.BLEVEL BLev, t.LEAF_BLOCKS, t.DISTINCT_KEYS, t.NUM_ROWS, t.AVG_LEAF_BLOCKS_PER_KEY, t.AVG_DATA_BLOCKS_PER_KEY, t.CLUSTERING_FACTOR, to_char(t.last_analyzed,'MM-DD-YYYY') from all_ind_partitions t, all_indexes i where i.table_name = upper('&Table_name') and i.table_owner = upper(nvl('&Owner',user)) and i.owner = t.index_owner and i.index_name=t.index_name /
clear breaks set verify on set feedback on set termout on
------ The End
|
