关于read by other session，db file scattered read，db file sequential read等待时间的优化（下）
5tt2k7djxub2m
select t.unionorderid as serialid , t.lotteryid as lotteryid , fn_get_lotteryname(t.lotteryid) as lotterydesc , t.lotteryissue as issue , fn_get_username(t.uuid) as username , to_char(t.addtime, 'yyyy-mm-dd hh24:mi') as createtime , case when t.schstatus=1 and t.currentvalue<t.schemevalue and t.endtime>sysdate then fn_get_schstatusdesc(0) else fn_get_schstatusdesc(t.schstatus) end as schstatusdesc , to_char(t.bonustime, 'yyyy-mm-dd hh24:mi') as bonustime , t.bonusvalue as bonusvalue , t.schemevalue as lotteryvalue , case when t.schstatus=1 and t.currentvalue<t.schemevalue and t.endtime>sysdate then 0 else t.schstatus end as schstatus , case when t.schemeissue>1 then 1 else 0 end as issueflag , t.bonusstop as bonusstop , t.schemeissue as schemeissue , t.schtype as schtype , t.schemetitle as schemetitle , t.schemmemo as schemedesc , t.viewtype as viewtypedesc , t.schembonusrate as bon usrate , t.limitvalue as limitvalue , t.schemlowmoney as schemelowvalue , t.currentvalue as buyvalue , t.bodivalue as baodivalue , case when t.saletype=-1 and t.childtype=-1 then fn_get_saletypedesc(t.lotteryid, t.saletype, t.childtype) else fn_get_childtypedesc(t.lotteryid , t.childtype)||fn_get_saletypedesc(t.lotteryid, t.saletype, t.childtype) end as saletypedesc , fn_get_passbonus(t.unionorderid) as passbonus , case when t.viewtype=0 then t.schdetail when t.viewtype=1 and t.endtime<sysdate and t.uuid!='' then t.schdetail when t.uuid='' then t.schdetail when t.viewtype=3 and fn_check_schfollow(t.unionorderid, '')=1 then t.schdetail when t.viewtype=4 and fn_check_schfollow(t.unionorderid, '')=1 and t.endtime<sysdate and t.uuid!='' then t.schdetail else 'nulls' end as schdetail , t.schnumbers as schnumbers , fn_get_lotterylevelid(t.uuid, t.lotteryid) as lotterylevel , fn_get_buyvalues(t.unionorderid, t.uuid) as schbuyvalue , to_cha r(t.endtime, 'yyyy-mm-dd hh24:mi') as endtime , lotterynumbers , istop from (select a.*, rownum r from (select a.* from tb_lotteryschemeinfo a inner join tb_lottery_info b on a.lotteryid=b.lotteryid and a.lotteryissue=b.lotteryissue where 1=1 and b.status<1 and a.schtype in (2, 3) and a.agenterid=10000001 order by a.istop desc, case when a.schstatus>2 then 5 when a.schstatus=2 then 4 when a.schstatus=1 and a.currentvalue<a.schemevalue and a.endtime>sysdate then 0 when a.schstatus=0 and a.currentvalue<a.schemevalue then 0 else 1 end, a.currentvalue/a.schemevalue desc, a.bodivalue/a.schemevalue desc, a.schemevalue desc , (a.schemevalue-a.currentvalue-a.bodivalue) desc, fn_get_lottery levelid(a.uuid, a.lotteryid) desc ) a where rownum <= 16) t where t.r > 0
首先想到的是查看算起来的执行计划，先看sql_id为0ygf63rbau963的sql
SQL> set line 1200
SQL>set autotrace traceonly explain
SQL> set timing on
SQL> select * from ( select row_.*, rownum rownum_ from ( select this_.ID as ID3_0_, this_.ART_ID as ART2_3_0_, this_.COM_CONTENT as COM3_3_0_, this_.COM_NAME as COM4_3_0_, this_.COM_TIME as COM5_3_0_, this_.TZZ_ID as TZZ6_3_0_ from ZHCWSQ.TZZ_ARTICLE_COMMENT this_ where this_.ART_ID=662 order by this_.ID desc ) row_ where rownum <=11) where rownum_ >5;
no rows selected
?
Elapsed: 00:04:23.01
?
Execution Plan
--------------------------------------------------------
| Id? | Operation????????????????????? | Name?????????????????? | Rows? | Bytes | Cost (%CPU)|
--------------------------------------------------------
|?? 0 | SELECT STATEMENT?????????????? |??????????????????????? |??? 11 | 22990 |? 9683?? (1)|
|?? 1 |? VIEW????????????????????????? |??????????????????????? |??? 11 | 22990 |? 9683?? (1)|
|?? 2 |?? COUNT STOPKEY??????????????? |? ??????????????????????|?????? |?????? |?????????? |
|?? 3 |??? VIEW??????????????????????? |??????????????????????? |??? 12 | 24924 |? 9683?? (1)|
|?? 4 |???? TABLE ACCESS BY INDEX ROWID| TZZ_ARTICLE_COMMENT??? |?? 810 |? 2023K|? 9683?? (1)|
|?? 5 |????? INDEX FULL SCAN DESCENDING| PK_TZZ_ARTICLE_COMMENT | 10860 |?????? |??? 30?? (0)|
--------------------------------------------------------
?
看到这个sql走的是索引，但是4分23秒才查完76万条记录的表，这让我想到了db file sequential read等待事件，可能是因为这张表的索引建立的不正确，查看这张表的索引信息
SQL> set autotrace off
SQL>selectINDEX_OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,COLUMN_NAME from dba_ind_columns where table_name='TZZ_ARTICLE_COMMENT';
?
INDEX_OWNER ?INDEX_NAME???? ??????????TABLE_OWNE ????TABLE_NAME???? ???????COLUMN_NAM
----------- ?---------------------- ??----------???? -----------------???? -----------
ZHCWSQ?? ????PK_TZZ_ARTICLE_COMMENT ??ZHCWSQ?? ??????TZZ_ARTICLE_COMMENT? ?ID
发现这张表只有ID字段有索引，而查询用到的where条件的字段ART_ID并没有索引，执行计划中走的是INDEX FULL。
通过观察sql_id为7pn5pxb6sdusu的sql也是这种情况。
再看下sql_id为5tt2k7djxub2m的大sql，还是看下索引情况
SQL> select * from? dba_indexes where table_name='TB_LOTTERYSCHEMEINFO';
?
no rows selected
?
SQL> select * from user_ind_columns where table_name='TB_LOTTERYSCHEMEINFO';
?
no rows selected
发现这个表竟然没有索引
现在已将情况向领导反映，等领导要求优化的时候建立相应的索引，在看下统计信息和执行计划，由于是生产环境，未经批准，不能操作，真急人啊。