Oracle培训PPT课件

1、1,二、Oracle 体系结构,五、Oracle SQL编写规范,四、典型案例分析暨优化要点,三、Oracle SQL优化基础,一、培训目的,培训总目录,2,培训目的,培训目标,培训内容,课堂交互,本次课程大约需要3个小时，课后，我们会留半个小时与大家进行探讨，希望在 座各位在培训过程中记录下自己的问题；,Oracle 体系结构；,Oracle SQL优化及编写规范；,了解Oracle 体系架构； 掌握Oracle SQL优化基本方法；,Oracle SQL优化典型案例分析；,3,二、Oracle 体系结构,五、Oracle SQL 编写规范,四、典型案例分析暨优化要点,三、Oracle SQ

2、L 优化基础,一、培训目的,培训总目录,4,Oracle服务器架构,5,Oracle内存体系架构,6,Oracle进程体系架构,用户进程:在数据库用户或批处理进程连接到 Oracle DB 时启动 服务器进程:连接到Oracle 实例，在用户建立 会话时启动 后台进程:在Oracle 实例启动时启动,7,Oracle存储体系架构,8,Oracle高可用性_双机热备,优点 管理简单 相对较为稳定 缺点 切换间存在停顿 备机空闲 无扩展性,9,Oracle高可用性_双机热备切换,10,Oracle高可用性_RAC,优点 良好的可伸缩性、可扩展性 高可用性 新进的Cache Fusion技术 负载均

3、衡 缺点 管理复杂 对网络要求高,11,Oracle高可用性_RAC,将应用和用户自动而透明地重新连接到另一个系统 登录的上下文可被保持 应用查询不会中断,12,Oracle RAC负载均衡,数据库启动时，向监听进程注册 节点向监听进程报告CPU 的使用情况 当建立连接时，监听进程选择最空闲的节点 处理请求,13,二、Oracle 体系结构,五、Oracle SQL 编写规范,四、典型案例分析暨优化要点,三、Oracle SQL 优化基础,一、培训目的,培训总目录,14,性能优化概述,谁来调整系统 应用设计人员、应用开发人员 数据库管理人员 什么时候调整 系统设计、系统开发过程 系统运行过程,

4、15,应用系统类型,联机事务处理（OLTP） 高吞吐量 增、删、改多 决策支持（DSS） 数据量大 主要用于查询,16,SQL语句处理过程,查询语句处理 DML语句处理 （insert、delete、update） DDL语句处理 （create、drop、alter）,17,SQL语句处理各个阶段,18,SQL语句处理必经阶段,第1步: Create a Cursor 创建游标 第2步: Parse the Statement 分析语句 第5步: Bind Any Variables 绑定变量 第7步: Run the Statement 运行语句 第9步: Close the Cursor

5、 关闭游标,19,SQL语句处理可选阶段,并行功能 第6步：Parallelize the Statement 并行执行语句 查询语句 第3步：Describe Results of a Query 描述查询的结果集 第4步：Define Output of a Query 定义查询的输出数据 第8步：Fetch Rows of a Query 取查询出来的行,20,DDL语句处理过程,不同于DML语句和查询语句的执行 DDL成功后需要对数据字典表进行修改 分析阶段还包括分析、查找数据字典 信息和执行,21,Oracle优化器,优化: 选择最有效的执行计划来执行 SQL语句的过程 优化器: 选

6、择执行计划的数据库引擎 基于规则（RBO） 基于代价（CBO）,22,Oracle统计信息,影响CBO执行计划最关键的因素 分析的方法 analyze命令 dbms_stat包 分析的频率 Oracle 10g自动分析,23,共享SQL语句,储存于共享池（shared_pool） 判断SQL语句是否与共享池中某一SQL相同的步骤（CURSOR_SHARING） 对所发出语句的文本串进行hashed 将所发出语句的文本串进行比较 将SQL中涉及的对象进行比较,24,基本概念,ROWID 伪列，唯一。定位数据的最快方法 索引创建时会记录ROWID值 Driving Table（驱动表）: 外层表，

7、用于嵌套以及hash连接中 可选择性: “唯一键的数量/表中的行数”的比值,25,Oracle SQL 执行计划,SQL语句: SELECT ename, job, sal, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND NOT EXISTS ( SELECT * FROM salgrade WHERE emp.sal BETWEEN losal AND hisal );,26,Oracle SQL 执行计划图,27,Oracle SQL 执行计划图,28,访问路径,全表扫描（FTS） 通过rowid的表存取（Table Acc

8、ess by rowid） 索引扫描（Index Scan） 索引唯一扫描(index unique scan) 索引范围扫描(index range scan) 索引全扫描(index full scan) 索引快速扫描(index fast full scan),29,表连接,表连接JOIN: 将两个表结合在一起，一次只能连接2个表， 表连接也可以被称为表关联 排序合并连接（Sort Merge Join） 嵌套循环（Nested Loops） 哈希连接（Hash Join）,30,排序-合并连接图,31,嵌套循环连接图,32,连接适用情况,排序合并连接 非等值连接、关联列都有索引 嵌套连

9、接 驱动表较小 哈希连接 等值连接,33,生成执行计划,sql set autotrace on sql explain plan for select 用dbms_system存储过程生成执行计划 用PL/SQL DEVELOPER（F5）,34,干预执行计划_访问路径,FULL /*+ FULL ( table ) */ 指定该表使用FTS INDEX /*+ INDEX ( table index) */ 使用该表上指定的索引对表进行索引扫描 INDEX_FFS /*+ INDEX_FFS ( table index) */ 使用快速全索引扫描 NO_INDEX /*+ NO_INDEX

10、 ( table index) */ 不使用该表上指定的索引进行存取，仍然可以使用 其它的索引进行索引扫描,35,干预执行计划_表连接,USE_NL /*+ USE_NL ( tab ,tab, . ) */ 使用嵌套连接 USE_MERGE /*+ USE_MERGE ( tab ,tab,.) */ 使用排序- -合并连接 USE_HASH /*+ USE_HASH ( tab ,tab, .) */ 使用HASH连接,36,二、Oracle 体系结构,五、Oracle SQL 编写规范,四、典型案例分析暨优化要点,三、Oracle SQL 优化基础,一、培训目的,培训总目录,37,典型案

11、例_1,系统名称：综合集中维护支撑系统 故障现象：压力测试系统CPU资源100% 故障分析： 数据库实际的数据量仅为6GB左右。但是每天却产生大概150GB左右的REDO LOG 使用LOGMNR分析ORACLE日志，发现基本上都为JMSSTORE表的增、删操作。该表为WEBLOGIC的消息表（CLOB字段）。,38,典型案例_1,系统名称：综合集中维护支撑系统 故障处理： 运行相关查询业务，在数据库中查询v$sqlarea，JMSSTORE表相关操作的频率 对应用进行跟踪调试，查找每笔查询业务所运行的SQL 最终定位问题产生原因：应用程序在每笔查询后面都附带了消息传输（10次） 将该SQL屏

12、蔽，问题得到解决,39,典型案例_2,系统名称：OA系统 故障现象：SQL语句执行速度慢（5S） select distinct b.fd_workflowname,a.fd_fileid,a.fd_c_filetitle,a.fd_c_enddate from tb_model_workflow b,tb_document c,tb_engine_filecommon a,tb_engine_filetache g where a.fd_c_workflowid=b.fd_workflowid and g.fd_fileid=a.fd_fileid and a.fd_c_fileno=c.i

13、d and a.fd_c_isend=1and b.fd_workflowtype=1 and (c.security_level_code=1 or(c.security_level_code=2 and g.fd_ft_staffid=2)or (c.security_level_code=3 and g.fd_ft_staffid=2) and a.fd_c_enddate=(sysdate-30)and a.fd_c_enddate=(sysdate+1) and (pany_id=1 or c.send_company=1) order by a.fd_c_enddate desc;

14、,40,典型案例_2,系统名称：OA系统 故障处理： 查看该sql的执行计划 tb_engine_filecommon表为FTS（full table scan），而且cost大,41,典型案例_2,系统名称：OA系统 故障处理： 查询该表fd_c_enddate的可选择性，发现很高，因此对该字段建索引，执行计划变为如下，执行时间缩短为0.3S左右,42,典型案例_3,系统名称：商务领航系统 故障现象：出账速度慢 故障处理： 查找V$SESSION中正在长时间运行的SQL update zjxc.BIZ_CUST_BALANCE t set t.warefee = 0 where t.cusi

15、d in (select b.cusid from _cusid_temp_0401 b) and t.year_month = 201003; 表biz_cusid_temp_0401为FTS，但cusid字段建有索引,43,典型案例_3,系统名称：商务领航系统 故障处理： 改写上述SQL语句 update _cust_balance t set t.warefee = 0 where exists (select 1 from _cusid_temp_0401 b where t.cusid=b.cusid and t.year_month

16、= 201003) ; 表biz_cusid_temp_0401的访问从FTS改变为索引唯一扫描 SQL执行时间缩短为12秒,44,典型案例_4,系统名称：CRM系统 故障现象：过多的latch free锁影响性能 故障处理： 查找latch free对应的相关语句，都为 SELECT COL_VALUE FROM PUB_COLUMN_REFERENCE WHERE REFER_ID = :B1 而该SQL语句都为函数GET_COLUMN_VALUE调用所产生 查询调用该函数的相关可疑SQL语句,45,典型案例_4,系统名称：CRM系统 故障处理： 定位SQL有相关子查询调用了该函数 对该S

17、QL进行trace跟踪，发现该SQL每执行一次，将会调用该函数38355次，也就是说latch free相应的SQL将被执行38355次 修改相关应用（cache相关刷新机制）,46,Oracle SQL优化要点,使用执行计划来分析SQL性能进而进行优化 使用HINTS来干预执行计划 最大限度地避免全表扫描（FTS） 减少子查询的使用 规范SQL语句的编写，使得SQL语句能够被共享 减少SQL解析的次数：使用绑定变量,47,Oracle SQL优化要点,用索引提高效率 避免在索引列上使用计算 避免在索引列上使用函数转换 避免改变索引列的类型 避免在索引列上使用IS NULL和IS NOT NU

18、LL 避免在索引列上使用like%AAA,48,Oracle SQL优化要点,尽量避免SQL语句不必要的排序操作 UNION操作要用UNION ALL来代替 ORDER BY 中的字段最好建立索引 尽量避免使用DISTINCT语句 尽量避免使用(NOT) IN，而使用（NOT） EXISTS来代替 对表超过10%以上的delete、update操作要重建索引。,49,五、Oracle SQL 编写规范,二、Oracle 体系结构,四、典型案例分析暨优化要点,三、Oracle SQL 优化基础,一、培训目的,培训总目录,50,SQL编写规范_书写格式,缩进 对于存储过程文件，缩进为8个空格 对于Java Source里的SQL字符串，不可有缩进，即每一行字符串不以空格开头 空格 SQL内算数运算符、逻辑运算符连接的两个元素之间必须用空格分隔 逗号之后必须接一个空格 关键字、保留字和左括号间必有一个空格,51,SQL编写规范_