一次資料庫無法登陸的問題及排查

Enter user-name: 
ERROR:
ORA-12536: TNS:operation would block
當我再次登入資料庫伺服器的時候突然看到報了一行錯誤。提示系統資源的問題。
Last login: Mon Dec 29 12:33:55 2014 from xxxxxxx
-bash: fork: Resource temporarily unavailable

等我重新登入的時候，沒有使用tns連線的時候還是報錯。
> sqlplus n1/n1
SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 29 12:38:11 2014
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-12536: TNS:operation would block

根據以往碰到的問題情況，是session滿了，這個庫目前設定的session數支援近10000個session。連線暫時出現問題，趕緊先檢視下系統級的程式情況。
-bash-3.2$ ps -ef|wc -l
9513

-bash-3.2$ ps -ef|grep oracle|wc -l
8103

在稍等待了幾秒，再次嘗試終於連進資料庫了，我使用如下的sql語句定位了基本的問題情況。
set feed off
set verify off
set line 132
set pages 200

col username format a15
col sql_id format a20
col sql_address format a20
col machine format a30
col osuser format a15
col logon_time format a10
col program format a35
break on report
compute sum of  cnt  on report
select status,count(*) cnt from v$session group by status;
select USERNAME,OSUSER,machine, program,status,count(*) cnt from v$session group by status,USERNAME,OSUSER,machine, program  having count(*)>2 order by cnt desc;
select USERNAME,OSUSER,machine, program,status,to_char(logon_time,'yyyy-mm-dd')logon_date,count(*) cnt from v$session group by status,USERNAME,OSUSER,machine, program,to_char(logon_time,'yyyy-mm-dd') order by username,osuser,cnt desc;

語句執行的結果如下，結果做了一些修改。
STATUS          CNT
-------- ----------
ACTIVE           90
INACTIVE       8985
         ----------
sum            9075

USERNAME        OSUSER          MACHINE                        PROGRAM                             STATUS          CNT
--------------- --------------- ------------------------------ ----------------------------------- -------- ----------
 DAPPC           mwrk01        client1                       JDBC Thin Client                    INACTIVE       6215
 DAPPC           rwrk01        client1                       JDBC Thin Client                    INACTIVE        126
 CCCBSCUST01     pggate        client3                       extract@xxxxxxxx (TNS V1-V3)        INACTIVE         90
 DAPPC           uwl45         client4                       JDBC Thin Client                    INACTIVE         84
 DAPPC           uwl15         client1                       JDBC Thin Client                    INACTIVE         83
                                                                                                            ----------
sum                                                                                                               6598

COUNT(*) OSUSER                            PREV_HASH_VALUE      PREV_SQL_ID
---------- --------------- -------------------- --------------- -------------
      1326 mwrk01                        201716277               dqaf35060bwjp
      1972 arwrk01                        2096946154              f41ncsxygtqza
       534 mwrk01                         3203606695              dm03006zg6a57

可以看到在mwrk01這個使用者上已經有好幾千個session執行著同樣的sql語句。檢視這些session的登入時間還是能發現一些潛在的問題。
USERNAME        OSUSER          MACHINE                        PROGRAM                             STATUS   LOGON_DATE        CNT
--------------- --------------- ------------------------------ ----------------------------------- -------- ---------- ----------
 DAPPC           mwrk01        client1                       JDBC Thin Client                    INACTIVE 2014-12-29       1206
 DAPPC           mwrk01        client1                       JDBC Thin Client                    INACTIVE 2014-12-27        990
 DAPPC           mwrk01        client1                       JDBC Thin Client                    INACTIVE 2014-12-28        664
 DAPPC           mwrk01        client1                       JDBC Thin Client                    INACTIVE 2014-12-26        498
 DAPPC           mwrk01        client1                       JDBC Thin Client                    INACTIVE 2014-12-24        168
 DAPPC           mwrk01        client1                       JDBC Thin Client                    INACTIVE 2014-12-22         99
 DAPPC           mwrk01        client1                       JDBC Thin Client                    INACTIVE 2014-12-23         38

對應的sql語句都是同一個Insert操作。

這是一個每天都需要執行的job，但是根據開發的反饋這些job執行完就會停掉。
從上面的情況來看似乎沒有按照預期的方式來執行。
這種問題按照以往的思路都是已經基本定論，配合開發來做進一步的排查了。發現很多問題再深入一點，還是會有一些收穫，對於這個問題開發主動找到我，我們大概聊了下，他們反饋說這個job執行的頻率並不高。每天一次
，他們也很納悶為什麼還存在著幾天前的session,問題又迴歸到我這了，不過也是可以理解，我和他們解釋說，如果一個job從客戶端斷開後，是會被資料庫的後臺程式清理掉的，如果一直沒有釋放session就很可能是一直存在著
未完成的事務，從這個思路來考慮，有大量的session都在執行同樣的insert操作，從業務上講也是存在問題的，他們解釋說根據新的業務處理，每處理一個外部檔案，都會有一個單獨的session在處理。
我就追問那是都處理完成之後是等待都處理完了再commit還是每處理一個就commit,他們就有些支支吾吾了,說在這塊沒做過變化，都是處理完成再提交。
這樣問題就比較明朗了。我建議他們再確認一下事務結束的處理，以前是一個session處理多個檔案，都是每處理一個檔案commit一次，最後考慮到效能是在處理完成後再commit,這次的變更使用了多個session處理，
把事務的處理部分再做變更，很可能就忽略了那個部分。如果是那種情況的話，很可能就會導致大量的session佔用。最後他們反饋說這個地方確實存在著一定的問題，問題的處理就進入開發修復的階段了。