1. 首页
  2. IT资讯

cursor: pin S模拟与处理

–并发很高会产生如下等待事件
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

–与此等待事件相关的一些资料
http://dba-oracle.com/googlesearchsite_proc.htm?cx=000522505899594707971%3A4-ldikxixw4&cof=FORID%3A10&ie=UTF-8&q=cursor%3A+pin+S&sa=Search&siteurl=www.dba-oracle.com%2Foracle_news%2F&ref=space.itpub.net%2F9240380%2F&ss=11219j46753447j8

–并发模拟代码            
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/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:℅傍ㄖ免沦陷dε鬼,转转请注明出处:http://www.cxybcw.com/192860.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code