oracle 常见问题诊断思路（一招鲜、吃遍天）

1、文章介绍

该文档介绍了数据库出现故障情况下，诊断故障的思路，需要收集的信息，处理问题的方法，以及诊断故障方面的测试案例。

2 、性能问题处理思路

在数据库发生故障情况下，根据具体故障采取必要的应急处理措施尽快恢复服务。

通常的故障应急处理流程如下：

故障现场信息采集*—故障性质确认—故障处理–服务恢复。

2.1 enq: SQ - contention

问题原因：是由于sequence的cache不足够大引起的。

解决方法：

找出被阻塞sql语句的sql_id，以及引起阻塞的对象ROW_WAIT_OBJ#（dba_objects.object_id）.

su - Oracle

sqlplus as sysdba

SQL> select inst_id,

       sid,

       event,

       sql_hash_value,

       ROW_WAIT_OBJ#,

       sql_id,

       blocking_session,

       blocking_instance

  from gv$session where event = 'enq: SQ - contention';

根据sql_id查看被阻塞的sql

su - oracle

sqlplus as sysdba

SQL> select SQL_FULLTEXT fromv$sqlarea where sql_id = '&sql_id';

根据object_id查看引起阻塞的对象（sequence名称）

su - oracle

sqlplus as sysdba

SQL> Select owner,object_name,object_type from dba_objects where object_id=&oi;

最后适当调整sequence 的cache大小：

SQL> alter sequence  seq_name  cache xxxxxx;

2.2   enq: TX

问题原因：

enq: TX - row lock contention 通常是application级别的问题。

enq是一种保护共享资源的锁定机制，排队机制，先进先出(FIFO)。

解决方法：

找出被阻塞的sql_id，引起阻塞的对象ROW_WAIT_OBJ#（dba_objects.object_id）.

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

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

SQL>Conn/ as sysdba

SQL>alter system kill session '195,12';

SQL>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.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.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.emp where ename='MILLER'

用sql语句4f74t4ab7rd5y执行计划2872589290  替换sql语句329d885bxvrcr的所有执行计划。执行coe_load_sql_profile.sql这个脚本的用户必须是非sys的dba用户，比如system用户。

SQL>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:4F74T4AB7RD5Y PHV: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.5  IO大的sql语句

问题描述：

在IO比较繁忙时候，查看数据库等待事件，比如db file sequential read等，wait_classs=’ User I/O’类的等待事件。

解决思路：

查看sql_id多的sql。根据这个sql_id查看session信息：

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;

上面的语句多执行几次，如果在20秒内，这些并发量较大的sql的sid变化不大，那就查看个sql语句：

su - oracle

sqlplus / as sysdba

SQL> select inst_id,

       sid,

       event,

       sql_hash_value,

       ROW_WAIT_OBJ#,

       sql_id,

       blocking_session,

       blocking_instance

  from  gv$session

where Sql_id=’&sqlid’;

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.6   CPU使用率高的sql语句

问题描述：

有些sql语句消耗CPU，比如引起等待事件db files cattered read的sql语句，这是全表或全索引扫描引起的。

解决方法：

su - oracle

sqlplus / as sysdba

SQL> select inst_id,

       Sql_id,

       count(1)

  from gv$session

where event = 'db file scattered 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语句

SQL> select SQL_FULLTEXT fromv$sqlarea where sql_id = '&sql_id';

并且分析这个sql语句的执行计划：

su - oracle

sqlplus / as sysdba

SQL>SELECT * FROMTABLE(dbms_xplan.display_awr('&sql_id'));

可以使用合理的索引，正确的统计信息，或者采用【2.1.3】和【2.1.4】中内容优化sql。

2.7   RAC数据库私有网卡流量大

问题描述：

RAC数据库私有网卡流量大时候，会影响数据库性能，一些sql语句会导致私有网卡流量大，这会引起gc类的等待事件。

解决方法：

查看sql_id多的sql。根据这个sql_id查看session信息：

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> 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语句

SQL> select SQL_FULLTEXT from v$sqlareawhere sql_id = '&sql_id';

并且分析这个sql语句的执行计划：

SQL>SELECT * FROMTABLE(dbms_xplan.display_awr('&sql_id'));

可以使用合理的索引，正确的统计信息，或者采用【2.1.3】和【2.1.4】中内容优化sql。

2.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.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.10  library cache lock和cursor: pin

解决思路：

这两个等待事件多的情况下，首先看看参数sga_target是否为非0

su - oracle

SQL>sqlplus / as sysdba

SQL>show parameter sga_target

如果是非0，再看看发生问题时间段是否有sga自动调整

SQL>

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。