云和恩墨技术专家
本文来自于本周四云和恩墨大讲堂于远的内容分享:一条让人欲罢不能的 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 再分析(应用层面)
通过与应用深入沟通,了解到,SQL 是属于信控模块,目的是判断用户是否订购取消节假日免停机,方法 isOrderProductInOffer(long userId, longprodId) ,而 prod_id 传入的参数始终是 212079900009,但是在SQL的书写上没有加入 prod_id 限制条件。
而该 SQL 每日查询超过 200W 次,每次都返回不少数据,会给数据库,网络,应用程序都带来负担。如果在 SQL 上加上条件 prod_id=212079900009,将减少大量不必要的开销。通过沟通,应用方面也确认可以在这个地方业务上做优化,将会大大减少返回的数据量。
SQL 再优化
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
联系客服