ORACLE中STATUS為INACTIVE但是SERVER為SHARED狀態的會話淺析

   我們知道當ORACLE資料庫啟用共用伺服器模式時，通過共用伺服器模式連接到資料庫的會話是有一些特征的。在v$session裡面，其SERVER的狀態一般為SHARED和NONE， 為SHARED時，表示當前會話正在執行SQL語句，其占用共用伺服器進程，會話的STATUS狀態為ACTIVE；當會話狀態STATUS處於INACITVE時，它的SERVER欄位值一般為NONE，意味著此時並沒有共用伺服器進程服務該會話，這個詳細請見v$session中server為none與shared值解析 這篇博客。但是最近在一資料庫中突然見到一些會話STATUS為INACTIVE，但是SERVER狀態為SHARED的會話，如下所示：

其實發現這個問題是因為在追查一個TNS-12535的問題時發現的。當時突然出現短暫的資料庫（Oracle 10g R2）連接不上的情況，nmon監控發現當時的整體資源開銷都非常小，也分析過AWR、ASH報告，並沒有發現很特殊的情況，但是在bdump下麵發現shared server進程生成的trc文件。例如下麵一個 epps_d004_24858.trc，截圖所示:

在這篇博客”TNS-12535: TNS:operation timed out案例解析”裡面我分析、構造過出現TNS-12535錯誤的場景。但是我們分析ASH報告和查詢dba_hist_active_sess_history時發現出現問題的時間段，active會話的數量不超過4個。所以可以排除是這種情形。後面檢查發現共用伺服器模式的會話居然有STATUS為INACTIVE但是SERVER為SHARED狀態的會話，而且數量較多，本身這台伺服器的max_shared_servers參數為32，所以當大量INACTIVE會話一直占用shared server進程時，當ACITVE會話需要shared server服務進程時就會由於shared server進程不夠而處於等待狀態，時間長了就會出現TNS-12535錯誤。那麼就有可能出現active session不多，但是連接不上資料庫的這種情況。分析至此，那麼就有兩個問題需要解決：1 為什麼INACTIVE的會話會占用shared server進程不釋放？ 2 這個分析必須要經測試驗證確認. 3:如何解決這個問題?

SQL> show parameter max_shared_servers;

NAME                                 TYPE        VALUE

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

max_shared_servers                   integer     32

SQL> 

關於第一個問題，剛剛開始一直沒有找到答案，後面才在oracle metalink上面找到了答案,官方文檔High Number Of Shared Servers Usage In 10g When Compared To 9i (文檔 ID 444950.1)裡面有相關介紹，如下摘抄所示：

APPLIES TO:

SYMPTOMS

On 10g having a shared server configuration and seeing many sessions with "STATUS" "INACTIVE" but still these sessions are not releasing the shared server process.

When you query from V$SESSION

In 10g Since the sessions are not releasing the shared server processes, Number of shared server processes increases and will cause new connections to terminate with a message max limit(MAX_SHARED_SERVERS) for shared_servers exceeded.

If you trace the session then you will observe the following wait events.

CAUSE

When using WORKAREA_SIZE_POLICY=AUTO, In 10g for certain operations (Like SORT) SQL memory can intentionally spill into the PGA of the shared server rather than taking up space in shared memory. When data spills to the PGA the session has to stay tied to that specific shared server. And hence the Process in Inactive can still be holding a shared server until it is terminated.

SOLUTION

The behavior observed is expected in 10g when WORKAREA_SIZE_POLICY is set to automatic.

Use WORKAREA_SIZE_POLICY=MANUAL to prevent the SORT operation to spill over the PGA. This will make sure the session doesn't require any more shared server process and the SHARED_SERVER will be released by the process when it is in INACTIVE status.

BUG 5689608  can be referred for more information

REFERENCES

當資料庫參數WORKAREA_SIZE_POLICY = AUTO時，在10g中對於某些SQL操作（如SORT）所用的SQL記憶體，可能有意地放入（ spill over這裡沒有翻譯為溢出）共用伺服器的PGA當中，而不是占用共用記憶體（shared memroy）中的空間。 當數據放入到PGA時，會話必須保持綁定到該特定的共用伺服器（shared server）。 因此，處於非活動狀態的進程仍可以持有共用伺服器，直到會話終止。

另外，解決方案裡面也介紹，可以將參數WORKAREA_SIZE_POLICY設置為MANUAL，這樣可以阻止排序操作將數據放入PGA當中。這個設定可以確保會話不會要求更多的共用伺服器進程，並且當會話變成INACTIVE狀態時，共用伺服器很快就能釋放。

那麼第一個問題解決了，接下來我們來看第二個問題，後面我觀察時，發現出現問題的時候，ACTIVE和INACTVIE的shared server數量等於32了. 那麼我們接下來看看，如何構造這種狀態的會話

在共用連接方式的會話1中執行下麵SQL

SQL> show user;

USER is "TEST"

SQL> create table test as select * from dba_objects;

Table created.

SQL> create or replace package cursor_package as

  2  cursor mycursor is select * from test order by object_name;

  3  end;

  4  /

Package created.

SQL> begin

  2   open cursor_package.mycursor;

  3   end;

  4   /

PL/SQL procedure successfully completed.

SQL> create or replace procedure test_case is

  2  l_row TEST%rowtype;

  3  begin

  4  if cursor_package.mycursor%isopen then

  5  fetch cursor_package.mycursor into l_row;

  6  end if;

  7  end;

  8  /

Procedure created.

SQL> select sys_context('userenv', 'sid') from dual;

SYS_CONTEXT('USERENV','SID')

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

932

如上所示，我們知道這個會話ID為932，此時開啟另外一個會話2，查看會話ID為932的狀態。如下所示

SQL> select sid, serial#, status ,server from v$session where sid=932;

       SID    SERIAL# STATUS   SERVER

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

       932         23 INACTIVE NONE

在會話1中執行下麵SQL語句，如下截圖所示：

SQL> exec test_case;

PL/SQL procedure successfully completed.

SQL> 

然後去會話2中檢查會話ID為932的狀態，此時就會出現STATUS為INACTIVE，SERVER狀態為SHARED的會話狀態了。

關於如何解決這個問題，我們並沒有將將參數WORKAREA_SIZE_POLICY設置為MANUAL，因為修改這個參數過後，需要調整sort_area_size,hash_area_size等參數. 在複雜環境下，一個固定值比較難滿足各個時段的需求。這個資料庫實例本身max_shared_server的值（32）比較小，我們將其調整為48， 另外本身設置了一個crontab作業， 定期清理那些空閑超過一段時間的INACTIVE會話。

參考資料：

High Number Of Shared Servers Usage In 10g When Compared To 9i (文檔 ID 444950.1)