你的位置:首页 > 数据库

[数据库]ORACLE SQL调优案例一则


收到监控告警日志文件(Alert)的作业发出的告警邮件,表空间TEMPSCM2不能扩展临时段,说明临时表空间已经被用完了,TEMPSCM2表空间不够用了

Dear All:
 
  The Instance SCM2' alert log occured the ora errors ,please see the detail blow and take action for it. many thanks!

------------------------------------------- The errors is blow ------------------------------------------------------


193 |  | ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMPSCM2 
198 |  | ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMPSCM2 
200 |  | ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMPSCM2 
205 |  | ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMPSCM2 

--------------------------------------------end of errors-----------------------------------------------------------


Oracle Alert Services

同事在分析处理时,定位到临时表空间是被一个问题SQL语句给耗尽了。

SELECT B.TABLESPACE, B.SEGFILE#, B.SEGBLK#, B.BLOCKS, A.SID, A.SERIAL#, A.USERNAME, A.OSUSER, A.STATUS
FROM v$session A, v$tempseg_usage B, v$sqlarea C
WHERE A.saddr = B.session_addr
AND C.address= A.sql_address
AND C.hash_value = A.sql_hash_value
ORDER BY B.tablespace, B.blocks;


clip_image001

WORKLOAD REPOSITORY SQL Report显示,单个该SQL的HASH GROUP BY操作就要耗用临时表空间229M,他给的建议是不扩展TEMPSCM2表空间,而是去优化这个SQL语句,因为大部分时候,该数据库的临时表空间使用率是非常低。我也同意他的分析结果。

clip_image002

从该SQL语句的执行计划,就能看出这个SQL语句有问题,例如SC_LOT、PO_HD全表扫描只是为了获取一小部分数据。

clip_image003

SELECT 'CEG'             AS FTY_CD, 
       2015              AS PD_Year, 
       2                 AS PD_Month, 
       a.po_no, 
       SUM(a.total_qty)  AS Order_Qty, 
       SUM(c.total_qty)  GO_QTY, 
       b.buyer_po_del_date, 
       b.status, 
       c.sam_group_cd, 
       c.style_chn_desc, 
       Max(e.short_name) AS CUSTOMER 
FROM   sc_lot a, 
       po_hd b, 
       sc_hd c, 
       gen_customer e, 
       (SELECT ct_no AS Job_order_no 
        FROM   mars_upload_temp 
        WHERE  fty_cd = 'CEG' 
               AND pd_yr = 2015 
               AND pd_mth = 2 
               AND date_type = '20150529 10:00:23881698737881698737') d 
WHERE  Upper(a.po_no) = Upper(b.po_no) 
       AND b.sc_no = c.sc_no 
       AND Upper(a.po_no) = Upper(d.job_order_no) 
       AND c.customer_cd = e.customer_cd(+) 
GROUP  BY b.buyer_po_del_date, 
          b.status, 
          c.sam_group_cd, 
          c.style_chn_desc, 
          a.sc_no, 
          a.po_no


了解了该语句的业务逻辑并和开发人员沟通后,发现WHERE语句的条件Upper函数根本没有必要,取消Upper函数后PO_HD、GEN_CUSTOMER表走索引扫描了

SELECT 'CEG'             AS FTY_CD, 
       2015              AS PD_Year, 
       2                 AS PD_Month, 
       a.po_no, 
       SUM(a.total_qty)  AS Order_Qty, 
       SUM(c.total_qty)  GO_QTY, 
       b.buyer_po_del_date, 
       b.status, 
       c.sam_group_cd, 
       c.style_chn_desc, 
       Max(e.short_name) AS CUSTOMER 
FROM   sc_lot a, 
       po_hd b, 
       sc_hd c, 
       gen_customer e, 
       (SELECT ct_no AS Job_order_no 
        FROM   mars_upload_temp 
        WHERE  fty_cd = 'CEG' 
               AND pd_yr = 2015 
               AND pd_mth = 2 
               AND date_type = '20150529 10:00:23881698737881698737') d 
WHERE  a.po_no= b.po_no
       AND b.sc_no = c.sc_no 
       AND a.po_no= d.job_order_no 
       AND c.customer_cd = e.customer_cd(+) 
GROUP  BY b.buyer_po_del_date, 
          b.status, 
          c.sam_group_cd, 
          c.style_chn_desc, 
          a.sc_no, 
          a.po_no


clip_image004

但是SC_LOT表还是走全表扫描,经过分析发现SC_LOT表的PO_NO列的区分度非常大,应该可以通过建立索引优化。如下所示,建立索引后,SC_LOT不走全表扫描了。

执行计划的代价(Cost)也从7014降为了254. 优化的效果非常显著(Cardinality变得非常大,是因为表MARS_UPLOAD_TEMP数据在我测试阶段发生了变化)

clip_image005