SQL代码优化Oracle数据库性能的案例分析辽宁工程技术大学摘要：以剖析某医院叫号程序出现的故障为例，诊断为不良SQL代码造成争用系统资源。通过采用绑定变量等措施有效降低SQL代码的逻辑读，并进一步总结了优化SQL代码的方法，阐明其提升Oracle数据库性能的重要性。关键字：SQL代码，Oracle，性能一．引言在生产环境中，可能会经常遇到CPU过度使用而影响Oracle数据库系统性能或异常运行的问题。大多数情况下，Oracle系统的性能问题都是由不良SQL代码引起的。作为DBA，怎样发现和解决这些SQL问题就显得尤为重要。通常，程序开发人员很少注意SQL代码的效率，而更着眼于功能的实现。至于性能问题通常被认为是次要的，而且在应用系统开发初期，由于数据库数据量较少，对于查询SQL语句等，不容易体会到各种SQL句法的性能差异。但是，一旦这些应用上线运行，随着数据库中数据量的增加，大量并发访问，系统的响应速度可能就会成为系统需要解决的最主要问题之一。在少量用户下性能可以接受的SQL，可能在大量用户并发的条件下就会成为性能瓶颈。二．问题描述某医院有两个服务器，属双机热备。通常，医院9:00~11:00点是业务高峰。在监控系统性能过程中，发现A服务器的CPU使用率在60%以下，而B服务器的却在80%左右，两个节点存在明显的差距。经提取报告，发现一条SQL语句执行得很频繁：SELECT*FROMT_callWHERE诊室='儿科一诊室'or诊室='儿科二诊室'or诊室='儿科专家诊室'or诊室='眼科诊室'or诊室='眼科专家诊室'or诊室='口腔科诊室'or诊室='口腔科专家诊室'or诊室='皮肤科诊室'or诊室='皮肤科专家诊室';经确认，是该医院管理系统HIS中的一个叫号程序的SQL，通过触发器将药品处方的信息写入到一个临时表，叫号程序每秒两次读取一次临时表，当叫号程序使用完毕后，再把临时表的记录delete掉。这条SQL执行频率太高，而实际业务上并不需要这么高的频率；再加上该段SQL没有使用绑定变量，故消耗了大量的CPU资源。三．分析及解决有效地降低SQL的逻辑读是SQL优化的基本原则之一。在ORACLE数据库中，适当地使用绑定变量可提高SQL的执行性能并节省了内存的使用。3.1绑定变量的使用Oracle中，对于一个提交的SQL语句，存在两种可选的解析过程，一种叫做硬解析，一种叫做软解析。一个硬解析需要经解析，制定执行路径，优化访问计划等许多的步骤。硬解析不仅耗费大量的CPU，更重要的是会占据重要的闩（latch）资源，严重的影响系统的规模的扩大（即限制了系统的并发行），而且引起的问题不能通过增加内存条和CPU的数量来解决。之所以这样是因为门闩是为了顺序访问以及修改一些内存区域而设置的，这些内存区域是不能被同时修改。当一个sql语句提交后，oracle会首先检查一下共享缓冲池（sharedpool）里有没有与之完全相同的语句，如果有的话只须执行软分析即可，否则需进行硬分析。而唯一使得oracle能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是用变量替代sql语句中的常量。绑定变量能够使得每次提交的sql语句都完全一样。如果在SQL*PLUS中，绑定变量的相关代码为：variablev_callnumberdeclare:v_call:=？;end;SELECT*FROMT_callWHERE诊室=:V_call;3.2修改NOLOGGING参数以减少日志量考虑到叫号程序查询的临时表，只是临时使用，使用完即删除，这样频繁的INSERT或UPDATE的表，如果使用NOLOGGING选项，能减少重做日志产生的量，对性能调整有一定的效果：ALTERTABLEHIS.T_callNOLOGGING;ALTERTABLEHIS.T_call_tempNOLOGGING;修改后经测试，两个节点的CPU使用率相似。需要注意的是：在适合的情况下，使用绑定变量可以提升效率，起到优化Oracle性能的作用。但在下述情形中，需避免使用：（1）对于间隔时间较长才执行一次的SQL语句，利用绑定变量的好处会被不能有效利用优化器而抵消。（2）数据仓库的OLAP情况下，其优势会被抵消。（3）绑定变量不能当作嵌入的字符串来使用，只能当作语句中的变量来用。不能用绑定变量来代替表名、过程名、字段名等。绑定变量只是起到占位的作用，同名的绑定变量并不意味着在它们是同样的，在传递时要考虑的是传递的值与绑定变量出现顺序的对位，而不是绑定变量的名称。（4）ORACLE只对简单的表提供高速缓冲（cachebuffering），但此功能并不适用于多表连接查询。（5）在字段（包括字段集）建有