收到監控告警日志文件（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;
WORKLOAD REPOSITORY SQL Report顯示，單個該SQL的HASH GROUP BY操作就要耗用臨時表空間229M，他給的建議是不擴展TEMPSCM2表空間，而是去優化這個SQL語句，因為大部分時候，該數據庫的臨時表空間使用率是非常低。我也同意他的分析結果。
從該SQL語句的執行計劃，就能看出這個SQL語句有問題，例如SC_LOT、PO_HD全表掃描只是為了獲取一小部分數據。
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
但是SC_LOT表還是走全表掃描，經過分析發現SC_LOT表的PO_NO列的區分度非常大，應該可以通過建立索引優化。如下所示，建立索引後，SC_LOT不走全表掃描了。
執行計劃的代價（Cost）也從7014降為了254. 優化的效果非常顯著（Cardinality變得非常大，是因為表MARS_UPLOAD_TEMP數據在我測試階段發生了變化）
