[i=s] 本帖最後由 wei-xh 於 2013-7-3 17:55 編輯

今天朋友遭遇瞭如下的問題，遠端聯絡我解決
SQL> select * from xxx ;
select * from xxx
ERROR at line 1:
ORA-00018: maximum number of sessions exceeded

相信只要是幹過一兩年DBA的對與這個錯誤ORA-00018是比較熟悉的，也知道問題的解決辦法，那就是增加processes的引數設定，由於sessions是依據processes推匯出來的，因此processes的增大，會導致sessions也會增大，也就解決了這個問題。具體推導的公式，可能9I,10G版本基本遵循的是sessions=(1.1*processes)+5，11G以後（可能是11GR2之後，抱歉我沒11GR1的版本來做測試），不是這樣了，在我11GR2的環境下，3000 processes的設定，已經有4560個sessions,大大的增加了session的數量。

這個錯大部分情況下，都是發生在建立連線的時候，由於超出了sessions的設定值，導致連線拋錯。但是。。。
但是我朋友的這個案例是，他已經連線了！！！！
其實這個問題不難回答，因為只要看過TOM書的同學都知道，一個連線，可能會導致多個session。
舉個例子：
比如在你資料庫剛啟動的時候、或者flush shared pool之後，或者你查詢的表的資料字典資訊已經不在共享池裡
你建立連線後，ORACLE分配給你一個session，我們稱之為session1
然後你select * from xxx的時候
Oracle會獨立新開一個session，我們稱之為session2 ,這個session2會去遞迴的查詢資料字典（硬解析的需要），比如tab$,col$,seg$以及一些統計資訊的資料字典基表
等它做完這些工作後，就立即消失了。

當然除了查詢，還會很多操作會產生這種行為，比如create,drop，alter等等，由於這些操作都修改了資料字典基表，因此他們也會產生一個遞迴的session的來去幫他們完成這些資料字典的操作，而且你還會“驚奇”的發現，這些遞迴session的使用者竟然不是你當時連線的使用者。
比如你用test/test做的連線，但是遞迴語句的session的使用者是sys!!!!

說到這裡，你可能覺得，你大概知道了這裡面的奧秘，那就是，我朋友當時發出這個查詢的時候，連線session的數量剛好等於了允許存在session的最大數量，因此導致遞迴的session無法建立而報錯。
在確認這個問題前，還有些問題要解決。

是不是查詢都要遞迴的產生session ?
不是的，只有需要的資料字典資訊不在資料字典cache裡的時候。
當時那個環境下，查詢其他常用的表會不會報錯？
經過我的測試，當時查詢一些常用的表，是不報錯的！！！因為它的資料字典資訊都是在資料字典cache裡的。

好，上面的疑問解決後，我們來看下，當時session的數量，和設定的最大值分別是多少
select count(*) from v$session;
  COUNT(*)
----------
   216

show parameter sessions

NAME                               TYPE                VALUE
------------------------------------ ---------------------- ------------------------------                                           sessions                            integer             232

奇怪的事又發生了！
當前session的數量還沒達到設定的最大值，如果是要建立遞迴的session，那僅僅需要一個session就夠了。而他的環境裡，還剩餘232-216=16個session可以用。
問題出哪了呢？
我的猜測應該是v$session這個檢視，對於遞迴的session做了過濾。
經過查詢V$FIXED_VIEW_DEFINITION，我們可以知道gv$session是依據資料字典基表X$KSUSE建立出來的（建立語句我就不貼出來了）
可以看到建立的最後where語句後，過濾掉了一些內容，我數學沒學好，不確認是不是過濾掉的遞迴的session ，但是我還有辦法，繼續往下看。
怎麼來證明呢？看我的。
SESSION1：
select sid from v$mystat where rownum=1;
   SID
----------
   1768
select paddr from v$session where sid=1768;
PADDR
----------------
0000000AE9530BD0

SESSION 2：
lock table tab$ IN exclusive MODE;

SESSION 1：
CREATE TABLE A (ID NUMBER);
會被hang住，因為建立表需要往資料字典基表$tab裡插入記錄。而且這個工作是由遞迴產生session做的

session 2:
select paddr from v$session where sid=1768;
PADDR
----------------
0000000AE9530BD0

select decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?'),ksuudsna
from x$ksuse s where ksusepro = '0000000AE9530BD0';
DECODE(BITAND(KSUSEF KSUUDSNA
-------------------- ------------------------------------------------------------
USER                ILSMP23ST
RECURSIVE          SYS

上面測試的思路是：建立一張表，由於這種DDL，會往資料字典基表tab$裡插入記錄，如果我們把tab$表鎖住，那麼就可以觀察到遞迴的session在那裡被hang住等待鎖，這樣我們就能觀察到這個“神奇”的會話了。

經過我們的測試發現：果然，v$session裡只有一條記錄，而資料字典基表裡，已經有2個會話了，其中一個是遞迴產生的，sys使用者的，而這個遞迴會話在v$session裡沒看到，也就是說過濾掉了。

說到這裡，我們差不多知道這個案例的原因了
雖然v$session裡總共的sessoin數量與設定的引數值之間還有16個session可以用，但是由於v$session遮蔽了遞迴session的數量，因此實際上，非常可能這16個你認為可以用的session早被遞迴的session佔用了。
如果你看DSI比較多的話，特別是鎖那一節，你會知道有一個檢視其實已經暴露給我們，方便的查詢各種資源的使用情況以及最大值設定情況。

select * from v$resource_limit;
RESOURCE_NAME                CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
------------------------------ ------------------- --------------- -------------------- --------------------
processes                                     120          121    3000                3000
sessions                                     205          206    4560                4560
enqueue_locks                                  27             31    55020             55020
enqueue_resources                            22             22    20284          UNLIMITED
ges_procs                                     0             0       0                   0
ges_ress                                        0             0       0          UNLIMITED
ges_locks                                     0             0       0          UNLIMITED
ges_cache_ress                                  0             0       0          UNLIMITED
ges_reg_msgs                                  0             0       0          UNLIMITED
ges_big_msgs                                  0             0       0          UNLIMITED
ges_rsv_msgs                                  0             0       0                   0
gcs_resources                                  0             0  UNLIMITED          UNLIMITED
gcs_shadows                                     0             0  UNLIMITED          UNLIMITED
smartio_overhead_memory                         0             0       0          UNLIMITED
smartio_buffer_memory                         0             0       0          UNLIMITED
smartio_metadata_memory                         0             0       0          UNLIMITED
smartio_sessions                               0             0       0          UNLIMITED
dml_locks                                     0             0    20064          UNLIMITED
temporary_table_locks                         0             0  UNLIMITED          UNLIMITED
transactions                                  0             0    5016          UNLIMITED
branches                                        0             0    5016          UNLIMITED
cmtcallbk                                     0             0    5016          UNLIMITED
max_rollback_segments                         0             0    5016             65535
sort_segment_locks                            0             0  UNLIMITED          UNLIMITED
k2q_locks                                     0             0    9120          UNLIMITED
max_shared_servers                            1             1  UNLIMITED          UNLIMITED
parallel_max_servers                            0             0    1600                3600
27 rows selected.

select count(*) from v$session;
  COUNT(*)
----------
   116
v$resource_limit裡顯示的第二行，列為CURRENT_UTILIZATION 的值，就是真正的已經被佔用的sessoin，這個值比較我查詢v$session多出了很多，這些多餘的session就是遞迴的session。

深度剖析：ORA-00018: maximum number of sessions exceeded

相關文章