[itpubnet]SQL语句常用的优化方法.ppt

背景：OLTP系统，ORACLE10G; 1 为什么要调优SQL? 2 哪些SQL需要调优? 3 如何获取需要调优的SQL? 4 如何手工调优SQL? 5 另外一些调优方法和工具。 6 11G在执行计划上的一些改进。;为什么要调优SQL？;例子;哪些SQL需要优化？;从哪里获取需要调优的SQL？;;如何手工调优SQL?;如何获取语句的执行计划？;如何解读执行计划中的执行顺序？;;;SQL 语句的调优原则;SQL调优中的一些常识执行计划中涉及的一些概念;ACCESS和FILTER的区别;在本文中，这样定义此词汇;手工调优的粗略思路;7 若觉得计划中的预估值与手工计算的结果相差太大，可以先对SQL中涉及到 的表作统计，或者，有针对性对约束条件中的字段/索引作统计。在这过程 中，分析数据的分布属性，可考虑建索引，建分区等方法，尽 量让执行计 划只读取必要的数据。 8 在上述各环节的判断过程中，可使用10046事件跟踪部分SQL的执行过程中 的运行效率，判断是否合理。还可使用HINT来改变执行计划中，表/结果集 的读取顺序，关联方法，数据的读取方法等。对比不同执行计划的效率，分 析原因，再调整改进，包括改写成等效的SQL。 9 必要时，可以考虑对不清晰，不符合判断的部分SQL作10053事件分析。 对于由多个动态视图组成的复杂的语句，若发觉整个语句效率比拆分执行 的总的耗时要多，可以先单独拆分找出各个视图的最佳执行计划，之后设 法确保整个语句按照拆分时各个模块的执行计划执行。 　　 以上是我分析SQL语句的执行计划时的大致思路，现实中的情况千变万化 ，可能与上面的思路稍有不同，但大体都是这样，都是从最强条件入手，再往 外扩展/关联与上一步有关系对象。实际分析中，没有我上述描述的那么复杂， DBA根据语句的约束条件，对比分析CBO给出的执行计划，大体一眼能找出计 划中的疑点，再加以计算分析比较，即可找出问题的结症。;数据的采集统计;4种关联方式和两个参数;OPTIMIZER_INDEX_COST_ADJ: （1 to 10000） 用来给DBA人为对索引访问的开销作比例设定。缺省值100 ，表示默认情况下， Cbo将按照正常情况下计算出来的??引访问开销和全表扫描的开销来比较。 DBA 调整此值后，CBO将这样计算索引访问的开销：正常情况下计算出来的Cost * Optimizer_index_cost_adj。此值越小，则表示索引的开销越小，Cbo将越倾 向于走索引；超过100，越大，Cbo将越倾向于走全表扫描。 OPTIMIZER_INDEX_CACHING：（0 to 100） 表示数据缓冲区中，缓存着的索引的数量。此值越大，意味着缓冲区中，缓存的 索引块越多，这对于使用索引作嵌套循环的代价越低，此时CBO将更加偏向走 嵌套循环连接，而非哈希或排序连接。;IN 和 EXISTS适用的场景;　　例子;改用IN写法后，COST大大降低;;改为 exists后的 cost;索引;常用的HINT;善用分区;善用分区;各版本中的分区功能;与SQL调优有关的几个数字字典;10046事件;10046事件的用法，跟踪级别;10046事件的查看方法;;　　语句在执行过程中，各事件的耗时，; 使用10046事件的前提条件 TIMED_STATISTICS=TRUE, SESSION级可设置。 MAX_DUMP_FILE_SIZE 要有足够的空间，通常设置为 Umlimited. 10046事件为何有时没有执行计划？ 这是因为该语句在执行后，该语句的游标没有关闭，导致没写入执行 计划，可以在执行完该语句后，执行一简单语句，如：select * from dual; 促使之前的游标结束，即可得到执行计划信息。;列表分区优化一例;运行的语句;---------------------------------------------------------------------------------------------------------- | Id? | Operation?????????????????????? | Name?????????????????? | Rows? | Bytes | Cost (%CPU)| Time???? | -------------------------------------------------------------------------------------