关于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

发现这个表竟然没有索引

现在已将情况向领导反映，等领导要求优化的时候建立相应的索引，在看下统计信息和执行计划，由于是生产环境，未经批准，不能操作，真急人啊。