alert日誌中的一條ora警告資訊的分析

今天照例檢查資料庫alert日誌，發現一個錯誤。但是也沒在意，想可能有大的操作導致的，馬上會釋放空間的，但是轉眼一想，這是生產庫，而且現在時早上，泰國的運營商還不算忙時，需要重視這個問題，看有沒有什麼潛在的問題，

從alert日誌裡面看到的

Fri Jul 12 09:08:23 ICT 2013

ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP

 

查詢temp_usage，發現目前使用的只有goldengate的10多個session,佔用的自用很少，查詢現在的temp usage已經恢復正常了。

SQL> select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS

------------------------------- ------------ ----------- -----------

TEMP                                 1023872        7936     1015936

匯出awr報告，資料庫整體負載很小。top sql裡面看到的sql貌似都加了Hint，是被最佳化過的。

（awr報告時1小時一生成，可能有很多資訊都不準確）

沒辦法，最後查ASH，精確到那一分鐘，得到了以下的資訊，

Service	Module	% Activity	Action	% Action
XXXX01	TOAD 9.6.1.1	83.08	UNNAMED	83.08
	JDBC Thin Client	13.85	UNNAMED	13.85

 

並且發現下面的sql耗費了大量的資源，

Top SQL Statements

SQL ID	Planhash	% Activity	Event	% Event	SQL Text
	3702571469	83.08	CPU + Wait for CPU	83.08	SELECT /*+ leading (ar1_charge...
	1042878405	9.23	CPU + Wait for CPU	9.23	SELECT MT.SHORT_DESC, MO.ENTIT...
	3257149028	1.54	CPU + Wait for CPU	1.54	SELECT AR_BALANCE FROM AR1_ACC..

 

猛一看，這個sql應用了大量的hint,細細一看，是一個很有問題的sql

關聯了好幾個大表，但是沒有關聯。

SQL details:

SQL Id	SQL Text
7v8g1ffh5mwz7	SELECT /*+ leading (xxxxx1 xxxx2  xxx3) use_nl (xxxxx1 xxxx2   xxx3) index (xxxxx1 xxxx2 _ix) index (xxxx2  xxxx2 _pk) */ xxxxx1 .CHARGE_ID, xxxxx2.debit_id, xxxx2.invoice_id, xxxx1.partition_id, xxxx1.period_key, ROW_NUMBER () OVER (ORDER BY xxxx2.DEBIT_ID DESC) RN FROM xxxx1, xxxx2, xxx3 WHERE xxxx1.ACCOUNT_ID = 10000027

 

最後馬上和team裡面確認了下，是有一個人執行的。

然後為了阻止隱患，為郵件給關聯的team,對於sql的最佳化問題一點那個要最佳化轉發到dba team。

看似一個很小的問題，可能包含著錯誤的操作。