ORACLE SQL性能优化ppt课件

ORACLE培训 SQL性能优化 内容概述 课程主要讨论 SQL语句执行的过程 ORACLE优化器 表之间的关联 如何得到SQL执行计划 如何分析执行计划等内容 从而由浅到深的方式了解SQL优化的过程 使大家逐步掌握SQL优化 目录 优化基础知识性能调整综述有效的应用设计SQL语句的处理过程Oracle的优化器Oracle的执行计划注意事项 一 优化基础知识 概述 性能管理性能问题调整的方法SQL优化机制应用的调整SQL语句的处理过程共享SQL区域SQL语句处理的阶段共享游标SQL编码标准Oracle优化器介绍SQLTunningTips优化Tools 性能管理 尽早开始设立合适目标边调整边监控相关人员进行合作及时处理过程中发生的意外和变化80 20定律 SQL优化衡量指标 随着软件技术的不断发展 系统性能越来越重要 系统性能主要有 系统响应时间和并发性来衡量 造成SQL语句性能不佳大致有两个原因 开发人员只关注查询结果的正确性 忽视查询语句的效率 开发人员只关注SQL语句本身的效率 对SQL语句执行原理 影响SQL执行效率的主要因素不清楚 前者可以通过深入学习SQL语法及各种SQL调优技巧进行解决 SQL调优是一个系统工程 熟悉SQL语法 掌握各种内嵌函数 分析函数的用法只是编写高效SQL的必要条件 后者从分析SQL语句执行原理入手 指出SQL调优应在优化SQL解析和优化CBO上 调优领域 调优领域 应用程序级调优 SQL语句调优 管理变化调优2 实例级调优 内存 数据结构 实例配置3 操作系统交互 I O SWAP Parameters本课程内容只讲解讨论应用程序级 OracleSQL语句调优及管理变化调优 调整的方法 调整业务功能调整数据设计调整流程设计调整SQL语句调整物理结构调整内存分配调整I O调整内存竞争调整操作系统 不同调整产生相应性能收益 调整的角色 SQL语句优化是提高性能的重要环节 开发人员不能只注重功能的实现 不管性能如何开发人员不能把Oracle当成一个黑盒子 必须了解其结构 处理SQL和数据的方法必需遵守既定的开发规范未经过SQL语句优化的模块不要上线 SQL语句优化的过程 定位有问题的语句检查执行计划检查执行过程中优化器的统计信息分析相关表的记录数 索引情况改写SQL语句 使用HINT 调整索引 表分析有些SQL语句不具备优化的可能 需要优化处理方式达到最佳执行计划 什么是好的SQL语句 尽量简单 模块化易读 易维护节省资源内存CPU扫描的数据块要少少排序不造成死锁 SQL共享原理 ORACLE将执行过的SQL语句存放在内存的共享池 sharedbufferpool 中 可以被所有的数据库用户共享 当你执行一个SQL语句 有时被称为一个游标 时 如果它和之前的执行过的语句完全相同 ORACLE就能很快获得已经被解析的语句以及最好的执行路径 这个功能大大地提高了SQL的执行性能并节省了内存的使用 SQL共享原理 为了不重复解析相同的SQL语句 在第一次解析之后 Oracle将SQL语句存放在内存中 这块位于系统全局区域SGA systemglobalarea 的共享池 sharedbufferpoo1 中的内存可以被所有的数据库用户共享 因此 当你执行一个SQL语句 有时被称为一个游标 时 如果它和之前执行过的语句完全相同 Oracle就能很快获得已经被解析的语句以及最好的执行方案 Oracle的这个功能大大地提高了SQL的执行性能并节省了内存的使用 可惜的是 Oracle只对简单的表提供高速缓冲 cachebufferiIlg 这个功能并不适用于多表连接查询 数据库管理员必须在启动参数文件中为这个区域设置合适的参数 当这个内存区域越大 就可以保留更多的语句 当然被共享的可能性也就越大了 当向Oracle提交一个SQL语句时 Oracle会首先在这块内存中查找相同的语句 SQL共享的三个条件 当前被执行的语句和共享池中的语句必须完全相同 包括大小写 空格 换行等 两个语句所指的对象必须完全相同 同义词与表是不同的对象 两个SQL语句中必须使用相同的名字的绑定变量 bindvariables 共享SQL语句 注意 Oracle对两者采取的是一种严格匹配策略 要达成共享 SQL语句必须完全相同 包括空格 换行等 能够使用共享的语句必须满足三个条件 字符级的比较 当前被执行的语句和共享池中的语句必须完全相同 例如 SELECT FROMATABLE 和下面每一个SQL语句都不同 SELECT fromATABLESelect FromAtable 语句所指对象必须完全相同即两条SQL语句操作的数据库对象必须同一 语句中必须使用相同命名的绑定变量 如 第一组的两个SQL语句是相同的 可以共享 而第二组中两个语句不同 即使在运行时赋予不同的绑定变量以相同的值 第一组selectpin namefrompeoplewherepin blk1 pin selectpin namefrompeoplewherepin blk1 pin 第二组selectpin namefrompeoplewherepin blk1 ot jnd selectpin namefrompeoplewherepin blk1 ov jnd SQL语句的处理过程 共享SQL区域 Sql处理过程 SQLPARSE与共享SQL语句 当一个Oracle实例接收一条sql后1 CreateaCursor创建游标2 ParsetheStatement分析语句3 DescribeResultsofaQuery描述查询的结果集4 DefineOutputofaQuery定义查询的输出数据5 BindAnyVariables绑定变量6 ParallelizetheStatement并行执行语句7 RuntheStatement运行语句8 FetchRowsofaQuery取查询出来的行9 ClosetheCursor关闭游标 为什么要bindvariables 字符级的比较 SELECT FROMUSER FILESWHEREUSER NO 10001234 与SELECT FROMUSER FILESWHEREUSER NO 10004321 检查 selectname executionsfromv db object cachewherenamelike select fromuser files 什么叫做重编译问题 什么叫做重编译 下面这个语句每执行一次就需要在SHAREPOOL硬解析一次 一百万用户就是一百万次 消耗CPU和内存 如果业务量大 很可能导致宕库 如果绑定变量 则只需要硬解析一次 重复调用即可select fromdConMsgwherecontract no 32013484095139 绑定变量解决重编译问题 未使用绑定变量的语句sprintf sqlstr insertintoscott test1 num1 num2 values d d n var1 n var2 EXECSQLEXECUTEIMMEDIATE sqlstr EXECSQLCOMMIT 使用绑定变量的语句strcpy sqlstr insertintotest num1 num2 values v1 v2 EXECSQLPREPAREsql stmtFROM sqlstr EXECSQLEXECUTEsql stmtUSING n var1 n var2 EXECSQLCOMMIT 绑定变量的注意事项 注意 1 不要使用数据库级的变量绑定参数cursor sharing来强制绑定 无论其值为force还是similar2 有些带 的语句绑定变量后可能导致优化器无法正确使用索引 SQL语句的四个处理阶段 SQL语句的处理过程 解析 PARSE SQL语句的处理过程 在共享池中查找SQL语句检查语法检查语义和相关的权限合并 MERGE 视图定义和子查询确定执行计划 绑定 BIND SQL语句的处理过程 在语句中查找绑定变量赋值 或重新赋值 执行 EXECUTE SQL语句的处理过程 应用执行计划执行必要的I O和排序操作 提取 FETCH 从查询结果中返回记录必要时进行排序使用ARRAYFETCH机制 共享游标 好处 减少解析动态内存调整提高内存使用率 书写可共享的SQL 绑定变量和共享游标 ORACLE优化器模式概述 Oracle的优化器共有3种模式 RULE 基于规则 COST 基于成本 CHOOSE 基于选择 设置缺省的优化器的方法 是在启动参数文件中针对OPTIMIZER MODE参数的各种声明进行选择 如RULE COST CHOOSE ALL ROWS FIRST ROWS 当然也可以在SQL语句级别或是会话级别对其进行覆盖 为了使用基于成本的优化器 CBO Cost BasedOptimizer 必须经常运行analyze命令 以增加数据库中的对象统计信息 objectstatistics 的准确性 如果数据库的优化器模式设置为基于选择 那么实际的优化器模式将和是否运行过analyze命令有关 如果数据表已经被analyze过 优化器模式将自动切换成CBO 反之 数据库将采用RULE形式的优化器 在缺省情况下 Oracle采用CHOOSE优化器 为避免那些不必要的全表扫描 必须尽量避免使用CHOOSE优化器 而直接采用基于规则或者基于成本的优化器 访问数据表的方式 全表扫描全表扫描就是顺序地访问表中每条记录 Oracle采用一次读入多个数据块 databaseblock 的方式优化全表扫描 通过ROWID访问表ROWID包含了表中记录的物理位置信息 可以采用基于ROWID的访问方式情况提高访问表的效率 Oracle采用索引实现了数据和存放数据的物理位置 ROWID 之间的联系通常索引提供了快速访问ROWID的方法 因此那些基于索引列的查询就可以得到性能的提高 数据库性能 影响数据库系统性能的要素 主机CPU RAM 存储系统 OS参数配置 ORACLE参数配置 应用方面 数据库设计及SQL编程的质量一个性能优秀的应用系统需要 良好的硬件配置 正确合理的数据库及中间件参数配置 合理的数据库设计 良好的sql编程 运行期的性能优化 SQLTunning的重点 SQL insert update delete select 主要关注的是select关注的是 如何用最小的硬件资源消耗 最少的响应时间定位数据位置 SQL优化的一般性原则 目标 减少服务器资源消耗 主要是磁盘IO 设计方面 尽量依赖oracle的优化器 并为其提供条件 合适的索引 索引的双重效应 列的选择性 编码方面 利用索引 避免大表FULLTABLESCAN 合理使用临时表 避免写过于复杂的sql 不一定非要一个sql解决问题 在不影响业务的前提下减小事务的粒度 优化概括 Oracle数据库SQL语句优化的总体策略 以这些优化策略为指导 通过经验总结 我们可以不断地丰富优化方案 进而指导我们进行应用系统的数据库性能优化 以下枚举几则被证明行之有效的优化方案 创建表的时候 应尽量建立主键 尽量根据实际需要调整数据表的PCTFREE和PCTUSED参数 大数据表删除 用truncatetable代替delete 合理使用索引 在OLTP应用中一张表的索引不要太多 数据重复量大的列不要建立二叉树索引 可以采用位图索引 组合索引的列顺序尽量与查询条件列顺序保持一致 对于数据操作频繁的表 索引需要定期重建 以减少失效的索引和碎片 优化概括 查询尽量用确定的列名 少用 号 selectcount key fromtabwherekey 0性能优于selectcount fromtab 尽量少嵌套子查询 这种查询会消耗大量的CPU资源 对于有比较多or运算的查询 建议分成多个查询 用unionall联结起来 多表查询的查询语句中 选择最有效率的表名顺序 Oracle解析器对表解析从右到左 所以记录少的表放在右边 尽量多用commit语句提交事务 可以及时释放资源 解锁 释放日志空间 减少管理花费 在频繁的 性能要求比较高的数据操作中 尽量避免远程访问 如数据库链等 访问频繁的表可以常驻内存 altertable cache 在Oracle中动态执行SQL 尽量用execute方式 不用dbms sql包 SQLTunningTips sql语句的编写原则和优化 随着数据库中数据的增加 系统的响应速度就成为目前系统需要解决的最主要的问题之一 系统优化中一个很重要的方面就是SQL语句的优化 对于大量数据 劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍 对于一个系统不是简单地能实现其功能就可 而是要写出高质量的SQL语句 提高系统的可用性 在多数情况下 Oracle使用索引来更快地遍历表 优化器主要根据定义的索引来提高性能 如果在SQL语句的where子句中写的SQL代码不合理 就会造成优化器删去索引而使用全表扫描 一般就这种SQL语句就是所谓的劣质SQL语句 sql语句的编写原则和优化 在编写SQL语句时我们应清楚优化器根据何种原则来使用索引 这有助于写出高性能的SQL语句 SQL语句的编写原则和SQL语句的优化 请跟我一起学习以下几方面 TunningTip的各个方面 1 不要让Oracle做得太多 2 给优化器更明确的命令 3 减少访问次数 4 细节上的影响 1 不要让Oracle做得太多 避免复杂的多表关联 select fromuser filesuf df money filesdm cw charge recordccwhereuf user no dm user noanddm user no cc user noand andnotexists select 很难优化 随着数据量的增加性能的风险很大 避免使用 当你想在SELECT子句中列出所有的COLUMN时 使用动态SQL列引用 是一个方便的方法 不幸的是 这是一个非常低效的方法 实际上 ORACLE在解析的过程中 会将 依次转换成所有的列名 这个工作是通过查询数据字典完成的 这意味着将耗费更多的时间 只提取你所要使用的列 使用别名能够加快解析速度 避免使用耗费资源的操作 带有DISTINCT UNION MINUS INTERSECT ORDERBY的SQL语句会启动SQL引擎执行耗费资源的排序 SORT 功能 DISTINCT需要一次排序操作 而其他的至少需要执行两次排序 例如 一个UNION查询 其中每个查询都带有GROUPBY子句 GROUPBY会触发嵌入排序 NESTEDSORT 这样 每个查询需要执行一次排序 然后在执行UNION时 又一个唯一排序 SORTUNIQUE 操作被执行而且它只能在前面的嵌入排序结束后才能开始执行 嵌入的排序的深度会大大影响查询的效率 通常 带有UNION MINUS INTERSECT的SQL语句都可以用其他方式重写 例如 低效 SELECTDISTINCTDEPT NO DEPT NAMEFROMDEPTD EMPEWHERED DEPT NO E DEPT NO高效 SELECTDEPT NO DEPT NAMEFROMDEPTDWHEREEXISTS SELECT X FROMEMPEWHEREE DEPT NO D DEPT NO 用EXISTS替换DISTINCT 用UNION ALL替换UNION ifpossible 当SQL语句需要UNION两个查询结果集合时 这两个结果集合会以UNION ALL的方式被合并 然后在输出最终结果前进行排序 举例 低效 SELECTACCT NUM BALANCE AMTFROMDEBIT TRANSACTIONSWHERETRAN DATE 31 DEC 95 UNIONSELECTACCT NUM BALANCE AMTFROMDEBIT TRANSACTIONSWHERETRAN DATE 31 DEC 95 高效 SELECTACCT NUM BALANCE AMTFROMDEBIT TRANSACTIONSWHERETRAN DATE 31 DEC 95 UNIONALLSELECTACCT NUM BALANCE AMTFROMDEBIT TRANSACTIONSWHERETRAN DATE 31 DEC 95 2 给优化器更明确的命令 自动选择索引 如果表中有两个以上 包括两个 索引 其中有一个唯一性索引 而其他是非唯一性 在这种情况下 ORACLE将使用唯一性索引而完全忽略非唯一性索引 举例 SELECTENAMEFROMEMPWHEREEMPNO 2326ANDDEPTNO 20 这里 只有EMPNO上的索引是唯一性的 所以EMPNO索引将用来检索记录 TABLEACCESSBYROWIDONEMPINDEXUNIQUESCANONEMP NO IDX 至少要包含组合索引的第一列 如果索引是建立在多个列上 只有在它的第一个列 leadingcolumn 被where子句引用时 优化器才会选择使用该索引 SQL createtablemultiindexusage indanumber indbnumber descrvarchar2 10 Tablecreated SQL createindexmultindexonmultiindexusage inda indb Indexcreated SQL setautotracetraceonlySQL select frommultiindexusagewhereinda 1 ExecutionPlan 0SELECTSTATEMENTOptimizer CHOOSE10TABLEACCESS BYINDEXROWID OF MULTIINDEXUSAGE 21INDEX RANGESCAN OF MULTINDEX NON UNIQUE SQL select frommultiindexusagewhereindb 1 ExecutionPlan 0SELECTSTATEMENTOptimizer CHOOSE10TABLEACCESS FULL OF MULTIINDEXUSAGE 很明显 当仅引用索引的第二个列时 优化器使用了全表扫描而忽略了索引 避免在索引列上使用函数 WHERE子句中 如果索引列是函数的一部分 优化器将不使用索引而使用全表扫描 举例 低效 SELECT FROMDEPTWHERESAL 12 25000 高效 SELECT FROMDEPTWHERESAL 25000 12 避免使用前置通配符 WHERE子句中 如果索引列所对应的值的第一个字符由通配符 WILDCARD 开始 索引将不被采用 SELECTUSER NO USER NAME ADDRESSFROMUSER FILESWHEREUSER NOLIKE 109204421 在这种情况下 ORACLE将使用全表扫描 避免在索引列上使用NOT 通常 我们要避免在索引列上使用NOT NOT会产生在和在索引列上使用函数相同的影响 当ORACLE 遇到 NOT 他就会停止使用索引转而执行全表扫描 举例 低效 这里 不使用索引 SELECT FROMDEPTWHEREDEPT CODENOT 0 高效 这里 使用了索引 SELECT FROMDEPTWHEREDEPT CODE 0 避免在索引列上使用ISNULL和ISNOTNULL 避免在索引中使用任何可以为空的列 ORACLE将无法使用该索引 对于单列索引 如果列包含空值 索引中将不存在此记录 对于复合索引 如果每个列都为空 索引中同样不存在此记录 如果至少有一个列不为空 则记录存在于索引中 如果唯一性索引建立在表的A列和B列上 并且表中存在一条记录的A B值为 123 null ORACLE将不接受下一条具有相同A B值 123 null 的记录 插入 然而如果所有的索引列都为空 ORACLE将认为整个键值为空而空不等于空 因此你可以插入1000条具有相同键值的记录 当然它们都是空 因为空值不存在于索引列中 所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引 任何在where子句中使用isnull或isnotnull的语句优化器是不允许使用索引的 避免出现索引列自动转换 当比较不同数据类型的数据时 ORACLE自动对列进行简单的类型转换 假设EMP TYPE是一个字符类型的索引列 SELECTUSER NO USER NAME ADDRESSFROMUSER FILESWHEREUSER NO 109204421这个语句被ORACLE转换为 SELECTUSER NO USER NAME ADDRESSFROMUSER FILESWHERETO NUMBER USER NO 109204421因为内部发生的类型转换 这个索引将不会被用到 在查询时尽量少用格式转换 如用WHEREa order no b order no不用WHERETO NUMBER substr a order no instr b order no 1 TO NUMBER substr a order no instr b order no 1 3 减少访问次数 减少访问数据库的次数 当执行每条SQL语句时 ORACLE在内部执行了许多工作 解析SQL语句 估算索引的利用率 绑定变量 读数据块等等 由此可见 减少访问数据库的次数 就能实际上减少ORACLE的工作量 类比 工程实施 使用DECODE来减少处理时间 例如 SELECTCOUNT SUM SAL FROMEMPWHEREDEPT NO 0020ANDENAMELIKE SMITH SELECTCOUNT SUM SAL FROMEMPWHEREDEPT NO 0030ANDENAMELIKE SMITH 你可以用DECODE函数高效地得到相同结果SELECTCOUNT DECODE DEPT NO 0020 X NULL D0020 COUNT COUNT DECODE DEPT NO 0030 X NULL D0030 COUNT SUM DECODE DEPT NO 0020 SAL NULL D0020 SAL SUM DECODE DEPT NO 0030 SAL NULL D0030 SALFROMEMPWHEREENAMELIKE SMITH 减少对表的查询 在含有子查询的SQL语句中 要特别注意减少对表的查询 例如 低效SELECTTAB NAMEFROMTABLESWHERETAB NAME SELECTTAB NAMEFROMTAB COLUMNSWHEREVERSION 604 ANDDB VER SELECTDB VERFROMTAB COLUMNSWHEREVERSION 604 高效SELECTTAB NAMEFROMTABLESWHERE TAB NAME DB VER SELECTTAB NAME DB VER FROMTAB COLUMNSWHEREVERSION 604 4 细节上的影响 WHERE子句中的连接顺序 ORACLE采用自下而上的顺序解析WHERE子句 根据这个原理 当在WHERE子句中有多个表联接时 WHERE子句中排在最后的表应当是返回行数可能最少的表 有过滤条件的子句应放在WHERE子句中的最后 如 设从emp表查到的数据比较少或该表的过滤条件比较确定 能大大缩小查询范围 则将最具有选择性部分放在WHERE子句中的最后 select fromempe deptdwhered deptno 10ande deptno 30 如果dept表返回的记录数较多的话 上面的查询语句会比下面的查询语句响应快得多 select fromempe deptdwheree deptno 30andd deptno 10 WHERE子句 函数 表达式使用 最好不要在WHERE子句中使用函或表达式 如果要使用的话 最好统一使用相同的表达式或函数 这样便于以后使用合理的索引 Orderby语句 ORDERBY语句决定了Oracle如何将返回的查询结果排序 Orderby语句对要排序的列没有什么特别的限制 也可以将函数加入列中 象联接或者附加等 任何在Orderby语句的非索引项或者有计算表达式都将降低查询速度 仔细检查orderby语句以找出非索引项或者表达式 它们会降低性能 解决这个问题的办法就是重写orderby语句以使用索引 也可以为所使用的列建立另外一个索引 同时应绝对避免在orderby子句中使用表达式 联接列 对于有联接的列 即使最后的联接值为一个静态值 优化器是不会使用索引的 select fromemploysswherefirst name last name BeillCliton 系统优化器对基于last name创建的索引没有使用 当采用下面这种SQL语句的编写 Oracle系统就可以采用基于last name创建的索引 select fromemployeewherefirst name Beill andlast name Cliton 带通配符 的like语句 通配符 在搜寻词首出现 Oracle系统不使用last name的索引 select fromemployeewherelast namelike cliton 在很多情况下可能无法避免这种情况 但是一定要心中有底 通配符如此使用会降低查询速度 然而当通配符出现在字符串其他位置时 优化器就能利用索引 在下面的查询中索引得到了使用 select fromemployeewherelast namelike c 用Where子句替换HAVING子句 避免使用HAVING子句 HAVING只会在检索出所有记录之后才对结果集进行过滤 这个处理需要排序 总计等操作 如果能通过WHERE子句限制记录的数目 那就能减少这方面的开销 例如 低效 SELECTREGION AVG LOG SIZE FROMLOCATIONGROUPBYREGIONHAVINGREGIONREGION SYDNEY ANDREGION PERTH 高效SELECTREGION AVG LOG SIZE FROMLOCATIONWHEREREGIONREGION SYDNEY ANDREGION PERTH GROUPBYREGION顺序WHERE GROUP HAVING 用NOTEXISTS替代NOTIN 在子查询中 NOTIN子句将执行一个内部的排序和合并 无论在哪种情况下 NOTIN都是最低效的 因为它对子查询中的表执行了一个全表遍历 使用NOTEXISTS子句可以有效地利用索引 尽可能使用NOTEXISTS来代替NOTIN 尽管二者都使用了NOT 不能使用索引而降低速度 NOTEXISTS要比NOTIN查询效率更高 例如 语句1SELECTdname deptnoFROMdeptWHEREdeptnoNOTIN SELECTdeptnoFROMemp 语句2SELECTdname deptnoFROMdeptWHERENOTEXISTS SELECTdeptnoFROMempWHEREdept deptno emp deptno 2要比1的执行性能好很多 因为1中对emp进行了fulltablescan 这是很浪费时间的操作 而且1中没有用到emp的index 因为没有where子句 而2中的语句对emp进行的是缩小范围的查询 用索引提高效率 索引是表的一个概念部分 用来提高检索数据的效率 ORACLE使用了一个复杂的自平衡B tree结构 通常 通过索引查询数据比全表扫描要快 当ORACLE找出执行查询和Update语句的最佳路径时 ORACLE优化器将使用索引 同样在联结多个表时使用索引也可以提高效率 另一个使用索引的好处是 它提供了主键 primarykey 的唯一性验证 通常 在大型表中使用索引特别有效 当然 你也会发现 在扫描小表时 使用索引同样能提高效率 虽然使用索引能得到查询效率的提高 但是我们也必须注意到它的代价 索引需要空间来存储 也需要定期维护 每当有记录在表中增减或索引列被修改时 索引本身也会被修改 这意味着每条记录的INSERT DELETE UPDATE将为此多付出4 5次的磁盘I O 因为索引需要额外的存储空间和处理 那些不必要的索引反而会使查询反应时间变慢 定期的重构索引是有必要的 避免在索引列上使用计算 WHERE子句中 如果索引列是函数的一部分 优化器将不使用索引而使用全表扫描 低效 SELECT FROMDEPTWHERESAL 12 25000 高效 SELECT FROMDEPTWHERESAL 25000 12 用 替代 如果DEPTNO上有一个索引 高效 SELECT FROMEMPWHEREDEPTNO 4低效 SELECT FROMEMPWHEREDEPTNO 3 通过使用 等 避免使用NOT命令 例子 select fromemployeewheresalary3000 对这个查询 可以改写为不使用NOT select fromemployeewheresalary3000 虽然这两种查询的结果一样 但是第二种查询方案会比第一种查询方案更快些 第二种查询允许Oracle对salary列使用索引 而第一种查询则不能使用索引 如果有其它办法 不要使用子查询 外部联接 的用法 外部联接 按其在 的左边或右边分左联接和右联接 若不带 运算符的表中的一个行不直接匹配于带 预算符的表中的任何行 则前者的行与后者中的一个空行相匹配并被返回 利用外部联接 可以替代效率十分低下的notin运算 大大提高运行速度 例如 下面这条命令执行起来很慢 selecta empnofromempawherea empnonotin selectempnofromemp1wherejob SALE 利用外部联接 改写命令如下 selecta empnofromempa emp1bwherea empno b empno andb empnoisnullandb job SALE 这样运行速度明显提高 尽量多使用COMMIT 事务是消耗资源的 大事务还容易引起死锁COMMIT所释放的资源 回滚段上用于恢复数据的信息 被程序语句获得的锁redologbuffer中的空间ORACLE为管理上述3种资源中的内部花费 用TRUNCATE替代DELETE 当删除表中的记录时 在通常情况下 回滚段 rollbacksegments 用来存放可以被恢复的信息 如果你没有COMMIT事务 ORACLE会将数据恢复到删除之前的状态 准确地说是恢复到执行删除命令之前的状况 而当运用TRUNCATE时 回滚段不再存放任何可被恢复的信息 当命令运行后 数据不能被恢复 因此很少的资源被调用 执行时间也会很短 计算记录条数 和一般的观点相反 count 比count 1 稍快 当然如果可以通过索引检索 对索引列的计数仍旧是最快的 例如COUNT EMPNO 比如有的表PHONE NO字段是CHAR型 而且创建有索引 但在WHERE条件中忘记了加引号 就不会用到索引 WHEREPHONE NOWHEREPHONE NO字符型字段的引号 优化EXPORT和IMPORT 使用较大的BUFFER 比如10MB 10 240 000 可以提高EXPORT和IMPORT的速度 ORACLE将尽可能地获取你所指定的内存大小 即使在内存不满足 也不会报错 这个值至少要和表中最大的列相当 否则列值会被截断 优化Tools SQL语句的执行步骤 语法分析 分析语句的语法是否符合规范 衡量语句中各表达式的意义 语义分析 检查语句中涉及的所有数据库对象是否存在 且用户有相应的权限 视图转换 将涉及视图的查询语句转换为相应的对基表查询语句 表达式转换 将复杂的SQL表达式转换为较简单的等效连接表达式 选择优化器 不同的优化器一般产生不同的 执行计划 选择连接方式 ORACLE有三种连接方式 对多表连接ORACLE可选择适当的连接方式 选择连接顺序 对多表连接ORACLE选择哪一对表先连接 选择这两表中哪个表做为源数据表 选择数据的搜索路径 根据以上条件选择合适的数据搜索路径 如是选用全表搜索还是利用索引或是其他的方式 运行 执行计划 优化器与执行计划 Oracle在执行一个SQL之前 首先要分析一下语句的执行计划 然后再按执行计划去执行 分析语句的执行计划的工作是由优化器 Optimizer 来完成的Oracle的优化器共有两种的优化方式 即基于规则的优化方式 Rule BasedOptimization 简称为RBO 和基于代价的优化方式 Cost BasedOptimization 简称为CBO A RBO方式 优化器在分析SQL语句时 所遵循的是Oracle内部预定的一些规则 比如我们常见的 当一个where子句中的一列有索引时去走索引 B CBO方式 是看语句的代价 Cost 了 这里的代价主要指Cpu和内存 优化器在判断是否用这种方式时 主要参照的是表及索引的统计信息 很多的时侯过期统计信息会令优化器做出一个错误的执行计划在Oracle8及以后的版本 Oracle推荐用CBO的方式 在Oracle10g中 取消了RBO的支持 优化器与执行计划 Rule 即走基于规则的方式Choose 默认的情况下Oracle用的便是这种方式 当一个表或或索引有统计信息 则走CBO的方式 如果表或索引没统计信息 表又不是特别的小 而且相应的列有索引时 那么就走索引 走RBO的方式FirstRows 它与Choose方式是类似的 所不同的是当一个表有统计信息时 它将是以最快的方式返回查询的最先的几行 从总体上减少了响应时间AllRows all rows是oracle优化器默认的模式 它将选择一种在最短时间内返回所有数据的执行计划 它将基于整体成本的考虑 first rows n first rows n是根据成本而不是基于硬编码的规则来选择执行计划 n可以是1 10 100 1000或者直接用first rows n hint指定任意正数 这里的n是我们想获取结果集的前n条记录 这种需求在很多分页语句的需求中会碰到 用EXPLAINPLAN分析SQL语句 EXPLAINPLAN是一个很好的分析SQL语句的工具 它甚至可以在不执行SQL的情况下分析语句 通过分析 我们就可以知道ORACLE是怎么样连接表 使用什么方式扫描表 索引扫描或全表扫描 以及使用到的索引名称 你需要按照从里到外 从上到下的次序解读分析的结果 EXPLAINPLAN分析的结果是用缩进的格式排列的 最内部的操作将被最先解读 如果两个操作处于同一层中 带有最小操作号的将被首先执行 NESTEDLOOP是少数不按照上述规则处理的操作 正确的执行路径是检查对NESTEDLOOP提供数据的操作 其中操作号最小的将被最先处理 Autotrace解读 Currentmode 对于修改的数据从数据段中读Read consistentmode 读一致性模式Physicalblock 物理块 如8192字节 Recursivecalls 嵌套调用次数 使用TKPROF工具 SQLtrace工具收集正在执行的SQL的性能状态数据并记录到一个跟踪文件中 这个跟踪文件提供了许多有用的信息 例如解析次数 执行次数 CPU使用时间等 这些数据将可以用来优化你的系统 设置SQLTRACE在会话级别 有效ALTERSESSIONSETSQL TRACETRUE设置SQLTRACE在整个数据库有效 你必须将SQL TRACE参数在init ora中设为TRUE USER DUMP DEST参数说明了生成跟踪文件的目录再使用TKPROF对TRACE文件进行分析分析结果更加准确 清楚 在SQLPLUS配置AUTOTRACE 在SQLPLUS配置AUTOTRACE 1 首先创建PLUSTRACE角色并且赋给public Sql ORACLE HOME sqlplus admin plustrce sql2 赋权限给用户Sql grantplustracetopublic 预赋权的用户名 3 以SYSTEM用户创建PLAN TABLE表Sql ORACLE HOME rdbms admin utlxplan sqlSql createpublicsynonymplan tableforplan table Sql grantallonplan tabletopublic 在每个用户下设置AUTOTRACE可显示其执行计划 SQL selectename dnamefromemp deptwhereemp deptno dept deptnoanddept dnamein ACCOUNTING RESEARCH SALES OPERATIONS ExecutionPlan 0SELECTSTATEMENTOptimizer CHOOSE10NESTEDLOOPS21TABLEACCESS FULL OF EMP 31TABLEACCESS BYINDEXROWID OF DEPT 43INDEX UNIQUESCAN OF PK DEPT UNIQUE 最起码要解决全表扫描问题改变where条件的次序一般没有用 优化器与执行计划 目录 优化基础知识性能调整综述有效的应用设计SQL语句的处理过程Oracle的优化器Oracle的执行计划注意事项 性能调整综述 谁来调整系统 什么时候调整 建立有效调整的目标在设计和开发时的调整 谁来调整系统 应用设计人员必须传达应用系统的设计 使得每个人都清楚应用中的数据流动 应用开发人员必须传达他们选择的实现策略 使得语句调整的过程中能快速 容易地识别有问题的应用模块和可疑的SQL语句 数据库管理人员必须仔细地监控系统活动并提供它们的资料 使得异常的系统性能可被快速得识别和纠正 硬件 软件管理人员必须传达系统的硬件 软件配置并提供它们的资料 使得相关人员能有效地设计和管理系统 谁来调整系统 与系统涉及的每个人都在调整过程中起某些作用 当上面提及的那些人员传达了系统的特性并提供了它们的资料 调整就能相对的容易和更快一些 事实上的结果是 数据库管理员对调整负有全部或主要的责任 但是 数据库管理员很少有合适的系统方面的资料 而且 在很多情况下 数据库管理员往往是在实施阶段才介入数据库 这就给调整工作带来许多负面的影响 因为在设计阶段的缺陷是不能通过DBA的调整而得以解决 而设计阶段的缺陷往往对数据库性能造成极大的影响 在真正成熟的开发环境下 开发人员作为纯代码编写人员时 对性能的影响最小 此时大部分的工作应由应用设计人员完成 而且数据库管理员往往在前期的需求管理阶段就介入 为设计人员提供必要的技术支持 调整并不是数据库管理员的专利 相反大部分应该是设计人员和开发人员的工作 这就需要设计人员和开发人员具体必要的数据库知识 这样才能组成一个高效的团队 然而事实上往往并非如此 什么时候调整系统 多数人认为当用户感觉性能差时才进行调整 这对调整过程中使用某些最有效的调整策略来说往往是太迟了 此时 如果你不愿意重新设计应用的话 你只能通过重新分配内存 调整SGA 和调整I O的办法或多或少地提高性能 Oracle提供了许多特性 这些特性只有应用到正确地设计的系统中时才能够很大地提高性能 应用设计人员需要在设计阶段设置应用的性能期望值 然后在设计和开发期间 应用设计人员应考虑哪些Oracle特性可以对系统有好处 并使用这些特性 通过良好的系统设计 你就可以在应用的生命周期中消除性能调整的代价和挫折 下图说明在应用的生命周期中调整的相对代价和收益 最有效的调整时间是在设计阶段 在设计期间的调整能以最低的代价给你最大的收益 什么时候调整系统 图 在应用生命周期中调整的代价 什么时候调整系统 图 在应用生命周期中调整的收益 调整的目标 不管正在设计或维护系统 应该建立专门的性能目标 它使你知道何时要作调整 调整你的系统的最有效方法如下 当设计系统时考虑性能 调整操作系统的硬件和软件 识别性能瓶颈 确定问题的原因 采取纠正的动作当你设计系统时 制定专门的目标 例如 响应时间小于 秒 当应用不能满足此目标时 识别造成变慢的瓶颈 例如 I O竞争 确定原因 采取纠正动作 在开发期间 你应测试应用研究 确定在采取应用之前是否满足设计的性能目标 调整的目标 调整通常是一系列开销 一旦确定了瓶颈 可能要牺牲一些其它方面的指标来达到所要的结果 例如 如果I O有问题 你可能需要更多内存或磁盘 如果不可能买 你可能要限制系统的并发性 来获取所需的性能 如果你已经明确地定义了性能的目标 那用什么来交换高性能的决策就变的很容易的 因为已经确定了哪些方面是最重要的 如果我的目标为高性能 可能牺牲一些空间资源 随着应用的越来越庞大 硬件性能的提高 全面的调整应用逐渐变成代价高昂的行为 在这样情况下 要取得最大的投入 效率之比 较好的办法是调整应用的关键部分 使其达到比较高的性能 这样从总体上来说 整个系统的性能也是比较高的 这也就是有名的20 80原则 调整应用的20 关键部分 能解决80 的问题 SQL调整的目标 去掉不必要的大型表的全表扫描 缓存小型表的全表扫描 校验优化索引的使用 检验优化的连接技术 以上目标任务将占据SQL调整90 以上的工作 在设计和开发时调整 良好设计的系统可防止在应用生命周期中产生性能问题 系统设计人员和应用开发人员必须了解Oracle的查询处理机制以便写出高效的SQL语句 有效的应用设计 讨论了你的系统中各种可用的配置 以及每种配置更适合哪种类型的应用 优化器 讨论了Oracle的查询优化器 以及如何写语句以获取最快的结果 在设计和开发时调整 当设计你的系统时 使用下列优化性能的准则 消除客户机 服务器应用中不必要的网络传输 使用存储过程 使用适合你系统的Oracle服务器选件 例如 并行查询或分布式数据库 除非你的应用有特殊的需要 否则使用缺省的Oracle锁 利用数据库记住应用模块 以便能以每个模块为基础来追踪性能 选择你的数据块的最佳大小 原则上来说大一些的性能较好 分布你的数据 使得一个节点使用的数据本地存贮在该节点中 目录 优化基础知识性能调整综述有效的应用设计SQL语句的处理过程Oracle的优化器Oracle的执行计划注意事项 有效的应用设计 将最常用的应用分为2种类型 联机事务处理类型 OLTP 决策支持系统 DSS 联机事务处理 OLTP 该类型的应用是高吞吐量 插入 更新 删除操作比较多的系统 这些系统以不断增长的大容量数据为特征 它们提供给成百用户同时存取 典型的OLTP系统是订票系统 银行的业务系统 订单系统 OTLP的主要目标是可用性 速度 并发性和可恢复性 当设计这类系统时 必须确保大量的并发用户不能干扰系统的性能 还需要避免使用过量的索引与cluster表 因为这些结构会使插入和更新操作变慢 有效的应用设计