1、今天早上(11 月 12 号),发现计划系统 ORACLE 很繁忙,通过 QLISE 工具找到了消耗系统CPU 资源最厉害的 ORACLE 线程号(8ec),再通过系统的数据字典(V$SESSION,V$SQL,V$PROCESS),找出了正在系统中运行的 SQL 语句.SELECT inid, sender, imei, materialcode, lot, phonemodelcode, colorcode, jobid,dealflag, remarkFROM tcs.vw_phoneiosubdown_for_ml_v25WHERE checkdate = TO_DATE (TO_CH
2、AR (:v1, yyyy/mm/dd), yyyy/mm/dd)- 1AND nodeid = :v2其中视图 tcs.vw_phoneiosubdown_for_ml_v25 的内容为:select T_Exchange.Id as InId,T_Exchange.CompanyCode as NodeId,T_ExchangeOutImei.IMEI as IMEI,T_ExchangeOutImei.MATERIALCODE as MaterialCode,T_ExchangeOutImei.Lot as LOT,T_ExchangeOutImei.ModelCode as Phone
3、ModelCode,T_ExchangeOutImei.Color as ColorCode,T_Exchange.Checkdate as CheckDate, as JobId,0 as Sender,null as DealFlag, as Remarkfrom T_Exchange,T_ExchangeOutImei where T_ExchangeOutImei.TableId=T_Exchange.Id and T_Exchange.ReceiveFlag=Y and T_Exchange.billTypeCode like ZZJ% union- select T_Exchang
4、e.Id as InId,T_Exchange.CompanyCode as NodeId,T_ExchangeOutImei.IMEI as IMEI,T_ExchangeOutImei.MATERIALCODE as MaterialCode,T_ExchangeOutImei.Lot as LOT,T_ExchangeOutImei.ModelCode as PhoneModelCode,T_ExchangeOutImei.Color as ColorCode,T_Exchange.Checkdate as CheckDate, as JobId,0 as Sender,null as
5、DealFlag, as Remarkfrom T_Exchange,T_ExchangeOutImei where T_ExchangeOutImei.TableId=T_Exchange.Id and T_Exchange.ReceiveFlag=Y and T_Exchange.billTypeCode like ZZMK% union -(StockIOType=N)select JobId2Number(T_PhoneIOSub.IONo) as InId,T_PhoneIOMain.Receiver as NodeId,T_PhoneIOSub.IMEI as IMEI,T_Pho
6、neIOSub.MaterialCode as MaterialCode,T_PhoneIOSub.LOT as LOT,T_PhoneIOSub.PhoneModelCode as PhoneModelCode,T_PhoneIOSub.ColorCode as ColorCode,T_PhoneIOMain.LastModified as CheckDate,T_PhoneAccept.Originaljobid as JobId,T_PhoneIOMain.NodeId as Sender,T_PhoneIOSub.Dealflag as DealFlag, as Remarkfrom
7、T_PhoneIOSub,T_PhoneIOMain,T_PhoneAcceptwhere T_PhoneIOSub.IONo=T_PhoneIOMain.IONo and T_PhoneIOSub.Jobid=T_PhoneAccept.JobId(+) andDeposeFlag=N and StockIOType=N and T_PhoneIOMain.NodeIDT_PhoneIOMain.ReceiverUNION-select JobId2Number(T_PhoneIOSub.IONo) as InId,T_PhoneIOMain.Receiver as NodeId,T_Pho
8、neIOSub.IMEI as IMEI,T_PhoneIOSub.MaterialCode as MaterialCode,T_PhoneIOSub.LOT as LOT,T_PhoneIOSub.PhoneModelCode as PhoneModelCode,T_PhoneIOSub.ColorCode as ColorCode,T_PhoneIOMain.LastModified as CheckDate, as JobId,T_PhoneIOMain.NodeId as Sender,T_PhoneIOSub.Dealflag as DealFlag,T_PhoneIOSub.Rem
9、arkfrom T_PhoneIOSub,T_PhoneIOMain,T_DamageAfterArrivewhere T_PhoneIOSub.IONo = T_PhoneIOMain.IONo and T_PhoneIOMain.DeposeFlag=N and T_PhoneIOMain.StockIOType=O and (T_PhoneIOMain.StockType=B) and T_PhoneIOMain.NodeID =TO_DATE (TO_CHAR (:v1, yyyy/mm/dd), yyyy/mm/dd) 1”于是为 checkdate 创建索引:create inde
10、x tcs.T_PhoneIOMain_checkdate on tcs.T_PhoneIOMain(lastmodified) tablespace tcs重新分析其执行计划,如下图此时 tcs.T_PhoneIOMain 已经走索引检索,COST=403,提高近 10 倍.再次分析执行计划, logis.t_exchange 仍然走全表扫描,且有近 7 万条数据,再次为此表的 CHECKDATE 创建索引:create index LOGIS.INDEX_CHECKDATE on LOGIS.T_EXCHANGE (CHECKDATE)tablespace LOGIS再次分析执行计划如下, logis.t_exchange 已经走索引检索 ,COST=103,再次提高近4 倍 .至此,执行成本从 3993 降到 103,提高近 40 倍.系统 CPU 使用率急剧下跌.
