• 1. ORACLE SQL优化经验交流 系统支撑部 董建忠1www.si-tech.com.cn www.emobile.com.cn
  • 2. 第一章:概述什么样的SQL需要优化 常见的问题 发现问题的方法2www.si-tech.com.cn www.emobile.com.cn
  • 3. 什么样的SQL需要优化引发严重的等待事件 消耗大量的系统资源(CPU/IO/MEM) 运行时间超长 不能满足压力测试指标3www.si-tech.com.cn www.emobile.com.cn
  • 4. 常见的问题没有恰当的索引(全表扫描) 没有使用到恰当的索引 重编译问题 多表关联条件不当或关联太多 分区表没有分析,未能使用索引 死锁4www.si-tech.com.cn www.emobile.com.cn
  • 5. 发现问题的方法从v$session_wait查看等待事件 SQLPLUS使用AUTOTRACE查看执行计划 在TOAD中直接查看执行计划 从STATSPACK查看资源(CPU、I/O)消耗状况 生成SESSION TRACE文件(一般为DBA使用) 用命令tkprof对TRACE文件进行分析 5www.si-tech.com.cn www.emobile.com.cn
  • 6. 第二章: 从等待事件中发现问题查看SESSION WAIT的语句 典型事件: Db File Sequential Read 典型事件: Db File Sequential Read 典型事件: Latch Free(latch 释放) 6www.si-tech.com.cn www.emobile.com.cn
  • 7. 查看SESSION WAIT 的语句set pagesize 2000 set linesize 110 col event format a25 col program format a20 select 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 like '%SQL%' and a.event not like '%message%' and a.event not like '%time%'7www.si-tech.com.cn www.emobile.com.cn
  • 8. 典型事件:Db File Scattered Read 数据文件分散读取 这种情况通常显示与全表扫描相关的等待。 一般表明该表找不到索引,或者只能找到有限的索引。 特定条件下执行全表扫描可能比索引扫描更有效,但如果出现这种等待时,最好检查一下这些全表扫描是否必要。 建议将小而常用的表CACHE到内存中,以避免一次又一次地重复读取它们 8www.si-tech.com.cn www.emobile.com.cn
  • 9. 典型事件: Db File Sequential Read 数据文件顺序读取这一事件通常显示单个块的读取(如索引读取) 表示表的连接顺序不佳,或者使用了不恰当的索引 检查每个扫描是否必要的,并检查多表连接的连接顺序 一般会消耗大量PGA内存,从而在顺序读取时导致大量等待。9www.si-tech.com.cn www.emobile.com.cn
  • 10. 典型事件: Latch Free(latch 释放)latch 是一种低级排队机制,用于保护系统全局区域(SGA)中共享内存结构。latch 就像是一种快速地被获取和释放的内存锁。latch 用于防止共享内存结构被多个用户同时访问。如果latch 不可用,就会记录latch 释放失败。 大多数latch 问题都与以下操作相关:不能使用绑定变量(库缓存latch)、重复生成问题(重复分配latch)、缓冲存储器竞争问题(缓冲器存储LRU 链),以及缓冲存储器中的“热”块(缓冲存储器链)。也有一些latch 等待与bug(程序错误)有关 当latch不命中率大于0.5%时,就应当研究这一问题 10www.si-tech.com.cn www.emobile.com.cn
  • 11. 第三章:SQL语句的执行计划SQL语句的执行步骤 ORACLE的优化器 在SQLPLUS 配置AUTOTRACE 使用QUEST TOAD 查看执行计划 安装AUTOTRACE环境 使用QUEST TOAD 查看执行计划 查看执行计划 11www.si-tech.com.cn www.emobile.com.cn
  • 12. SQL 语句的执行步骤语法分析 ,分析语句的语法是否符合规范,衡量语句中各表达式的意义。 语义分析 ,检查语句中涉及的所有数据库对象是否存在,且用户有相应的权限。 视图转换,将涉及视图的查询语句转换为相应的对基表查询语句。 表达式转换, 将复杂的 SQL 表达式转换为较简单的等效连接表达式。 选择优化器,不同的优化器一般产生不同的“执行计划” 选择连接方式, ORACLE 有三种连接方式,对多表连接 ORACLE 可选择适当的连接方式。 选择连接顺序, 对多表连接 ORACLE 选择哪一对表先连接,选择这两表中哪个表做为源数据表。 选择数据的搜索路径, 根据以上条件选择合适的数据搜索路径,如是选用全表搜索还是利用索引或是其他的方式。 运行“执行计划”12www.si-tech.com.cn www.emobile.com.cn
  • 13. ORACLE 的优化器 ORACLE 有两种优化器:基于规则的优化器( RBO , Rule Based Optimizer ),和基于代价的优化器( CBO , Cost Based Optimizer ) ORACLE V7以来缺省的设置应是“choose”,即如果对已分析的表查询的话选择CBO,否则选择RBO。如果该参数设为“rule”,则不论表是否分析过,一概选用RBO,除非在语句中用hint强制 各“执行计划”的 cost 的计算根据,依赖于数据表中数据的统计分布 ,须要分析表和相关的索引,才能搜集到 CBO 所需的数据 13www.si-tech.com.cn www.emobile.com.cn
  • 14. 在SQLPLUS 配置AUTOTRACEAUTOTRACE 参数解 释SET AUTOTRACE OFF不能获得AUTOTRACE报告. 这是默认的.SET AUTOTRACE ON EXPLAIN仅仅显示优化器执行计划的AUTOTRACE报告SET AUTOTRACE ON STATISTICS仅仅显示SQL语句执行的统计结果的AUTOTRACE报告SET AUTOTRACE ON包括上面两项内容的AUTOTRACE报告SET AUTOTRACE TRACEONLY与SET AUTOTRACE ON类似,所有的统计和数据都在,但不可以打印14www.si-tech.com.cn www.emobile.com.cn
  • 15. 安装AUTOTRACE环境用户必须被赋予PLUSTRACE角色,而PLUSTRACE角色需要DBA来赋予。另外,该用户必须创建PLAN_TABLE表 1、 首先创建PLUSTRACE角色并且赋给DBA: CONNECT sys/sys’s password AS SYSDBA @$ORACLE_HOME/sqlplus/admin/plustrce.sql 2、 赋权限给用户 CONNECT / AS SYSDBA GRANT PLUSTRACE TO USER(预赋权的用户名); 这样,就可以在该用户下设置AUTOTRACE报告的显示与否了。15www.si-tech.com.cn www.emobile.com.cn
  • 16. 使用QUEST TOAD 查看执行计划安装QUEST TOAD软件 建立数据库连接 进入SQL语句执行窗口 输入并选定SQL语句 在’SQL-WINDOW’菜单中选‘EXPLAIN PLAN CURRENT SQL’, 即可看到执行计划, 并不真正执行语句,不需要等待结果16www.si-tech.com.cn www.emobile.com.cn
  • 17. 查看执行计划Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'TEST' Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 547 bytes sent via SQL*Net to client 655 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 4 rows processed 17www.si-tech.com.cn www.emobile.com.cn
  • 18. 第四章:如何分析问题的原因查找原因的一般步骤 18www.si-tech.com.cn www.emobile.com.cn
  • 19. 查找原因的步骤(一)检查被索引的列或组合索引的首列是否出现在PL/SQL语句的WHERE子句 看采用了哪种类型的连接方式。ORACLE的共有Sort Merge Join(归并 SMJ)、Hash Join(散列HJ)和Nested Loop Join(嵌套循环 NL)。在两张表连接,且内表的目标列上建有索引时,只有Nested Loop才能有效地利用到该索引。SMJ即使相关列上建有索引,最多只能因索引的存在,避免数据排序过程。HJ由于须做HASH运算,索引的存在对数据查询速度几乎没有影响 19www.si-tech.com.cn www.emobile.com.cn
  • 20. 查找原因的步骤(二)看连接顺序是否允许使用相关索引。假设表emp的deptno列上有索引,表dept的列deptno上无索引,WHERE语句有emp.deptno=dept.deptno条件。在做NL连接时,emp做为外表,先被访问,由于连接机制原因,外表的数据访问方式是全表扫描,emp.deptno上的索引显然是用不上,最多在其上做索引全扫描或索引快速全扫描 20www.si-tech.com.cn www.emobile.com.cn
  • 21. 查找原因的步骤(三)是否用到系统数据字典表或视图。由于系统数据字典表都未被分析过,可能导致极差的“执行计划”。但是不要擅自对数据字典表做分析,否则可能导致死锁,或系统性能下降 索引列是否函数的参数。如是,索引在查询时用不上 是否存在潜在的数据类型转换。如将字符型数据与数值型数据比较,ORACLE会自动将字符型用to_number()函数进行转换,从而导致第六种现象的发生 21www.si-tech.com.cn www.emobile.com.cn
  • 22. 查找原因的步骤(四)是否为表和相关的索引搜集足够的统计数据。对数据经常有增、删、改的表最好定期对表和索引进行分析,可用SQL语句“analyze table xxxx compute statistics for all indexes;”。ORACLE掌握了充分反映实际的统计数据,才有可能做出正确的选择 索引列的选择性不高 (字段值重复率高)22www.si-tech.com.cn www.emobile.com.cn
  • 23. 查找原因的步骤(五)索引列值是否可为空(NULL)。如果索引列值可以是空值,在SQL语句中那些需要返回NULL值的操作,将不会用到索引,如COUNT(*),而是用全表扫描。这是因为索引中存储值不能为全空 看是否有用到并行查询(PQO)。并行查询将不会用到索引 看PL/SQL语句中是否有用到bind变量。由于数据库不知道bind变量具体是什么值,在做非相等连接时,如“<”,“>”,“like”等。ORACLE将引用缺省值,在某些情况下会对执行计划造成影响 23www.si-tech.com.cn www.emobile.com.cn
  • 24. 第五章:SQL重编译问题SQL共享原理 SQL共享的三个条件 PROC程序的SQL共享 PROC程序中以下类型的语句不需进行变量绑定 PROC程序的CLIENT参数 存储过程的SQL共享 SQL共享的数据库参数的利弊 24www.si-tech.com.cn www.emobile.com.cn
  • 25. SQL共享原理 ORACLE将执行过的SQL语句存放在内存的共享池(shared buffer pool)中,可以被所有的数据库用户共享 当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的 执行路径. 这个功能大大地提高了SQL的执行性能并节省了内存的使用 25www.si-tech.com.cn www.emobile.com.cn
  • 26. SQL共享的三个条件当前被执行的语句和共享池中的语句必须完全相同 (包括大小写、空格、换行等) 两个语句所指的对象必须完全相同 (同义词与表是不同的对象) 两个SQL语句中必须使用相同的名字的绑定变量(bind variables) 26www.si-tech.com.cn www.emobile.com.cn
  • 27. PROC程序的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 SQL PREPARE sql_stmt FROM :sqlstr; EXEC SQL EXECUTE sql_stmt USING :n_var1, :n_var2; EXEC SQL COMMIT; 动态表也可以使用以上方式避免重编译27www.si-tech.com.cn www.emobile.com.cn
  • 28. PROC程序中以下类型的语句不需进行变量绑定 for (i = 0; i < 10000; i++) { EXEC SQL insert into tab_test1 (id ) select id from tab_test where id=:i ; EXEC SQL COMMIT; } 本语句在数据库解析后的结果 insert into tab_test1 (id)select id from tab_test where id=:b0 28www.si-tech.com.cn www.emobile.com.cn
  • 29. PROC程序的CLIENT参数(一)PROC编译参数,控制客户端游标缓存,使用的是本地的内存,与服务器无关 HOLD_CURSOR=yes --保留游标在缓存中,默认为no RELEASE_CURSOR=no --释放缓存,默认为no 这两个参数也可以在程序中设置 EXEC ORACLE OPTION (HOLD_CURSOR=NO); EXEC ORACLE OPTION (RELEASE_CURSOR=YES); 29www.si-tech.com.cn www.emobile.com.cn
  • 30. PROC程序的CLIENT参数(二)数据库参数OPEN_CURSORS指保留在SERVER的shared pool 中的游标数(默认为50) PROC参数MAXOPENCURSORS 指保留在CLIENT端的游标数(默认为10)30www.si-tech.com.cn www.emobile.com.cn
  • 31. 存储过程的SQL共享存储过程中以下类型的语句不需要绑定 CREATE OR REPLACE procedure proc_test as n_id int:=0; BEGIN FOR i IN 1..1000 LOOP insert into TAB_TEST(ID) values(i); commit; END LOOP; END;31www.si-tech.com.cn www.emobile.com.cn
  • 32. 强制SQL共享的数据库参数的利弊ORACLE8i以后,可以使用数据库参数 cursor_sharing =FORCE 或者SIMILAR(9i) 使程序SQL在数据库中硬分析之前共享类似SQL分析的结果,只进行软分析,避免重编译,设置了该参数之后,程序将不需要使用绑定变量 --此参数会触发一些BUG,建议小范围使用或不使用32www.si-tech.com.cn www.emobile.com.cn
  • 33. 第六章:索引33www.si-tech.com.cn www.emobile.com.cn
  • 34. 索引的类型B-tree 索引 位图索引(一般用于数据仓库中的静态数据) 函数索引 --需要设置两个数据库参数 QUERY_REWRITE_ENABLED=TRUE QUERY_REWRITE_INTEGRITY=TRUSTED 反向索引(一般少用)34www.si-tech.com.cn www.emobile.com.cn
  • 35. 表和索引的分析exec dbms_stats.GATHER_TABLE_STATS('ST',‘DCUSTMSG',ESTIMATE_PERCENT=>50); ANALYZE table TABLE_NAME ESTIMATE STATISTICS SAMPLE 50 PERCENT; ORACLE9i建议使用dbms_stats.GATHER_TABLE_STATS35www.si-tech.com.cn www.emobile.com.cn
  • 36. 分区表、索引的特点分区表应尽量建立分区索引 分区表的主键索引若不包含分区字段,则只能建为全局索引 分区表和索引便于管理,但对于底层IO均匀的存储,并不能提高查询性能 分区表和索引需要定期分析,才能恰当的被执行计划使用36www.si-tech.com.cn www.emobile.com.cn
  • 37. 第七章:杂项37www.si-tech.com.cn www.emobile.com.cn
  • 38. IN和EXISTS ... where column in(select * from ... where ...); ... where exists (select 'X' from ...where ...); 第二种格式要远比第一种格式的效率高。在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询 使用EXIST,Oracle系统会首先检查主查询,然后运行子查询直到它找到第一个匹配项,这就节省了时间 Oracle系统在执行IN子查询时,首先执行子查询,并将获得的结果列表存放在在一个加了索引的临时表中 38www.si-tech.com.cn www.emobile.com.cn
  • 39. IS NULL 与 IS NOT NULL 不能用null作索引,任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下,只要这些列中有一列含有null,该列就会从索引中排除。也就是说如果某列存在空值,即使对该列建索引也不会提高性能 任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的 39www.si-tech.com.cn www.emobile.com.cn
  • 40. Order by语句 ORDER BY语句决定了Oracle如何将返回的查询结果排序 任何在Order by语句的非索引项或者有计算表达式都将降低查询速度 40www.si-tech.com.cn www.emobile.com.cn
  • 41. 用Where子句替换HAVING子句避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销 41www.si-tech.com.cn www.emobile.com.cn
  • 42. 带通配符(%)的like语句 select * from employee where last_name like '%cliton%'; select * from employee where last_name like 'c%'; 第二句能够使用到字段‘last_name’的索引 42www.si-tech.com.cn www.emobile.com.cn
  • 43. 找使用CPU多的用户session’12’是指被这个SESSION使用的CPU select a.sid,spid,status,substr(a.program,1,40) prog,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; 43www.si-tech.com.cn www.emobile.com.cn
  • 44. 典型SQLselect 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字段 44www.si-tech.com.cn www.emobile.com.cn