select sql_text from v$sqltext a where a.hash_value = (select sql_hash_value from v$session b where b.sid = '&sid')order by piece asc/
sql> @getsqlenter value for sid: 18old 5: where b.sid='&sid'new 5: where b.sid='18'sql_text----------------------------------------------------------------select i.vc2title,i.numinfoguid from hs_info i where i.intenabledflag = 1 and i.intpublishstate = 1 and i.datpublishdate <=sysdate and i.numcatalogguid = 2047 order by i.datpublishdate desc, i.numorder descsql> /enter value for sid: 54old 5: where b.sid='&sid'new 5: where b.sid='54'sql_text----------------------------------------------------------------select i.vc2title,i.numinfoguid from hs_info i where i.intenabledflag = 1 and i.intpublishstate = 1 and i.datpublishdate <=sysdate and i.numcatalogguid = 33 order by i.datpublishdate desc, i.numorder descsql> /enter value for sid: 49old 5: where b.sid='&sid'new 5: where b.sid='49'sql_text----------------------------------------------------------------select i.vc2title,i.numinfoguid from hs_info i where i.intenabledflag = 1 and i.intpublishstate = 1 and i.datpublishdate <=sysdate and i.numcatalogguid = 26 order by i.datpublishdate desc, i.numorder desc
sql> set autotrace trace explainsql> select i.vc2title,i.numinfoguid 2 from hs_info i where i.intenabledflag = 1 3 and i.intpublishstate = 1 and i.datpublishdate <=sysdate 4 and i.numcatalogguid = 3475 5 order by i.datpublishdate desc, i.numorder desc ;execution plan---------------------------------------------------------- 0 select statement optimizer=choose (cost=228 card=1 bytes=106) 1 0 sort (order by) (cost=228 card=1 bytes=106) 2 1 table access (full) of 'hs_info' (cost=218 card=1 bytes=106)sql> select count(*) from hs_info; count(*)---------- 227404
该表这里有22万记录,全表扫描已经不再适合.
检查该表,存在以下索引:
sql> select index_name,index_type from user_indexes where table_name='hs_info';index_name index_type------------------------------ ---------------------------hsidx_info1 function-based normalhsidx_info_searchkey domainpk_hs_info normal检查索引键值:sql> select index_name,column_name from user_ind_columns where table_name ='hs_info';index_name column_name------------------------------ --------------------hsidx_info1 numorderhsidx_info1 sys_nc00024$hsidx_info_searchkey vc2indexwordspk_hs_info numinfoguidsql> desc hs_info name null? type ----------------------------------------------------------------- -------- --------------------------- numinfoguid not null number(15) numcatalogguid not null number(15) inttexttype not null number(38) vc2title not null varchar2(60) vc2author varchar2(100) numprevinfoguid number(15) numnextinfoguid number(15) numorder not null number(15) datpublishdate not null date intpublishstate not null number(38) vc2publisherid varchar2(30) vc2indexwords varchar2(200) vc2wapprevpath varchar2(200) vc2webprevpath varchar2(200) vc2wap2prevpath varchar2(200) numvisited not null number(15) intenabledflag not null number(38) datcreatetime not null date datmodifytime not null date vc2notes varchar2(1000) intinfotype not null number(38) vc2prizeflag varchar2(1) vc2desc varchar2(1000)
6.决定创建新的索引以消除全表扫描
sql> create index hs_info_numcatalogguid on hs_info(numcatalogguid);index created.sql> set autotrace trace explainsql> select i.vc2title,i.numinfoguid 2 from hs_info i where i.intenabledflag = 1 3 and i.intpublishstate = 1 and i.datpublishdate <=sysdate 4 and i.numcatalogguid = 3475 5 order by i.datpublishdate desc, i.numorder desc ;execution plan---------------------------------------------------------- 0 select statement optimizer=choose (cost=12 card=1 bytes=106) 1 0 sort (order by) (cost=12 card=1 bytes=106) 2 1 table access (by index rowid) of 'hs_info' (cost=2 card=1 bytes=106) 3 2 index (range scan) of 'hs_info_numcatalogguid' (non-unique) (cost=1 card=1)