oracle 数据库sql调优.doc

Forpersonaluseonlyinstudyandresearch;mercialuseOracle数据库性能优化(SQL调优篇)引言对于数据库系统来说,优化是一件非常重要而且烦琐的工作。通过优化我们可以大大的提高系统运行效率和存储空间的利用率,达到用有限的资源实现一个高效的应用系统;说它烦琐是因为它涉及的面太宽了,从系统的规划、库表的设计、sql语句的编写、物理设备和网络设备的性能、内存和存储空间的分配等等都影响着系统的性能。正因为它的烦琐,才给系统优化工作者们提供了一个施展才华的舞台。对于sql是一个基础简单却有是较难写好的查询语言,它的好坏直接影响系统的性能;本篇将从sql查询的内部原理、oracle数据库服务器是怎么处理sql的、oracle数据库sql优化原则、oraclesql怎么优化,对于sql怎样使用就没有作说明。Sql查询内部原理查询在处理过程中分为四个大的阶段:将查询转换为内部格式阶段、将内部格式转换为规范格式阶段、为执行选择低层过程阶段、生成并选择最低的查询计划阶段,如图:目录表源查询DML处理器编译后查询元数据结果源查询查询计划运行时管理器优化器数据库编译视图处理翻译关系代数表达式表达式转换代价估算优化后的代码执行阶段1:将查询转换为内部格式阶段这一阶段主要是进行语法分析,将原查询转换为数据库内部格式以便于机器处理,不符合语法规范的报错返回,为sql优化过程铺平道路。阶段2:将内部格式转换为规范格式在这一阶段,数据库优化器将执行一系列“保证能够优化”的优化过程,是不会去考虑实际数据的值和数据库的存取路径;优化器将查询的内部表示转换为等价的规范格式。比如说将“A=B替换为B=A或者是pandq替换为qandp”,这样做的目的是消除语句表面上的差异,以便能够找到一种在某些方面比原查询更为高效的表示方法。比如说能够将表达式(AJOINB)WHEREretrictiononA转为等价高效的表达式(AWHERErestrictiononA)JOINB。阶段3:为执行选择低层过程在这一阶段,优化器考虑的是索引、物理存取路径、数据值的分布、数据的物理聚集存储等问题。基本的策略就是将查询表达式看成一系列的“低层操作”,对于每一个可能的低层操作,都有一组可用的低层过程,而每一个低层过程都会有一个相关的代价计算公式(磁盘i/o代价,cpu利用率)。阶段4:生成并选择最低代价的查询计划这一阶段就是构造一组查询计划,选择一个最优,也就是代价最小的查询计划。因为每个计划都绑定一系列的低层过程,每一个过程对应查询中的一个低层操作。优化器此时将所有的过程进行计算,选择一个代价最低的执行。那么oracle又是怎么来处理sql的呢?我们来看看:oracle分析处理sql的简单过程oracle在处理SQL语句上我们简单的概括为的三个阶段:语法分析、OK用户进程服务器进程解析StatementOKHandle执行Handle提取Results执行、返回指令(如图)Select过程:用户select查询语句发送至server,server在共享池进行语法分析和生成执行计划,然后由后台专有server从磁盘读取数据到databuffer,并且返回给用户,整个过程如图:Server共享池数据缓冲区SGAUserUserUserB1B2EMPTableBlock1Block2UPDATE操作过程:为了支持读一致性,恢复和回滚,所有修改操作需要回滚段。修改操作执行:(1)将数据块送到数据缓冲区(2)将回滚块送到数据缓冲区(3)在修改行上设置行锁(4)保存回滚数据到回滚段块(5)将修改写到数据块Update、select在oracle并行处理:如图Server1UserUserUser1Server2UserUserUser2共享池数据缓冲区SGAB1B2R2R1EMPTableBlock1Block2RB01R1R2DataFile1DataFile2Insert和delete操作和update类似,这里不作论述。我们在对查询的原理、oracle内部查询机制作了一些简单讨论后,我们现在开始讨论oracle的sql优化:oraclesql优化原则Oracle的SQL调优是一个非常复杂的主题,可以豪不夸张的说需要一本书的内容来介绍OracleSQL调优的细微差别。不过有一些基本的规则是我们每个开发人员都需要跟从的,这些规则可以改善我们系统的性能。oraclesql优化的原则是:消除不必要的大表全表搜索、最优的索引使用、最优的JOIN操作。消除不必要的大表全表搜索:不必要的全表搜索导致大量不必要的I/O,从而拖慢整个数据库的性能。调优专家首先会根据查询返回的行数目来评价SQL。在一个有序的表中,如果查询返回少于40%的行,或者在一个无序的表中,返回少