《orlace培训(高级).ppt》由会员分享，可在线阅读，更多相关《orlace培训(高级).ppt（53页珍藏版）》请在三一文库上搜索。
1、2015年4月,SQL性能调优,我们为什么这么苦逼,经常听到的抱怨 系统很慢，业务跑不动了！ 同样的数据量，怎么还没有我的笔记本跑的快！ 测试系统上运行很快，怎么生产系统这么慢！ 才做过优化，怎么又慢了！ 咋搞的嘛，一会儿快一会儿慢，是不是系统有问题？ ,我们为什么这么苦逼,根本原因 我们被拖上了关系型数据库这艘“贼船” 关系型数据库的特点 以关系代数为理论设计基础 逻辑简单，容易上手 结构灵活，易于调整,我们为什么这么苦逼,近十年关系数据库的显著变化 支持海量数据存储 优化器更“智能” 一切都交给它 它做的多了，需要我们考虑的就少了，问题也就来了！,SQL语句是如何被执行的,SQL语句是如何
2、被执行的,Evaluation fo expressons and conditions Statement transformation Choice of optimizer approaches Choice of access paths Choice of join orders Choice of join methods,SQL语句是如何被执行的,Access Paths RANK 1: Single Row by Rowid RANK 2: Single Row by Cluster Join RANK 3: Single Row by Hash Cluster Key wit
3、h Unique or Primary Key RANK 4: Single Row by Unique or Primary Key RANK 5: Clustered Join RANK 6: Hash Cluster Key RANK 7: Indexed Cluster Key RANK 8: Composite Index,SQL语句是如何被执行的,RANK 9: Single-Column Indexes RANK 10: Bounded Range Search on Indexed Columns RANK 11: Unbounded Range Search on Index
4、ed Columns RANK 12: Sort Merge Join RANK 13: MAX or MIN of Indexed Column RANK 14: ORDER BY on Indexed Column RANK 15: Full Table Scan,SQL语句是如何被执行的,Join Methods,优化器现在已经很智能，为什么还需要优化,其实，针对业务系统的数据应用，你比它了解的更多 它永远无法预测业务流程、业务场景的变化 它不会主动适应政策法规 在一定程度上，它只会按照我们的指令行事,影响优化器生成执行计划的要素,内在因素 表的结构定义 表的物理存储结构 数据量 数据操
5、作特性 数据分布特性,影响优化器生成执行计划的要素,外在因素 可用系统资源（静态） 系统工作负载（动态） 索引 统计信息 SQL语句的复杂程度,SQL语句的优化过程,定位有问题的语句 检查执行计划和统计信息 分析相关表的记录数、索引情况 改写SQL语句、使用HINT、调整索引、表分析 有些SQL语句不具备优化的可能，需要优化处理方式 达到最佳执行计划,SQL语句的优化规则,一个“好 SQL语句”的评价标准 第一：逻辑正确 （最基本的要求） 第二：语法清晰 （简单为王；便于知识转移、后期维护） 第三：时间响应快 （性能的基本要求） 第四：资源消耗少 （最高境界）,SQL语句的优化规则,规范书写
6、大小写风格、注释、缩排规则等,SQL语句的优化规则,提高优化器使用索引的概率 避免对索引列进行计算 例： X WHERE sal * 1.1 950 O WHERE sal 950 / 1.1 X WHERE substr(name,1,7) = CAPITAL O WHERE name LIKE CAPITAL%,SQL语句的优化规则,提高优化器使用索引的概率 注意比较值与索引列数据类型的一致性 例： emp_no： NUMBER型 O WHERE emp_no = 123 （好） O WHERE emp_no = 123 （也可） emp_type：CHAR型 X WHERE emp_ty
7、pe = 123 （查询时不利用索引列） O WHERE emp_type = 123,SQL语句的优化规则,提高优化器使用索引的概率 尽量避免使用NULL 例： X WHERE comm IS NOT NULL X WHERE comm IS NULL O WHERE comm = 0,SQL语句的优化规则,提高优化器使用索引的概率 尽量避免使用NOT=和!= 例： X WHERE deptno != 0 O WHERE deptno 0,SQL语句的优化规则,提高优化器使用索引的概率 对于复合索引，必须使用主索引列 例：复合索引（deptno, job） O WHERE deptno =
8、20 AND job = MANAGER O WHERE deptno = 20 O WHERE job = MANAGER AND deptno = 20 X WHERE job = MANAGER,SQL语句的优化规则,提高优化器使用索引的概率 ORDER BY子句 例： O 子句中，列的顺序与索引列的顺序一致。 O 子句中，列应为非空列。,SQL语句的优化规则,使用表的别名来引用列 例： X SELECT abc002, abd003 FROM ab001, ab020 WHERE ab001.col2 = ab020.col3 O SELECT t1.abc002, t2.abd003
9、 FROM ab001 t1, ab020 t2 WHERE t1.col2 = t2.col3 ,SQL语句的优化规则,考虑用NOT EXISTS代替NOT IN 例： X SELECT FROM emp WHERE dept_no NOT IN ( SELECT dept_no FROM dept WHERE dept_cat = A); O SELECT FROM emp e WHERE NOT EXISTS ( SELECT X FROM dept WHERE dept_no = e.dept_no AND dept_cat = A );,SQL语句的优化规则,考虑用多表连接代替EXI
10、STS子句 例： X SELECT FROM emp e WHERE EXISTS ( SELECT X FROM dept WHERE dept_no = e.dept_no AND dept_cat = A ); O SELECT FROM emp e, dept d WHERE e.dept_no = d.dept_no AND d.dept_cat = A;,SQL语句的优化规则,少用DISTINCT，可考虑用EXISTS代替 例： X SELECT DISTINCT d.dept_code, d.dept_name FROM dept d, emp e WHERE e.dept_co
11、de = d.dept_code; O SELECT dept_code, dept_name FROM dept d WHERE EXISTS ( SELECT X FROM emp e WHERE e.dept_code = d.dept_code);,SQL语句的优化规则,用WHERE子句替代HAVING子句 例： X SELECT . FROM emp HAVING dept_no = 10; O SELECT . FROM emp WHERE dept_no = 10;,SQL语句的优化规则,使用UNION ALL、UNION、MINUS、INTERSECT操作提高性能 UNION
12、ALL无需内部排序操作，在适当的情况下可以替代UNION UNION ALL、UNION、MINUS、INTERSECT可用于分解复杂的SQL逻辑,SQL语句的优化规则,使用ROWID提高检索速度 例： SELECT rowid INTO v_rowid FROM t1 WHERE con1 FOR UPDATE OF col2; UPDATE t1 SET col2 = WHERE rowid = v_rowid;,SQL语句的优化规则,指定FROM子句中表的顺序：扮演driving table角色的表放在最后 例：表emp有10000万条记录，表dept有10条记录 X SELECT a.
13、name, b.name FROM emp a, dept b WHERE b.dept_no = a.dept_no AND b.job = Salesman; O SELECT a.name, b.name FROM dept b, emp a WHERE b.dept_no = a.dept_no AND b.job = Salesman;,SQL语句的优化规则,WHERE子句AND条件的过滤原则：应使过滤记录最有效的条件放在最后面 例：如果emp表上没有索引 X SELECT . FROM emp e WHERE emp_salary 50000 AND emp_type = MANA
14、GER AND 25 ( SELECT count(*) FROM emp WHERE emp_mgr = e.emp_no );,SQL语句的优化规则,续 O SELECT . FROM emp e WHERE 25 50000 AND emp_type = MANAGER;,SQL语句的优化规则,WHERE子句OR条件的过滤原则：应使过滤记录数最有效的条件放在最前面 例：如果emp表上没有索引 X SELECT . FROM emp e WHERE 25 50000 AND emp_type = MANAGER );,SQL语句的优化规则,续 O SELECT . FROM emp e W
15、HERE ( emp_salary 50000 AND emp_type = MANAGER ) OR 25 ( SELECT count(*) FROM emp WHERE emp_mgr = e.emp_no );,SQL语句的优化规则,尽量使用共享的SQL语句 如经常使用： SELECT * FROM dept WHERE deptno=值 如果每一个值都是常量，则每一次都会重新解释，不能共享内存中的SQL语句优化结果。应把值设置为一个变量，所有的共同语句都可以优化一次，高度共享语句解释优化的结果。 例：SELECT * FROM dept WHERE deptno = :d;,SQL语
16、句的优化规则,使用优化线索机制进行访问路径控制 例： SELECT e.ename FROM emp e WHERE e.job | = CLERK; 不如下面的语句好 SELECT /*+ FULL(EMP) */ e.ename FROM emp e WHERE e.job = CLERK;,SQL语句的优化规则,显示光标优于隐式光标 例： UPDATE target t SET t_field = ( SELECT . FROM source s1 WHERE s1.key = t.key ) WHERE EXISTS ( SELECT . FROM source s2 WHERE s2
17、.key = t.key ),SQL语句的优化规则,续 不如下面的语句好： DECLARE CURSOR cur_src IS SELECT * FROM source; BEGIN FOR rec_row IN cur_src LOOP UPDATE target SET t_field = rec_row.s_information WHERE key = rec_row.key; END LOOP; END;,数据库维护“十不准”,不准随意添加或修改索引 针对索引的操作不能太“任性” 索引类型、字段的选择要有专人进行专业评估 一般情况下，一个表的索引总数控制在5个以内 索引是典型的“以空
18、间换时间”的代表 创建索引虽不破坏数据，但会增加系统维护的资源消耗,数据库维护“十不准”,不准随意修改数据库或操作系统的核心参数 核心参数“牵一发而动全身” 因修改SGA而导致系统故障的事件曾多次发生 大多数核心参数是“动态”自动调整的，会引发连锁反应 核心参数不要轻易调整 我们自己安装的系统，绝大多数情况下核心参数的设置是完全能满足业务需要 核心参数的修改，要有专人进行专业评估,数据库维护“十不准”,不准随意添加、移动或删除数据库的物理文件 预防“无知者无畏”的心态 为了空间扩容，将数据文件创建在本地磁盘上 为了清理空间，误删“无用”的物理文件,数据库维护“十不准”,不准随意修改用户密码 不
19、准随意安装或卸载数据库、操作系统的补丁 不准随意更改主机系统的硬件时钟，操作系统的系统时间或时区，以及数据库的时区 不准随意修改操作系统或数据库的字符集,数据库维护“十不准”,不准随意修改系统环境变量 不准随意启停数据库 尤其是RAC数据库系统 不准在未做数据备份的前提下，进行其他高风险性的系统集成工作，如：微码升级、存储架构调整、更换备件等,建议,培养、设立开发型DBA角色或岗位，专职负责数据库的逻辑模式、物理模式设计 制定SQL、PL/SQL的编程规范 定期组织培训，加强规范的宣贯，并在生产中抽查执行情况 培养良好的操作习惯和测试意识 要能读懂执行计划和资源报告,性能调优的基本原则,设置性
20、能基线 聚焦主要矛盾：每次只关注最主要的1-2个问题 例如，OEM的Top 10就可以帮助我们定位最主要的矛盾 按照“应用 中间件数据库操作系统 硬件架构”的顺序依次进行,性能监控工具：SQL*PLUS,设置简单 SQL set timing on SQL set autotrace on 可显示SQL语句的运行时间、执行计划、资源使用情况,性能监控工具：SQL*PLUS,性能监控工具：SQL*PLUS,性能监控工具：Oracle EM,重点关注 主机资源监控（CPU、内存、I/O） 数据库主要性能参数 死锁状态监控 Top 10 SQL监控 AWR报告,性能监控工具：topas或者top,性能监控工具：sar、vmstat、iostat,sar vmstat iostat,系统故障排查,基本顺序：自顶而下 1、应用 是否可用 收集/分析出错信息（包括屏幕截图） 2、系统平台 是否可用 收集/分析系统日志,系统故障排查,基本顺序 3、操作系统 服务器的资源使用情况是否正常（CPU、内存、磁盘空间、网络） 检查/分析系统日志信息 4、硬件设备 是否可用 控制面板是否报警 检查/分析系统日志信息,生命不息，优化不止！,