Oracle性能调优实践中的几点心得

原创 2003年08月11日 12:13:00

       很多的时侯,做Oracle DBA的我们,当应用管理员向我们通告现在应用很慢、数据库很慢的时侯,我们到数据库时做几个示例的Select也发现同样的问题时,有些时侯我们会无从下手,因为我们认为数据库的各种命种率都是满足Oracle文档的建议。实际上如今的优化己经向优化等待(waits)转型了,实际中性能优化最根本的出现点也都集中在IO,这是影响性能最主要的方面,由系统中的等待去发现Oracle库中的不足、操作系统某些资源利用的不合理是一个比较好的办法,下面把我的一点实践经验与大家分享一下,本文测重于Unix环境。

一、通过操作系统的一些工具检查系统的状态,比如CPU、内存、交换、磁盘的利用率,根据经验或与系统正常时的状态相比对,有时系统表面上看起来看空闲这也可能不是一个正常的状态,因为cpu可能正等待IO的完成。除此之外我们还应观注那些占用系统资源(cpu、内存)的进程。

1、如何检查操作系统是否存在IO的问题?使用的工具有sar,这是一个比较通用的工具。
  Rp1#Sar -u 2 10
  即每隔2秒检察一次,共执行20次,当然这些都由你决定了。
  示例返回:
  HP-UX hpn2 B.11.00 U 9000/800    08/05/03
  18:26:32    %usr    %sys    %wio   %idle
  18:26:34      80       9      12       0
  18:26:36      78      11      11       0
  18:26:38      78       9      13       1
  18:26:40      81      10       9       1
  18:26:42      75      10      14       0
  18:26:44      76       8      15       0
  18:26:46      80       9      10       1
  18:26:48      78      11      11       0
  18:26:50      79      10      10       0
  18:26:52      81      10       9       0

  Average       79      10      11       0

    其中的%usr指的是用户进程使用的cpu资源的百分比,%sys指的是系统资源使用cpu资源的百分比,%wio指的是等待io完成的百分比,这是值得我们观注的一项,%idle即空闲的百分比。如果wio列的值很大,如在35%以上,说明你的系统的IO存在瓶颈,你的CPU花费了很大的时间去等待IO的完成。Idle很小说明系统CPU很忙。像我的这个示例,可以看到wio平均值为11说明io没什么特别的问题,而我的idle值为零,说明我的cpu已经满负荷运行了。

当你的系统存在IO的问题,可以从以下几个方面解决

  ♀联系相应的操作系统的技术支持对这方面进行优化,比如hp-ux在划定卷组时的条带化等方面。
  ♀查找Oracle中不合理的sql语句,对其进行优化
  ♀对Oracle中访问量频繁的表除合理建索引外,再就是把这些表分表空间存放以免访问上产生热点,再有就是对表合理分区。

2、关注一下内存。
    常用的工具便是vmstat,对于hp-unix来说可以用glance,Aix来说可以用topas,当你发现vmstat中pi列非零,memory中的free列的值很小,glance,topas中内存的利用率多于80%时,这时说明你的内存方面应该调节一下了,方法大体有以下几项。
  ♀划给Oracle使用的内存不要超过系统内存的1/2,一般保在系统内存的40%为益。
  ♀为系统增加内存
  ♀如果你的连接特别多,可以使用MTS的方式
  ♀打全补丁,防止内存漏洞。

3、如何找到点用系用资源特别大的Oracle的session及其执行的语句。
Hp-unix可以用glance,top
IBM AIX可以用topas
些外可以使用ps的命令。
通过这些程序我们可以找到点用系统资源特别大的这些进程的进程号,我们就可以通过以下的sql语句发现这个pid正在执行哪个sql,这个sql最好在pl/sql developer,toad等软件中执行, 把<>中的spid换成你的spid就可以了。
SELECT a.username,
       a.machine,
       a.program,
       a.sid,
       a.serial#,
       a.status,
       c.piece,
       c.sql_text
  FROM v$session a,
       v$process b,
       v$sqltext c
 WHERE b.spid=<spid> 
   AND b.addr=a.paddr
   AND a.sql_address=c.address(+)
 ORDER BY c.piece     

   我们就可以把得到的这个sql分析一下,看一下它的执行计划是否走索引,对其优化避免全表扫描,以减少IO等待,从而加快语句的执行速度。

提示:我在做优化sql时,经常碰到使用in的语句,这时我们一定要用exists把它给换掉,因为Oracle在处理In时是按Or的方式做的,即使使用了索引也会很慢。
比如:
SELECT  col1,col2,col3 FROM table1 a
 WHERE a.col1 not in (SELECT  col1 FROM table2)
       可以换成:
SELECT  col1,col2,col3 FROM table1 a
 WHERE not exists
 (SELECT  'x'  FROM table2 b
WHERE  a.col1=b.col1)

4、另一个有用的脚本:查找前十条性能差的sql.
 SELECT * FROM
  (
   SELECT PARSING_USER_ID
          EXECUTIONS,
          SORTS,
          COMMAND_TYPE,
          DISK_READS,
          sql_text
      FROM  v$sqlarea
     ORDER BY disk_reads DESC
   ) 
  WHERE ROWNUM<10 ;


二、迅速发现Oracle Server的性能问题的成因,我们可以求助于v$session_wait这个视图,看系统的这些session在等什么,使用了多少的IO。以下是我提供的参考脚本:

脚本说明:查看占io较大的正在运行的session
 SELECT se.sid,
       se.serial#,
       pr.SPID,
       se.username,
       se.status,
       se.terminal,
       se.program,
       se.MODULE,
       se.sql_address,
       st.event,
       st.p1text,
       si.physical_reads,
       si.block_changes
  FROM v$session se,
       v$session_wait st,
       v$sess_io si,
       v$process pr
 WHERE st.sid=se.sid
   AND st.sid=si.sid
   AND se.PADDR=pr.ADDR
   AND se.sid>6
   AND st.wait_time=0
   AND st.event NOT LIKE '%SQL%'
 ORDER BY physical_reads DESC

对检索出的结果的几点说明:
1、我是按每个正在等待的session已经发生的物理读排的序,因为它与实际的IO相关。

2、你可以看一下这些等待的进程都在忙什么,语句是否合理?
  Select sql_address from v$session where sid=<sid>;
  Select * from v$sqltext where address=<sql_address>;
执行以上两个语句便可以得到这个session的语句。
你也以用alter system kill session 'sid,serial#';把这个session杀掉。

3、应观注一下event这列,这是我们调优的关键一列,下面对常出现的event做以简要的说明:
a、buffer busy waits,free buffer waits这两个参数所标识是dbwr是否够用的问题,与IO很大相关的,当v$session_wait中的free buffer wait的条目很小或没有的时侯,说明你的系统的dbwr进程决对够用,不用调整;free buffer wait的条目很多,你的系统感觉起来一定很慢,这时说明你的dbwr已经不够用了,它产生的wio已经成为你的数据库性能的瓶颈,这时的解决办法如下:
a.1增加写进程,同时要调整db_block_lru_latches参数
示例:修改或添加如下两个参数
  db_writer_processes=4
  db_block_lru_latches=8
a.2开异步IO,IBM这方面简单得多,hp则麻烦一些,可以与Hp工程师联系。
b、db file sequential read,指的是顺序读,即全表扫描,这也是我们应该尽量减少的部分,解决方法就是使用索引、sql调优,同时可以增大db_file_multiblock_read_count这个参数。

c、db file scattered read,这个参数指的是通过索引来读取,同样可以通过增加db_file_multiblock_read_count这个参数来提高性能。

d、latch free,与栓相关的了,需要专门调节。

e、其他参数可以不特别观注。

结篇:匆忙之中写下了这篇文章,希望能抛砖引玉,能为你的Oracle调优实践带来帮助。

Oracle性能调优实践中的几点心得

 很多的时侯,做Oracle DBA的我们,当应用管理员向我们通告现在应用很慢、数据库很慢的时侯,我们到数据库时做几个示例的Select也发现同样的问题时,有些时侯我们会无从下手, 因为我们认为数据库...
  • TechChan
  • TechChan
  • 2008年10月11日 13:57
  • 559

浅谈工作中的几点小心得

最近,和同事几个人一起在研究Gearman的源代码。看到同事看源码飞速,顿时压力山大。自己自觉不自己的也在跟人家赶速度。但是,总感觉自己没啥收获,后面发现自己好好停下来,用自己的节奏看,反而看懂了不少...
  • zhouhong1026
  • zhouhong1026
  • 2013年12月17日 09:48
  • 1713

Oracle DBA培训教程:从实践中学习Oracle数据库管理与维护(第2版)笔记

 要获得企业管理器控制台的HTTP端口号,可进入$ORACLE_HOME\install目录(可能为F:\oralce\product\10.2.0\db_1\install),寻找po...
  • qingfengfeixin
  • qingfengfeixin
  • 2015年05月09日 15:47
  • 883

状态机设计模式理解

1.从工作流状态机实践中总结状态模式使用心得:http://www.jdon.com/10981 2.
  • findaway123
  • findaway123
  • 2015年06月09日 21:11
  • 1832

Oracle 性能调优心得

 Delphi/Delphi.NET多层应用系统开发技术(II)  终于入秋了,眼看这1Q就要结束了, 不过DevCo的技术研讨会可还没结束,由于上次的Delphi/Delphi.NET多层应用系...
  • success_dream
  • success_dream
  • 2008年03月11日 14:44
  • 463

《ORACLE DBA基础培训教程:从实践中学习ORACLE DBA》 读书笔记

《ORACLE DBA基础培训教程:从实践中学习ORACLE DBA》 oerr帮助ORA-XXXX错误 [oracle@dd ~]$ oerr Usage: oerr facility e...
  • heizistudio
  • heizistudio
  • 2013年02月27日 17:02
  • 586

oracle性能调优思想与概论

锁争用(锁等待)的原因: (1)块锁:ITL等待,一个数据块的事务达到上限,其他会话需要修改这个数据块,需要等待其他事务释放这个数据块的事务槽才行; (2)一般大量进程同时更新同一条记录,或者特定...
  • u011538954
  • u011538954
  • 2015年11月15日 22:16
  • 878

Oracle和MySQL的几点区别

Oracle数据库与MySQL数据库的区别是本文我们主要介绍的内容,希望能够对您有所帮助。 1.组函数用法规则 mysql中组函数在select语句中可以随意使用,但在oracle中如果查询语句中...
  • tanga842428
  • tanga842428
  • 2016年08月16日 09:47
  • 422

Oracle 11g性能调优官方指南

Oracle 11g性能调优官方指南Oracle Database Performance Tuning Guide 11g Release 1 (11.1)http://docs.oracle.co...
  • Testing_is_believing
  • Testing_is_believing
  • 2012年02月14日 23:01
  • 4402

Oracle使用心得

1 截取的几种方法round() trunc()       trim(to_char(xxx,9999.99))2 锁oracle的锁普通是乐观锁(不用主动加,通过版本来约束)还有可以主动加的悲观锁...
  • stephenxu111
  • stephenxu111
  • 2008年05月09日 18:36
  • 2706
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle性能调优实践中的几点心得
举报原因:
原因补充:

(最多只允许输入30个字)