该文档介绍了数据库出现故障情况下，诊断故常的思路，需要收集的信息，处理问题的方法，以及一下故障方面的测试案例。
在数据库发生故障情况下，并根据具体故障采取必要的应急处理措施尽快恢复服务。
通常的故障应急处理流程如下：
故障现场信息采集*—故障性质确认—故障处理–服务恢复。
2.1 业务处理能力下降
2.1.1 enq: SQ - contention
是由于sequence的cache不足够大引起的。查询被阻塞的对象
su - oracle
sqlplus / as sysdba
SQL> select inst_id,
sid,
event,
sql_hash_value,
ROW_WAIT_OBJ#,
sql_id,
blocking_session,
blocking_instance
fromgv$session where event = 'enq: SQ - contention';
找出被阻塞的sql_id，引起阻塞的对象ROW_WAIT_OBJ#（dba_objects.object_id）.
查看被阻塞的sql
su - oracle
sqlplus / as sysdba
SQL> select SQL_FULLTEXT fromv$sqlarea where sql_id = '&sql_id';
查看引起阻塞的对象
su - oracle
sqlplus / as sysdba
SQL> Selectowner,object_name,object_type from dba_objects where object_id=&oi;
2.1.2 enq: TX
su - oracle
sqlplus / as sysdba
SQL> select inst_id,
sid,
event,
sql_hash_value,
ROW_WAIT_OBJ#,
sql_id,
blocking_session,
blocking_instance
fromgv$session where event = like 'enq:TX%');
找出被阻塞的sql_id，引起阻塞的对象ROW_WAIT_OBJ#（dba_objects.object_id）.
查看被阻塞的sql
su - oracle
sqlplus / as sysdba
SQL> select SQL_FULLTEXT fromv$sqlarea where sql_id = '&sql_id';
查看引起阻塞的对象
su - oracle
sqlplus / as sysdba
SQL> Selectowner,object_name,object_type from dba_objects where object_id=&oi;
查看锁和被锁关系：
su - oracle
sqlplus / as sysdba
SQL>SELECTdecode(request,0,'holder: ','waiter: ') || inst_id || ':' || sid sess,
id1, id2, lmode, request, type
FROM gv$lock
WHERE (id1, id2, type) in ( SELECT id1, id2,type FROM gv$lock WHERE request > 0 )
order by id1, request;
SESS ID1 ID2 LMODE REQUEST TY
---------------- -------- -------------------- ---------- --
holder: 1:195 589856 209 6 0 TX
waiter: 1:194 589856 209 0 6 TX
waiter: 1:193 589856 209 0 6 TX
holder: 1:211 655391 206 6 0 TX
waiter: 1:213 655391 206 0 6 TX
waiter: 1:200 655391 206 0 6 TX
找出holder后面跟着waiter的session实例1的195和221号session，和应用维护人员协商是否杀掉这些阻塞其他连接的session。如果需要杀掉这些session执行下面的sql
select'alter system kill session '''||SID||','||SERIAL#||''';' from gv$session whereinst_id =1 and sid in (195,221);
'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
-----------------------------------------------------
alter system kill session '195,12';
alter system kill session '221,1';
然后执行下面的sql杀掉session
Conn/ as sysdba
alter system kill session '195,12';
alter system kill session '221,1';
或者根据session的sid,找到对应的操作系统进程号，从操作系统杀掉这个进程
Conn / as sysdba
SQL> select spid from v$process whereaddr in (select paddr from v$session where sid =221);
SPID
------------
2769
SQL> select spid from v$process whereaddr in (select paddr from v$session where sid =195);
SPID
------------
3102
SQL> exit
Session 195对应的操作系统进程是3102，查看这个进程是应用进程，并杀掉这个进程
[oracle@linux ~]$ ps -ef|grep 3102
oracle 3102 3101 0 11:04 ? 00:00:00 oracleora10g(DESCRIPTION=(LOCAL=NO)(ADDRESS=(PROTOCOL=beq)))
oracle 8053 2915 0 14:16 pts/0 00:00:00 grep 3102
[oracle@linux ~]$ kill 3102
杀掉进程会让启动这些进程的应用停掉。别杀数据库自带的进程（pmon,dbwn,lgwr,ckpt,smon,mmon,mmnl,arcn），否则会引起数据库异常，严重的会让数据库crash掉。
2.1.3 使用sql tuning Advisor
在sqlplus下依次执行下面脚本
创建优化task
su - oracle
sqlplus / as sysdba
SQL> DECLARE
my_task_name VARCHAR2(30);
v_sql_id v$sqlarea.sql_id%type;
BEGIN
my_task_name :=DBMS_SQLTUNE.CREATE_TUNING_TASK
(sql_id => v_sql_id,
User_name => 'USERNAME',
task_name => 'my_sql_tuning_task');
END;
/
执行优化task
execDBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
查看优化建议
SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECTDBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task') FROM DUAL;
查看建议是否适用
删除优化task
EXECDBMS_SQLTUNE.EXECUTE_TUNING_TASK('my_sql_tuning_task');
2.1.4 用sql profile替换执行计划
一个sql有多个执行计划时候，用一个好的执行计划替换差的执行计划。参见MOS文档Doc ID1400903.1，需要下载Oracle工具SQLT （详见215187.1）中的脚本，解压目录sqlt/utl/下脚本coe_load_sql_profile.sql 。
查询出sql语句的sql_id和执行计划的plan_hash_value
SQL>select sql_id ,plan_hash_value, sql_text from v$sql where sql_text like'%scott.emp%';
SQL_IDPLAN_HASH_VALUE SQL_TEXT
------------------------ ------------------------------------------------------------------
329d885bxvrcr 3045807146 select ename from scott.emp whereename='MILLER'
4f74t4ab7rd5y 2872589290 select /*+ FULL (EMP) */ ename from scott.empwhere ename='MILLER'
用sql语句4f74t4ab7rd5y执行计划2872589290 替换sql语句329d885bxvrcr的所有执行计划。执行coe_load_sql_profile.sql这个脚本的用户必须是非sys的dba用户，比如system用户。
connect system/pass
SQL> @coe_load_sql_profile.sql
Parameter 1:
ORIGINAL_SQL_ID (required)
Enter value for 1: 329d885bxvrcr
Parameter 2:
MODIFIED_SQL_ID (required)
Enter value for 2: 4f74t4ab7rd5y
PLAN_HASH_VALUE AVG_ET_SECS
----------------------------------------
2872589290 .003
Parameter 3:
PLAN_HASH_VALUE (required)
Enter value for 3: 2872589290
Values passed tocoe_load_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID:"329d885bxvrcr"
MODIFIED_SQL_ID:"4f74t4ab7rd5y"
PLAN_HASH_VALUE:"2872589290"
.
.
.
ORIGINAL:329D885BXVRCR MODIFIED:4F74T4AB7RD5YPHV:2872589290 SIGNATURE:15822026218863957422 CREATED BYCOE_LOAD_SQL_PROFILE.SQL
SQL>SET ECHO OFF;
****************************************************************************
* Enter SCOTT password to exportstaging table STGTAB_SQLPROF_329d885bxvrcr
****************************************************************************
Export: Release 11.2.0.3.0 -Production on Sun Mar 11 14:45:47 2012
Copyright (c) 1982, 2011, Oracleand/or its affiliates. All rightsreserved.
Password:
.
.
.
coe_load_sql_profile completed.
2.1.5 IO大的sql语句
在IO比较繁忙时候，查看数据库等待事件，比如db file sequential read等，wait_classs=’ User I/O’类的等待事件。
su - oracle
sqlplus / as sysdba
SQL> select inst_id,
Sql_id,
event,
count(1)
fromgv$session
where wait_classs=’User I/O’
group by inst_id,
sid,
event;
查看sql_id多的sql。根据这个sql_id查看session信息：
su - oracle
sqlplus / as sysdba
SQL> select inst_id,
sid,
event,
sql_hash_value,
ROW_WAIT_OBJ#,
sql_id,
blocking_session,
blocking_instance
fromgv$session
where Sql_id=’&sqlid’;
多执行几次，如果在20秒内，这些并发量较大的sql的sid变化不大，那就查看个sql语句
su - oracle
sqlplus / as sysdba
SQL> select SQL_FULLTEXT fromv$sqlarea where sql_id = '&sql_id';
并且分析这个sql语句的执行计划：
su - oracle
sqlplus / as sysdba
SELECT * FROMTABLE(dbms_xplan.display_awr('&sql_id'));
可以使用合理的索引，正确的统计信息，或者采用【2.1.3】和【2.1.4】中内容优化sql。
2.1.6 CPU使用率高的sql语句
有些sql语句消耗CPU，比如引起等待事件db filescattered read的sql语句，这是全表或全索引扫描引起的。
su - oracle
sqlplus / as sysdba
SQL> select inst_id,
Sql_id,
count(1)
fromgv$session
where event = 'db filescattered read'
group by inst_id,
sid;
查看sql_id多的sql。根据这个sql_id查看session信息：
su - oracle
sqlplus / as sysdba
SQL> select inst_id,
sid,
event,
sql_hash_value,
ROW_WAIT_OBJ#,
sql_id,
blocking_session,
blocking_instance
fromgv$session
where Sql_id=’&sqlid’;
多执行几次，如果在20秒内，这些并发量较大的sql的sid变化不大，那就查看个sql语句
su - oracle
sqlplus / as sysdba
SQL> select SQL_FULLTEXT fromv$sqlarea where sql_id = '&sql_id';
并且分析这个sql语句的执行计划：
su - oracle
sqlplus / as sysdba
SELECT * FROMTABLE(dbms_xplan.display_awr('&sql_id'));
可以使用合理的索引，正确的统计信息，或者采用【2.1.3】和【2.1.4】中内容优化sql。
2.1.7 RAC数据库私有网卡流量大
RAC数据库私有网卡流量大时候，会影响数据库性能，一些sql语句会导致私有网卡流量大，这会引起gc类的等待事件。
su - oracle
sqlplus / as sysdba
SQL> select inst_id,
Sql_id,
count(1)
fromgv$session
where event like 'gc%'
group by inst_id,
sid;
查看sql_id多的sql。根据这个sql_id查看session信息：
su - oracle
sqlplus / as sysdba
SQL> select inst_id,
sid,
event,
sql_hash_value,
ROW_WAIT_OBJ#,
sql_id,
blocking_session,
blocking_instance
fromgv$session
where Sql_id=’&sqlid’;
多执行几次，如果在20秒内，这些并发量较大的sql的sid变化不大，那就查看个sql语句
su - oracle
sqlplus / as sysdba
SQL> select SQL_FULLTEXT from v$sqlareawhere sql_id = '&sql_id';
并且分析这个sql语句的执行计划：
su - oracle
sqlplus / as sysdba
SELECT * FROMTABLE(dbms_xplan.display_awr('&sql_id'));
可以使用合理的索引，正确的统计信息，或者采用【2.1.3】和【2.1.4】中内容优化sql。
2.1.8 热点块等待事件
热点块是把数据库从数据文件复制到db buffer cache的等待。主要体现在等待事件latch: cache bufferschains或buffer busywaits。
su - oracle
sqlplus / as sysdba
SQL> select inst_id,
Sql_id,
count(1)
fromgv$session
where event in ('latch:cache buffers chains', 'buffer busy waits')
group by inst_id,
sid;
查看sql_id多的sql。根据这个sql_id查看session信息：
su - oracle
sqlplus / as sysdba
SQL> select inst_id,
sid,
event,
sql_hash_value,
ROW_WAIT_OBJ#,
sql_id,
blocking_session,
blocking_instance
fromgv$session
where Sql_id=’&sqlid’;
多执行几次，如果在20秒内，这些并发量较大的sql的sid变化不大，那就查看个sql语句
su - oracle
sqlplus / as sysdba
SQL> select SQL_FULLTEXT fromv$sqlarea where sql_id = '&sql_id';
并且分析这个sql语句的执行计划：
su - oracle
sqlplus / as sysdba
SELECT * FROMTABLE(dbms_xplan.display_awr('&sql_id'));
可以使用合理的索引，正确的统计信息，或者采用【2.1.3】和【2.1.4】中内容优化sql。
2.1.9 根据等待事件找出相关sql和对象
su - oracle
sqlplus / as sysdba
SQL> select inst_id,
sid,
event,
sql_hash_value,
ROW_WAIT_OBJ#,
sql_id,
blocking_session,
blocking_instance
fromgv$session where event = like 'event%');
找出被阻塞的sql_id，引起阻塞的对象ROW_WAIT_OBJ#（dba_objects.object_id）.
查看被阻塞的sql
su - oracle
sqlplus / as sysdba
SQL> select SQL_FULLTEXT fromv$sqlarea where sql_id = '&sql_id';
查看引起阻塞的对象
su - oracle
sqlplus / as sysdba
SQL> Select owner,object_name,object_typefrom dba_objects where object_id=&oi;
2.1.10 library cache lock和cursor: pin
这两个等待事件多的情况下，首先看看参数sga_target是否为非0
su - oracle
sqlplus / as sysdba
showparameter sga_target
如果是非0，再看看发生问题时间段是否有sga自动调整
su - oracle
sqlplus / as sysdba
select
INST_ID
COMPONENT
OPER_TYPE
OPER_MODE
PARAMETER
INITIAL_SIZE
TARGET_SIZE
FINAL_SIZE
STATUS
START_TIME
END_TIME
Fromgv$sga_resize_ops;
如果在出现问题时间段（FINAL_SIZE-INITIAL_SIZE）值比较大，这说明sga自动调整影响了数据库性能。
解决这个问题有两种方式，等待或者杀掉出问题的session。找出有问题sql语句参见【2.1.9】。
事后，可以调整db_cache_size和shared_pool_size最大值，或者把sga_target设置为0
6980
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
