1、www.si-1ORACLE SQL优化经验交流优化经验交流 系统支撑部系统支撑部 董建忠董建忠www.si-2第一章第一章:概述概述 数据库优化的几个环节数据库优化的几个环节 什么样的什么样的SQL需要优化需要优化 常见的问题常见的问题 发现问题的方法发现问题的方法www.si-3数据库优化的几个环节数据库优化的几个环节 物理存储物理存储IO均衡均衡 数据库规划合理（参数、表空间分布）数据库规划合理（参数、表空间分布）数据表和索引的设计数据表和索引的设计 SQL语句的优化语句的优化www.si-4什么样的什么样的SQL需要优化需要优化 引发严重的等待事件（引发严重的等待事件（IO、锁）、锁）
2、消耗大量的系统资源（消耗大量的系统资源（CPU/IO/MEM）运行时间超长（排序段、回滚段占用太大）运行时间超长（排序段、回滚段占用太大）不能满足压力测试指标不能满足压力测试指标www.si-5常见的问题常见的问题 没有恰当的索引（全表扫描问题）没有恰当的索引（全表扫描问题）索引不适当（索引低效、无用）索引不适当（索引低效、无用）重编译问题（程序开发问题）重编译问题（程序开发问题）多表关联条件不当或关联太多多表关联条件不当或关联太多 分区表没有分析，未能使用索引（维护问题）分区表没有分析，未能使用索引（维护问题）死锁（应用逻辑问题）死锁（应用逻辑问题）www.si-6发现问题的方法发现问题的方
3、法 从从v$session_wait查看等待事件查看等待事件 SQLPLUS使用使用AUTOTRACE查看执行计划查看执行计划 在在TOAD中直接查看执行计划中直接查看执行计划 从从STATSPACK查看资源查看资源(CPU、I/O)消耗状况消耗状况 生成生成SESSION TRACE文件（一般为文件（一般为DBA使用）使用）用命令用命令tkprof对对TRACE文件进行分析文件进行分析 www.si-7第二章：第二章：从等待事件中发现问题从等待事件中发现问题 查看查看SESSION WAIT的语句的语句 典型事件：典型事件：Db File Sequential Read 典型事件：典型事件：
4、Db File Sequential Read 典型事件：典型事件：Latch Free（latch 释放）释放）www.si-8查看查看SESSION WAIT 的语句的语句set pagesize 2000set linesize 110col event format a25col program format a20select a.event,substr(b.program,1,20)program,b.sid,a.p1,a.p2,a.p3 from gv$session_wait a,v$session b where a.sid=b.sid and a.event not li
5、ke%SQL%and a.event not like%message%and a.event not like%time%www.si-9典型事件：Db File Scattered Read 数据文件分散读取数据文件分散读取 这种情况通常显示与全表扫描相关的等待。这种情况通常显示与全表扫描相关的等待。一般表明该表找不到索引，或者只能找到有限一般表明该表找不到索引，或者只能找到有限的索引。的索引。特定条件下执行全表扫描可能比索引扫描更有特定条件下执行全表扫描可能比索引扫描更有效，但如果出现这种等待时，最好检查一下这效，但如果出现这种等待时，最好检查一下这些全表扫描是否必要。些全表扫描是否必要
6、。建议将小而常用的表建议将小而常用的表CACHE到内存中，以避到内存中，以避免一次又一次地重复读取它们免一次又一次地重复读取它们 www.si-10典型事件：Db File Sequential Read数据文件顺序读取数据文件顺序读取 这一事件通常显示单个块的读取这一事件通常显示单个块的读取(如索引读取如索引读取)表示表的连接顺序不佳，或者使用了不恰当的表示表的连接顺序不佳，或者使用了不恰当的索引索引 检查每个扫描是否必要的，并检查多表连接的检查每个扫描是否必要的，并检查多表连接的连接顺序连接顺序 一般会消耗大量一般会消耗大量PGA内存，从而在顺序读取时内存，从而在顺序读取时导致大量等待。导
7、致大量等待。www.si-11典型事件：Latch Free（latch 释放）释放）latch 是一种低级排队机制，用于保护系统全局区域是一种低级排队机制，用于保护系统全局区域（SGA）中共享内存结构。）中共享内存结构。latch 就像是一种快速地被就像是一种快速地被获取和释放的内存锁。获取和释放的内存锁。latch 用于防止共享内存结构被用于防止共享内存结构被多个用户同时访问。如果多个用户同时访问。如果latch 不可用，就会记录不可用，就会记录latch 释放失败。释放失败。大多数大多数latch 问题都与以下操作相关：不能使用绑定变问题都与以下操作相关：不能使用绑定变量（库缓存量（库缓
8、存latch）、重复生成问题（重复分配）、重复生成问题（重复分配latch）、）、缓冲存储器竞争问题（缓冲器存储缓冲存储器竞争问题（缓冲器存储LRU 链），以及缓链），以及缓冲存储器中的冲存储器中的“热热”块（缓冲存储器链）。也有一些块（缓冲存储器链）。也有一些latch 等待与等待与bug（程序错误）有关（程序错误）有关 当当latch不命中率大于不命中率大于0.5%时，就应当研究这一问题时，就应当研究这一问题 www.si-12第三章：第三章：SQL语句的执行计划语句的执行计划 SQL语句的执行步骤语句的执行步骤 ORACLE的优化器的优化器 在在SQLPLUS 配置配置AUTOTRACE
9、 使用使用QUEST TOAD 查看执行计划查看执行计划 安装安装AUTOTRACE环境环境 使用使用QUEST TOAD 查看执行计划查看执行计划 查看执行计划查看执行计划www.si-13SQL 语句的执行步骤语句的执行步骤语法分析语法分析，分析语句的语法是否符合规范，衡量语句中各表达式的，分析语句的语法是否符合规范，衡量语句中各表达式的意义。意义。语义分析语义分析，检查语句中涉及的所有数据库对象是否存在，且用户有，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。相应的权限。视图转换，将涉及视图的查询语句转换为相应的对基表查询语句。视图转换，将涉及视图的查询语句转换为相应的对基
10、表查询语句。表达式转换，表达式转换，将复杂的将复杂的 SQL 表达式转换为较简单的等效连接表达表达式转换为较简单的等效连接表达式。式。选择优化器，不同的优化器一般产生不同的选择优化器，不同的优化器一般产生不同的“执行计划执行计划”选择连接方式，选择连接方式，ORACLE 有三种连接方式，对多表连接有三种连接方式，对多表连接 ORACLE 可选择适当的连接方式。可选择适当的连接方式。选择连接顺序，选择连接顺序，对多表连接对多表连接 ORACLE 选择哪一对表先连接，选择选择哪一对表先连接，选择这两表中哪个表做为源数据表。这两表中哪个表做为源数据表。选择数据的搜索路径，选择数据的搜索路径，根据以上
11、条件选择合适的数据搜索路径，如根据以上条件选择合适的数据搜索路径，如是选用全表搜索还是利用索引或是其他的方式。是选用全表搜索还是利用索引或是其他的方式。运行运行“执行计划执行计划”www.si-14ORACLE 的优化器的优化器 ORACLE 有两种优化器：基于规则的优化器（有两种优化器：基于规则的优化器（RBO，Rule Based Optimizer），和基于代价的优化器），和基于代价的优化器（CBO，Cost Based Optimizer）ORACLE V7以来缺省的设置应是以来缺省的设置应是“choose”，即如果，即如果对已分析的表查询的话选择对已分析的表查询的话选择CBO，否则选
12、择，否则选择RBO。如。如果该参数设为果该参数设为“rule”，则不论表是否分析过，一概选，则不论表是否分析过，一概选用用RBO，除非在语句中用，除非在语句中用hint强制强制 各各“执行计划执行计划”的的 cost 的计算根据，依赖于数据表中的计算根据，依赖于数据表中数据的统计分布数据的统计分布，须要分析表和相关的索引，才能搜，须要分析表和相关的索引，才能搜集到集到 CBO 所需的数据所需的数据 www.si-15在在SQLPLUS 配置配置AUTOTRACEAUTOTRACE 参数参数解解 释释SET AUTOTRACE OFF不能获得AUTOTRACE报告.这是默认的.SET AUTOT
13、RACE ON EXPLAIN仅仅显示优化器执行计划的AUTOTRACE报告SET AUTOTRACE ON STATISTICS仅仅显示SQL语句执行的统计结果的AUTOTRACE报告SET AUTOTRACE ON包括上面两项内容的AUTOTRACE报告SET AUTOTRACE TRACEONLY与SET AUTOTRACE ON类似,所有的统计和数据都在，但不可以打印www.si-16安装安装AUTOTRACE环境环境1、首先创建首先创建PLUSTRACE角色并且赋给角色并且赋给DBA：Sql$ORACLE_HOME/sqlplus/admin/plustrce.sql Sql gra
14、nt plustrace to public2、赋权限给用户赋权限给用户SqlGRANT PLUSTRACE TO USER（预赋权的用户（预赋权的用户名）名）;3、以、以SYSTEM用户创建用户创建PLAN_TABLE表表 Sql$ORACLE_HOME/rdbms/admin/utlplan.sqlSql create public synonym plan_table for plan_table;Sql grant all on plan_table to public;在每个用户下设置在每个用户下设置AUTOTRACE可显示其执行计划。可显示其执行计划。www.si-17使用使用QU
15、EST TOAD 查看执行计划查看执行计划 安装安装QUEST TOAD软件软件 建立数据库连接建立数据库连接 进入进入SQL语句执行窗口语句执行窗口 输入并选定输入并选定SQL语句语句 在在SQL-WINDOW菜单中选菜单中选EXPLAIN PLAN CURRENT SQL，即可看到执行计划，即可看到执行计划，并不真正执行语句，不需要等待结果并不真正执行语句，不需要等待结果www.si-18查看执行计划查看执行计划Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS(FULL)OF TESTStatistics-
16、0 recursive calls0 db block gets4 consistent gets0 physical reads0 redo size547 bytes sent via SQL*Net to client655 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts(memory)0 sorts(disk)4 rows processedwww.si-19第四章：如何分析问题的原因第四章：如何分析问题的原因 查找原因的一般步骤查找原因的一般步骤www.si-20查找原因
17、的步骤（一）查找原因的步骤（一）检查被索引的列或组合索引的首列是否出现在检查被索引的列或组合索引的首列是否出现在PL/SQL语句的语句的WHERE子句子句 看采用了哪种类型的连接方式。看采用了哪种类型的连接方式。ORACLE的共有的共有Sort Merge Join（归并（归并 SMJ）、）、Hash Join（散列（散列HJ）和）和Nested Loop Join（嵌套循环（嵌套循环 NL）。在两张表连接，）。在两张表连接，且内表的目标列上建有索引时，只有且内表的目标列上建有索引时，只有Nested Loop才能才能有效地利用到该索引。有效地利用到该索引。SMJ即使相关列上建有索引，即使相关
18、列上建有索引，最多只能因索引的存在，避免数据排序过程。最多只能因索引的存在，避免数据排序过程。HJ由于由于须做须做HASH运算，索引的存在对数据查询速度几乎没运算，索引的存在对数据查询速度几乎没有影响有影响 www.si-21查找原因的步骤（二）查找原因的步骤（二）看连接顺序是否允许使用相关索引。假设表看连接顺序是否允许使用相关索引。假设表emp的的deptno列上有索引，表列上有索引，表dept的列的列deptno上无索引，上无索引，WHERE语句有语句有emp.deptno=dept.deptno条件。在做条件。在做NL连接时，连接时，emp做为外表，先被访问，由于连接机制原因，做为外表，
19、先被访问，由于连接机制原因，外表的数据访问方式是全表扫描，外表的数据访问方式是全表扫描，emp.deptno上的索引显然是用不上，最多在其上做索引全上的索引显然是用不上，最多在其上做索引全扫描或索引快速全扫描扫描或索引快速全扫描 www.si-22查找原因的步骤（三）查找原因的步骤（三）是否用到系统数据字典表或视图。由于系统数是否用到系统数据字典表或视图。由于系统数据字典表都未被分析过，可能导致极差的据字典表都未被分析过，可能导致极差的“执执行计划行计划”。但是不要擅自对数据字典表做分析，。但是不要擅自对数据字典表做分析，否则可能导致死锁，或系统性能下降否则可能导致死锁，或系统性能下降 索引列
20、是否函数的参数。如是，索引在查询时索引列是否函数的参数。如是，索引在查询时用不上用不上 是否存在潜在的数据类型转换。如将字符型数是否存在潜在的数据类型转换。如将字符型数据与数值型数据比较，据与数值型数据比较，ORACLE会自动将字符会自动将字符型用型用to_number()函数进行转换，从而导致第函数进行转换，从而导致第六种现象的发生六种现象的发生 www.si-23查找原因的步骤（四）查找原因的步骤（四）需要定期分析表和索引需要定期分析表和索引 如果数据经常有增、删、改的表，最好定期对如果数据经常有增、删、改的表，最好定期对表和索引进行分析，可用表和索引进行分析，可用SQL语句语句“anal
21、yze table xxxx compute statistics for all indexes;”。ORACLE掌握了充分反映实际的统计数据，才掌握了充分反映实际的统计数据，才有可能做出正确的选择有可能做出正确的选择 www.si-24查找原因的步骤（五）查找原因的步骤（五）索引列的选择性不高索引列的选择性不高（字段值重复率高）（字段值重复率高）假设有表假设有表emp，共有一百万行数据，但其中的，共有一百万行数据，但其中的emp.deptno列数据只有列数据只有4种不同的值，如种不同的值，如10、20、30、40。ORACLE缺省认定表中列的值是缺省认定表中列的值是在所有数据行均匀分布的。
22、假设在所有数据行均匀分布的。假设SQL搜索条件搜索条件DEPTNO=10，利用，利用deptno列上的索引进行数列上的索引进行数据搜索效率，往往不比全表扫描的高，据搜索效率，往往不比全表扫描的高，ORACLE因此对索引因此对索引“视而不见视而不见”，认为该索，认为该索引的选择性不高引的选择性不高 www.si-25查找原因的步骤（六）查找原因的步骤（六）索引列值是否可为空索引列值是否可为空（NULL）。如果索引列值可以）。如果索引列值可以是空值，在是空值，在SQL语句中那些需要返回语句中那些需要返回NULL值的操作，值的操作，将不会用到索引，如将不会用到索引，如COUNT（*），而是用全表扫描
23、。），而是用全表扫描。这是因为索引中存储值不能为全空这是因为索引中存储值不能为全空 看是否有用到并行查询（看是否有用到并行查询（PQO）。并行查询将不会用）。并行查询将不会用到索引到索引 看看PL/SQL语句中是否有用到语句中是否有用到bind变量。由于数据库不变量。由于数据库不知道知道bind变量具体是什么值，在做非相等连接时，如变量具体是什么值，在做非相等连接时，如“”,“like”等。等。ORACLE将引用缺省值，在某将引用缺省值，在某些情况下会对执行计划造成影响些情况下会对执行计划造成影响 www.si-26第五章：第五章：SQL重编译问题重编译问题 SQL共享原理共享原理 SQL共享
24、的三个条件共享的三个条件 PROC程序的程序的SQL共享共享 PROC程序中以下类型的语句不需进行变量绑程序中以下类型的语句不需进行变量绑定定 PROC程序的程序的CLIENT参数参数 存储过程的存储过程的SQL共享共享 SQL共享的数据库参数的利弊共享的数据库参数的利弊www.si-27SQL共享原理共享原理 ORACLE将执行过的将执行过的SQL语句存放在内存的语句存放在内存的共享池共享池(shared buffer pool)中，可以被所有的中，可以被所有的数据库用户共享数据库用户共享 当你执行一个当你执行一个SQL语句语句(有时被称为一个游标有时被称为一个游标)时时,如果它和之前的执行
25、过的语句完全相同如果它和之前的执行过的语句完全相同,ORACLE就能很快获得已经被解析的语句以及就能很快获得已经被解析的语句以及最好的最好的 执行路径执行路径.这个功能大大地提高了这个功能大大地提高了SQL的执行性能并节省了内存的使用的执行性能并节省了内存的使用 www.si-28SQL共享的三个条件共享的三个条件 当前被执行的语句和共享池中的语句必须完全当前被执行的语句和共享池中的语句必须完全相同相同（包括大小写、空格、换行等）（包括大小写、空格、换行等）两个语句所指的对象必须完全相同两个语句所指的对象必须完全相同（同义词与（同义词与表是不同的对象）表是不同的对象）两个两个SQL语句中必须使
26、用相同的名字的绑定变语句中必须使用相同的名字的绑定变量量(bind variables)www.si-29PROC程序的程序的SQL共享共享 未使用绑定变量的语句未使用绑定变量的语句 sprintf(sqlstr,insert into scott.test1(num1,num2)values(%d,%d),n_var1,n_var2);EXEC SQL EXECUTE IMMEDIATE:sqlstr;EXEC SQL COMMIT;使用绑定变量的语句使用绑定变量的语句strcpy(sqlstr,insert into test(num1,num2)values(:v1,:v2);EXEC
27、SQL PREPARE sql_stmt FROM:sqlstr;EXEC SQL EXECUTE sql_stmt USING:n_var1,:n_var2;EXEC SQL COMMIT;动态表也可以使用以上方式避免重编译动态表也可以使用以上方式避免重编译www.si-30PROC程序中以下类型的语句不需进行变量绑定 for(i=0;i 50);ANALYZE table TABLE_NAME ESTIMATE STATISTICS SAMPLE 50 PERCENT;ORACLE9i建议使用建议使用dbms_stats.GATHER_TABLE_STATSwww.si-38分区表、索引的
28、特点分区表、索引的特点 分区表应尽量建立分区索引分区表应尽量建立分区索引 分区表的主键索引若不包含分区字段，则只能分区表的主键索引若不包含分区字段，则只能建为全局索引建为全局索引 分区表和索引便于管理，但对于底层分区表和索引便于管理，但对于底层IO均匀的均匀的存储，并不能提高查询性能存储，并不能提高查询性能 分区表和索引需要定期分析，才能恰当的被执分区表和索引需要定期分析，才能恰当的被执行计划使用行计划使用www.si-39第七章：杂项第七章：杂项www.si-40IN和和EXISTS .where column in(select*from.where.);.where exists(sel
29、ect X from.where.);第二种格式要远比第一种格式的效率高。在第二种格式要远比第一种格式的效率高。在Oracle中中可以几乎将所有的可以几乎将所有的IN操作符子查询改写为使用操作符子查询改写为使用EXISTS的子查询的子查询 使用使用EXIST，Oracle系统会首先检查主查询，然后运系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间行子查询直到它找到第一个匹配项，这就节省了时间 Oracle系统在执行系统在执行IN子查询时，首先执行子查询，并子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中将获得的结果列表存放在在一个加了索引的临
30、时表中 www.si-41IS NULL 与与 IS NOT NULL 不能用不能用null作索引，任何包含作索引，任何包含null值的列都将值的列都将不会被包含在索引中。即使索引有多列这样的不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有情况下，只要这些列中有一列含有null，该列，该列就会从索引中排除。也就是说如果某列存在空就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能值，即使对该列建索引也不会提高性能 任何在任何在where子句中使用子句中使用is null或或is not null的的语句优化器是不允许使用索引的语句优化器是不允许使用索
31、引的 www.si-42Order by语句语句 ORDER BY语句决定了语句决定了Oracle如何将返回的查如何将返回的查询结果排序询结果排序 任何在任何在Order by语句的非索引项或者有计算表语句的非索引项或者有计算表达式都将降低查询速度达式都将降低查询速度 www.si-43用用Where子句替换子句替换HAVING子句子句 避免使用避免使用HAVING子句子句,HAVING 只会在检索只会在检索出所有记录之后才对结果集进行过滤出所有记录之后才对结果集进行过滤.这个处这个处理需要排序理需要排序,总计等操作总计等操作.如果能通过如果能通过WHERE子句限制记录的数目子句限制记录的数目
32、,那就能减少这方面的开销那就能减少这方面的开销 www.si-44带通配符（带通配符（%）的）的like语句语句 select*from employee where last_name like%cliton%;select*from employee where last_name like c%;第二句能够使用到字段第二句能够使用到字段last_name的索引的索引www.si-45找使用找使用CPU多的用户多的用户session 12是指被这个是指被这个SESSION使用的使用的CPU select a.sid,spid,status,substr(a.program,1,40)pro
33、g,a.terminal,osuser,value/60/100 value from v$session a,v$process b,v$sesstat c where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;www.si-46典型典型SQL select id_no,cust_id from dCustMsgDead where phone_no=13844773080 and substr(run_code,2,1)=a问题问题1：字符字段的：字符字段的phone_no没有加引号！没有加引号！问题问题2：应该将该索引建成分区索引（表为分区：应该将该索引建成分区索引（表为分区表）表）问题问题3：组合索引中包含：组合索引中包含RUN_CODE毫无意义，毫无意义，应只含应只含PHONE_NO字段字段
