以往在蘇州作資料庫調優的實戰案例

一 . Xymon 監控值的修改

10/24 10:10 將SZ1AD005  把Xymon server moniton ,

VMIO( SZ1AD005,/home/xymon/client/ext/bb-iostat.sh) 修改如下           

主機	SWAP之前監控值		SR之前監控值		SWAP現在監控值		SR現在監控值
	Yellow	Red	Yellow	Red	Yellow	Red	Yellow	Red
SZ1AD005	512000	2048000	25	250	1782579	1887436.8	9912.32	19824.64
SZ1AD006	512000	2048000	25	250	2097152	2936012.8	9912.32	19824.64

Process  (Xymon server 10.7.96.16 /export/home/xymon/server/etc/hobbit-clients.cfg)

主機	Process	之前監控值		現在監控值
		Yellow	Red	Yellow	Red
SZ1AD005	oracleszmespd5	130	-	160	-

CPU Loading (Xymon server 10.7.96.16 /export/home/xymon/server/etc/hobbit-clients.cfg)

主機	之前監控值		現行監控值
	Yellow	Red	Yellow	Red
SZ1AD005	5	10	7	10

以下修改完監控值後針對SZ1AD005 作出的修改

二 . 針對 SZMESPD5 sql 調優

CASE1 :10/24 下午已將修正後的SQL語法更新至 MES系統

修改前SQL 語法

select decode(EQUIPMENTNO,'Null','','NULL','','null','',EQUIPMENTNO) TESTERID  ,decode(PROBECARDNO,'Null','','NULL','','null','',PROBECARDNO) PROBECARD  ,decode(PROGRAMNAME,'Null','','NULL','','null','',PROGRAMNAME)PROGRAMNAME,SERIALNO,'' Temperature  ,TBLWIPLOTSTATE.LOTSERIAL   from TBLWIPCHECKINPROGRAM,TBLWIPLOTSTATE  where TBLWIPCHECKINPROGRAM.lotno=TBLWIPLOTSTATE.LOTNO and effectflag='1'  and TBLWIPCHECKINPROGRAM.lotno='MNK1A228A0'  and OPNO='CP2'  union ALL  select distinct decode(TBLWIPPROGRAMCONTENT.EQUIPMENTNO,'Null','','NULL','','null','',TBLWIPPROGRAMCONTENT.EQUIPMENTNO) TESTERID  ,decode(TBLWIPPROGRAMCONTENT.PROBECARDNO,'Null','','NULL','','null','',TBLWIPPROGRAMCONTENT.PROBECARDNO) PROBECARDNO  , decode(PROGRAMNAME,'Null','','NULL','','null','',PROGRAMNAME) PROGRAMNAME,SERIALNO,'' Temperature,TBLWIPLOTLOG.LOTSERIAL   from TBLWIPPROGRAMCONTENT, TBLWIPLOTLOG  where TBLWIPPROGRAMCONTENT.LOTSERIAL=TBLWIPLOTLOG.LOTSERIAL and TBLWIPPROGRAMCONTENT.lotno='MNK1A228A0'  and TBLWIPLOTLOG.OPNO='CP2' order by LOTSERIAL DESC,SERIALNO desc

建議修改的SQL 語法

select decode(EQUIPMENTNO,'Null','','NULL','','null','',EQUIPMENTNO) TESTERID  ,decode(PROBECARDNO,'Null','','NULL','','null','',PROBECARDNO) PROBECARD  ,decode(PROGRAMNAME,'Null','','NULL','','null','',PROGRAMNAME)PROGRAMNAME,SERIALNO,'' Temperature  ,TBLWIPLOTSTATE.LOTSERIAL   from TBLWIPCHECKINPROGRAM,TBLWIPLOTSTATE  where TBLWIPCHECKINPROGRAM.lotno=TBLWIPLOTSTATE.LOTNO and effectflag='1'  and TBLWIPCHECKINPROGRAM.lotno='MNK1A228A0'  and OPNO='CP2'  union ALL select   distinct decode(TBLWIPPROGRAMCONTENT.EQUIPMENTNO,'Null','','NULL','','null','',TBLWIPPROGRAMCONTENT.EQUIPMENTNO) TESTERID  ,decode(TBLWIPPROGRAMCONTENT.PROBECARDNO,'Null','','NULL','','null','',TBLWIPPROGRAMCONTENT.PROBECARDNO) PROBECARDNO  , decode(PROGRAMNAME,'Null','','NULL','','null','',PROGRAMNAME) PROGRAMNAME  ,SERIALNO  ,'' Temperature  ,TBLWIPPROGRAMCONTENT.LOTSERIAL   from TBLWIPPROGRAMCONTENT  where LOTSERIAL in  ( select lotserial from TBLWIPLOTLOG    where LOTNO='MNK1A228A0' and OPNO= 'CP2'  )  order by LOTSERIAL DESC,SERIALNO desc

CASE2: 10/24 16:04已完成對tblnvtsmswafer (ERF_NO,RUNCARD)建index

造成影響的SQL

DELETE FROM   mcpmes.tblnvtsmswafer WHERE  erf_no = 'MDTB801803' AND    deal_count = 1 AND    wfr_no = 5; 在檢視tblnvtsmswafer  後發現此表沒有建index  , 此表 有42644 筆記錄

解決方案: 建index 解決

create index idx_blnvtsmswafer_erfno_runca ON TBLNVTSMSWAFER (ERF_NO,RUNCARD)

CASE3: 10/26 上午已由MES更新至程式中

修改前SQL 語法

Select * From (Select B.MainStatusNo as MainStatusNo1,A.Location,A.X,A.Y,A.EqpWidth,A.EqpHeight ,Substr('00'||B.MainStatusNo,-2) As MainStatusNo,   PKGEAMS.GetCorrTimeByEquipmentNo(A.Location) As CorrTime ,   (Select Max(ReviseDate)  From tblWIPEquipmentCorr Where   EquipmentNo=A.Location ) As CorrDate, C.EquipmentCategory, D.LogicalDescrip From TblWipEAMSLocation A,TblMstEquipment B, TblMstEquipmentType C, TblSYSParameterDetail D   Where A.Location=B.EquipmentNo And B.EquipmentType = C.EquipmentType And b.MainStatusNo = D.PhysicalValue   And B.Location='V3K' And A.LocationType=1 And B.State<>0 And D.SysParaNo = 'EquipStatus'   )

建議修改的SQL 語法

由於PKGEAMS.GetCorrTimeByEquipmentNo(A.Location) 返回是空值且在執行時效率不佳，改成null 值取代.

CASE4 由於在CASE3 將PKGEAMS 修改為空值後,發現以下sql 執行次數大幅度降低.

修改前SQL 語法

SELECT MAX( lotno ) FROM   tblwipequipstatuslotlog WHERE  equipmentno=:b1 AND    equipmentserial=(         SELECT MAX( TO_NUMBER( equipmentserial ) )         FROM   tblwipequipstatuslotlog         WHERE  equipmentno=:b1         AND    lotno IS NOT NULL        )

建議修改的SQL 語法

暫不作更動

CASE5
10/27 12:15 待MES 找出此段sql 在MES所在的位置去作更新

修改前SQL 語法

SELECT *   FROM (SELECT *           FROM tblwipequipstatuslotlog          Where EquipmentNo = 'ND-03'          ORDER BY TO_NUMBER(equipmentserial) DESC)  WHERE ROWNUM = 1;

建議修改的SQL 語法

SELECT * FROM tblwipequipstatuslotlog   WHERE equipmentno = 'ST-04'      and equipmentserial = (select max(TO_NUMBER(equipmentserial))                                from tblwipequipstatuslotlog                               Where EquipmentNo = 'ST-04');

CASE6  幾個sql 需要要綁定變量

修改前SQL 語法

SELECT *  FROM (           SELECT *           FROM tblwipequipasslog           WHERE equipmentno = 'E320-01'                 <-- span="">未綁定變量  會作hard parse           ORDER BY TO_NUMBER( equipmentserial ) DESC        ) WHERE  ROWNUM = 1

建議方案

1.針對tblwipequipasslog (tequipmentno ,equipmentserial) 作index . 2.改成綁定變量 SELECT *  FROM (           SELECT *           FROM tblwipequipasslog           WHERE equipmentno =:b3 <--- span="">改成綁定變量 減少硬解析           ORDER BY TO_NUMBER( equipmentserial ) DESC        ) WHERE  ROWNUM = 1

總結: 自 10/24 日後相關修改的SQL 有2件,1件新增index  ,整體cpu使用率有顯著下降

由上圖區間為2011/09/23 ~2011/10/01 平均CPU負載在4.6, 最大.CPU使用量在18.7

上圖區間為2011/10/24 ~ 2011/11/5, 平均CPU負載在31 最大.CPU使用量在23.6
由於是10/24開始針對高耗費CPU資源的SQL作調整，最大值是在調整前產生。

上圖區間為2011/10/31 ~ 2011/11/9 , 平均CPU負載在3.2 最大.CPU使用量在14

VMIO 部份這是修改xymon 後那一星期有稍微減少報警次數，VMIO 次數仍是偏高

Process 在調整SQL完後有明顯下降　（如下圖所示）