ORACLE 性能分析指引—–Hand/Locking(一)发现问题

一、识别到底是Hang 还是 Locking

1.Hang的表现:用户不能登陆、数据库不能操作、select 1 from dual; 无返回、无法创建表。

2.Locking的表现:一个或者多个session无响应。

3.可能导致问题的原因:schema 的变更、数据库参数的修改、应用的修改、数据库的升级等。

4.理清本次问题:受影响的用户、可能导致该问题的事件的顺序、何时以及如何被发现的、明显的问题、什么还在正常工作、可接受的结果、可以尝试解决问题的方案。

二、收集信息

1.收集系统信息:OSW、LTOM、EM、手工收集。

2.收集HANGANALYZE和SystemSTAT.

                 HANGANALYZE使用:

                  ALTER SESSION SET EVENTS ‘immediate trace name HANGANALYZE level <level>’;

ORADEBUG hanganalyze <level>
ORADEBUG -g def hanganalyze <level>    (Cluster wide syntax)

                  The levels are defined as follows:
10  Dump all processes (IGN state)
5  Level 4 + Dump all processes involved in wait chains (NLEAF state)
4  Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)
3  Level 2 + Dump only processes thought to be in a hang (IN_HANG state)
1-2  Only HANGANALYZE output, no process dump at all

                

Systemstate使用:

                  ALTER SESSION SET EVENTS ‘immediate trace name SYSTEMSTATE level <level>’;
ORADEBUG dump systemstate  <level>
ORADEBUG -g all dump systemstate <level>    (Cluster wide syntax)

                   1  Very basic process information only
2  process + session state objects
10  Most common level – includes state object trees for all processes

                  Level + 256    Adding 256 to the level will try to dump short stack info for each process. 

                  Typical levels are 266 (Solaris or Linux) and 10 (other platforms)

 

使用LTOM和HANGFG工具自动收集信息

             手工收集:

               A.非集群环境:

开3个窗口分别执行(分别为SQL1、SQL2、SQL3):

                           sqlplus ” / as sysdba”  (8.1.7–9.2.x)

 

                           export ORACLE_SID=PROD

                           sqlplus -prelim / as sysdba   (10.1版本以上)

                     

                           SQL1> oradebug setmypid
SQL1> oradebug unlimit
SQL1> oradebug hanganalyze 3

 

                           SQL2> oradebug setmypid
SQL2> oradebug unlimit
SQL2> oradebug dump systemstate 266

                           等待2分钟以后

                           SQL1> oradebug hanganalyze 3

 

                           SQL3> oradebug setmypid
SQL3> oradebug unlimit
SQL3> oradebug dump systemstate 266

B.集群环境:

                         sqlplus “/ as sysdba” ( 9.0.1 – 9.2.x)

                         export ORACLE_SID=PROD

                         sqlplus -prelim / as sysdba (10.1版本以上)

                         SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug -g all hanganalyze 3
SQL> oradebug -g all dump systemstate 266

                         等待2分钟以后

                         SQL> oradebug -g all hanganalyze 3
SQL> oradebug -g all dump systemstate 266

3.收集v$视图信息

       Gather V$ View Data SQL

 

 

三、分析

1.系统资源的使用情况

A.CPU的使用情况

                 CPU使用率  USER+SYS<90%

CPU队列 每个CPU的run queue<4

B.内存的使用情况

内存的使用率

Memory Page Scan Rate

SWAP的使用率

2.查看数据库是否hang住了

检查HANDANALYZE文件中是否有“CYLCLE”,如果有CYLCLE,则记下记下blocker和waiter session的SID和OSPID。

Example:

 

3.查看数据库是否有锁

检查HANDANALYZE文件中是否有“OPEN chains”,如果有,则记下记下blocker和waiter session的SID和OSPID。     

Example:

 

 

 

4.通过OSPID收集Errorstack

             sqlplus ” / as sysdba”  (8.1.7–9.2.x)

export ORACLE_SID=PROD

sqlplus -prelim / as sysdba   (10.1版本以上)

SQL> oradebug setospid <ospid>

SQL>oradebug unlimit

SQL>oradebug errorstack 3

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>