Oracle SQL性能优化及案例教程

1、信息技术中心应用集成研究所-支持一室SQL性能优化及案例分析2011-04-222009年年9月月目录1优化器优化器2查看执行计划查看执行计划3数据库开发调优三板斧数据库开发调优三板斧4确定优化目标确定优化目标5SQLSQL优化优化6案例讨论案例讨论第2页优化器Oracle 有以下两种优化器：有以下两种优化器：?规则优化器规则优化器 RBO（Rule Based Optimizer ）?成本优化成本优化 CBO（Cost Based Optimizer ）优化器的设置：?Instance级别-Optimizer_mode = first_rows?Session级别-Alter session

2、set optimizer_mode = first_rows;?SQL语句级别-Hint /*+ first_rows */第3页RBORBO内置优先顺序1.使用ROWID 读取一行数据2.依据聚簇连接读取一行数据3.依据Unique HASH Cluster 读取一行数据4.依据Unique Index 读取一行数据5.CLUSTER 连接6.Unique HASH Cluster Key7.Unique Cluster Key8.组合索引9.单一列索引10.依据索引的范围查询11. 依据索引的整体范围查询12.Sort Merge 连接13.索引列的MIN、MAX计算14.索引列的Ord

3、er by15.全表扫描SQL的执行顺序是由上到下，表的读取和过滤条件下是从右向左。第4页CBO成本优化器由查询转换器（ Query Transformer) 、成本估算器 (Estimator)、执行计划生成(Plan Generator) 构成.查询转换器： 把当前的SQL语句向比较合适的形式转换，以便获得较好的执行计划.视图合并、谓词推进、子查询非嵌套化等.成本估算器： 由选择度（ selectivity) 、基数（Cardinality) 、成本(Cost)三部分内容简介 .选择度是指满足条件的行在查询的结果集中所占的比率.基数判定对象查询结果行数据或者向下一执行阶段传递的中间结果.成

4、本执行各个操作所需要的时间成本进行相对性运算后而获得的预测值.执行计划生成器： 主要任务是从能够处理当前查询语句的所有可以应用的执行计划中选择成本最小的一个，并对其进行验证 .第5页CBO 具有的优点：1.最大的优点就是判断更具有现实性2.通过对统计信息的管理来控制最优化3.没有深刻理解优化器的工作原理，也能确保最优化的性能CBO 具有的缺点：1.提前预测执行计划比较困难2.不同版本中存在来得变化3.控制执行计划比较困难注: 11G已没有规则优化器 .第6页查看执行计划俗话说“功欲善其事，必先利其器”，掌握一些调优的工具会使你更快捷、更准确的找到需要优化的瓶颈之处，达到事半功倍的效果。以下来介

5、绍Oracle查看执行计划工具，如使用工具 PL/SQL、Toad 、Explain plan 、Autotrace.1.PL/SQL ( 按F5或打开一个 Test Window)1.使用PL/SQL第7页2.使用Explain plan 查询EXPLAIN PLAN SET STATEMENT_ID=QUERY1 FORSELECT * FROM SCOTT.DEPT,SCOTT.EMP WHERE DEPT.DEPTNO = EMP.DEPTNO;显示执行计划SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY();Plan hash value: 1858509

6、404-| Id | Operation |Name |Rows|Bytes|Cost (%CPU)|Time |-| 0 | SELECT STATEMENT | | 14 | 798 | 6 (17)|00:00:01| 1 | MERGE JOIN | | 14 | 798 | 6 (17)|00:00:01| 2 | TABLE ACCESS BY INDEX ROWID|DEPT | 4 | 80 | 2 (0)|00:00:01| 3 | INDEX FULL SCAN |PK_DEPT| 4 | | 1 (0)|00:00:01|* 4 | SORT JOIN | | 14 |

7、518 | 4 (25)|00:00:01| 5 | TABLE ACCESS FULL |EMP | 14 | 518 | 3 (0)|00:00:01|-Predicate Information (identified by operation id):-4 - access(DEPT.DEPTNO=EMP.DEPTNO)filter(DEPT.DEPTNO=EMP.DEPTNO)也可是通过下面语句查询SELECT * FROM DBA_HIST_SQL_PLAN WHERE SQL_ID=QUERY1 ORDER BY ID;第8页3.使用sqlplus包来查看执行计划和运行开销-运行

8、sqlplus包SQLD:oracleproduct10.1.0Db_1sqlplusadminplustrce; SQLD:oracleproduct10.1.0Db_1RDBMSADMIN%utlxplan;SQL set autotrace traceonly-运行需要查询的脚本SQL SELECT * FROM SCOTT.DEPT,SCOTT.EMP WHERE DEPT.DEPTNO = EMP.DEPTNO;执行计划-0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=14 Bytes=798)1 0 MERGE JOIN (Co

9、st=6 Card=14 Bytes=798)2 1 TABLE ACCESS (BY INDEX ROWID) OF DEPT (TABLE) (Cost=2Card=4 Bytes=80)3 2 INDEX (FULL SCAN) OF PK_DEPT (INDEX (UNIQUE) (Cost=1 Card=4)4 1 SORT (JOIN) (Cost=4 Card=14 Bytes=518)5 4 TABLE ACCESS (FULL) OF EMP (TABLE) (Cost=3 Card=14 Bytes=518)统计信息-0 recursive calls0 db block

10、gets11 consistent gets0 physical reads0 redo size1597 bytes sent via SQL*Net to client512 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)14 rows processed 第9页autotrace运行方式：?开始跟踪语句Set autotrace on?隐藏SQL语句执行结果（执行 SQL语句）Set autotrace traceonly?只查看

11、执行统计数据（执行 SQL语句）Set autotrace traceonlystatistics?只查看执行计划（不执行 SQL语句）Set autotrace traceonlyexplain注：PL/SQLt 和Explain plan 命令并不真正执行 SQL语句第10页数据库开发调优三板斧? SQL优化优化? 创建索引创建索引? 修改表结构修改表结构第11页SQL优化优化基本技能:1）基本SQL优化常识.如使用Exists 、用=和) C1(=), C2(=),C3(Like)C1(=), C2(=), C4()字段字段C1C2C3C4次数次数3432=值比较值比较3310离散度离散

12、度查询顺序查询顺序3100从右边比较结果得知C1 + C2 创建组合比较好第16页修改表结构修改表结构一般是表重构，数据排序后重新插入到表、修改表的类型、分区表。对项目里的SQL不会产生影响，这也是一般优化的前提 .详细请看此页备注第17页确定优化目标确定优化目标优化是个没有终点的工作，不管理怎样总有优化空间，所以在优化前先确定优化的目标，预计需要达到什么效果。优化目标有以下参数：1）响应时间 (5秒以内)2）减少运算复杂度3）CUP、内存的使用量 (使用多少MB)4）吞吐量5）数据冗余量第18页单条单条SQLSQL优化优化单条SQL优化只针对脚本级优化，调整脚本的顺序或算法等，一般不要涉及结

13、构和索引.举例:例1. 查看用户林小华是否存在 ,前提staff_name 字段没有索引declare n number;begin select count(*) into n from ims.staff where staff_name=林小华;if n0 .end; 例2.查询最大值 ,前提是根据 staff_ID已创建索引select max(staff_ID) from ims.staff ;例3. 进度测量_优化前.sql第19页优化例1declare n number;begin select 1 into n from dual where exists (select 1 from ims.staff where staff_name=林小华);if n0 .end;例2select /*+ Index_desc(staff PK_STAFF) */ max(staff_ID) from ims.staffwhere rownum=1;例3 . 进度测量_优化后.sql第20页案例讨论1.股票交易系统需要对数据进行分析，要求：1）查询用户买卖了那些股票2）统计所有股票的月资金流入流出量3）统计那天的大单量，假如大于 =500手为大单涉及的信息