Oracle培训课件

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

2、QL优化典型案例分析； 二、二、Oracle Oracle 体系结构体系结构 五、五、Oracle SQL Oracle SQL 编写规范编写规范 四、典型案例分析暨优化要点四、典型案例分析暨优化要点 三、三、Oracle SQL Oracle SQL 优化基础优化基础 一、培训目的一、培训目的 培训总目录培训总目录 OracleOracle服务器架构服务器架构 OracleOracle内存体系架构内存体系架构 OracleOracle进程体系架构进程体系架构 用户进程用户进程:在数据库用户或批处理进程连接到在数据库用户或批处理进程连接到 Oracle DB 时启动时启动 服务器进程服务器进程

3、:连接到连接到Oracle 实例，在用户建立实例，在用户建立 会话时启动会话时启动 后台进程后台进程:在在Oracle 实例启动时启动实例启动时启动 OracleOracle存储体系架构存储体系架构 OracleOracle高可用性高可用性_ _双机热备双机热备 优点优点 管理简单管理简单 相对较为稳定相对较为稳定 缺点缺点 切换间存在停顿切换间存在停顿 备机空闲备机空闲 无扩展性无扩展性 OracleOracle高可用性高可用性_ _双机热备切换双机热备切换 OracleOracle高可用性高可用性_RAC_RAC 优点优点 良好的可伸缩性、可扩展性良好的可伸缩性、可扩展性 高可用性高可用性

4、 新进的新进的Cache Fusion技术技术 负载均衡负载均衡 缺点缺点 管理复杂管理复杂 对网络要求高对网络要求高 OracleOracle高可用性高可用性_RAC_RAC 将应用和用户自动而透明地重新连接到将应用和用户自动而透明地重新连接到 另一个系统另一个系统 登录的上下文可被保持登录的上下文可被保持 应用查询不会中断应用查询不会中断 当集群内节点当集群内节点 A A 失失 败败, , 用户被转移用户被转移 OracleOracle RACRAC负载均衡负载均衡 数据库启动时，向监听进程注册数据库启动时，向监听进程注册 节点向监听进程报告节点向监听进程报告CPU 的使用情况的使用情况

5、当建立连接时，监听进程选择最空闲的节点当建立连接时，监听进程选择最空闲的节点 处理请求处理请求 二、二、Oracle Oracle 体系结构体系结构 五、五、Oracle SQL Oracle SQL 编写规范编写规范 四、典型案例分析暨优化要点四、典型案例分析暨优化要点 三、三、Oracle SQL Oracle SQL 优化基础优化基础 一、培训目的一、培训目的 培训总目录培训总目录 性能优化概述性能优化概述 14 谁来调整系统谁来调整系统 应用设计人员、应用开发人员应用设计人员、应用开发人员 数据库管理人员数据库管理人员 什么时候调整什么时候调整 系统设计、系统开发过程系统设计、系统开发

6、过程 系统运行过程系统运行过程 应用系统类型应用系统类型 15 联机事务处理（联机事务处理（OLTPOLTP） 高吞吐量高吞吐量 增、删、改多增、删、改多 决策支持（决策支持（DSSDSS） 数据量大数据量大 主要用于查询主要用于查询 SQLSQL语句处理过程语句处理过程 16 查询语句处理查询语句处理 DMLDML语句处理语句处理 （insertinsert、deletedelete、updateupdate） DDLDDL语句处理语句处理 （createcreate、dropdrop、alteralter） SQLSQL语句处理各个阶段语句处理各个阶段 17 SQLSQL语句处理必经阶段语

7、句处理必经阶段 18 第第1 1步步: Create a Cursor : Create a Cursor 创建游标创建游标 第第2 2步步: Parse the Statement : Parse the Statement 分析语句分析语句 第第5 5步步: Bind Any Variables : Bind Any Variables 绑定变量绑定变量 第第7 7步步: Run the Statement : Run the Statement 运行语句运行语句 第第9 9步步: Close the Cursor : Close the Cursor 关闭游标关闭游标 SQLSQL语句处

8、理可选阶段语句处理可选阶段 19 并行功能并行功能 第第6 6步：步：Parallelize the Statement Parallelize the Statement 并行执行语并行执行语 句句 查询语句查询语句 第第3 3步：步：Describe Results of a Query Describe Results of a Query 描述查描述查 询的结果集询的结果集 第第4 4步：步：Define Output of a QueryDefine Output of a Query 定义查询的定义查询的 输出数据输出数据 第第8 8步：步：Fetch Rows of a Quer

9、yFetch Rows of a Query 取查询出来的行取查询出来的行 DDLDDL语句处理过程语句处理过程 20 不同于不同于DMLDML语句和查询语句的执行语句和查询语句的执行 DDLDDL成功后需要对数据字典表进行修改成功后需要对数据字典表进行修改 分析阶段还包括分析、查找数据字典分析阶段还包括分析、查找数据字典 信息和执行信息和执行 OracleOracle优化器优化器 21 优化优化: : 选择最有效的执行计划来执行选择最有效的执行计划来执行 SQL SQL语句的过程语句的过程 优化器优化器: : 选择执行计划的数据库引擎选择执行计划的数据库引擎 基于规则（基于规则（RBORBO

10、） 基于代价（基于代价（CBOCBO） OracleOracle统计信息统计信息 22 影响影响CBOCBO执行计划最关键的因素执行计划最关键的因素 分析的方法分析的方法 analyzeanalyze命令命令 dbms_statdbms_stat包包 分析的频率分析的频率 Oracle 10gOracle 10g自动分析自动分析 共享共享SQLSQL语句语句 23 储存于共享池（储存于共享池（shared_poolshared_pool） 判断判断SQLSQL语句是否与共享池中某一语句是否与共享池中某一SQLSQL相相 同的步骤（同的步骤（CURSOR_SHARINGCURSOR_SHARIN

11、G） 对所发出语句的文本串进行对所发出语句的文本串进行hashedhashed 将所发出语句的文本串进行比较将所发出语句的文本串进行比较 将将SQLSQL中涉及的对象进行比较中涉及的对象进行比较 基本概念基本概念 24 ROWIDROWID 伪列，唯一。定位数据的最快方法伪列，唯一。定位数据的最快方法 索引创建时会记录索引创建时会记录ROWIDROWID值值 Driving TableDriving Table（驱动表）（驱动表）: : 外层表，用于嵌套以及外层表，用于嵌套以及hashhash连接中连接中 可选择性可选择性: : “唯一键的数量唯一键的数量/ /表中的行数表中的行数”的比值的比

12、值 Oracle SQL Oracle SQL 执行计划执行计划 25 SQLSQL语句语句: : SELECT ename, job, sal, dnameSELECT ename, job, sal, dname FROM emp, dept FROM emp, dept WHERE emp.deptno = dept.deptno WHERE emp.deptno = dept.deptno AND NOT EXISTS AND NOT EXISTS ( SELECT ( SELECT * * FROM salgrade FROM salgrade WHERE emp.sal WHERE

13、 emp.sal BETWEEN losal AND hisal ); BETWEEN losal AND hisal ); Oracle SQL Oracle SQL 执行计划图执行计划图 26 Oracle SQL Oracle SQL 执行计划图执行计划图 27 访问路径访问路径 28 全表扫描（全表扫描（FTSFTS） 通过通过rowidrowid的表存取（的表存取（Table Access by rowidTable Access by rowid） 索引扫描（索引扫描（Index ScanIndex Scan） 索引唯一扫描索引唯一扫描(index unique scan)(ind

14、ex unique scan) 索引范围扫描索引范围扫描(index range scan)(index range scan) 索引全扫描索引全扫描(index full scan)(index full scan) 索引快速扫描索引快速扫描(index fast full scan)(index fast full scan) 表连接表连接 29 表连接表连接JOIN:JOIN: 将两个表结合在一起，一次只能连接将两个表结合在一起，一次只能连接2 2个表，个表， 表连接也可以被称为表关联表连接也可以被称为表关联 排序排序合并连接（合并连接（Sort Merge JoinSort Merge

15、 Join） 嵌套循环（嵌套循环（Nested LoopsNested Loops） 哈希连接（哈希连接（Hash JoinHash Join） 排序排序- -合并连接图合并连接图 30 嵌套循环连接图嵌套循环连接图 31 连接适用情况连接适用情况 32 排序排序合并连接合并连接 非等值连接、关联列都有索引非等值连接、关联列都有索引 嵌套连接嵌套连接 驱动表较小驱动表较小 哈希连接哈希连接 等值连接等值连接 生成执行计划生成执行计划 33 sql set autotrace onsql set autotrace on sql explain plan for select sql expla

16、in plan for select 用用dbms_systemdbms_system存储过程生成执行计划存储过程生成执行计划 用用PL/SQL DEVELOPERPL/SQL DEVELOPER（F5F5） 干预执行计划干预执行计划_ _访问路径访问路径 34 FULL FULL / /* *+ FULL ( table ) + FULL ( table ) * */ / 指定该表使用指定该表使用FTSFTS INDEX INDEX / /* *+ INDEX ( table index) + INDEX ( table index) * */ / 使用该表上指定的索引对表进行索引扫描使用该

17、表上指定的索引对表进行索引扫描 INDEX_FFS / INDEX_FFS /* *+ INDEX_FFS ( table index) + INDEX_FFS ( table index) * */ / 使用快速全索引扫描使用快速全索引扫描 NO_INDEX / NO_INDEX /* *+ NO_INDEX ( table index) + NO_INDEX ( table index) * */ / 不使用该表上指定的索引进行存取，仍然可以使用不使用该表上指定的索引进行存取，仍然可以使用 其它的索引进行索引扫描其它的索引进行索引扫描 干预执行计划干预执行计划_ _表连接表连接 35 US

18、E_NL / USE_NL /* *+ USE_NL ( tab ,tab, . ) + USE_NL ( tab ,tab, . ) * */ / 使用嵌套连接使用嵌套连接 USE_MERGE / USE_MERGE /* *+ USE_MERGE ( tab ,tab,.) + USE_MERGE ( tab ,tab,.) * */ / 使用排序使用排序- - -合并连接合并连接 USE_HASH / USE_HASH /* *+ USE_HASH ( tab ,tab, .) + USE_HASH ( tab ,tab, .) * */ / 使用使用HASHHASH连接连接 二、二、O

19、racle Oracle 体系结构体系结构 五、五、Oracle SQL Oracle SQL 编写规范编写规范 四、典型案例分析暨优化要点四、典型案例分析暨优化要点 三、三、Oracle SQL Oracle SQL 优化基础优化基础 一、培训目的一、培训目的 培训总目录培训总目录 典型案例典型案例_1_1 37 系统名称：综合集中维护支撑系统系统名称：综合集中维护支撑系统 故障现象：压力测试系统故障现象：压力测试系统CPUCPU资源资源100%100% 故障分析：故障分析： 数据库实际的数据量仅为数据库实际的数据量仅为6GB6GB左右。但是每左右。但是每 天却产生大概天却产生大概150GB

20、150GB左右的左右的REDO LOGREDO LOG 使用使用LOGMNRLOGMNR分析分析ORACLEORACLE日志，发现基本上日志，发现基本上 都为都为JMSSTOREJMSSTORE表的增、删操作。该表为表的增、删操作。该表为 WEBLOGICWEBLOGIC的消息表的消息表（CLOBCLOB字段）字段）。 典型案例典型案例_1_1 38 系统名称：综合集中维护支撑系统系统名称：综合集中维护支撑系统 故障处理：故障处理： 运行相关查询业务，在数据库中查询运行相关查询业务，在数据库中查询 v$sqlareav$sqlarea，JMSSTOREJMSSTORE表相关操作的频率表相关操作

21、的频率 对应用进行跟踪调试，查找每笔查询业务对应用进行跟踪调试，查找每笔查询业务 所运行的所运行的SQLSQL 最终定位问题产生原因：应用程序在每笔最终定位问题产生原因：应用程序在每笔 查询后面都附带了消息传输（查询后面都附带了消息传输（1010次）次） 将该将该SQLSQL屏蔽，问题得到解决屏蔽，问题得到解决 典型案例典型案例_2_2 39 系统名称：系统名称：OAOA系统系统 故障现象：故障现象：SQLSQL语句执行速度慢（语句执行速度慢（5S5S） select distinct select distinct b.fd_workflowname,a.fd_fileid,a.fd_c_f

22、iletitle,a.fd_c_enddateb.fd_workflowname,a.fd_fileid,a.fd_c_filetitle,a.fd_c_enddate from tb_model_workflow b,tb_document c,tb_engine_filecommon from tb_model_workflow b,tb_document c,tb_engine_filecommon a,tb_engine_filetache ga,tb_engine_filetache g where a.fd_c_workflowid=b.fd_workflowid and g.fd

23、_fileid=a.fd_fileid and where a.fd_c_workflowid=b.fd_workflowid and g.fd_fileid=a.fd_fileid and a.fd_c_fileno=c.ida.fd_c_fileno=c.id and a.fd_c_isend=1and b.fd_workflowtype=1 and (c.security_level_code=1 and a.fd_c_isend=1and b.fd_workflowtype=1 and (c.security_level_code=1 or(c.security_level_code=

24、2 and g.fd_ft_staffid=2)or or(c.security_level_code=2 and g.fd_ft_staffid=2)or (c.security_level_code=3 and g.fd_ft_staffid=2)(c.security_level_code=3 and g.fd_ft_staffid=2) and a.fd_c_enddate=(sysdate-30)and a.fd_c_enddate=(sysdate-30)and a.fd_c_enddate=(sysdate+1) and (pany_id=1 or c.send_company=

25、1) and (pany_id=1 or c.send_company=1) order by a.fd_c_enddate desc;order by a.fd_c_enddate desc; 典型案例典型案例_2_2 40 系统名称：系统名称：OAOA系统系统 故障处理：故障处理： 查看该查看该sqlsql的执行计划的执行计划 tb_engine_filecommontb_engine_filecommon表为表为FTSFTS（full full table scantable scan），而且），而且costcost大大 典型案例典型案例_2_2 41 系统名称：系统名称：OAOA系统系

26、统 故障处理：故障处理： 查询该表查询该表fd_c_enddatefd_c_enddate的可选择性的可选择性，发现发现 很高，因此对该字段建索引，很高，因此对该字段建索引，执行计划变为执行计划变为 如下，执行时间缩短为如下，执行时间缩短为0.3S0.3S左右左右 典型案例典型案例_3_3 42 系统名称：商务领航系统系统名称：商务领航系统 故障现象：出账速度慢故障现象：出账速度慢 故障处理：故障处理： 查找查找V$SESSIONV$SESSION中正在长时间运行的中正在长时间运行的SQLSQL update zjxc.BIZ_CUST_BALANCE t set t.warefee = 0

27、where t.cusid inupdate zjxc.BIZ_CUST_BALANCE t set t.warefee = 0 where t.cusid in (select b.cusid from _cusid_temp_0401 b) and (select b.cusid from _cusid_temp_0401 b) and t.year_month = 201003; t.year_month = 201003; 表表biz_cusid_temp_0401biz_cusid_temp_0401为为FTSFTS，但，但cusidcusid 字段建

28、有索引字段建有索引 典型案例典型案例_3_3 43 系统名称：商务领航系统系统名称：商务领航系统 故障处理：故障处理： 改写上述改写上述SQLSQL语句语句 update _cust_balance t set t.warefee = 0 where update _cust_balance t set t.warefee = 0 where exists (select 1 from _cusid_temp_0401 b exists (select 1 from _cusid_temp_0401 b where t.cusid

29、=b.cusid and t.year_month = 201003) ; where t.cusid=b.cusid and t.year_month = 201003) ; 表表biz_cusid_temp_0401biz_cusid_temp_0401的访问从的访问从FTSFTS改变改变 为索引唯一扫描为索引唯一扫描 SQL SQL执行时间缩短为执行时间缩短为1212秒秒 典型案例典型案例_4_4 44 系统名称：系统名称：CRMCRM系统系统 故障现象：过多的故障现象：过多的latch freelatch free锁影响性能锁影响性能 故障处理：故障处理： 查找查找latch free

30、latch free对应的相关语句，都为对应的相关语句，都为 SELECT COL_VALUE FROM PUB_COLUMN_REFERENCE WHERE REFER_ID = :B1SELECT COL_VALUE FROM PUB_COLUMN_REFERENCE WHERE REFER_ID = :B1 而该而该SQLSQL语句都为函数语句都为函数GET_COLUMN_VALUEGET_COLUMN_VALUE 调用所产生调用所产生 查询调用该函数的相关可疑查询调用该函数的相关可疑SQLSQL语句语句 典型案例典型案例_4_4 45 系统名称：系统名称：CRMCRM系统系统 故障处理

31、：故障处理： 定位定位SQLSQL有相关子查询调用了该函数有相关子查询调用了该函数 对该对该SQLSQL进行进行tracetrace跟踪，发现该跟踪，发现该SQLSQL每执行每执行 一次，将会调用该函数一次，将会调用该函数3835538355次，也就是说次，也就是说 latch freelatch free相应的相应的SQLSQL将被执行将被执行3835538355次次 修改相关应用（修改相关应用（cachecache相关刷新机制）相关刷新机制） Oracle SQLOracle SQL优化要点优化要点 46 使用执行计划来分析使用执行计划来分析SQLSQL性能进而进行优化性能进而进行优化 使

32、用使用HINTSHINTS来干预执行计划来干预执行计划 最大限度地避免全表扫描（最大限度地避免全表扫描（FTSFTS） 减少子查询的使用减少子查询的使用 规范规范SQLSQL语句的编写，使得语句的编写，使得SQLSQL语句能够被共享语句能够被共享 减少减少SQLSQL解析的次数：使用绑定变量解析的次数：使用绑定变量 Oracle SQLOracle SQL优化要点优化要点 47 用索引提高效率用索引提高效率 避免在索引列上使用计算避免在索引列上使用计算 避免在索引列上使用函数转换避免在索引列上使用函数转换 避免改变索引列的类型避免改变索引列的类型 避免在索引列上使用避免在索引列上使用IS NU

33、LLIS NULL和和IS NOT IS NOT NULLNULL 避免在索引列上使用避免在索引列上使用like%AAAlike%AAA Oracle SQLOracle SQL优化要点优化要点 48 尽量避免尽量避免SQLSQL语句不必要的排序操作语句不必要的排序操作 UNIONUNION操作要用操作要用UNION ALLUNION ALL来代替来代替 ORDER BY ORDER BY 中的字段最好建立索引中的字段最好建立索引 尽量避免使用尽量避免使用DISTINCTDISTINCT语句语句 尽量避免使用尽量避免使用(NOT) IN(NOT) IN，而使用（，而使用（NOTNOT） EXI

34、STSEXISTS来代替来代替 对表超过对表超过10%10%以上的以上的deletedelete、updateupdate操作要重操作要重 建索引。建索引。 五、五、Oracle SQL Oracle SQL 编写规范编写规范 二、二、Oracle Oracle 体系结构体系结构 四、典型案例分析暨优化要点四、典型案例分析暨优化要点 三、三、Oracle SQL Oracle SQL 优化基础优化基础 一、培训目的一、培训目的 培训总目录培训总目录 SQLSQL编写规范编写规范_ _书写格式书写格式 50 缩进缩进 对于存储过程文件，缩进为对于存储过程文件，缩进为8 8个空格个空格 对于对于Java SourceJava Source里的里的SQLSQL字符串，不可有字符串，不可有 缩进，即每一行字符串不以空格开头缩进，即每一行字符串不以空格开头 空格空格 SQL SQL内算数运算符、逻辑运算符连接的两个内算数运算符、逻辑运算符连接的两个 元素之间必须用空格分隔元素之间必须用空格分隔 逗号之后必须接一个空格逗号之后必须接一个空格 关键字、保留字和左括号间必有一个空格关键字、保留字和左括号间必有一个空格 SQLSQL编写规范编写规范_ _书写格式书写格式 51 换行换行_1_1 Select/From/Where/Order b