今天在檢查生產庫的問題的時候，收到開發的郵件，他們在執行一個job的時候報出了ora的錯誤，想讓我們來看一下是什麼原因。
ora錯誤是01652的錯誤，單純來看是由於臨時表空間不足造成的。

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

因為問題發生在上午，從shared pool裡檢視對應的sql已經查不到了，這個時候使用ash是一個很方便的方式。
參考問題發生的時間點，抓取了一個4分鐘的ash報告。
首先看到時間基本都消耗在了兩個程式上，其中一個還是toad連線進來的session.

Service	Module	% Activity	Action	% Action
USG01	EnvelopeMT@ccbdbpr2 (TNS V1-V3)	72.81	UNNAMED	72.81
	TOAD 11.0.0.116	21.58	UNNAMED	21.58

想看看具體的session情況,可以結合等待事件來分析一下，這個時候就可以很清楚的看到那個時間段的操作了。

Sid, Serial#	% Activity	Event	% Event	User	Program	# Samples Active	XIDs
6937, 1129	27.91	db file sequential read	21.29	USG1C	(TNS V1-V3)	148/240 [ 62%]	0
		CPU + Wait for CPU	6.04			42/240 [ 18%]	0
3992,34841	21.58	direct path read	18.99	XXXXXX	Toad.exe	132/240 [ 55%]	0
		CPU + Wait for CPU	2.59			18/240 [ 8%]	0
6844,29137	10.50	db file sequential read	10.22	USG1C	(TNS V1-V3)	71/240 [ 30%]	0
384, 4043	9.93	db file sequential read	6.62	PRDUSG1C	(TNS V1-V3)	46/240 [ 19%]	23
		CPU + Wait for CPU	3.31			23/240 [ 10%]	14
7130, 5817	5.18	db file sequential read	5.04	PRDUSG1C	(TNS V1-V3)	35/240 [ 15%]	0

但是分析sql語句的時候卻沒有發現toad相關的session執行的sql語句。

SQL ID	Planhash	Sampled # of Executions	% Activity	Event	% Event	Top Row Source	% RwSrc	SQL Text
	421773076	1	27.91	db file sequential read	21.29	TABLE ACCESS - BY LOCAL INDEX ROWID	18.56	SELECT RE.L3_NET_START_TIME, R...
				CPU + Wait for CPU	6.04	SORT - ORDER BY	3.02
	2387198001	30	7.77	db file sequential read	6.76	UPDATE	6.47	UPDATE RATED_EVENT SET L3_NET_...
				CPU + Wait for CPU	1.01	UPDATE	0.86
	958688106	28	5.76	CPU + Wait for CPU	5.76	SELECT STATEMENT	3.45	/* */ SELECT CYCLE_CODE, CYCLE...
	421773076	1	5.32	db file sequential read	5.32	TABLE ACCESS - BY LOCAL INDEX ROWID	5.18	SELECT RE.L3_NET_START_TIME, R...
	421773076	1	5.04	db file sequential read	4.89	TABLE ACCESS - BY LOCAL INDEX ROWID	4.89	SELECT RE.L3_NET_START_TIME, R...

從以上問題可以簡單的分析出，資源的消耗在一個job和toad相關的session上，至於toad的程式在那個時間點在做什麼透過ash還沒有抓取到更詳細的資訊。但是可以從等待事件來看，是在做一個大查詢。

根據系統的負載最後給出了幾點建議。
首先是根據報告對那個時間點操作的客戶進行確認，是否做了一些額外的操作。
然後從目前的系統角度來看，這個庫的temp空間本身也存在著一定的不足，目前只有8G，需要做一定的擴充套件，因為庫中有幾個大表，都在百G級別，一些排序操作可能會消耗相當大的temp空間。
最後和開發確認資源消耗較多的sql語句。（)

開發給出的反饋是這個job的程式很久沒有更新了。我簡單檢查了一下最近的執行歷史做了確認。
所以問題就鎖定在兩個方面，一個是toad相關的session導致的，一個是temp空間不足造成的（一種可能甚至是toad對應的session消耗了一部分的temp空間，到了sql_id() temp空間或者說sql_id在做排序操作的時候消耗的temp空間過大）
排除了其他原因之後，再次嘗試跑就沒有問題了。對於臨時表空間的擴充套件也在稍後做了新增。

使用ash分析ORA-01652問題

相關文章