Oracle10g数据库优化实用心得小结

  很多的时侯,做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

  注:我在redhat下查看是这种结果,不知%system就是所谓的%wio

Linux 2.4.21-20.ELsmp (YY075) 05/19/2005
10:36:07 AM CPU %user %nice %system %idle
10:36:09 AM all 0.00 0.00 0.13 99.87
10:36:11 AM all 0.00 0.00 0.00 100.00
10:36:13 AM all 0.25 0.00 0.25 99.49
10:36:15 AM all 0.13 0.00 0.13 99.75
10:36:17 AM all 0.00 0.00 0.00 100.00
10:36:17 AM CPU %user %nice %system %idle
10:36:19 AM all 0.00 0.00 0.00 100.00
10:36:21 AM all 0.00 0.00 0.00 100.00
10:36:23 AM all 0.00 0.00 0.00 100.00
10:36:25 AM all 0.00 0.00 0.00 100.00

  其中的%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=@#ORCL@# 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=;
Select * from v$sqltext where 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 用户组来说,Marcia Pollard(2003 年去世)同样也很重要,她致力于研究ODTUG,即使我 们没有按时完成报告书,她也会保持优雅的风度。Marcia 是出色的人士!我们要记住充满热情的、聪明的 Lex de Haan(2006 年2 月1 日去世),他是Oracle 方面的专家和Oracle 的优秀讲师,并且是世界级的性 能调整和优化专家。我们要记住Mark Beaton(2006 年8 月去世),他是一位充满热情的Oracle 解决方案推 销员和优秀的英式足球运动员。我们也要记住Ray Mansfiled(2006 年11 月去世),他是一位才华横溢的 Warehouse Builder 顾问(脸上总是带着幸福的微笑)。最后,我们还要记住杰出的Elaine DeMeo(2007 年2 月11 日去世),他是优秀的MOUG 和IOUG 技 术支持人员。当某一天我们完成自己的工作时,上帝会带我们 回家,我们将在那儿和这些朋友再次见面,并且“在铺满金子的街道上和天使一起奔跑”。我期待那一 天 的到来,但是在此之前,我们将继续完善自己的工作,确保将我们的杰出成果互相传达!通过始终寻求改 进诚实、知识、勇气、忠诚、自律、热情、无私、机敏、 尊敬、谦卑和主动等方面的品质,我们确保可以 坚强地面对将来任何残酷的挑战。当然,永远不要忘记信念、希望、爱……这些品质中最重要的就是爱。 不断改进自己的品质,并且心中总是想着其他人的优点!这就是我的生活目标!
相关推荐
在《让Oracle跑得更快:Oracle 10g性能分析与优化思路》里读者将会学到作者在性能优化方面的一些思路和思考,一些故障处理的方法和原则,这些东西是作者在实践中长期积累的心得体会,当读者掌握了一些处理问题的基本思路之后,成为一名合格的DBA就是一件轻而易举的事情了。 《让Oracle跑得更快:Oracle 10g性能分析与优化思路》适用对象:Oracle DBA、Oracle开发人员,和其他对Oracle数据库感兴趣的人员。 目录: 第1章 引起数据库性能问题的因素 1 1.1 软件设计对数据库的影响 1 1.1.1 软件架构设计对数据库性能的影响 1 1.1.2 软件代码的编写对数据库性能的影响 2 1.2 数据库的设计 8 1.2.1 oltp数据库 9 1.2.2 olap数据库 10 1.3 数据库的硬件设计 14 1.3.1 存储容量 15 1.3.2 存储的物理设计 16 1.3.3 数据的安全 17 1.4 小结 19 第2章 锁和阻塞 20 2.1 关于锁 20 2.2 锁和阻塞 22 2.3 引起阻塞的其他情况 30 2.3.1 select for update 30 2.3.2 外键和索引 36 第3章 latch和等待 44 3.1 共享池中的latch争用 45 .3.2 数据缓冲池latch争用 54 3.2.1 表数据块 54 3.2.2 索引数据块 59 3.2.3 索引根数据块 62 3.2.4 段头数据块 65 第4章 优化器 66 4.1 rbo基于规则的优化器 66 4.2 cbo基于成本的优化器 69 第5章 执行计划 85 5.1 cardinality (基数) 85 5.2 sql的执行计划 94 第6章 hint 109 6.1 和优化器相关的hint 115 6.1.1 all_rows和first_rows(cbo) 115 6.1.2 rule hint 117 6.2 访问路径相关的hint 117 6.2.1 full hint 118 6.2.2 index hint 118 6.2.3 no_index hint 118 6.2.4 index_desc hint 119 6.2.5 index_combine hint 119 6.2.6 index_ffs 119 6.2.7 index_join 120 6.2.8 index_ss hint 120 6.3 表关联顺序的hint 125 6.3.1 leading hint 125 6.3.2 ordered hint 126 6.4 表关联操作的hint 127 6.4.1 use_hash,use_nl和use_merge hint 127 6.4.2 no_use_hash hint 132 6.4.3 no_use_merge hint 133 6.4.4 no_use_nl hint 133 6.5 并行执行相关的hint 134 6.5.1 parallel hint 134 6.5.2 no_parallel hint 134 6.6 其他方面的一些hint 135 6.6.1 append hint 135 6.6.2 dynamic_sampling hint 135 6.6.3 driving_site hint 136 6.6.4 cache hint 136 6.7 小结 136 第7章 分析及动态采样 138 7.1 直方图 141 7.2 dbms_stats包 147 7.3 动态采样 176 7.3.1 什么是动态采样 176 7.3.2 动态采样的级别 182 7.3.3 什么时候使用动态采样? 185 7.4 小结 185 第8章 并行执行 186 8.1 并行和olap系统 187 8.2 并行处理的机制 189 8.3 读懂一个并行处理的执行计划 191 8.4 一个很常见的并行执行等待事件 192 8.5 并行执行的适用范围 194 8.5.1 并行查询 194 8.5.2 并行ddl操作 195 8.5.3 并行dml操作 203 8.6 并行执行的设定 210 8.6.1 并行相关的初始化参数 210 8.6.2 并行度的设定 211 8.7 直接加载 213 8.7.1 直接加载和redo 216 8.7.2 直接加载和索引 219 8.7.3 直接加载和并行 221 8.7.4 直接加载和sql*loader 226 第9章 变量绑定 232 9.1 什么是变量绑定,为什么要做变量绑定 232 9.2 为什么说oltp必须要求变量绑定而olap不应该绑定变量 241 9.3 bind peaking 248 第10章 sql_trace和10046事件 254 10.1 sql_trace 254 10.2 tkprof工具 256 10.3 10046事件 268 第11章 10053事件 276 第12章 性能视图和性能参数 294 12.1 性能视图 294 12.1.1 v$sql 295 12.1.2 v$sql_shared_cursor 300 12.1.3 v$session 305 12.1.4 v$sessstat 309 12.1.5 v$session_wait 310 12.2 性能参数 312 12.2.1 cursor_sharing 313 12.2.2 db_file_multiblock_read_count 328 12.2.3 pga_aggregate_target和sga_target 334 12.2.4 optimizer_dynamic_sampling 334 第13章 性能报告 335 13.1 awr性能报告 335 13.1.1 生成awr性能报告 337 13.1.2 awr性能报告分析 342 13.2 statspack性能报告 386 13.2.1 statspack的安装 386 13.2.2 statspack性能采集 391 13.3 ash性能报告 394 13.3.1 生成ash性能报告 395 13.3.2 ash性能报告分析 405 13.4 小结 416 附录a 常见的等待事件 417 后记 关于数据库的学习方法 434
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页