orlace培训(高级).ppt

2015年4月,SQL性能调优,我们为什么这么苦逼,经常听到的抱怨系统很慢，业务跑不动了！同样的数据量，怎么还没有我的笔记本跑的快！测试系统上运行很快，怎么生产系统这么慢！才做过优化，怎么又慢了！咋搞的嘛，一会儿快一会儿慢，是不是系统有问题？,我们为什么这么苦逼,根本原因我们被拖上了关系型数据库这艘“贼船”关系型数据库的特点以关系代数为理论设计基础逻辑简单，容易上手结构灵活，易于调整,我们为什么这么苦逼,近十年关系数据库的显著变化支持海量数据存储优化器更“智能”一切都交给它它做的多了，需要我们考虑的就少了，问题也就来了！,SQL语句是如何被执行的,SQL语句是如何被执行的,Evaluation fo expressons and conditionsStatement transformationChoice of optimizer approachesChoice of access pathsChoice of join ordersChoice of join methods,SQL语句是如何被执行的,Access PathsRANK 1:Single Row by RowidRANK 2:Single Row by Cluster JoinRANK 3:Single Row by Hash Cluster Key with Unique or Primary KeyRANK 4:Single Row by Unique or Primary KeyRANK 5:Clustered JoinRANK 6:Hash Cluster KeyRANK 7:Indexed Cluster KeyRANK 8:Composite Index,SQL语句是如何被执行的,RANK 9:Single-Column IndexesRANK 10:Bounded Range Search on Indexed ColumnsRANK 11:Unbounded Range Search on Indexed ColumnsRANK 12:Sort Merge JoinRANK 13:MAX or MIN of Indexed ColumnRANK 14:ORDER BY on Indexed ColumnRANK 15:Full Table Scan,SQL语句是如何被执行的,Join Methods,优化器现在已经很智能，为什么还需要优化,其实，针对业务系统的数据应用，你比它了解的更多它永远无法预测业务流程、业务场景的变化它不会主动适应政策法规在一定程度上，它只会按照我们的指令行事,影响优化器生成执行计划的要素,内在因素表的结构定义表的物理存储结构数据量数据操作特性数据分布特性,影响优化器生成执行计划的要素,外在因素可用系统资源（静态）系统工作负载（动态）索引统计信息SQL语句的复杂程度,SQL语句的优化过程,定位有问题的语句检查执行计划和统计信息分析相关表的记录数、索引情况改写SQL语句、使用HINT、调整索引、表分析有些SQL语句不具备优化的可能，需要优化处理方式达到最佳执行计划,SQL语句的优化规则,一个“好 SQL语句”的评价标准第一：逻辑正确（最基本的要求）第二：语法清晰（简单为王；便于知识转移、后期维护）第三：时间响应快（性能的基本要求）第四：资源消耗少（最高境界）,SQL语句的优化规则,规范书写大小写风格、注释、缩排规则等,SQL语句的优化规则,提高优化器使用索引的概率避免对索引列进行计算例：X WHERE sal*1.1 950O WHERE sal 950/1.1X WHERE substr(name,1,7)=CAPITALO WHERE name LIKE CAPITAL%,SQL语句的优化规则,提高优化器使用索引的概率注意比较值与索引列数据类型的一致性例：emp_no：NUMBER型O WHERE emp_no=123（好）O WHERE emp_no=123（也可）emp_type：CHAR型X WHERE emp_type=123（查询时不利用索引列）O WHERE emp_type=123,SQL语句的优化规则,提高优化器使用索引的概率尽量避免使用NULL例：X WHERE comm IS NOT NULLX WHERE comm IS NULLO WHERE comm=0,SQL语句的优化规则,提高优化器使用索引的概率尽量避免使用NOT=和!=例：X WHERE deptno!=0O WHERE deptno 0,SQL语句的优化规则,提高优化器使用索引的概率对于复合索引，必须使用主索引列例：复合索引（deptno,job）O WHERE deptno=20 AND job=MANAGERO WHERE deptno=20O WHERE job=MANAGER AND deptno=20X 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 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语句的优化规则,考虑用多表连接代替EXISTS子句例：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_code=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 ALL无需内部排序操作，在适当的情况下可以替代UNIONUNION 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.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=MANAGER 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 WHERE(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语句的优化规则,使用优化线索机制进行访问路径控制例：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.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个以内索引是典型的“以空间换时间”的代表创建索引虽不破坏数据，但会增加系统维护的资源消耗,数据库维护“十不准”,不准随意修改数据库或操作系统的核心参数核心参数“牵一发而动全身”因修改SGA而导致系统故障的事件曾多次发生大多数核心参数是“动态”自动调整的，会引发连锁反应核心参数不要轻易调整我们自己安装的系统，绝大多数情况下核心参数的设置是完全能满足业务需要核心参数的修改，要有专人进行专业评估,数据库维护“十不准”,不准随意添加、移动或删除数据库的物理文件预防“无知者无畏”的心态为了空间扩容，将数据文件创建在本地磁盘上为了清理空间，误删“无用”的物理文件,数据库维护“十不准”,不准随意修改用户密码不准随意安装或卸载数据库、操作系统的补丁不准随意更改主机系统的硬件时钟，操作系统的系统时间或时区，以及数据库的时区不准随意修改操作系统或数据库的字符集,数据库维护“十不准”,不准随意修改系统环境变量不准随意启停数据库尤其是RAC数据库系统不准在未做数据备份的前提下，进行其他高风险性的系统集成工作，如：微码升级、存储架构调整、更换备件等,建议,培养、设立开发型DBA角色或岗位，专职负责数据库的逻辑模式、物理模式设计制定SQL、PL/SQL的编程规范定期组织培训，加强规范的宣贯，并在生产中抽查执行情况培养良好的操作习惯和测试意识要能读懂执行计划和资源报告,性能调优的基本原则,设置性能基线聚焦主要矛盾：每次只关注最主要的1-2个问题例如，OEM的Top 10就可以帮助我们定位最主要的矛盾按照“应用 中间件数据库操作系统 硬件架构”的顺序依次进行,性能监控工具：SQL*PLUS,设置简单SQL set timing onSQL set autotrace on可显示SQL语句的运行时间、执行计划、资源使用情况,性能监控工具：SQL*PLUS,性能监控工具：SQL*PLUS,性能监控工具：Oracle EM,重点关注主机资源监控（CPU、内存、I/O）数据库主要性能参数死锁状态监控Top 10 SQL监控AWR报告,性能监控工具：topas或者top,性能监控工具：sar、vmstat、iostat,sarvmstatiostat,系统故障排查,基本顺序：自顶而下1、应用是否可用收集/分析出错信息（包括屏幕截图）2、系统平台是否可用收集/分析系统日志,系统故障排查,基本顺序3、操作系统服务器的资源使用情况是否正常（CPU、内存、磁盘空间、网络）检查/分析系统日志信息4、硬件设备是否可用控制面板是否报警检查/分析系统日志信息,生命不息，优化不止！,