--併發很高會產生如下等待事件
SQL> select sid,row_wait_obj#,event,program from v$session where program like '%J%';

       SID ROW_WAIT_OBJ# EVENT                                                            PROGRAM
---------- ------------- ---------------------------------------------------------------- ----------------------------------------------------------------
        10            -1 cursor: pin S                                                    ORACLE.EXE (J011)
        12            -1 cursor: pin S                                                    ORACLE.EXE (J007)
       144             0 cursor: pin S                                                    ORACLE.EXE (J010)

19 rows selected

cursor: pin S

何時產生此事件?另一會話正在更新同一個cursor object的共享mutex pin時；你也想更新這個共享的mutex pin
就會產生此事件;
此等待事件一般很少見；

此等待事件持續時間非常短
Wait Time: Microseconds

---p1,p2,p3的含義

P1 Hash value of cursor --即要更新的cursor object的hash value

P2 Mutex value (top 2 bytes contains SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)
--mutex的值：高2位元組包括以排它模式扶持mutex的sid,低2位元組一般為0)

   P3 Mutex where (an internal code locator) OR'd with Mutex Sleeps
       --mutex


說到此這兒，有幾個概念要弄清楚：
1,shared mutex pin
2,mutex
3,pin
4,cursor object
5,p3的含義

--與上述有關的檢視,可獲知一個時間段的mutex分配的資訊,注意mutex_type和gets,requesting_session,blocking_session
SQL> select * from v$mutex_sleep_history where to_char(sleep_timestamp,'yyyymmdd hh24:mi:ss')>'20130319 22:00:00' order by gets desc;

MUTEX_IDENTIFIER SLEEP_TIMESTAMP                                                                  MUTEX_TYPE                             GETS     SLEEPS REQUESTING_SESSION BLOCKING_SESSION LOCATION                                 MUTEX_VALUE              P1 P1RAW                    P2         P3         P4 P5
---------------- -------------------------------------------------------------------------------- -------------------------------- ---------- ---------- ------------------ ---------------- ---------------------------------------- ---------------- ---------- ---------------- ---------- ---------- ---------- ----------------------------------------------------------------
       311143737 19-MAR-13 10.49.05.507000 PM                                                     Cursor Pin                         12315948   13676350                 55              105 kksfbc [KKSCHLFSP2]                      0000006900000000          0 00                        0          0          0
       311143737 19-MAR-13 10.49.04.777000 PM                                                     Cursor Pin                         12277667   13675635                  9               55 kksLockDelete [KKSCHLPIN6]               0000003700000004          0 00                        0          0          0
       311143737 19-MAR-13 10.49.04.712000 PM                                                     Cursor Pin                         12273427   13675623                105                9 kksfbc [KKSCHLFSP2]                      0000000900000005          0 00                        0          0          0
       311143737 19-MAR-13 10.49.04.296000 PM                                                     Cursor Pin                         12255509   13671258                  9               97 kksLockDelete [KKSCHLPIN6]               0000006100000007          0 00                        0          0          0

--上述的現實版
SQL> select * from v$mutex_sleep where rownum<=10;

MUTEX_TYPE                       LOCATION                                     SLEEPS WAIT_TIME
-------------------------------- ---------------------------------------- ---------- ----------
Library Cache                    kglhdgn2 106                                    873      47906
注：sleeps即mutex_type和location的sleep次數
    wait_time等待時間
    mutex_type為mutex保護的物件或行為名稱
    location The code location where the waiter slept for the mutex

v$mutex_sleep_history官方如下

--併發模擬程式碼
SQL> declare
2 v_job pls_integer;
3 begin
4 for i in 1..500 loop
5 dbms_job.submit(v_job,what => 'declare v_out date;begin select sysdate into v_out from dual;end;',next_date => sysdate,interval=>'sysdate+1');
6 commit;
7 end loop;
8 end;
9 /

PL/SQL procedure successfully completed

--配置如下引數true，可以減少對於library cache 的shared cursor pin競爭,
--在library cache每個sql對應一個parent cursor和多個child cursor,會話訪問
--sql，必須先持有sql對應的parent cursor及child cursor的pin;
--mutex即保護如上cursor的一種機制

如發現超高的Cursor: Pin S等待，這是一個由於頻繁執行SQL共享解析時產生的競爭。當一個會話嘗試以共享模式（S - Share）來獲得一個遊標時，
需要修改相應的Mutex結構的引用計數（reference count），或者增加該計數，或者減少。修改引用技術的原子操作很快（其實和Latch的獲取釋放類似），但是在頻繁解析的情況下，
仍然產生了競爭和等待，由此就產生了 cursor : pin S 的等待。

這通常是由於某些SQL以超高頻繁的頻率執行導致的，當然也可能與系統的CPU能力不足有關。

Mutex機制在Oracle 10g引入，用於替代Library cache pin操作，其效能更高，其原理為在每個Child Cursor上分配一個地址空間記錄Mutex，當該Cursor被共享執行時，
透過將該位進行加一處理來實現。雖然是指遊標共享，但是更新Mutex結構的操作需要排他，當某一個SQL被頻繁共享執行時，可能就會出現Pin S的等待。

每個Library Cache物件都有一個reference count （引用計數），用來表明有多少其他物件目前正保留一個對它的引用（reference). 物件A 想要引用物件B,
A 就把B 的 reference count 加 1。當A 結束了對B 的引用， A 就把 B 的reference count 減 1.   當沒有任何物件再引用 B 時， B 的 reference count就減為0，
B 就被清除（deallocated）, 記憶體就被釋放。清除B的時候，被B所用的物件的 reference count 也可能減小，也可能使它們被清除。

最簡單的解決方案是，將頻繁執行的SQL分割槽拆解，分散競爭，如以下SQL透過註釋將同一條SQL分解為2條，就分散了競爭：
    select /*SQL 1*/ a from t_a where id=?

    select /*SQL 2*/ a from t_a where id=?
這種做法在Ebay、Papal、支付寶等公司被廣泛採用。

--當然你也可以用v$session_event但如果高併發環境，此檢視flush相當快
--如果為了精確分析等待事件表現，可增加取樣間隔及手工取樣

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval => 2);

begin dbms_workload_repository.modify_snapshot_settings(interval => 2); end;

ORA-13511: invalid INTERVAL 2, must be in the range (10, 52560000)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 174
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 222
ORA-06512: at line 2

--快照取樣最短時間為10分鐘
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval => 10);

PL/SQL procedure successfully completed

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL                 RETENTION                  TOPNSQL
---------- ----------------------------- --------------------------------------------- ----------
1331266241 +00000 00:10:00.0             +00010 00:00:00.0          DEFAULT

--配置為cursor_space_for_time = FALSE ,library cache hit命中一直保持在94%左右
SQL> select * from v$librarycache order by gets desc;

NAMESPACE                                                              GETS    GETHITS GETHITRATIO       PINS    PINHITS PINHITRATIO    RELOADS INVALIDATIONS DLM_LOCK_REQUESTS DLM_PIN_REQUESTS DLM_PIN_RELEASES DLM_INVALIDATION_REQUESTS DLM_INVALIDATIONS
---------------------------------------------------------------- ---------- ---------- ----------- ---------- ---------- ----------- ---------- ------------- ----------------- ---------------- ---------------- ------------------------- -----------------
SQL AREA                                                              74625      70612 0.946224455   22707794   29972082 1.319902849         50           102                 0                0                0                         0                 0
TABLE/PROCEDURE                                                       16655      14609 0.877154007      43192      40926 0.947536580        211             0                 0                0                0                         0                 0
SCHEMA                                                                 9262       9251 0.998812351          0          0           1          0             0                 0                0                0                         0                 0
DBINSTANCE                                                                1          0           0          0          0           1          0             0                 0                0                0                         0                 0
JAVA DATA                                                                 1          0           0          1          0           0          0             0                 0                0                0                         0                 0

17 rows selected

--description可知其引數的含義,其引數會話，例項不能動態修改，只能重啟庫生效
SQL> select * from v$parameter where name='cursor_space_for_time';

       NUM NAME                                                                                   TYPE VALUE                                                                            DISPLAY_VALUE                                                                    ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE ISINSTANCE_MODIFIABLE ISMODIFIED ISADJUSTED ISDEPRECATED ISBASIC DESCRIPTION                                                                      UPDATE_COMMENT                                                                         HASH
---------- -------------------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------- ---------------- ---------------- --------------------- ---------- ---------- ------------ ------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
      1461 cursor_space_for_time                                                                     1 FALSE                                                                            FALSE                                                                            TRUE      FALSE            FALSE            FALSE                 FALSE      FALSE      TRUE         FALSE   use more memory in order to get faster execution                                                                                                                  4277774490

---配置為cursor_space_for_time = true library cache的命中率還保持在94%左右
SQL> /

NAMESPACE                                                              GETS    GETHITS GETHITRATIO       PINS    PINHITS PINHITRATIO    RELOADS INVALIDATIONS DLM_LOCK_REQUESTS DLM_PIN_REQUESTS DLM_PIN_RELEASES DLM_INVALIDATION_REQUESTS DLM_INVALIDATIONS
---------------------------------------------------------------- ---------- ---------- ----------- ---------- ---------- ----------- ---------- ------------- ----------------- ---------------- ---------------- ------------------------- -----------------
SQL AREA                                                              76501      72464 0.947229447   23687906   31293009 1.321054254         50           102                 0                0                0                         0                 0
TABLE/PROCEDURE                                                       16872      14824 0.878615457      44419      42149 0.948895742        213             0                 0                0                0                         0                 0
DBINSTANCE                                                                1          0           0          0          0           1          0             0                 0                0                0                         0                 0
JAVA DATA                                                                 1          0           0          1          0           0          0             0                 0                0                0                         0                 0

17 rows selected

小結：在library cache miss很低時，配置cursor_space_for_time為false;否則維持預設值false;

--對比下cursor_space_for_time為true和false對於cursor:pin x的影響

/------------------此sql獲取壓力測試範圍的cursor:pin x的統計資料-----------------------------/
select *
from dba_hist_system_event
where wait_class = 'Concurrency'
      and event_name like '%cursor: pin S%'
      and snap_id
      in (select snap_id
          from dba_hist_snapshot
          where to_char(begin_interval_time, 'yyyymmdd hh24:mi:ss')>=&a
                and
                to_char(end_interval_time, 'yyyymmdd hh24:mi:ss')<=&b
                )
      and event_id=352301881


--配置為cursor_space_for_time為true的統計資料,注：16:03開始至16:10的快照

SNAP_ID DBID INSTANCE_NUMBER EVENT_ID EVENT_NAME WAIT_CLASS_ID WAIT_CLASS TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED_MICRO TOTAL_WAITS_FG TOTAL_TIMEOUTS_FG TIME_WAITED_MICRO_FG
---------- ---------- --------------- ---------- ---------------------------------------------------------------- ------------- ---------------------------------------------------------------- ----------- -------------- ----------------- -------------- ----------------- --------------------
804 1331266241 1 352301881 cursor: pin S 3875070507 Concurrency 23758 0 3081035280 23758 0 3081035280

--配置為cursor_space_for_time為false的統計資料,注：16:15開始至16:25的快照

806 1331266241               1 352301881 cursor: pin S                                                       3875070507 Concurrency                                                            26317              0        3286106587          26317                 0           3286106587

小結：cursor_space_for_time與cur pin s等待事件無關

cursor: pin S模擬與處理

相關文章