ORACLE SQL语句级与策略级优化小结

一 基本的一 基本的 Sql 编写注意事项编写注意事项 尽量少用 IN 操作符 基本上所有的 IN 操作符都可以用 EXISTS 代替 用表连接替换 EXISTS 通常来说 采用表连接的方式比 EXISTS 更 有效率 RBO 中适用 因为前者需要 FILTER nested loops semi 是 nested loop 连接的变种 又叫半连接 原理与 nl 相同 通常用于 in exist 操作 这种操作 join 时候 通常查找到一条纪录就可以了 所 以用 semi 表示 与 semi 相似的有一种叫 anti 反连接 一般用于 not in not exists 也有 nest loop anti 和 hash anti 两种 不用 NOT IN 操作符 可以用 NOT EXISTS 或者外连接 替代 Oracle 在执行 IN 子查询时 首先执行子查询 将查询结果放入临时表 再执行主查询 而 EXIST 则是首先检查主查询 然后运行子查询直到找 到第一个匹配项 NOT EXISTS 比 NOT IN 效率稍高 但具体在选择 IN 或 EXIST 操作时 要根据主子表数据量大小来具体考虑 不用 或者 操作符 对不等于操作符的处理会造成全表扫描 可以用 代替 Where 子句中出现 IS NULL 或者 IS NOT NULL 时 Oracle 会停止使用索 引而执行全表扫描 可以考虑在设计表时 对索引列设置为 NOT NULL 这样就可以用其他操作来取代判断 NULL 的操作 当通配符 或者 作为查询字符串的第一个字符时 索引不会被 使用 对于有连接的列 最后一个连接列索引会无效 尽量避免连接 可以分开连接或者使用不作用在列上的函数替代 如果索引不是基于函数的 那么当在 Where 子句中对索引列使用函数时 索引不再起作用 Where 子句中避免在索引列上使用计算 否则将导致索引失效而进行全 表扫描 对数据类型不同的列进行比较时 会使索引失效 UNION 操作符会对结果进行筛选 消除重复 数据量大的情况下可能会 引起磁盘排序 如果不需要删除重复记录 应该使用 UNION ALL Order By 语句中的非索引列会降低性能 可以通过添加索引的方式处理 严格控制在 Order By 语句中使用表达式 不同区域出现的相同的 Sql 语句 要保证查询字符完全相同 以利用 SGA 共享池 防止相同的 Sql 语句被多次分析 多利用内部函数提高 Sql 效率 当在 Sql 语句中连接多个表时 使用表的别名 并将之作为每列的前缀 这样可以减少解析时间 用 TRUNCATE 替代 DELETE 开发中不准使用 当删除表中的记录时 在通常情况下 回滚段 rollback segments 用来存放可以被恢复的信息 如果你没有 COMMIT 事务 ORACLE 会 将数据恢复到删除之前的状态 准确地说是恢复到执行删除命令之前的状 况 而当运用 TRUNCATE 时 回滚段不再存放任何可被恢复的信息 当 命令运行后 数据不能被恢复 因此很少的资源被调用 执行时间也会 很短 SELECT 子句中避免使用 当你想在 SELECT 子句中列出所有的 COLUMN 时 使用动态 SQL 列 引用 是一个方便的方法 不幸的是 这是一个非常低效的方法 实 际上 ORACLE 在解析的过程中 会将 依次转换成所有的列名 这 个工作是通过查询数据字典完成的 这意味着将耗费更多的时间 Count 10G 中例外 用 Where 子句替换 HAVING 子句 避免使用 HAVING 子句 HAVING 只会在检索出所有记录之后才对 结果集进行过滤 这个处理需要排序 总计等操作 如果能通过 WHERE 子句限制记录的数目 那就能减少这方面的开销 需要注意的是 随着 Oracle 的升级 查询优化器会自动对 Sql 语句进行 优化 某些限制可能在新版本的 Oracle 下不再是问题 尤其是采用 CBO Cost Based Optimization 基于代价的优化方式 时 二 跟踪和分析系统及二 跟踪和分析系统及 SESSION 级的级的 SQL 跟踪跟踪 SQL 语句语句 SQL trace 工具收集正在执行的 SQL 的性能状态数据并记录到一个跟踪文 件中 这个跟踪文件提供了许多有用的信息 例如解析次数 执行次数 CPU 使用 时间等 这些数据将可以用来优化你的系统 设置 SQL TRACE 在会话级别 有效 ALTER SESSION SET SQL TRACE TRUE 设置 SQL TRACE 在整个数据库有效仿 你必须将 SQL TRACE 参数在 init ora 中设为 TRUE USER DUMP DEST 参数说明了生成跟踪文件的目录 跟踪会话和系统跟踪 跟踪自己的会话或者是别人的会话 跟踪自己的会话很简单 Alter session set sql trace true false or exec dbms session set sql trace TRUE 如果跟踪别人的会话 需要调用一个包 exec dbms system set sql trace in session sid serial true false 或 exec sys dbms system set ev sid serial 10046 12 停止 TRACE sid serial 10046 0 跟踪的信息在 user dump dest 目录下可以找到 可以通过 Tkprof 来解析跟踪文件 如 Tkprof 原文件 目标文件 sys n sort exeela 设置整个数据库系统跟踪 其实文档上的 alter system set sql trace true 是不成功的 但是可以通过设置事件来完成这个工作 作用相等 alter system set events 10046 trace name context forever level 1 如果关闭跟踪 可以用如下语句 alter system set events 10046 trace name context off 其中的 level 1 与上面的 8 都是跟踪级别 level 0 停止 level 1 跟踪 SQL 语句 等于 sql trace true level 4 包括变量的详细信息 level 8 包括等待事件 level 12 包括绑定变量与等待事件 eg alter system set max dump file size unlimited ALTER SESSION SET EVENTS 10046 trace name context forever level 12 ALTER SESSION SET EVENTS 942 trace name errorstack level 10 对对 SQLSQL TRACETRACE 的用法也不够准确的用法也不够准确 设置设置 SQLSQL TRACETRACE 首先要在首先要在 init orainit ora 中设定中设定 TIMED STATISTICS TIMED STATISTICS 这样才能得到那些重要的时间状态这样才能得到那些重要的时间状态 生成的生成的 tracetrace 文件是文件是 不可读的不可读的 所以要用所以要用 TKPROFTKPROF 工具对其进行转换工具对其进行转换 TKPROF TKPROF 有许多执行参数有许多执行参数 大家大家 可以参考可以参考 ORACLEORACLE 手册来了解具体的配置手册来了解具体的配置 分析分析 SQL 语句语句 用用 EXPLAINEXPLAIN PLANPLAN 分析分析 SQLSQL 语句语句 EXPLAIN PLAN 是一个很好的分析 SQL 语句的工具 它甚至可以在不执行 SQL 的情况下分析语句 通过分析 我们就可以知道 ORACLE 是怎么样连接表 使用什 么方式扫描表 索引扫描或全表扫描 以及使用到的索引名称 你需要按照从里到外 从上到下的次序解读分析的结果 EXPLAIN PLAN 分析 的结果是用缩进的格式排列的 最内部的操作将被最先解读 如果两个操作处 于同一层中 带有最小操作号的将被首先执行 NESTED LOOP 是少数不按照上述规则处理的操作 正确的执行路径是检查对 NESTED LOOP 提供数据的操作 其中操作号最小的将被最先处理 通过实践通过实践 感到还是用感到还是用 SQLPLUSSQLPLUS 中的中的 SETSET TRACETRACE 功能比较方便功能比较方便 举例举例 SQL SQL listlist 1 1 SELECTSELECT 2 2 FROMFROM dept dept empemp 3 3 WHEREWHERE emp deptnoemp deptno dept deptnodept deptno SQL SQL setset autotraceautotrace traceonlytraceonly traceonly traceonly 可以不显示执行结果可以不显示执行结果 SQL SQL 1414 rowsrows selected selected ExecutionExecution PlanPlan 0 0 SELECTSELECT STATEMENTSTATEMENT Optimizer CHOOSEOptimizer CHOOSE 1 1 0 0 NESTEDNESTED LOOPSLOOPS 2 2 1 1 TABLETABLE ACCESSACCESS FULL FULL OFOF EMP EMP 3 3 1 1 TABLETABLE ACCESSACCESS BY BY INDEXINDEX ROWID ROWID OFOF DEPT DEPT 4 4 3 3 INDEXINDEX UNIQUE UNIQUE SCAN SCAN OFOF PK DEPT PK DEPT UNIQUE UNIQUE StatisticsStatistics 0 0 recursiverecursive callscalls 2 2 dbdb blockblock getsgets 3030 consistentconsistent getsgets 0 0 physicalphysical readsreads 0 0 redoredo sizesize 25982598 bytesbytes sentsent viavia SQL NetSQL Net toto clientclient 503503 bytesbytes receivedreceived viavia SQL NetSQL Net fromfrom clientclient 2 2 SQL NetSQL Net roundtripsroundtrips to fromto from clientclient 0 0 sortssorts memory memory 0 0 sortssorts disk disk 1414 rowsrows processedprocessed 通过以上分析通过以上分析 可以得出实际的执行步骤是可以得出实际的执行步骤是 1 1 TABLETABLE ACCESSACCESS FULL FULL OFOF EMP EMP 2 2 INDEXINDEX UNIQUE UNIQUE SCAN SCAN OFOF PK DEPT PK DEPT UNIQUE UNIQUE 3 3 TABLETABLE ACCESSACCESS BY BY INDEXINDEX ROWID ROWID OFOF DEPT DEPT 4 4 NESTEDNESTED LOOPSLOOPS JOINING JOINING 1 1 ANDAND 3 3 注注 目前许多第三方的工具如目前许多第三方的工具如 TOADTOAD 和和 ORACLEORACLE 本身提供的工具如本身提供的工具如 OMSOMS 的的 SQLSQL AnalyzeAnalyze 都提供了极其方便的都提供了极其方便的 EXPLAINEXPLAIN PLANPLAN 工具工具 也许喜欢图形化界面的朋友也许喜欢图形化界面的朋友 们可以选用它们们可以选用它们 10G 在同一个 SESSION 中执行以下两句 EXPLAIN PLAN FOR SELECT FROM C CONS WHERE CONS NO A SELECT dbms xplan display from dual 三 三 Oracle 优化器优化器 Oracle 优化器 Optimizer 是 Oracle 在执行 SQL 之前分析语句的工具 Oracle 的优化器有两种优化方式 基于规则的 RBO 和基于代价的 CBO RBO 优化器遵循 Oracle 内部预定的规则 CBO 依据语句执行的代价 主要指对 CPU 和内存的占用 优化器在判断 是否使用 CBO 时 要参照表和索引的统计信息 统计信息要在对表做 analyze 后才会有 Oracle8 及以后版本 推荐用 CBO 方式 Oracle 优化器的优化模式主要有四种 Rule 基于规则 Choose 默认模式 根据表或索引的统计信息 如果有统计信息 则使 用 CBO 方式 如果没有统计信息 相应列有索引 则使用 RBO 方式 First rows 与 Choose 类似 不同的是如果表有统计信息 它将以最快 的方式返回查询的前几行 以获得最佳响应时间 All rows 即完全基于 Cost 的模式 当一个表有统计信息时 以最快方 式返回表所有行 以获得最大吞吐量 没有统计信息则使用 RBO 方式 设定优化模式设定优化模式 Instance 级别 在 init ora 文件中设定 OPTIMIZER MODE Session 级别 通过 SQL ALTER SESSION SET OPTIMIZER MODE 来设定 语句级别 通过 SQL SELECT ALL ROWS 来设定 可用的 HINT 包括 ALL ROWS FIRST ROWS CHOOSE RULE 等 统计表信息统计表信息 要注意的是 如果表有统计信息 则可能造成语句不走索引的结果 可以用 SQL ANALYZE TABLE table name DELETE STATISTICS 删除统计信息 对列和索引更新统计信息的 SQL SQL ANALYZE TABLE table name COMPUTE STATISTICS SQL ANALYZE INDEX index name ESTIMATE STATISTICS 10g DBMS STATS 四 使用四 使用 HINT Oracle 使用的 hints 调整机制一直很复杂 Oracle Technical Network 对使 用 hints 调整 Oracle SQL 的过程有很好的全面评述 根据对 10g 数据库的介绍 可使用更多新的 optimizer hints 来控制优化行为 现在让我们迅速了解一下这 些强大的新 hints Oracle 使用的 hints 调整机制一直很复杂 Oracle Technical Network 对使 用 hints 调整 Oracle SQL 的过程有很好的全面评述 根据对 10g 数据库的介绍 可使用更多新的 optimizer hints 来控制优化行为 现在让我们迅速了解一下这 些强大的新 hints spread min analysis 使用这一 hint 你可以忽略一些关于如详细的关系依赖图分析等电子表格 的编译时间优化规则 其他的一些优化 如创建过滤以有选择性的定位电子表 格访问结构并限制修订规则等 得到了继续使用 由于在规则数非常大的情况下 电子表格分析会很长 这一提示可以帮助 我们减少由此产生的数以百小时计的编译时间 例如 SELECT SPREAD MIN ANALYSIS spread no analysis 通过这一 hint 可以使无电子表格分析成为可能 同样 使用这一 hint 可 以忽略修订规则和过滤产生 如果存在一电子表格分析 编译时间可以被减少 到最低程度 例如 SELECT SPREAD NO ANALYSIS use nl with index 这项 hint 使 CBO 通过嵌套循环把特定的表格加入到另一原始行 只有在以 下情况中 它才使用特定表格作为内部表格 如果没有指定标签 CBO 必须可以 使用一些标签 且这些标签至少有一个作为索引键值加入判断 反之 CBO 必须 能够使用至少有一个作为索引键值加入判断的标签 例如 SELECT USE NL WITH INDEX polrecpolrind 典型例子 电费发行中的一句话 INSERT INTO a rcvbl pl flow tmp rcvbl pl id pl amt item code acct no org no rcvbl ym rcv ed amt rcvbl amt id SELECT use hash b c index a CONSPRC PA FK pkg sp seq f a rcvbl pl flow rcvblid rcvblid SUM a pl amt p l amt a pl code pl code v acctno acct no b org no org no b ym y m 0 rcved amt c rcvbl amt id FROM e pl amt a e cons prc amt b a rcvbl flow tmp c WHERE a org no in org no AND a ym in ym AND b org no in org no AND b ym in ym AND b app code in app no AND a prc amt id b prc amt id AND c org no b org no AND c calc id b calc id GROUP BY a pl code b org no b ym c rcvbl amt id CARDINALITY 此 hint 定义了对由查询或查询部分返回的基数的评价 注意如果没有定义 表格 基数是由整个查询所返回的总行数 例如 SELECT CARDINALITY tablespec card 典型例子 临时表的使用 SELECT x x rcvbl owe x penalty owe amt FROM SELECT COUNT 1 row count COUNT a cons no cons count a rcvbl ym SUM a rcvbl amt a rcved amt rcvbl owe SUM pkg ca common f calcpenalty a rcvbl amt id penalty FROM a rcvbl flow a c cons b WHERE a cons no b cons no AND a org no b org no AND a org no IN SELECT CARDINALITY x 1 FROM TABLE v orgnolist x WHERE rownum 0 AND a rcvbl ym BETWEEN in rcvblymbgn AND in rcvblymend AND a settle flag IN 01 02 AND a pay mode LIKE in paymode AND a amt type LIKE in amttype AND nvl b cons sort code 00 LIKE in conssortcode AND b elec type code LIKE in electypecode AND nvl a period num 0 LIKE in periodnum AND rcvbl amt rcved amt v compareamtbgn AND rcvbl amt rcved amt v compareamtend AND a cons no LIKE in consno AND a status code in showtype GROUP BY a rcvbl ym ORDER BY rcvbl ym DESC x no use nl Hint no use nl 使 CBO 执行循环嵌套 通过把指定表格作为内部表格 把 每个指定表格连接到另一原始行 通过这一 hint 只有 hash join 和 sort merge joins 会为指定表格所考虑 例如 SELECT NO USE NL employees no use merge 此 hint 使 CBO 通过把指定表格作为内部表格的方式 拒绝 sort merge 把 每个指定表格加入到另一原始行 例如 SELECT NO USE MERGE employees dept no use hash 此 hint 使 CBO 通过把指定表格作为内部表格的方式 拒绝 hash joins 把每 个指定表格加入到另一原始行 例如 SELECT NO USE HASH employees dept no index ffs 此 hint 使 CBO 拒绝对指定表格的指定标签进行 fast full index scan Syntax NO INDEX FFS tablespecindexspec no index ss 此 hint 使 CBO 拒绝对指定表格的指定标签进行 skip scan Syntax NO INDEX SS tablespecindexspec index ss 此 hint 明确地为指定表格选择 index skip scan 如果语句使用 index range scan Oracle 将以对其索引值的升序排列来检查索引入口 在被分割的索引中 其结果为对每个部分内部的升序排列 Syntax INDEX SS tablespecindexspec cpu costing 此 hint 为 SQL 语句打开 CPU costing 这是优化器的默认评估模式 优化 器评估当执行给定查询时 数据库需要运行的 IO 操作数 IO 操作种类 以及 CPU 周期数 Syntax CPU COSTING tablespecindexspec no cpu costing 此 hint 为 SQL 语句关闭 CPU costing 然后 CBO 使用 IO cost 模式 此模 式忽略 CPU 花费 仅测量在 single block reads 中的所有指标 Syntax NO CPU COSTING 随着 Oracle 优化器越来越成熟 Oracle 专家必须不断增加自己对调整 SQL 语句的工具储备 当然 讨论所有复杂的 Oracle10g SQL 新 hints 远远超出了 本文的范围 你可以从 Mike Ault 的新书 Oracle Database 10g New Features 中 获得关于 Oracle10g 的更多信息 三 如何监控索引的使用 三 如何监控索引的使用 研究发现 oracle 数据库使用的索引不会超过总数的 25 或者不易他们 期望被使用的方式使用 通过 监控数据库索引的使用 释放那些未被使用的索 引 从而节省维护索引的开销 优化性能 用此理论基础测试各个数据库的 optimizer index cost adj 系统参数值 oracle9i 中如何确定索引的使用情况中如何确定索引的使用情况 在 oracle9i 中 情况会简单得多 因为有一个新得字典视图 V SQL PLAN 存储了实际计划 这些计划用于执行共享 SQL 区中得语句 V SQL PLAN 视 图很类似与计划表 但 V SQL PLAN 使用 ADDRESS 和 HASH VALUE 列 来 识别语句 下面的 SQL 显示了在一个 oracle9i 数据库中出现在共享 SQL 区中语 句使用的所有索引 select object owner object name options count from v sql plan where operation INDEX and object owner SYS group by object owner object name operation options order by count desc 所有基于共享 SQL 区中的信息来识别索引使用情况的方法 都可能会收 集到不完整的信息 共享 SQL 区是一 个动态结构 除非能对它进行足够频繁 的采样 否则在有关索引使用的情况的信息被收集之前 SQL 语句可 能就已 经 因为老化 被移出缓存了 oracle9i 提供了解决这个问题的方案 即它为 alter index 提供了一个 monitoring usage 子句 当启用 monitoring usage 时 oralce 记录简单的 yes 或 no 值 以指出在监控间隔 期间某个索引是否被使用 SQL select index name monitoring used start monitoring end monitoring from v object usage alter index test pk monitoring usage alter index test pk nomonitoring usage 五 其它五 其它 共享共享 SQL 语句语句 Library cache 共享的语句必须满足三个条件 A 字符级的比较 当前被执行的语句和共享池中的语句必须完全相同 连 空格个数都一样 B 两个语句所指的对象必须完全相同 C 两个 SQL 语句中必须使用相同的名字的绑定变量 bind variables BIND 变量对执行计划的影响变量对执行计划的影响 执行计划分析执行计划分析 PARTITION RANGE SINGLE INLIST INTE iteration all INDEX SCAN 快速全局扫描 在 Oracle7 3 后就可以使用快速全局扫描 Fast Full Scan 这个选项 这个选 项允许 Oracle 执行一个全局索引扫描操作 快速全局扫描读取 B 树索引上所有 树叶块