开发交流时给同事们讲解的一个sql优化案例

    这个sql其实是很早之前调整优化的一个sql了,前一阵儿在准备和开发同事们做sql交流的时候,把这个案例又整理了一下,不过在整理那次优化的文档的时候,发现一个以前没有注意的问题,下面会提到一下.这里的数据库版本:10.2.0.4
SQL> create table zsj_diy_userlog as select * from diy_userlog; SQL> alter table zsj_diy_userlog add constraint pk_zsj_diy_userlog primary key(id); SQL> exec dbms_stats.gather_table_stats(user,'zsj_diy_userlog',cascade=>true,estimate_percent=>100,degree=>4,method_opt=>'for all columns size 1'); zsj_diy_userlog上只有一个主键对应的唯一索引.

原来的语句是这样的(标记为sql1):

select t1.id, t1.itemid, (sysdate - t1.inputdate) as startprice, t1.inputdate, t1.type, t1.content, t1.userid, t1.username, t1.before_itemid, t1.back_itemid, rownum rn from (select id, itemid, inputdate, type, content, userid, username, before_itemid, back_itemid from zsj_diy_userlog t where username is not null and type = 9 and inputdate = (select max(inputdate) from zsj_diy_userlog t1 where t1.username = t.username) order by inputdate desc) t1 where rownum <= 5; 它的执行计划及其统计信息是这样的: ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------- |* 1 | COUNT STOPKEY | | 1 | | | 5 |00:00:47.77 | 64444 | | 2 | VIEW | | 1 | 1 | 1082 (29)| 5 |00:00:47.77 | 64444 | |* 3 | FILTER | | 1 | | | 5 |00:00:47.77 | 64444 | | 4 | SORT GROUP BY | | 1 | 1 | 1082 (29)| 56 |00:00:47.77 | 64444 | |* 5 | HASH JOIN | | 1 | 66475 | 1076 (28)| 17M|00:00:34.66 | 64444 | |* 6 | TABLE ACCESS FULL| ZSJ_DIY_USERLOG | 1 | 7502 | 538 (28)| 22384 |00:00:00.45 | 32222 | |* 7 | TABLE ACCESS FULL| ZSJ_DIY_USERLOG | 1 | 30010 | 538 (28)| 30010 |00:00:00.42 | 32222 | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=5) 3 - filter("INPUTDATE"=MAX("INPUTDATE")) 5 - access("T1"."USERNAME"="T"."USERNAME") 6 - filter(("USERNAME" IS NOT NULL AND "TYPE"=9)) 7 - filter("T1"."USERNAME" IS NOT NULL)

    这里步骤7估算是准确的,而步骤6的估算还是有很大的误差的,是因为这里有4个不同的type值,这里是按它们均匀分布计算出来的,实际上它们的分布并不均匀,type=9占据了绝大多数的数据行,既然优化器统计信息存在问题,那就先纠正一下这个问题,重新收集type这个列上的频度直方图信息:
exec dbms_stats.gather_table_stats(user,'zsj_diy_userlog',cascade=>false, estimate_percent=>100,method_opt=>'for columns size 254 type',no_invalidate=>false);
--这里只重新收集type列上的直方图信息,不重新收集其它列上的信息了,它们仍然保留上次的统计信息
    之后这个sql的执行计划及其统计信息是这样的:

----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------- |* 1 | COUNT STOPKEY | | 1 | | | 5 |00:00:37.75 | 64444 | | 2 | VIEW | | 1 | 1 | 1100 (30)| 5 |00:00:37.75 | 64444 | |* 3 | FILTER | | 1 | | | 5 |00:00:37.75 | 64444 | | 4 | SORT GROUP BY | | 1 | 1 | 1100 (30)| 56 |00:00:37.75 | 64444 | |* 5 | HASH JOIN | | 1 | 265K| 1079 (29)| 17M|00:00:34.77 | 64444 | |* 6 | TABLE ACCESS FULL| ZSJ_DIY_USERLOG | 1 | 30010 | 538 (28)| 30010 |00:00:00.51 | 32222 | |* 7 | TABLE ACCESS FULL| ZSJ_DIY_USERLOG | 1 | 29961 | 538 (28)| 22384 |00:00:00.49 | 32222 | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=5) 3 - filter("INPUTDATE"=MAX("INPUTDATE")) 5 - access("T1"."USERNAME"="T"."USERNAME") 6 - filter("T1"."USERNAME" IS NOT NULL) 7 - filter(("USERNAME" IS NOT NULL AND "TYPE"=9))

    我们可以看到这里步骤7的估算已经基本准确了,还不是太精确,是因为相关列的问题,这里不去深究下去了.至于步骤5 hash join后card的估算问题,和oracle的card估算策略有关,连接的选择性使用的是username上的density,或者说1/num_distinct=1/3387,这里num_distinct=3387是完全准确的,没有任何的问题,这里也不继续深究下去了.

    说一下这个语句的资源消耗:逻辑IO高,是因为zsj_diy_userlog的两次全表扫描,流逝时间主要消耗在了第5步的hash join和第4步的sort group by上.从其它的执行统计信息上看,CPU used by this session占据了绝大多数的流逝时间,也就是说时间主要消耗在了cpu时间上,这个也很容易理解,hash join和group by(无论是sort还是hash)都是消耗cpu的操作.hash join的结果集1700W行,而后又对这样一个大的结果集进行group by操作当然要消耗cpu时间了.

我们将这个sql语句改写一下(标记为sql2):
select t1.id, t1.itemid, (sysdate - t1.inputdate) as startprice, t1.inputdate, t1.type, t1.content, t1.userid, t1.username, t1.before_itemid, t1.back_itemid, rownum rn from (select id, itemid, inputdate, type, content, userid, t.username, before_itemid, back_itemid from zsj_diy_userlog t, (select username, max(inputdate) last_inputdate from zsj_diy_userlog where username is not null group by username) t1 where t.username is not null and t.type = 9 and t.inputdate = t1.last_inputdate and t.username = t1.username order by inputdate desc) t1 where rownum <= 5;

    它的执行计划和sql1的执行计划是完全一样的,执行统计信息也是一样的,也就是oracle将它们查询重写后认为是完全等效的实现了.

    但这个执行计划其实并不是我想要的,其实我想要的是什么呢? t1这个子查询得到结果集之后(也就是执行了group by之后)再去和zsj_diy_userlog执行连接操作,也就是说先执行group by操作,尔后执行连接,而不是这里的先执行连接后执行group by操作.添加提示就可以实现(标记为sql3)

select t1.id, t1.itemid, (sysdate - t1.inputdate) as startprice, t1.inputdate, t1.type, t1.content, t1.userid, t1.username, t1.before_itemid, t1.back_itemid, rownum rn from (select /*+ no_merge(t1)*/id, itemid, inputdate, type, content, userid, t.username, before_itemid, back_itemid from zsj_diy_userlog t, (select username, max(inputdate) last_inputdate from zsj_diy_userlog where username is not null group by username) t1 where t.username is not null and t.type = 9 and t.inputdate = t1.last_inputdate and t.username = t1.username order by inputdate desc) t1 where rownum <= 5; ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------ |* 1 | COUNT STOPKEY | | 1 | | | 5 |00:00:01.15 | 64444 | | 2 | VIEW | | 1 | 22 | 1080 (29)| 5 |00:00:01.15 | 64444 | |* 3 | SORT ORDER BY STOPKEY| | 1 | 22 | 1080 (29)| 5 |00:00:01.15 | 64444 | |* 4 | HASH JOIN | | 1 | 22 | 1079 (29)| 2825 |00:00:01.14 | 64444 | | 5 | VIEW | | 1 | 3387 | 540 (29)| 3387 |00:00:00.52 | 32222 | | 6 | HASH GROUP BY | | 1 | 3387 | 540 (29)| 3387 |00:00:00.51 | 32222 | |* 7 | TABLE ACCESS FULL| ZSJ_DIY_USERLOG | 1 | 30010 | 538 (28)| 30010 |00:00:00.43 | 32222 | |* 8 | TABLE ACCESS FULL | ZSJ_DIY_USERLOG | 1 | 29961 | 538 (28)| 22384 |00:00:00.50 | 32222 | ------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=5) 3 - filter(ROWNUM<=5) 4 - access("T"."INPUTDATE"="T1"."LAST_INPUTDATE" AND "T"."USERNAME"="T1"."USERNAME") 7 - filter("USERNAME" IS NOT NULL) 8 - filter(("T"."USERNAME" IS NOT NULL AND "T"."TYPE"=9))

    这里逻辑IO没变,因为还是两个全表扫描,但相当于前两个sql而言,执行时间大大减小了,就是因为这里是group by之后再进行hash join的.

    不知道大家有没有注意到:sql3和sql2的执行计划(sql2的执行计划也就是sql1的执行计划,我这里没有再重复贴了),sql2中没有使用提示,oracle默认选择了merge的执行计划,cost=1100,而使用提示强制执行的sql3的no_merge的执行计划的cost=1080,也就是说优化器默认选择了一个cost更高的执行计划,这是个很奇怪的问题(实际上no_merge的执行计划不仅cost更低,也确实更高效),其实这里就是我开篇提到的上次整理的时候没有注意到的一个问题,我在反复不断的研究这个问题的过程中发现,在我开启了cache统计信息并修改username上的num_distinct使得hash join的card估算也准确了之后,merge的执行计划的cost=2184,no_merge的执行计划的cost=272,这里差别就更大了,但优化器默认还是选择了执行起来低效并且估算时cost要大很多的merge的执行计划.我做了几组10053事件跟踪,发现这里存在一个问题,那就是:一开始会估算no_merge的执行计划的cost,这里没有问题,然后会估算merge的执行计划的cost,在这里比较它们的cost大小,抛弃cost大的执行计划,选择cost更低的执行计划,然后又会重新计算cost更低的执行计划这时候通常是merge的执行计划的cost,再次计算之后merge的执行计划的cost估算和前面完全不同了,一般是中间步骤的merge的执行计划的cost估算很小,而后来的merge执行计划的cost很大,当然也是我们通常在执行计划中看到的结果,不知道优化器为什么要这样做,也不知道中间步骤处计算merge的执行计划的cost的时候连接的选择性是哪里来的(连接选择率很小,导致估算的连接的card很低,对这样的小结果集进行group by操作的代价的估算也自然明显偏低,从而导致它的cost比no_merge的cost还要小,从而抛弃了no_merge的执行计划),完全没有任何的依据,而后来也就是最终我们看到的执行计划中连接的选择性是username列的1/num_distinct. 这里不去探讨这个问题了,以后有点结果的话,再贴出来.

    我们这里思考一下sql语句到底要干什么,实际上它就是要列出5个不同的非匿名用户最近的操作信息,要求他们是最近进行了操作并且他们各自最近的一次操作类型是9(查看方案)的日志记录.明白了它的意图之后,我们完全可以用分析函数来实现的(sql4):

select id, itemid, (sysdate - inputdate) as startprice, inputdate, type, content, userid, username, before_itemid, back_itemid, rownum rn from (select id, itemid, (sysdate - inputdate) as startprice, inputdate, type, content, userid, username, before_itemid, back_itemid from (select id, itemid, inputdate, type, content, userid, t.username, before_itemid, back_itemid, row_number() over(partition by username order by inputdate desc) r from zsj_diy_userlog t where t.username is not null ) where r = 1 and type = 9 order by inputdate desc ) where rownum <= 5; ---------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------------------------------------- |* 1 | COUNT STOPKEY | | 1 | | | 5 |00:00:00.71 | 32222 | | 2 | VIEW | | 1 | 30010 | 4006 (5)| 5 |00:00:00.71 | 32222 | |* 3 | SORT ORDER BY STOPKEY | | 1 | 30010 | 4006 (5)| 5 |00:00:00.71 | 32222 | |* 4 | VIEW | | 1 | 30010 | 814 (19)| 2809 |00:00:00.70 | 32222 | |* 5 | WINDOW SORT PUSHED RANK| | 1 | 30010 | 814 (19)| 30010 |00:00:00.66 | 32222 | |* 6 | TABLE ACCESS FULL | ZSJ_DIY_USERLOG | 1 | 30010 | 538 (28)| 30010 |00:00:00.46 | 32222 | ---------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=5) 3 - filter(ROWNUM<=5) 4 - filter(("R"=1 AND "TYPE"=9)) 5 - filter(ROW_NUMBER() OVER ( PARTITION BY "USERNAME" ORDER BY INTERNAL_FUNCTION("INPUTDATE") DESC )<=1) 6 - filter("T"."USERNAME" IS NOT NULL)

    这里需要说一下的是zsj_diy_userlog一共471W的数据,其实从第一个执行计划的执行统计信息我们已经可以知道username is not null的数据其实只有3W.这里使用分析函数,只全扫表一次,所以逻辑IO减半了,其实关键是这里步骤6过滤之后只有3W的数据了,对这3W的数据应用分析函数才可能执行比较快,如果对470W的数据应用分析函数的话,它的cpu消耗还是小不了的,执行时间还是不会短的.

    这里表数据一共471W,而非匿名的用户数据只有3W,这是一个很重要的信息.所以我们创建新的索引:
    create index ind1_zsj_diy_userlog on zsj_diy_userlog(nvl2(username,1,0),username,inputdate) online;
    这时因为索引添加的虚拟列nvl2(username,1,0)上连基本的列统计信息都没有,我们需要收集这个列上的频度直方图信息,让优化器知道nvl2(username,1,0)=1也就是username is not null的数据行很少:exec dbms_stats.gather_table_stats(user,'zsj_diy_userlog',cascade=>true,estimate_percent=>100,method_opt=>'for all hidden columns size 2');
    create index ind2_zsj_diy_userlog on zsj_diy_userlog(username,inputdate,type) online;
sql5:

select t1.id, t1.itemid, (sysdate - t1.inputdate) as startprice, t1.inputdate, t1.type, t1.content, t1.userid, t1.username, t1.before_itemid, t1.back_itemid, rownum rn from (select id, itemid, inputdate, type, content, userid, t.username, before_itemid, back_itemid from zsj_diy_userlog t, (select username, max(inputdate) last_inputdate from zsj_diy_userlog where nvl2(username,1,0)=1 group by username) t1 where t.type = 9 and t.inputdate = t1.last_inputdate and t.username = t1.username order by inputdate desc) t1 where rownum <= 5; ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------ |* 1 | COUNT STOPKEY | | 1 | | | 5 |00:00:00.33 | 9746 | | 2 | VIEW | | 1 | 22 | 1747 (1)| 5 |00:00:00.33 | 9746 | |* 3 | SORT ORDER BY STOPKEY | | 1 | 22 | 1747 (1)| 5 |00:00:00.33 | 9746 | | 4 | TABLE ACCESS BY INDEX ROWID| ZSJ_DIY_USERLOG | 1 | 1 | 1 (0)| 2825 |00:00:00.30 | 9746 | | 5 | NESTED LOOPS | | 1 | 22 | 1746 (1)| 6213 |00:00:00.26 | 6926 | | 6 | VIEW | | 1 | 3387 | 51 (6)| 3387 |00:00:00.15 | 133 | | 7 | HASH GROUP BY | | 1 | 3387 | 51 (6)| 3387 |00:00:00.15 | 133 | |* 8 | INDEX RANGE SCAN | IND1_ZSJ_DIY_USERLOG | 1 | 30010 | 48 (0)| 30010 |00:00:00.06 | 133 | |* 9 | INDEX RANGE SCAN | IND2_ZSJ_DIY_USERLOG | 3387 | 1 | 1 (0)| 2825 |00:00:00.07 | 6793 | ------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=5) 3 - filter(ROWNUM<=5) 8 - access("ZSJ_DIY_USERLOG"."SYS_NC00010$"=1) 9 - access("T"."USERNAME"="T1"."USERNAME" AND "T"."INPUTDATE"="T1"."LAST_INPUTDATE" AND "T"."TYPE"=9) filter("T"."USERNAME" IS NOT NULL)

    这里逻辑IO相对于原来的全表扫描是减小了些,但还是太大,大量并发的时候性能肯定是无法接受的.这里逻辑IO高是因为order by要排序的这个结果集实际上是all_rows的,它是group by的这个结果集返回的数据行都连接t表后取出整个的结果集之后再order by之后只是取前5行数据.因为只是取前5行,所以这里存在着大量不必要的调用,我们可以想象如何改成first_rows(n)的,我们完全可以利用nl连接的特性,对于外部行源返回的每一行,依次的访问内部数据行源,匹配的话就输出,把order by操作提前,使用NL特性消耗很少的资源就可以输出满足条件的前n行数据,从而避免不必要的资源浪费:
sql6:
select t1.id, t1.itemid, (sysdate - t1.inputdate) as startprice, t1.inputdate, t1.type, t1.content, t1.userid, t1.username, t1.before_itemid, t1.back_itemid, rownum rn from (select /*+ ordered use_nl(t1 t)*/id, itemid, inputdate, type, content, userid, t.username, before_itemid, back_itemid from (select username, max(inputdate) last_inputdate from zsj_diy_userlog where nvl2(username,1,0)=1 group by username order by last_inputdate desc ) t1,zsj_diy_userlog t where t.type = 9 and t.inputdate = t1.last_inputdate and t.username = t1.username ) t1 where rownum <= 5; ----------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------------------- |* 1 | COUNT STOPKEY | | 1 | | | 5 |00:00:00.11 | 154 | | 2 | TABLE ACCESS BY INDEX ROWID| ZSJ_DIY_USERLOG | 1 | 1 | 1 (0)| 5 |00:00:00.11 | 154 | | 3 | NESTED LOOPS | | 1 | 5 | 407 (1)| 11 |00:00:00.11 | 149 | | 4 | VIEW | | 1 | 787 | 13 (8)| 6 |00:00:00.11 | 133 | | 5 | SORT ORDER BY | | 1 | 3387 | 54 (12)| 6 |00:00:00.11 | 133 | | 6 | HASH GROUP BY | | 1 | 3387 | 54 (12)| 3387 |00:00:00.10 | 133 | |* 7 | INDEX RANGE SCAN | IND1_ZSJ_DIY_USERLOG | 1 | 30010 | 48 (0)| 30010 |00:00:00.03 | 133 | |* 8 | INDEX RANGE SCAN | IND2_ZSJ_DIY_USERLOG | 6 | 1 | 1 (0)| 5 |00:00:00.01 | 16 | ----------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=5) 7 - access("ZSJ_DIY_USERLOG"."SYS_NC00010$"=1) 8 - access("T"."USERNAME"="T1"."USERNAME" AND "T"."INPUTDATE"="T1"."LAST_INPUTDATE" AND "T"."TYPE"=9) filter("T"."USERNAME" IS NOT NULL)

逻辑IO降低为了154,现在的性能还算可以接受了.当然这里逻辑IO的消耗主要还是在
select username, max(inputdate) last_inputdate
  from zsj_diy_userlog
 where nvl2(username,1,0)=1
 group by username;
这里需要得到整个结果集,这里消耗了主要的逻辑IO.

 

使用程序处理的一种逻辑:
    我们可以建立这样一个索引:
create index ind3_zsj_diy_userlog on zsj_diy_userlog(nvl2(username,1,0),inputdate desc,username,type) online;
    我们试着这样去执行,我们走这个索引,找到nvl2(username,1,0)=1的索引叶节点,然后顺着这个链表去走,这样inputdate就是由大到小的,我们发现一个type=9并且username还从来都没有出现过的索引条目的时候那么它就是满足条件的,我们就通过这里的rowid回访表取得其它数据并输出显示,否则的话跳过继续往下走,无论如何,出现一个新的username就记录下来,直至找到满足要求的5条记录为止.这个用一个sql似乎实现不了,还得需要使用程序逻辑来处理,这里给出了pl/sql的一种实现:
create type t_tab_varchar2 is table of varchar2(256); create or replace procedure proc_zsj_test is v_count pls_integer default 0; v_rid rowid; v_username zsj_diy_userlog.username%type; v_type zsj_diy_userlog.type%type; cursor v_cursor is select /*+ index(t ind3_zsj_diy_userlog)*/rowid rid,username,type from zsj_diy_userlog t where nvl2(username,1,0)=1 order by nvl2(username,1,0),inputdate desc; v_exists boolean; v_tab_username t_tab_varchar2; v_rows zsj_diy_userlog%rowtype; begin v_tab_username:=t_tab_varchar2(); open v_cursor; loop fetch v_cursor into v_rid,v_username,v_type; exit when(v_cursor%notfound); v_exists:=false; for i in 1..v_tab_username.count loop if(v_username=v_tab_username(i)) then v_exists:=true; exit when(v_exists=true); end if; end loop; if(v_exists=false) then v_tab_username.extend(); v_tab_username(v_tab_username.count):=v_username; end if; if(v_type=9 and v_exists=false) then select * into v_rows from zsj_diy_userlog where rowid=v_rid; dbms_output.put_line('id:'||v_rows.id||',itemid:'||v_rows.itemid); v_count:=v_count+1; exit when(v_count>=5); end if; end loop; close v_cursor; exception when others then if(v_cursor%isopen) then close v_cursor; end if; end;

    现在逻辑IO降低为64.有时候不是说一个逻辑有多复杂,但一条sql就是不能高效的实现,它就是不能按这样的逻辑去处理,所以有时候还真不是tom说的那样:能用一条sql去实现就不要用pl/sql去实现,这里就是一个不错的例证.有时候可能还是需要通过程序逻辑才能够更为高效的实现.特别是在我们对数据,对数据的使用非常清楚的情况下,可能程序处理还是要比一条sql去实现同样的功能要高效.
    这里需要注意的一点是:10g的时候,在涉及到函数索引的排序问题时,可能存在着一些sql表现形式下不能通过这样的索引去避免排序的问题,比如这里的
    where nvl2(username,1,0)=1 order by inputdate desc; 和
    where nvl2(username,1,0)=1 order by nvl2(username,1,0),inputdate desc;
    我们知道它们是完全等价的,但10g时,前者不能通过使用ind3_zsj_diy_userlog这个索引避免排序,但后者却可以.有些网友反映说这个问题在11g中得到了解决.