锁等待及阻塞查询

锁等待及阻塞查询

锁等待

查询数据库等待时间和实际执行时间的相对百分比

select *
  from v$sysmetric a
 where a.METRIC_NAME in
       ('Database CPU Time Ratio', 'Database Wait Time Ratio')
   and a.INTSIZE_CSEC = (select max(intsize_csec) from v$sysmetric);

查询数据库中过去30分钟引起最多等待的sql语句

select ash.USER_ID,
       u.username,
       sum(ash.WAIT_TIME) ttl_wait_time,
       s.SQL_TEXT
  from v$active_session_history ash, v$sqlarea s, dba_users u
 where ash.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate
   and ash.SQL_ID = s.SQL_ID
   and ash.USER_ID = u.user_id
 group by ash.USER_ID, s.SQL_TEXT, u.username
 order by ttl_wait_time desc;

查询数据库中的等待事件

select event, count(*)
  from v$session_wait
 group by event
 order by count(*) desc;

查询数据库过去15分钟最重要的等待事件

select ash.EVENT, sum(ash.WAIT_TIME + ash.TIME_WAITED) total_wait_time
  from v$active_session_history ash
 where ash.SAMPLE_TIME between sysdate - 15 / 2880 and sysdate
 group by event
 order by total_wait_time desc;

在过去15分钟哪些用户经历了等待

select s.SID,
       s.USERNAME,
       sum(ash.WAIT_TIME + ash.TIME_WAITED) total_wait_time
  from v$active_session_history ash, v$session s
 where ash.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate
   and ash.SESSION_ID = s.SID
 group by s.SID, s.USERNAME
 order by total_wait_time desc;

查询等待时间最长的对象

select a.CURRENT_OBJ#,
       d.object_name,
       d.object_type,
       a.EVENT,
       sum(a.WAIT_TIME + a.TIME_WAITED) total_wait_time
  from v$active_session_history a, dba_objects d
 where a.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate
   and a.CURRENT_OBJ# = d.object_id
 group by a.CURRENT_OBJ#, d.object_name, d.object_type, a.EVENT
 order by total_wait_time desc;

查询过去15分钟等待时间最长的sql语句

select a.USER_ID,
       u.username,
       s.SQL_TEXT,
       sum(a.WAIT_TIME + a.TIME_WAITED) total_wait_time
  from v$active_session_history a, v$sqlarea s, dba_users u
 where a.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate
   and a.SQL_ID = s.SQL_ID
   and a.USER_ID = u.user_id
 group by a.USER_ID, s.SQL_TEXT, u.username
 order by total_wait_time desc;

哪些SQL消耗更多的IO

select *
  from (select s.PARSING_SCHEMA_NAME,
               s.DIRECT_WRITES,
               substr(s.SQL_TEXT, 1, 500),
               s.DISK_READS
          from v$sql s
         order by s.DISK_READS desc)
 where rownum < 20

查看哪些会话正在等待IO资源

SELECT username, program, machine, sql_id
  FROM V$SESSION
 WHERE EVENT LIKE 'db file%read';

查看正在等待IO资源的对象

SELECT d.object_name, d.object_type, d.owner
  FROM V$SESSION s, dba_objects d
 WHERE EVENT LIKE 'db file%read'
   and s.ROW_WAIT_OBJ# = d.object_id;

查看redo日志切换频率

Select round(FIRST_TIME, 'DD'), THREAD#, Count(SEQUENCE#)
  From v$log_history
 Group By round(FIRST_TIME, 'DD'), THREAD#
 Order By 1, 2

SELECT  trunc(first_time) "Date",
        to_char(first_time, 'Dy') "Day",
        count(1) "Total",
        SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0",
        SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1",
        SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2",
        SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3",
        SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4",
        SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5",
        SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6",
        SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7",
        SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8",
        SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9",
        SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10",
        SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11",
        SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12",
        SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13",
        SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14",
        SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15",
        SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16",
        SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17",
        SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18",
        SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19",
        SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20",
        SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21",
        SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22",
        SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23"
FROM    V$log_history
group by trunc(first_time), to_char(first_time, 'Dy')
Order by 1;

查询阻塞和被阻塞的session，支持RAC，按instance分组

SELECT a.INST_ID,
       a.sid,
       a.SERIAL#,
       a.USERNAME,
       a.SQL_ID,
       a.PROGRAM,
       a.EVENT,
       a.BLOCKING_SESSION,
       a.WAIT_TIME_MICRO
  FROM GV$SESSION A
 WHERE (A.INST_ID, a.SID) in
       (select b.BLOCKING_INSTANCE, b.BLOCKING_SESSION from gv$session b)
union all
select a.INST_ID,
       a.sid,
       a.SERIAL#,
       a.USERNAME,
       a.SQL_ID,
       a.PROGRAM,
       a.EVENT,
       a.BLOCKING_SESSION,
       a.WAIT_TIME_MICRO
  FROM GV$SESSION A
 where a.BLOCKING_SESSION is not null
 order by BLOCKING_SESSION nulls first;

最近30分钟内ASH采样到的等待事件排名，支持RAC,按instance分组

select *
  from (select inst_id,
               rank() over(partition by inst_id order by cnt desc) rk,
               event,
               cnt
          from (select ash.INST_ID, ash.EVENT, count(*) CNT
                  from gv$active_session_history ash
                 where ash.SAMPLE_TIME > sysdate - 1 / 24 / 60 * 30
                   and event is not null
                 group by ash.INST_ID, ash.EVENT))
 where rk <= 10;

某段事件内ASM采样到的等待事件排名，并查询采样中，产生此等待事件的SQL，支持rac，按 instance分组

select a.inst_id, a.event, a.sql_id, a.sql_cnt, b.cnt event_cnt, sql_rk
  from (select inst_id,
               event,
               sql_id,
               sql_cnt,
               rank() over(partition by event order by sql_cnt desc) sql_rk
          from (select ash.INST_ID, ash.EVENT, ash.SQL_ID, count(*) sql_cnt
                  from gv$active_session_history ash
                 where to_char(ash.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS') >
                       '2021-07-25 14:00:00'
                   AND to_char(ash.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS') <
                       '2021-07-25 16:00:00'
                   AND EVENT IS NOT NULL
                 GROUP BY ASH.INST_ID, ASH.EVENT, ASH.SQL_ID)) A,
       (SELECT INST_ID, EVENT, CNT
          FROM (SELECT INST_ID,
                       rank() over(partition by INST_ID order by cnt desc) rk,
                       EVENT,
                       CNT
                  FROM (SELECT ASH.INST_ID, ASH.EVENT, COUNT(*) CNT
                          FROM GV$ACTIVE_SESSION_HISTORY ASH
                         where to_char(ash.SAMPLE_TIME,
                                       'YYYY-MM-DD HH24:MI:SS') >
                               '2021-07-25 14:00:00'
                           AND to_char(ash.SAMPLE_TIME,
                                       'YYYY-MM-DD HH24:MI:SS') <
                               '2021-07-25 16:00:00'
                           AND EVENT IS NOT NULL
                         GROUP BY ASH.INST_ID, ASH.EVENT))
         WHERE RK <= 3) B
 WHERE A.INST_ID = B.INST_ID
   AND A.EVENT = B.EVENT
   AND A.SQL_RK <= 5
 ORDER BY INST_ID, EVENT_CNT DESC, SQL_CNT DESC, SQL_RK;

查看过去15天sql执行的hash_plan_id和资源消耗统计变化

select sql_id,
       endtime,
       plan_hash_value,
       nvl2(exed, exed, 0) "执行次数",
       nvl2(ems, ems, 0) "单次执行时间(ms)",
       nvl2(cms, cms, 0) "平均CCWAIT(ms)",
       nvl2(ams, ams, 0) "平均APWAIT(ms)",
       nvl2(clms, clms, 0) "平均CMWAIT(ms)",
       nvl2(ioms, ioms, 0) "平均IOWAIT(ms)",
       nvl2(ctms, ctms, 0) "平均CPU_WAIT(ms)",
       nvl2(bfgets, bfgets, 0) "平均buffer gets",
       nvl2(drd, drd, 0) "平均disk reads",
       nvl2(sd, sd, 0) "平均sort",
       nvl2(fet, fet, 0) "平均fetch",
       nvl2(rpd, rpd, 0) "平均rows process",
       snap_id
  from (select a.sql_Id,
               to_char(b.end_interval_time, 'yyyymmdd hh24:mi:ss') endtime,
               a.executions_delta exed,
               a.plan_hash_value,
               round(decode(a.executions_delta,0,0,a.elapsed_time_delta / a.executions_delta) / 1000,2) ems,
               round(decode(a.executions_delta,0,0,a.ccwait_delta / a.executions_delta) / 1000,2) cms,
               round(decode(a.executions_delta,0,0,a.apwait_delta / a.executions_delta) / 1000,2) ams,
               round(decode(a.executions_delta,0, 0,a.clwait_delta / a.executions_delta) / 1000,2) clms,
               round(decode(a.executions_delta,0, 0,a.iowait_delta / a.executions_delta) / 1000,2) ioms,
               round(decode(a.executions_delta, 0,0, a.cpu_time_delta / a.executions_delta) / 1000,2) ctms,
               round(decode(a.executions_delta, 0, 0,a.buffer_gets_delta / a.executions_delta),2) bfgets,
               round(decode(a.executions_delta,0, 0, a.disk_reads_delta / a.executions_delta),2) drd,
               round(decode(a.executions_delta,0,0, a.sorts_delta / a.executions_delta),2) sd,
               round(decode(a.executions_delta,0, 0,a.fetches_delta / a.executions_delta),2) fet,
               round(decode(a.executions_delta,0,0, a.rows_processed_delta / a.executions_delta), 4) rpd,
               b.snap_id
          from dba_hist_sqlstat a, dba_hist_snapshot b
           where sql_id = '62qcz0vz20a3t'
           and a.snap_id = b.snap_id
           and a.instance_number = b.instance_number
              --and b.instance_number=1
           and a.executions_delta <> 0
           and b.end_interval_time > sysdate - 15)
 order by snap_id;

查询某个会话的锁

select /*+rule*/SESSION_ID,OBJECT_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS,LOCKED_MODE from gv$locked_object where session_id=&sid;

查询TMTX锁

select /*+rule*/* from v$lock where ctime >100 and type in ('TX','TM') order by 3,9;

查询数据库中的锁

select /*+rule*/s.sid,p.spid,l.type,round(max(l.ctime)/60,0) lock_min,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name from v$session s, v$process p,v$lock l,v$locked_object o,dba_objects b where  o.SESSION_ID=s.sid and s.sid=l.sid and o.OBJECT_ID=b.OBJECT_ID and s.paddr = p.addr and l.ctime >100 and l.type in ('TX','TM','FB') group by s.sid,p.spid,l.type,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name order by 9,1,3,4;
# 说明：
# dba_hist_active_sess_history/v$active_session_history查看SQL执行历史情况
# v$session_wait/v$session_event查看数据库会话的等待事件
# v$sga_target_advice数据库sga优化顾问视图
# v$pga_target_advice数据库pga优化顾问视图
# v$memory_target_advice内存优化顾问视图
# dba_data_files查询表空间和对应数据文件
# v$session_longops记录执行超过5s的sql，可以查询一个SQL语句执行了多长时间以及还要执行多长时间
# v$fast_start_transactions/v$fast_start_servers可以查看所有回滚的事务
# v$log视图可以查找到联机重做日志的日志序列号v$archived_log视图或
# v$log_history视图中可以查找给定的归档重做日志的日志序列号
# user_tab_columns视图将显示当前用户模式下的每张表的每一列的详细数据情况
# gv$memory_dynamic_components记录内存变更的视图
# gv$sql_monitor SQL监视视图
# dba_tab_statistics表的信息统计**

提供运维人员使用，查看长时间持有锁并阻塞其他会话session，生成处理语句

SELECT '锁源session: '||A_S.SID||','||A_S.SERIAL#||',@'||A_S.INST_ID||', '||A_S.SCHEMANAME||', From: '||A_S.OSUSER||','||A_S.MACHINE||', '||A_S.PROGRAM||', Cur_SQL: '||A_S.SQL_ID||',Prev_SQL: '|| A_S.PREV_SQL_ID||', Status:'||A_S.STATUS||', Lock_Time: '||A.CTIME||'s.'||CHR(10)||
        ' -> 等待锁session:'||B.SID||','||B_S.INST_ID||','||B_S.MACHINE||','||B_S.EVENT||', Blocked_SQL: '||B_S.SQL_ID||', Locked_ON: '||OBJ.OWNER||'.'||OBJ.OBJECT_NAME||', Lock_Mode: '||A.TYPE||','||
       DECODE(A.LMODE, 0, '0,none', 1, '1,NULL', 2, '2,row-S(SS)', 3, '3,row-X(SX)', 4, '4,share(S)', 5, '5,S/Row-X(SSX)', 6, '6,exclusive(X)')||CHR(10)||
       ' --> 查询锁定数据SQL:  '||(DECODE(OBJ.OBJECT_TYPE, 'TABLE', 'SELECT * FROM '||OBJ.OWNER||'.'||OBJ.OBJECT_NAME||' WHERE ROWID = '''||
               DBMS_ROWID.ROWID_CREATE(1, OBJ.DATA_OBJECT_ID, B_S.ROW_WAIT_FILE#, B_S.ROW_WAIT_BLOCK#, B_S.ROW_WAIT_ROW#)||''';', NULL))||CHR(10)||
       ' --> 查询锁源操作SQL:  SELECT DISTINCT ASH.SESSION_ID, ASH.SESSION_SERIAL#, ASH.MACHINE, ASH.PROGRAM, ASH.XID, ASH.SAMPLE_TIME, ASH.SQL_ID , ASH.SQL_OPNAME, ASH.SQL_EXEC_START, ASH.EVENT, Q.SQL_TEXT FROM GV$ACTIVE_SESSION_HISTORY ASH JOIN GV$SQL Q ON ASH.INST_ID=q.INST_ID AND ASH.SQL_ID = q.SQL_ID and ASH.SQL_PLAN_HASH_VALUE=q.PLAN_HASH_VALUE WHERE ASH.SESSION_ID = '|| A_S.SID ||' AND ASH.SESSION_SERIAL# = '|| A_S.SERIAL# ||' AND ASH.INST_ID = '|| A_S.INST_ID ||' ORDER BY 1, 2, 3, 4, 5, 6;'||CHR(10)||
       ' --> 查询相关SQL文本:  SELECT DISTINCT SQL_ID,SQL_TEXT FROM GV$SQL WHERE SQL_ID IN ('''||A_S.SQL_ID||''','''||A_S.PREV_SQL_ID||''','''||B_S.SQL_ID||''');'||CHR(10)||
       ' --> 清理session命令:  alter system disconnect session '''||A_S.SID||','||A_S.SERIAL#||',@'||A_S.INST_ID||''' immediate;' AS BLOCK_DETAIL
  FROM GV$LOCK     A,
       GV$LOCK     B,
       GV$SESSION  A_S,
       GV$SESSION  B_S,
       DBA_OBJECTS OBJ,
       DBA_USERS U
 WHERE A.ID1 = B.ID1
   AND A.ID2 = B.ID2
   AND A_S.USER#=U.USER_ID
   AND U.ORACLE_MAINTAINED='N' /* 排除Oracle内部账户持有的锁 */
   AND A.CTIME >= 30 /* 持有锁时间超过 30s */
   AND A.BLOCK > 0 /* 阻塞了其他的会话 */
   AND B.REQUEST > 0
   AND A_S.TYPE='USER'
   AND A.SID = A_S.SID
   AND A.INST_ID = A_S.INST_ID
   AND B.SID = B_S.SID
   AND B.INST_ID = B_S.INST_ID
   AND A_S.SID = B_S.FINAL_BLOCKING_SESSION
   AND A_S.INST_ID = B_S.FINAL_BLOCKING_INSTANCE
   AND B_S.ROW_WAIT_OBJ# = OBJ.OBJECT_ID(+)
 ORDER BY A.INST_ID, A.SID;
set pagesize 1000 linesize 1000;
select count(*), machine, program, username, osuser
  from gv$session
 group by machine, program, username, osuser
 order by count(*) desc;
set pagesize 1000 linesize 1000;
select count(*) from gv$session;

TOP20慢查询语句

SELECT * FROM (
  SELECT s.sql_text,
  s.sql_fulltext, 
  s.sql_id,
  s.executions exectimes,
  ROUND(s.elapsed_time / 1000000, 2) exectime,
  ROUND(s.elapsed_time / 1000000 / s.executions, 2) avgexetime,
  s.first_load_time fathercreatetime,
  s.parsing_user_id userid,
  u.username username
  FROM v$sqlarea s
  LEFT JOIN all_users u ON s.parsing_user_id = u.user_id
  WHERE s.executions > 0
  AND u.username = 'HISUSER'
  ORDER BY avgexetime DESC)
WHERE rownum <= 20;

数据库历史阻塞事件查询

根据awr报告来观察某一时间段主要的等待事件

select event,blocking_session,sql_id,count(*) 
    from dba_hist_active_sess_history ash
    where sample_time>=to_timestamp('2023-12-20 10:00:00','yyyy-mm-dd hh24:mi:ss') and sample_time<=to_timestamp('2023-12-20 11:08:00','yyyy-mm-dd hh24:mi:ss') and event='SQL*Net message from client'
    group by event,blocking_session,sql_id;

根据阻塞的SQL_ID查询到具体的SQL语句

select sql_text from v$sql where sql_id='7521kc2bkpdh9';

根据阻塞的SQL_ID查询其他更多的信息

SELECT DISTINCT
       s.inst_id,
       s.machine,
       s.LOGON_TIME,
       s.PREV_EXEC_START
FROM
       gv$session s
WHERE
       s.sql_id = '03ukf2c56gs54'
order BY s.PREV_EXEC_START;
#=====================================#
SELECT DISTINCT
       s.inst_id,
       s.machine,
       i.instance_name,
       i.host_name
FROM
       gv$session s
       JOIN gv$instance i ON s.inst_id = i.inst_id
WHERE
       s.sql_id = '03ukf2c56gs54';
#=====================================#
SELECT DISTINCT
       s.inst_id,
       s.machine,
       i.instance_name,
       i.host_name,
       sys_context('USERENV', 'IP_ADDRESS') AS client_ip
FROM
       gv$session s
       JOIN gv$instance i ON s.inst_id = i.inst_id
WHERE
       s.sql_id = '03ukf2c56gs54';
#=====================================#
SELECT DISTINCT
       s.machine
FROM
       gv$session s
       JOIN v$sql q ON s.sql_id = q.sql_id
WHERE
       q.sql_id = '11xapz031xfr4'
       AND s.username IS NOT NULL
       AND s.logon_time >= TO_DATE('2023-10-08 22:20:00', 'yyyy-mm-dd hh24:mi:ss')
       AND s.logon_time <= TO_DATE('2023-10-08 22:50:00', 'yyyy-mm-dd hh24:mi:ss');
#=====================================#
SELECT DISTINCT
       s.machine
FROM
       gv$session s
WHERE
       s.sql_id = '11xapz031xfr4'
       AND s.logon_time <= TO_DATE('2023-10-08 22:30:00', 'yyyy-mm-dd hh24:mi:ss')
       AND (s.sid, s.serial#) IN (
           SELECT sid, serial#
           FROM gv$session
           WHERE end_time IS NULL OR end_time >= TO_TIMESTAMP('2023-10-08 22:30:00', 'yyyy-mm-dd hh24:mi:ss')
       );
#=====================================#
SELECT DISTINCT
       s.machine
FROM
       gv$session s
WHERE
       s.sql_id = '11xapz031xfr4'
       AND s.logon_time <= TO_TIMESTAMP('2023-10-08 22:30:00', 'yyyy-mm-dd hh24:mi:ss')
       AND (s.sid, s.serial#) IN (
           SELECT sid, serial#
           FROM gv$session
           WHERE end_time IS NULL OR end_time >= TO_TIMESTAMP('2023-10-08 22:30:00', 'yyyy-mm-dd hh24:mi:ss')
       );
#=====================================#
SELECT
    s.SID,
    s.SERIAL#,
    s.USERNAME,
    s.STATUS,
    s.OSUSER,
    s.MACHINE,
    s.PROGRAM,
    q.SQL_TEXT
FROM
    v$session s
JOIN
    v$sql q ON s.SQL_ID = q.SQL_ID
WHERE
    s.SID = 1723;