Oracle数据库异常情况排查

项目上线后，数据库难免有时会出现一些异常情况，尤其是刚上线阶段。
以下是一些平时积累的简单的数据库查询sql，可以配合实际情况进行问题排查。

按主机名统计连接数

select MACHINE,count(*) from v$session group by  MACHINE order by 2 desc;

检查表锁

select object_name as 对象名称,s.sid,s.serial#,p.spid as 系统进程号
from v$locked_object l , dba_objects o , v$session s , v$process p
where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;

kill锁

ALTER SYSTEM KILL SESSION '2118,31521'

查异常session

select r.root_sid, s.serial#,
       r.blocked_num, r.avg_wait_seconds,
       s.username,s.status,s.event,s.MACHINE,
       s.PROGRAM,s.sql_id,s.prev_sql_id
  from (select root_sid, avg(seconds_in_wait) as avg_wait_seconds,
               count(*) - 1 as blocked_num
          from (select CONNECT_BY_ROOT sid as root_sid, seconds_in_wait
                  from v$session
                 start with blocking_session is null
                connect by prior sid = blocking_session)
         group by root_sid
        having count(*) > 1) r,
       v$session s
 where r.root_sid = s.sid
 order by r.blocked_num desc, r.avg_wait_seconds desc;

根据sid查询sql

 SELECT
 sql_text
  FROM v$sqltext a
 WHERE (a.hash_value, a.address) IN
       (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
               DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
          FROM v$session b
         WHERE b.sid='2020')     ----查找耗时长的session
 ORDER BY piece ASC; 

查询目前有哪些语句存在硬解析（未绑定变量方式）

 select substr(sql_text,1,50),count(*) from  v$sqlarea group by substr(sql_text,1,50) having count(*) > 250 order by count(*);

 select sql_id , sql_text , module from v$sqlarea where sql_text like '%&name%' and rownum < 20;

一般的异常排查，都是根据报错内容，结合系统的具体代码，从代码方面开头进行检查，大多都是具体的sql、数据库事务问题引起。但有时候数据库的异常，没有具体的报错，也似乎与后台代码、进程联系不起来。
这种情况下的排查，可以从以下两个方面入手：

• 连接情况：
  数据库的连接数异常增高，会影响数据库的查询效率，甚至导致大量访问被拒绝。
  对于数据库连接情况，可以根据以下步骤来检查：

1. 检查数据库的连接数，是否超过正常连接量。
2. 用户的连接数量如何，各自检查，并对比，是否有哪个用户的连接数量比其他用户多很多。
3. 具体服务器的连接数量，各自检查，并与其他服务器进行对比。
4. 具体的库的连接数量，根据不同的系统业务，可能有些库的连接量会远超其他库。但也同样可以排除那些连接量少的库。

• 阻塞情况：
  阻塞情况的产生，也是不容易从系统代码上进行排查，毕竟一个系统的业务复杂起来，很难分辨清互相之间的事务影响。但一旦发生阻塞，就容易导致数据库查询异常缓慢、甚至系统崩溃。
  对于阻塞的情况，可以按以下步骤来排查：

1. 查看阻塞情况
2. 查找阻塞源头
3. 被阻塞时间最长的进程