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

目录

·   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: get sql statement by spid

   # parameter: spid

   # useage: get_by_spid.sh spid

   sqlplus -S /nolog <<EOF    

   connect / as sysdba;

   col SERIAL# format 999999

   col sid format 99999

   col username format a10

   col program format a32

   col sql_text format a15

   set lines 1000

   set pages 1000

   set verify off

   col sql_hash_value new_value hash_value  head hash_value

   select sid,serial#,username,program,sql_hash_value,

       to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') as login_time

   from v\$session

   where paddr in ( select addr from v\$process where spid=$1);

 

   select sql_text

   from v\$sqltext_with_newlines

   where hash_value = &hash_value

   order by piece;

   exit;

   EOF

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

   #!/bin/ksh

   # creator:Nico

   # function: get long operations sql statement

   # parameter: null

   # useage: get_longops.sh

   sqlplus -S /nolog <<EOF    

   connect / as sysdba;

   col username format a12

   col sid format 99999

   col opname format a15

   col progress format a15

   col sql_text format a81

   col time_remaining format a10

   set lines 1000

   set pages 1000

   set verify off

   select username,sid,opname,

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

   from v\$session_longops , v\$sqlarea

   where time_remaining <> 0

   and sql_address = address

   and sql_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

#create date: 2008-07-05

# function: get locked  object  & session

# parameter: null

# useage: get_locked_object_session.sh

sqlplus -S /nolog <<EOF    

connect / as sysdba;

col user_name format a15

col owner format a15

col object_name format a15

col object_type format a10

set linesize 280

set pages 1000

set verify off

 

prompt

prompt V\$LOCKED_OBJECT lists all locks acquired by every transaction on the system.

prompt It shows which sessions are holding DML locks (that is, TM-type enqueues) on what objects and in what mode.

col ORACLE_USERNAME heading 'Oracle|User Name' format a12

col OS_USER_NAME format a13

select * from v\$locked_object;

 

prompt

prompt ***********locked objects and hold session***************************

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#

from v\$locked_object l,dba_objects o,v\$session s

where l.object_id=o.object_id

and l.session_id=s.sid

order by o.object_id,xidusn desc;

prompt

prompt ********** lock session information********************************

select sid,type,id1,id2,lmode,request,ctime,block

from v\$lock

where type in ('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