SQL语句响应缓慢原因及优化

1    运行过程可能发生的原因及优化方案

?  SQL语句的运行过程

    1).使用hash算法得到sql语句的hash_value值

    2).如果hash_value值在内存中，叫做命中执行软解析

    3).如果hash_value值不存在，执行硬解析

    4).语法解析，查看是否有错误

    5).语意解析，查看权限是否符合

    6).若有视图，取出视图的定义

    7).进行sql语句的自动改写，如将子查询改写为连接

    8).选择最优的执行计划

    9).变量绑定

    10).运行执行计划

    11).返回结果给用户

?  下面针对其中几个不同阶段可能发生的各种问题做重点说明，并给出对应优化方案。

1.1  打开游标

?  游标cursor在服务器端分配内存以来对sql语句进行缓存区处理SQL解析。因此复用游标可以提高性能。

?  在这里简单说一下cursor_sharing参数三个不同选项（exact，similar，force）。

1).EXACT精确匹配，原语句不做处理，降低了sql的命中率，但可以保证执行计划是准确的，此种模式为系统默认的模式；                                                            2).SIMILAR近似匹配，将where条件都用变量处理，单可以区分列值的数据敏感性，一种折中的方案，但oracle在处理该类参数的sql语句时会有一定的问题，慎3).FORCE强制匹配，将where条件都用变量来处理，提高了SQL的命中率，但不能区分列值的数据敏感性，执行计划有时是正确的，但是有时会出现错误；           4).建议尽可能的保持系统默认的EXACT精确匹配模式，如需调整，建议在测试环境做好充足的验证。

2.2.1语法解析

?  语法解析不需要SGA，系统服务器进程直接进行处理，语法错误则直接显示。

2.2.2语义解析

?  解    释：语义校验需要数据字典中的信息来支撑

?  可能原因：Shared Pool中的Data Dictionary Cache空间过小或缓存信息不是本次需    要的数据，导致从磁盘的system表空间的数据文件中读取，性能降低。

?  优化方案：适当增加Shared pool 以便间接的增加Data Dictionary Cache的大小，以缓存更多的数据字典信息。

2.2.3生成执行计划

?  解    释：用户发出sql命令后，Server process会检查Shared Pool的Library Cache中是否存有有对应语句的执行计划。如果Shared Pool中的sql的命中率低于90%就需要对其进行优化。l

?  可能原因：1. Library Cache中没有的执行计划，进行硬解析（hard parse）                              2. Library Cache存有大量类似的执行计划

?  优化方案：1 首次执行一次即可，Library Cache便会保存此执行计划；适当的增加Shared pool 以便间接的增加Library Cache的大小，缓存更多的执行计划。                           2.优化SQL语句，书写程序时尽量不要过多的使用常量。（使用绑定变量，where department_id=&X）

1.2  运行执行计划

2.3.1寻找数据

?  解    释：服务器进程 查找SGA的Database Buffer Cache中是否存有对应数据块。

?  可能原因：1.对应数据快（例如如employees表）在内存中经常被换出。                                2.生成了错误的执行计划，如应走索引却进行了全盘扫描，反之亦然。

?  优化方案：1.将常用表缓存在Database Buffer Cache的Keep池中，以增加命中率                       2.引导CPU正确计算执行计划，如删除索引（前途是满足对应条件比如无外键关联等）、使用简单技巧避开索引（如对索引列进行+0函数运算等）；建立合适的索引（如在country列这种一般不会发生变化的列建立位图索引以提高效率）

2.3.2执行操作前

?  解    释：由于Oracle的日志提前写机制的存在，每次对数据进行操作前都会记录对应的操作日志，这一机制的存在可能会导致一些问题。

?  可能原因：1.Redo Log Buffer被占满、或过小（参数文件设置）                                                   2.检查点发生过于频繁，Redo Log Buffer中的数据以很快的频率写入到磁盘，内存利用率低，性能下滑                                                                                                          3.日志组成员分配空间过小，日志组数过少，日志切换切换频繁，循环复用因归档未完成而发生等待                                                                                                                 4.Undo 表空间过小，系统为保护原有数据不丢失而发生等待

?  优化方案：1.增加Redo Log Buffer的大小以缓存更多数据                                                           2.调整检查点发生频率，合理利用内存以提高效率                                                     3.增加日志组中成员大小，增加日志组个数，调整日志切换频率，关闭归档（生产库不建议）                                                                                                                               4.增大Undo表空间大小；调整Undo表空间为自动扩展模式 。

2.3.3执行过程中

?  可能原因：用户在执行更新或删除操作时，另一个用户在对同样的数据进行修改而发生锁等待。

?  优化方案：1.等待另一个用户进程commit或rollback操作；kill掉对应User；                        2.Oracle的Select For Update语句可以实现在读取数据后马上锁定相关资源，防止被其他session修改数据的目的。也就是我们常常谈到的“悲观锁定”（现实应用开发中，使用悲观锁定的情况少之又少，也许是因为乐观锁定的实现更加灵活和便捷的缘故）。

?  其他可能问题：1. 是否处在数据库工作高峰期执行操作                                                                           2. CPU、磁盘本身的性能问题

?  对应优化方案：1.尽量在数据库非高峰期执行操作

              2.硬件问题，更新硬件。

 

1.3  提取数据

?  解    释：SQL语句执行结束后要把执行结果返回给用户进程。

?  可能原因：1.返回数据时出现网络问题，数据传输缓慢或者中断。                                             2. 对处理结果进行了group by排序，导致PGA不够，启用临时表空间，性能低下。

?  优化方案：1找出网络问题并修复。                                                                                                          2.不进行排序操作；加大PGA空间大小。

1.4  关闭游标

?  可能原因：频繁对cursor进行开，关操作会降低游标的复用能力，降低系统性能。

?  优化方案：寻找平衡点。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29319205/viewspace-1062058/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29319205/viewspace-1062058/