Menu Close

Oracle等待事件cursor: pin S wait on X

lcc0556

1. 什么是cursor：pin S wait on X等待事件

当一个会话尝试得到一个mutex pin的时候，但是其他会话正在以exclusive模式持有相同cursor object的mutex，此时申请mutex pin的会话等待事件即为cursor：pin S wait on X 。

  • 检查当前会话中是否存在等待事件cursor: pin S wait on X
SELECT event
  FROM gv$session
 WHERE event LIKE '%cursor%';
  • 通过历史采样数据查询等待事件cursor: pin S wait on X
  SELECT du.username,
         dhash.snap_id,
         dhash.instance_number,
         dhash.event,
         dhash.sql_id,
         dhash.blocking_inst_id,
         dhash.blocking_session,
         dhash.blocking_session_serial#,
         count(*)
    FROM dba_hist_active_sess_history dhash
    LEFT JOIN dba_users du ON dhash.user_id = du.user_id
   WHERE dhash.sample_time >= to_timestamp('2021-09-09 16:00:00','yyyy-mm-dd hh24:mi:ss')
     AND dhash.sample_time <= to_timestamp('2021-09-09 17:00:00','yyyy-mm-dd hh24:mi:ss')
     AND dhash.event='cursor: pin S wait on X'
   GROUP BY du.username,dhash.snap_id,dhash.instance_number,dhash.event,dhash.sql_id,
            dhash.blocking_session,dhash.blocking_session_serial#, dhash.blocking_inst_id;

sid为22634, 的会话是阻塞源。查询该会话的username:

  SELECT DISTINCT
         du.username,
         dhash.snap_id,
         dhash.instance_number,
         dhash.session_id,
         dhash.session_serial#,
         dhash.event,
         dhash.sql_id
    FROM dba_hist_active_sess_history dhash
    LEFT JOIN dba_users du ON dhash.user_id = du.user_id
   WHERE dhash.sample_time >= to_timestamp('2021-09-09 16:00:00','yyyy-mm-dd hh24:mi:ss')
     AND dhash.sample_time <= to_timestamp('2021-09-09 17:00:00','yyyy-mm-dd hh24:mi:ss')
     AND dhash.session_id = 22634;

  • 通过历史采样数据查询等待事件
    在一个采样周期内按10分钟统计发生的等待事件信息
SELECT *
  FROM (SELECT instance_number,
               etime,
               nvl(event,'on cpu') events,
               dbtime,
               round(100*ratio_to_report(dbtime) OVER (partition by etime ),2) pct,
               row_number() over(partition by etime order by dbtime  desc) rn
         FROM (SELECT instance_number,
                      substr(to_char(sample_time,'yyyymmdd hh24:mi'),1,13)||'0' AS etime,
                      event,count(*)*10  AS dbtime
                 FROM dba_hist_active_sess_history
                WHERE sample_time between to_date('2021-09-09 16:00:00','yyyy-mm-dd hh24:mi:ss')
                                      AND to_date('2021-09-09 17:00:00','yyyy-mm-dd hh24:mi:ss')
                GROUP BY instance_number,
                         SUBSTR(to_char(sample_time,'yyyymmdd hh24:mi'),1,13),
                         event))
 WHERE rn < = 5;

Post Views: 382
Tagged , ,