快速检查处理数据库大量会话堵塞卡死情况

Oracle数据库

–查看连接等待数：

  SELECT COUNT (*), event, wait_class
    FROM gv$session_wait
GROUP BY event, wait_class
ORDER BY 1 DESC;

–查看具体机器等待连接数

select t.MACHINE,count(1) from gv$session t 
where t.EVENT='latch: cache buffers chains' group by t.MACHINE;

–查询oracle的连接数

select count(*) from v$session;

–查询oracle的并发连接数

select count(*) from v$session where status='ACTIVE';

–查看不同用户的连接数

select username,count(username) from v$session where username is not null group by username;

–当前的连接数

select count(*) from v$process;

–可用连接数

select value from v$parameter where name = 'processes' ; 

–查看当前活动会话正执行的sql:

  SELECT b.sid,
         b.machine,
         b.username,
         ROUND ( ( (a.cpu_time / a.executions) / 100000), 10) cpu_etime,
         a.executions,
         a.sql_id,
         a.sql_fulltext fullsql,
         a.sql_text,
         A.PARSE_CALLS,
         a.cpu_time,
         b.PROGRAM,
            -- a.BUFFER_GETS,
            'alter system kill session '
         || ''''
         || b.sid
         || ','
         || b.serial#
         || ''''
         || ';',
         b.CLIENT_INFO
    FROM v$sql a, v$session b
   WHERE     executions > 0
         AND b.status = 'ACTIVE'
         AND a.hash_value = b.sql_hash_value
         AND a.SQL_ID = b.SQL_ID
-- and b.USERNAME = 'HX_USER'
-- and   b.sql_id='27gff0m4jutv3'
--and ((cpu_time / executions) / 100000 >= 10)
ORDER BY (cpu_time / executions) DESC,
         a.BUFFER_GETS DESC,
         a.EXECUTIONS DESC,
         a.sql_id;

–根据会话条件关联查杀会话：

SELECT 'kill -9 ' || pp.spid
  FROM v$session ss, v$process pp
 WHERE     status = 'INACTIVE'
       AND SS.PADDR = pp.addr
       AND ss.program LIKE '%JDBC%'
       AND ss.username = 'DB_WSBS';

–查看表空间是否满：

select t.TABLESPACE_NAME,
       TO_CHAR(ROUND(t.TABLESPACE_SIZE * 8 / 1024 / 1024, 2), '99990.00') SIZES,
       TO_CHAR(ROUND(t.USED_SPACE * 8 / 1024 / 1024, 2), '99990.00') USED,
       TO_CHAR(ROUND((t.TABLESPACE_SIZE - t.USED_SPACE) * 8 / 1024 / 1024,2),'99990.00') FREE,
       TO_CHAR(ROUND(T.USED_PERCENT, 2), '99990.00') || '%' PERCENTS
  from dba_tablespace_usage_metrics t
 WHERE t.TABLESPACE_NAME NOT IN
       ('SYSAUX', 'UNDOTBS1', 'UNDOTBS2', 'SYSTEM', 'CSSBBFB')
 order by 2 desc;

–查看是否有锁：

select decode(t.REQUEST, 0, 'Holder:', 'Waiter:') || ' inst_id:' ||
       t.INST_ID || ', sid: ' || t.sid sess,v.username,t.CTIME,v.STATUS,
       t.ID1,v.SQL_ID,       t.id2,       t.LMODE,       t.REQUEST,
       t.TYPE,v.STATUS,v.EVENT,v.type,p.INST_ID,p.SPID,'ps -ef|grep '||p.SPID,'kill -9 '||p.SPID,v.PROGRAM
  from gv$lock t,gv$session v, gv$process p
 where (t.id1, t.id2, t.TYPE) in
       (select id1, id2, type from gv$lock where request > 0) and v.TYPE<>'BACKGROUND'
       and v.INST_ID=t.INST_ID and t.SID=v.SID and p.addr = v.paddr and p.INST_ID=v.INST_ID 
 order by id1, request;