Oracle-SQL性能优化及案例分析

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

2、imizer_mode = first_rows;SQL语句级别-Hint /*+ first_rows */第4页RBO内置优先顺序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.索引列的Order by15.全表扫

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

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

5、、Explain plan、Autotrace.1.PL/SQL (按F5或打开一个Test Window)1.使用PL/SQL第8页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: 1858509404-| Id | Operation |Name |Rows|Byte

6、s|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 | 518 | 4 (25)|00:00:01| 5 | TABLE ACCE

7、SS 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;第9页3.使用sqlplus包来查看执行计划和运行开销-运行sqlplus包SQLD:oracleproduct10.1.0Db

8、_1sqlplusadminplustrce; SQLD:oracleproduct10.1.0Db_1RDBMSADMINutlxplan;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=79 8) 1 0 MERGE JOIN (Cost=6 Card=14 Bytes=798) 2 1 TABL

9、E ACCESS (BY INDEX ROWID) OF DEPT (TABLE) (Cost=2 Card=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 B ytes=518)统计信息- 0 recursive calls 0 db block gets 11 consistent gets

10、 0 physical reads 0 redo size 1597 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 14 rows processed 第10页autotrace运行方式：l开始跟踪语句Set autotrace onl隐藏SQL语句执行结果（执行SQL语句）Set autotrace traceonlyl只查看执行统计数据（执行SQL语句

11、）Set autotrace traceonlystatisticsl只查看执行计划（不执行SQL语句）Set autotrace traceonlyexplain注：PL/SQLt 和Explain plan命令并不真正执行SQL语句第11页 SQL优化优化 创建索引创建索引 修改表结构修改表结构第12页 基本技能: 1）基本SQL优化常识. 如使用Exists 、用=和) C1(=), C2(=),C3(Like)C1(=), C2(=), C4()从右边比较结果得知 C1 + C2 创建组合比较好字段字段次数次数=值比较值比较离散度离散度查询顺序查询顺序C1333C2431C3310C4

12、200第17页 一般是表重构，数据排序后重新插入到表、修改表的类型、分区表。对项目里的SQL不会产生影响，这也是一般优化的前提.详细请看此页备注第18页优化是个没有终点的工作，不管理怎样总有优化空间，所以在优化前先确定优化的目标，预计需要达到什么效果。优化目标有以下参数：1）响应时间 (5秒以内)2）减少运算复杂度3）CUP、内存的使用量 (使用多少MB)4）吞吐量5）数据冗余量第19页单条SQL优化只针对脚本级优化，调整脚本的顺序或算法等，一般不要涉及结构和索引.举例:例1. 查看用户林小华是否存在 ,前提staff_name字段没有索引declare n number;begin sele

13、ct count(*) into n from ims.staff where staff_name=林小华; if n0 .end; 例2.查询最大值,前提是根据staff_ID已创建索引select max(staff_ID) from ims.staff ;例3. 进度测量_优化前.sql第20页优化例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第21页1.股票交易系统需要对数据进行分析，要求：1）查询用户买卖了那些股票2）统计所有股票的月资金流入流出量3）统计那天的大单量，假如大于=500手为大单 涉