Oracle数据库培训(高级)课件.pptx

1、培训-Oracle性能分析及一般故障排查培训目标uOracle 性能基本分析手段uOracle SQL优化基本知识uOracle 具体案例处理切入点Linux系统可以通过top命令查看系统的CPU、内存、运行时间、交换分区、执行的线程等信息。通过top命令可以有效的发现系统的缺陷出在哪里。是内存不够、CPU处理能力不够、IO读写过高。切入点切入点Oracle SQL性能优化知识非非SQL调整调整(*2)单个单个SQL的性能调优的性能调优(*1)多个多个SQL的性能调优的性能调优如何快速定位及认知性能问题点如何快速定位及认知性能问题点 应用调整应用调整应用变慢应用变慢DB调整调整(*1)(*1)

2、Oracle Database调优调优或功能调整或功能调整(*2)应用开发员的程序逻辑调整，性能调优应用开发员的程序逻辑调整，性能调优应用调整应用调整Oracle SQL性能优化知识应用变得反应很慢！是因为？非SQL原因需对应用逻辑进行审查SQL原因仅一两个SQL需要优化(索引，增加查询条件限制)非常多的SQL需要优化，如多个SQL的单一化SQL修改等(则需要审查整个应用程序逻辑)前端应用服务器/网络CPU资源等待DISK I/ODB ServerOracle Elaps响应STARTEND前端CPU资源等待DISK I/ODB ServerOracle Elaps响应STARTEND应用服务

3、器/网路应用变得反应很慢！数据库整体变慢？由于应用程序影响导致数据库整体变慢 对应用程序进行调整Oracle数据库在变慢 数据库调整（参数调整，扩大硬件内存等）修改应用程序(绑定变量，查询调优等)Oracle SQL性能优化知识Oracle SQL性能优化知识 开发员的开发员的SQL问题定位思路问题定位思路DBA指出的问题指出的问题SQL及当前正开发测试或正运行的及当前正开发测试或正运行的SQL考虑本质影响因素考虑本质影响因素 是否有效使用CPU 磁盘IO的使用情况 同步处理情况 查看外在表现形式查看外在表现形式 锁等待情况 表连接和索引使用情况 软硬解析情况 扫描读取情况 分析分析SQL使用

4、工具命令使用工具命令 set autotrace execute plan sql_traceOracle SQL性能优化知识STARTENDDB处理DB连接SQL执行FetchCOMMITROLLBACKDB断连应用逻辑应用逻辑数据库服务器数据库服务器服务器进程接收接收请求（连接，请求（连接，SQL）SQL语法、语义分析语法、语义分析生成生成SQL执行计划执行计划SQL执行执行取得结果取得结果Oracle SQL性能优化知识数据库处理时间通过下图，了解哪些因素可能会成为瓶颈。可以看到数据库处理时间被细分成三部分：1）CPU处理时间 2）等待时间(资源等待，同步处理)3）等待时间(磁盘I/O处

5、理)前端应用服务器/网络CPU资源等待DISK I/O数据库服务器非规范化表设计数据放置不合理执行计划不正确数据碎片化不必要的SQL解析并发(资源竞争)资源枯竭不必要的SQL解析结合负载排序Oracle Elaps响应时间可成为瓶颈的因素可成为瓶颈的因素STARTENDOracle SQL性能优化知识CPU处理时间（使用率20）同步处理等待时间Disk I/O(10000block)CPU处理时间（使用率80）同步等待Disk I/O(6000block)高速化Oracle处理时间如何使数据库处理得更快 有效利用CPU资源 减少等待时间 降低磁盘I/O 降低同步处理所需时间Oracle处理时间

6、处理时间CPU处理时间处理时间Disk I/O等待时间等待时间同步处理同步处理等待时间等待时间等待等待(Wait)时间时间Oracle SQL性能优化知识SQL语句重用 共享池解析SQL语句的执行计划被存储在共享SQL区(Shared SQL Area)如果每个用户已经运行相同的SQL，并使用相同的共享SQL区SELECT nameFROM empSELECT nameFROM deptSELECT nameFROM emp系统全局区系统全局区SGAOracle SQL性能优化知识共享共享SQL语句语句Sql语句在第一次解析之后,ORACLE将SQL语句存放在SGA的shared pool中,

7、被所有用户共享。再次执行的时候，ORACLE就能很快获得已经被解析的语句以及最好的执行路径。提升了执行性能并节省了内存的使用注：ORACLE只对简单的表提供高速缓冲(cache buffering),这个功能并不适用于多表连接查询.ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须 完全相同(包括空格,换行等).Oracle SQL性能优化知识SQL语句重用（1）使用绑定变量不使用绑定变量SELECT*FROM EMP WHERE EMPNO=:v_empno系统全局区系统全局区SGASELECT*FROM empWHERE empno=5223SELECT*FROM empW

8、HERE empno=8826SELECT*FROM empWHERE empno=4328SELECT*FROM empWHERE empno=5211SELECT*FROM empWHERE empno=2671 共享池浪费 SQL硬解析（HARD PARSE）过多的了数据库负荷数据字典引用处理加大Oracle SQL性能优化知识SQL语句重用（2）创建一个SQL编码规范，并按规范进行编码。（请注意请注意:下列看似相同的语句，下列看似相同的语句，Oracle并不认为其相同！因此并不会得到重用并不认为其相同！因此并不会得到重用）SELECT*FROM EMP WHERE EMPNO=:v_e

9、mpnoSELECT*FROM EMP WHERE EMPNO=:v_empnoSELECT*FROM EMPWHERE EMPNO=:v_empnoOracle SQL性能优化知识表连接（Nested Loop）查询流程：通过优化器确定驱动表（外部表）进行以下的循环处理：提取驱动表中有效数据的一行（可以访问索引，也可以无索引）在其他表（内部表）查找匹配的有效数据并提取（访问索引）将数据返回到Oracle客户端注意事项：被驱动表(内部表)如果没有索引的话，查询性能将很差Oracle SQL性能优化知识举例:Nested LoopSELECT d.dname,e.empno,e.ename FR

10、OM emp e,dept d WHERE e.deptno=d.deptno AND e.empno between 7000 and 7500;Id Operation Name 0 SELECT STATEMENT 1 NESTED LOOPS *2 TABLE ACCESS BY INDEX ROWID EMP *3 INDEX RANGE SCAN PK_EMP 4 TABLE ACCESS BY INDEX ROWID DEPT *5 INDEX UNIQUE SCAN PK_DEPT Predicate Information(identified by operation id

11、):2-filter(E.DEPTNO IS NOT NULL)3-access(E.EMPNO=7000 AND E.EMPNO0 then a.occur_balance else 0 end)as fund_in,(case when a.occur_balance=20100501 and a.init_date=20101201 order by a.init_date,a.curr_time,a.serial_no 由于在高峰时间执行了跨度较长的报表查询语句 并导致了gc buffer内部等待 使得cpu利用率和内存消耗比较厉害 故引起了其他业务产生了等待。某公司核心交易系统在重启

12、后节点2宕机 现象描述 客户在某一天对交易系统进行正常重启的之后发现节点2无法正常启动。某公司核心交易系统在重启后节点2宕机 故障分析 首先查看了数据库alter日志，CRS日志，结合节点1个节点2，发现节点2在服务器重启以后所有和数据库以及集群有关的日志全部没有产生，节点1不停的向节点2发送信息全部失败。在节点1上查看集群状态，节点2上所有应用全部漂移到节点1.由此判断，节点2在服务器启动以后没有成功的进入集群。某公司核心交易系统在重启后节点2宕机 故障分析 接着进行了节点1和节点2的系统日志/var/log/message 查看，发现节点2的启动日志里面有网卡被强制shutdown的操作，而且此网卡是绑定了bond用于private网络的。到此问题已经很明确了，操作系统层面网卡被down，导致心跳网络断了，节点1无法和节点2通信，节点2也无法进行任何操作。某公司核心交易系统在重启后节点2宕机 故障解决 后经排查，绑定的心跳网络的2个网口，不在交换机同一个vlan上，导致一个网口坏掉的时候，整个bond就被强制down掉了。解决：恢复网络，重启节点2的服务器，重新加入集群，问题解决。-论日志的重要性以及合理的时间点的判断。THANKS