生产SQL优化

今天某开发同事说生产系统打开表单时很慢很慢，特意向我请教！

因为他那边生产系统很严格，所以只能在他的仿真系统上搞！

首先让他打开他说响应很慢的那个界面，然后运行以下语句：

select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT
  from v$session a, v$sqlarea b
where a.sql_address = b.address ;

ok，捉到SQL语句了！

select workitemhi0_.DBID as DBID22_,
       workitemhi0_.ID_FLOWPROCESS as ID2_22_,
       workitemhi0_.ID_ROOTFLOWPROCESS as ID3_22_,
       workitemhi0_.ID_EXECUTION as ID4_22_,
       workitemhi0_.ID_TASK as ID5_22_,
       workitemhi0_.ID_SOURCETASK as ID6_22_,
       workitemhi0_.ID_SOURCEWORKITEM as ID7_22_,
       workitemhi0_.FROMACTIVITYID as FROMACTI8_22_,
       workitemhi0_.FROMACTIVITYNAME as FROMACTI9_22_,
       workitemhi0_.ACTIVITYID as ACTIVITYID22_,
       workitemhi0_.ACTIVITYNAME as ACTIVIT11_22_,
       workitemhi0_.STATEINT as STATEINT22_,
       workitemhi0_.SEQUENCENUM as SEQUENC13_22_,
       workitemhi0_.CREATEDTIME as CREATED14_22_,
       workitemhi0_.STARTEDTIME as STARTED15_22_,
       workitemhi0_.VISITEDTIME as VISITED16_22_,
       workitemhi0_.ENDEDTIME as ENDEDTIME22_,
       workitemhi0_.USERID as USERID22_,
       workitemhi0_.USERNAME as USERNAME22_,
       workitemhi0_.ORGANCOMBINEDCODE as ORGANCO20_22_,
       workitemhi0_.ORGANFULLNAME as ORGANFU21_22_,
       workitemhi0_.MAPPINGUNITFULLNAME as MAPPING22_22_,
       workitemhi0_.AGENTID as AGENTID22_,
       workitemhi0_.AGENTNAME as AGENTNAME22_,
       workitemhi0_.AGENTORGANCOMBINEDCODE as AGENTOR25_22_,
       workitemhi0_.AGENTORGANFULLNAME as AGENTOR26_22_,
       workitemhi0_.SYSID as SYSID22_,
       workitemhi0_.SYSNAME as SYSNAME22_,
       workitemhi0_.URLEVELINT as URLEVELINT22_,
       workitemhi0_.TODOLABEL as TODOLABEL22_,
       workitemhi0_.TODOTIP as TODOTIP22_,
       workitemhi0_.SENDERID as SENDERID22_,
       workitemhi0_.SENDERNAME as SENDERNAME22_,
       workitemhi0_.SENDERORGANCOMBINEDCODE as SENDERO34_22_,
       workitemhi0_.SENDERORGANFULLNAME as SENDERO35_22_,
       workitemhi0_.SENDAGENTID as SENDAGE36_22_,
       workitemhi0_.SENDAGENTNAME as SENDAGE37_22_,
       workitemhi0_.SENDAGENTORGANCOMBINEDCODE as SENDAGE38_22_,
       workitemhi0_.SENDAGENTORGANFULLNAME as SENDAGE39_22_,
       workitemhi0_.SENDOPTIONID as SENDOPT40_22_,
       workitemhi0_.DELPRETODOINT as DELPRET41_22_,
       workitemhi0_.KEEPTODO as KEEPTODO22_,
       workitemhi0_.SENDEMINT as SENDEMINT22_,
       workitemhi0_.SENDIMINT as SENDIMINT22_,
       workitemhi0_.SENDSMINT as SENDSMINT22_,
       workitemhi0_.SENDPMINT as SENDPMINT22_,
       workitemhi0_.STEPID as STEPID22_,
       workitemhi0_.STEPNAME as STEPNAME22_,
       workitemhi0_.FIRSTVISITTIME as FIRSTVI49_22_
  from T_WORKITEM_HIST workitemhi0_
 where workitemhi0_.ID_ROOTFLOWPROCESS = :1
   and workitemhi0_.STATEINT = :2
   and (workitemhi0_.USERID in ('U=1653459/O=03020001'))
 order by workitemhi0_.CREATEDTIME

然后，用PL/SQL看了一下他的执行计划，原来T_WORKITEM_HIST是走的全表扫描！

再看看这个表的条数：有385W左右，尼玛，坑爹的，生产至少是这个数的1000（业务决定的），所以可想而知全表扫描的后果了！

接下来，我们优化这个SQL，很简单的， 单表的优化，无非就是没有正确的索引！

仔细一看，建立这个样的组合索引

create index idx_No0629 on T_WORKITEM_HIST(ID_ROOTFLOWPROCESS,STATEINT,USERID)

然后再看看执行计划，秒出！

然后在生产中建立此索引，速度至少比之前快了上百倍！