当数据库变慢时，我们应如何入手

偏执的太偏执、 2022-08-24 05:22 105阅读 0赞

** 当数据库变慢时，我们应如何入手**

第一章 检查系统的状态  
1.1 使用sar来检查操作系统是否存在IO问题  
1.2 关注内存vmstat  
1.3 找到使用资源特别大的Oracle的session及其执行的语句  
1.4 查找前十条性能差的sql语句

第二章 检查会话状态

> 当应用管理员通告现在应用很慢、数据库很慢时，当Oracle DBA在数据库上做几个示例的Select也发现同样的问题时，有些时侯就会无从下手，因为DBA认为数据库的各种命种率都是满足Oracle文档的建议。实际上如今的优化己经向优化等待(waits)转型了，实际中性能优化最根本的出现点也都集中在I/O，这是影响性能最主要的方面，由系统中的等待去发现Oracle库中的不足、操作系统某些资源利用的不合理是一个比较好的办法。下面把一些实践经验与大家分享，本文测重于Unix环境。 
> 
> 第一章 检查系统的状态  
> 通过操作系统的一些工具检查系统的状态，比如CPU、内存、交换、磁盘的利用率，根据经验或与系统正常时的状态相比对，有时系统表面上看起来看空闲，这也可能不是一个正常的状态，因为cpu可能正等待IO的完成。除此之外，还应观注那些占用系统资源(cpu、内存)的进程。 
> 
> 1.1 使用sar来检查操作系统是否存在IO问题  
> \#sar -u 2 10 -- 即每隔2秒检察一次，共执行20次。  
> 结果示例：  
> 注：在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  
> 其中：  
> Ø %usr指的是用户进程使用的cpu资源的百分比；  
> Ø %sys指的是系统资源使用cpu资源的百分比；  
> Ø %wio指的是等待io完成的百分比，这是值得观注的一项；  
> Ø %idle即空闲的百分比。  
> 如果wio列的值很大，如在35%以上，说明系统的IO存在瓶颈，CPU花费了很大的时间去等待I/O的完成。Idle很小说明系统CPU很忙。像以上的示例，可以看到wio平均值为11，说明I/O没什么特别的问题，而idle值为零，说明cpu已经满负荷运行了。  
> 当系统存在IO问题时，可以从以下几个方面解决：  
> Ø 联系相应的操作系统的技术支持对这方面进行优化，比如hp-ux在划定卷组时的条带化等方面。  
> Ø 查找Oracle中不合理的sql语句，对其进行优化；  
> Ø 对Oracle中访问量频繁的表除合理建索引外，再就是把这些表分表空间存放以免访问上产生热点，再有就是对表合理分区。 
> 
> 1.2 关注内存  
> 常用的工具便是vmstat，对于hp-unix来说，可以用glance。Aix来说可以用topas。当发现vmstat中pi列非零，memory中的free列的值很小，glance、topas中内存的利用率多于80%时，这时说明内存方面应该调节一下。方法大体有以下几项：  
> Ø 划给Oracle使用的内存不要超过系统内存的1/2，一般保在系统内存的40%为益。  
> Ø 为系统增加内存；  
> Ø 如果你的连接特别多，可以使用MTS的方式；  
> Ø 打全补丁，防止内存漏洞。 
> 
> 1.3 找到使用资源特别大的Oracle的session及其执行的语句  
> Hp-unix可以用glance或top。IBM AIX可以用topas。此外可以使用ps的命令。  
> 通过这些程序可以找到点用系统资源特别大的这些进程的进程号，就可以通过以下的sql语句发现这个pid正在执行哪个sql，这个sql最好在pl/sql developer、toad等软件中执行：  
> 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) 
> 
> 1.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。以下是参考脚本：  
> \-- 脚本说明：查看占I/O较大的正在运行的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已经发生的物理读排的序，因为它与实际的I/O相关。  
> 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做以简要的说明：  
> 1） buffer busy waits，free buffer waits这两个参数所标识是dbwr是否够用的问题，与IO很大相关的，当v$session\_wait中的free buffer wait的条目很小或没有时，说明系统的dbwr进程决对够用，不用调整；free buffer wait的条目很多，系统感觉起来一定很慢，这时说明dbwr已经不够用了，它产生的wio已经成为数据库性能的瓶颈，这时的解决办法如下：  
> Ø 增加写进程，同时要调整db\_block\_lru\_latches参数：  
> 示例：修改或添加如下两个参数  
> db\_writer\_processes=4  
> db\_block\_lru\_latches=8  
> Ø 开异步IO。IBM这方面简单得多，hp则麻烦一些，可以与Hp工程师联系。  
> 2） db file sequential read，指的是顺序读，即全表扫描，这也是应尽量减少的部分，解决方法就是使用索引、sql调优，同时可以增大db\_file\_multiblock\_read\_count这个参数。  
> 3） db file scattered read参数指的是通过索引来读取，同样可以通过增加db\_file\_multiblock\_read\_count这个参数来提高性能。  
> 4） latch free与栓相关，需要专门调节。  
> 5） 其他参数可以不特别观注。
> 
>