对sql语句的优化问题

参照sql例句：

Select * from employees where employee_id=100;

Update  employees  set  employee_id =101 where employee_id=3;

优化问题：

运行sql语句的时间明显长于之前，需要对整体的操作系统进行优化分析。

思路分析：

   (通过对sql语句执行的各个过程进行分析以来查找优化项)。

SQL 语句执行过程详细分析：

<1> 打开游标cursor：

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

<2> 解析：

     语法校验：语法校验时不需要SGA，系统服务器进程直接进行处理，有错直接就会显示出来。

     语义校验：语义校验需要数据字典中的信息来支撑（数据字典存有用户信息，用户的权限信息，所有数据对象的结构信息，表的约束条件，统计分析数据库的视图等，数据字典逻辑存放在system表空间中，物理存放在数据文件中）。

在读取表的大量结构信息的时候，因为从磁盘数据文件直接取会导致系统性能缓慢，oracle引入了Data Dictionary Cache 组件以用来在内存中直接缓存数据字典中的信息，避免了系统从磁盘直接读取信息，提高了系统性能。如果Data Dictionary Cache 组件内存很小，或者缓存的信息不是本次所需要的数据，就需要从磁盘读取信息，会直接影响系统性能，此时，适当的增加Shared pool 以便间接的增加Data Dictionary Cache的内存，缓存更多的数据字典信息。（Data Dictionary Cache 组件提高了sql语句解析语义计划的效率）。

生成执行计划：当完成语法，语义校验后，生成执行计划并且缓存到shared pool 的library cache中以便下一次执行同样的SQL语句时不需要重新执行之前的操作（生成新的执行计划叫硬解析，直接复用之前的执行计划叫软解析）。反复做硬解析CPU浪费很严重，影响系统性能。因此可以通过适当增加library cache的内存大小，查看SQL语句并绑定变量，或者删除不常用的SQL执行计划以来将更多频繁使用的sql语句执行计划缓存到library cache中，以此来提高系统性能。

（shared pool 提高了对sql语句解析计划阶段的效率）

<3> 执行SQL语句

为了保证执行效率的提高，需要将数据从磁盘上的数据文件缓存到DataBase buffer cache中，直接从内存中对表进行操作，（执行阶段需考虑全盘扫描和走索引不同的情况，当有大量重复数据时全盘扫描比较快，当数据分布不均匀时索引比较快），在DataBase buffer cache中创建keep池，recycle池，default池（keep池用来缓存那些经常会被访问的表，并且使用的缓冲区独立于DEFAULT池和recycle池，平时要适当减少keep池的内存分配，使keep池的命中率维持在接近100%的数值。Recycle池用来保存那些不希望保存在内存中的表。一般没有细化内存分配时，系统默认只有一个default池。）

<4>提取结果并保存

     因为最终的结果是要通过net网络返回到应用服务器（客户服务端）上，所以如果网络出现问题，同样会导致用户执行sql语句时返回结果的性能降低。

     sqlprocess提取结果并把最终结果返回给客户端时，如果面临order by 排序时，将要进行PGA sort内存排，如果数据量很大，PGA不能满足内存排所需空间时，就需要与磁盘上的临时表空间进行存储分配交换，这个磁盘分配交换将要对临时文件频繁进行写和读，因为对磁盘频繁操作，导致系统性能大大降低。此时，需要将PGA优化，如果不得不使用临时文件时，就需要创建多个临时文件，并发降低读写压力，以达到优化效果。

<5>close cursor

     频繁对cursor进行开，关操作会降低游标的复用能力，降低系统性能。（找平衡点）

 

锁等待也可能是导致sql语句执行性能降低的一个原因，所谓的锁等待:就是一个事务a对一个数据表进行ddl或是dml操作时，系统就会对该表加上表级的排它锁，此时其他的事务对该表进行操作的时候会等待a提交或是回滚后，才可以继续b的操作。当一个用户a进行ddl或者dml操作却没提交或者回滚，用户b对相同行进行操作就会一直等待用户a提交或者回滚，导致sql语句执行时间增长。

当对表进行DML操作时：

<1><2>解析执行如上所示；

<3> 执行SQL语句

      为了保证修改数据的动作能够被数据库恢复过来，引入了redo机制，如果redo信息足够多时，当系统频繁向磁盘写入日志文件时就容易导致系统性能低下，降低整个数据库的运行效率，因此为了提高日志提交效率，引入了Redolog buffer cache组件。当发现问题为日志文件时，应该适当增加Redolog buffer cache的内存，redo file的大小和日志文件组以来优化dml操作的性能问题。

<4><5>如上

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

转载于:http://blog.itpub.net/29366942/viewspace-1062236/