cursor: pin S模拟与处理

最新推荐文章于 2024-01-11 08:56:09 发布

clg10051

最新推荐文章于 2024-01-11 08:56:09 发布

阅读量107

点赞数

文章标签：数据库 java

--并发很高会产生如下等待事件
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官方如下
http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_2069.htm#I1030417

--并发模拟代码
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等待事件无关

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-756655/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-756655/

clg10051

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
cursor: pin S模拟与处理

--并发很高会产生如下等待事件SQL> select sid,row_wait_obj#,event,program from v$session where program like '%J%'; SID ...
复制链接

扫一扫