oracle快速故障定位,快速定位数据库性能问题的几个要点

目录

·90%以上的性能问题都是由Application引起的

·10%以下的性能问题是由于DB的配置,资源限制，操作系统引起来的

·标识与解决性能问题的工作流程

·潜在的原因

·Top Process

·找出事务中长时间操作的SQL

·几个重要的视图

·从statspack report中找到更详细的信息，10g中使用AWR Report及ADDM Report

·分析statspack report

·SQL Trace on top SQL

·DB Hung住的问题

·内部错误

·Lock问题

·显示Lock相关的几个脚本

·死锁

·OS相关的命令

·90%以上的性能问题都是由Application引起的

·10%以下的性能问题是由于DB的配置,资源限制，操作系统引起来的

·标识与解决性能问题的工作流程

·如果是故障，最先检查的是alter.log文件

标识问题流程解决问题流程

·潜在的原因

1)客户端程序瓶颈

2)PL/SQL 问题

3)未知原因的数据库配置

4)网络问题

5)内存问题

6)IO问题

7)CPU问题

·Top Processes

用top命令查看前消耗资源的进程，Unix，Linux中用top命令查看，NT平台中用“任务管理器”。从OS一级找出top n 进程的PID，再从数据库一级找出对应的session，进一步再找出对应的SQL语句,使用以下脚本可以根据spid找到对应的SQL语句：

#!/bin/ksh

# creator:Nico

#function:getsqlstatementbyspid

# parameter:spid

# useage:get_by_spid.sh

spid

sqlplus-S/nolog<

connect/assysdba;

col SERIAL# format999999

colsidformat99999

col username format a10

colprogramformat a32

col sql_text format a15

setlines1000

setpages1000

setverifyoff

col sql_hash_value new_value hash_valuehead hash_value

selectsid,serial#,username,program,sql_hash_value,

to_char(logon_time,'yyyy/mm/dd

hh24:mi:ss')aslogin_time

fromv\$session

wherepaddrin(selectaddrfromv\$processwherespid=$1);

selectsql_text

fromv\$sqltext_with_newlines

wherehash_value=&hash_value

orderbypiece;

exit;

EOF

·找出事务中长时间操作的SQL

#!/bin/ksh

# creator:Nico

#function:getlongoperationssqlstatement

# parameter:null

# useage:get_longops.sh

sqlplus-S/nolog<

connect/assysdba;

col username format a12

colsidformat99999

col opname format a15

col progress format a15

col sql_text format a81

col time_remaining format a10

setlines1000

setpages1000

setverifyoff

selectusername,sid,opname,

round(sofar *100/totalwork,0)||'%'asprogress,time_remaining,sql_text

fromv\$session_longops,v\$sqlarea

wheretime_remaining<>0

andsql_address=address

andsql_hash_value=hash_value;

exit;

EOF

·几个重要的视图

1)v$porcess

2)v$session

3)v$sqlarea , v$sql , v$sqltext_with_newlines

4)v$session_longops

·从statspack report中找到更详细的信息，10g中使用AWR Report及ADDM Report

一般在peak time做statspack report，时间一般在20分钟左右

·分析statspack report

Report 中的几个重要部分：

·Load Profile

·Instance Efficiency Percentages

·Top 5 Wait Events

·SQL order by Gets

for DB

·SQL order by

Reads for DB

·Tablespace IO stats for DB

·File IO Stats for DB

·SQL Trace on top SQL

1)set timing on

2)set autotrace on

·DB Hung住的问题

3)这是一个非常严重的问题

4)大多数情况下需要与OS Team及Oracle Support一起去解决

·内部错误

5)ORA-00600 到 ORA-07445

6)大多数与Oracle的Bug相关

7)在Metalink上查看别人的解决方法

8)将相关trace文件发给Oracle Support寻求支持

·Lock问题

9)Top Event Enqueue

10)找出TM,TX类型的锁，并找出holder

11)与开发部门的同事一起工作从应用逻辑减少或释放被lock住的资源

12)dba_waiters视图，显示所有等待lock的session

DBA_WAITERS

DBA_WAITERS shows all the sessions that

are waiting for a lock.

Column

Datatype

Description

WAITING_SESSION

NUMBER

The waiting session

HOLDING_SESSION

NUMBER

The holding session

LOCK_TYPE

VARCHAR2(26)

The lock type

MODE_HELD

VARCHAR2(40)

The mode held

MODE_REQUESTED

VARCHAR2(40)

The mode requested

LOCK_ID1

VARCHAR2(40)

Lock ID 1

LOCK_ID2

VARCHAR2(40)

Lock ID 2

V$LOCKED_OBJECT

This

view lists all locks acquired by every transaction on the system. It shows

which sessions are holding DML locks (that is, TM-type enqueues) on what

objects and in what mode

Column

Datatype

Description

XIDUSN

NUMBER

Undo segment number

XIDSLOT

NUMBER

Slot number

XIDSQN

NUMBER

Sequence number

OBJECT_ID

NUMBER

Object ID being locked

SESSION_ID

NUMBER

Session ID

ORACLE_USERNAME

VARCHAR2(30)

Oracle user name

OS_USER_NAME

VARCHAR2(30)

OS user name

PROCESS

VARCHAR2(12)

OS process ID

LOCKED_MODE

NUMBER

Lock mode

·显示Lock相关的几个脚本

#!/bin/ksh

#creator:Nico

#createdate:2008-07-05

#function:getlockedobject&session

# parameter:null

# useage:get_locked_object_session.sh

sqlplus-S/nolog<

connect/assysdba;

col user_name format a15

col owner format a15

col object_name format a15

col object_type format a10

setlinesize280

setpages1000

setverifyoff

prompt

promptV\$LOCKED_OBJECTlistsalllocks acquiredbyeverytransactiononthesystem.

promptIt

shows which sessions are holdingDMLlocks(thatis,TM-typeenqueues)onwhat objectsandinwhatmode.

col ORACLE_USERNAME heading'Oracle|User

Name'format a12

col OS_USER_NAME format a13

select*fromv\$locked_object;

prompt

prompt***********lockedobjectsandholdsession***************************

select/*+

rule */lpad(' ',decode(l.xidusn,0,3,0))||l.oracle_username

user_name,

o.owner,o.object_name,o.object_type,s.sid,s.serial#

fromv\$locked_object l,dba_objects

o,v\$session s

wherel.object_id=o.object_id

andl.session_id=s.sid

orderbyo.object_id,xidusndesc;

prompt

prompt**********locksessioninformation********************************

selectsid,type,id1,id2,lmode,request,ctime,block

fromv\$lock

wheretypein('TM','TX');

exit;

EOF

·死锁

1)死锁是一个逻辑问题

2)两个或多个session要求锁住对方持有的对象

3)从trace文件中找出相关的SQL语句

4)与开发人员一起改变应用的逻辑

·OS相关的命令

5)sar

6)iostat

7)top/topas

8)vmstat

9)free

10)uptime