Oracle培训学习课程

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

2、训总目录第2页/共55页第二页，编辑于星期六：十一点 二十二分。OracleOracle服务器架构第3页/共55页第三页，编辑于星期六：十一点 二十二分。OracleOracle内存体系架构第4页/共55页第四页，编辑于星期六：十一点 二十二分。OracleOracle进程体系架构 用户进程:在数据库用户或批处理进程连接到 Oracle DB 时启动 服务器进程:连接到Oracle 实例，在用户建立 会话时启动 后台进程:在Oracle 实例启动时启动第5页/共55页第五页，编辑于星期六：十一点 二十二分。OracleOracle存储体系架构第6页/共55页第六页，编辑于星期六：十一点 二十二

3、分。OracleOracle高可用性_ _双机热备优点优点 管理简单管理简单 相对较为稳定相对较为稳定缺点缺点 切换间存在停顿切换间存在停顿 备机空闲备机空闲 无扩展性无扩展性第7页/共55页第七页，编辑于星期六：十一点 二十二分。OracleOracle高可用性_ _双机热备切换第8页/共55页第八页，编辑于星期六：十一点 二十二分。OracleOracle高可用性_RAC_RAC优点优点 良好的可伸缩性、可扩展性良好的可伸缩性、可扩展性 高可用性高可用性 新进的新进的Cache Fusion技术技术 负载均衡负载均衡缺点缺点 管理复杂管理复杂 对网络要求高对网络要求高第9页/共55页第九页

4、，编辑于星期六：十一点 二十二分。OracleOracle高可用性_RAC_RAC将应用和用户自动而透明地重新连接到另将应用和用户自动而透明地重新连接到另一个系统一个系统登录的上下文可被保持登录的上下文可被保持应用查询不会中断应用查询不会中断当集群内节点 A 失败, 用户被转移第10页/共55页第十页，编辑于星期六：十一点 二十二分。OracleOracle RACRAC负载均衡 数据库启动时，向监听进程注册数据库启动时，向监听进程注册 节点向监听进程报告节点向监听进程报告CPU 的使用情况的使用情况 当建立连接时，监听进程选择最空闲的节点当建立连接时，监听进程选择最空闲的节点 处理请求处理请

5、求第11页/共55页第十一页，编辑于星期六：十一点 二十二分。 二、二、Oracle 体系结构体系结构 五、五、Oracle SQL 编写规范编写规范 四、典型案例分析暨优化要点四、典型案例分析暨优化要点 三、三、Oracle SQL 优化基础优化基础 一、培训目的一、培训目的培训总目录培训总目录第12页/共55页第十二页，编辑于星期六：十一点 二十二分。性能优化概述13 谁来调整系统谁来调整系统 应用设计人员、应用开发人员应用设计人员、应用开发人员 数据库管理人员数据库管理人员 什么时候调整什么时候调整 系统设计、系统开发过程系统设计、系统开发过程 系统运行过程系统运行过程 第13页/共55

6、页第十三页，编辑于星期六：十一点 二十二分。应用系统类型14 联机事务处理（联机事务处理（OLTPOLTP） 高吞吐量高吞吐量 增、删、改多增、删、改多 决策支持（决策支持（DSSDSS） 数据量大数据量大 主要用于查询主要用于查询 第14页/共55页第十四页，编辑于星期六：十一点 二十二分。SQLSQL语句处理过程15 查询语句处理查询语句处理 DMLDML语句处理语句处理 （insertinsert、deletedelete、updateupdate） DDLDDL语句处理语句处理 （createcreate、dropdrop、alteralter）第15页/共55页第十五页，编辑于星期六

7、：十一点 二十二分。SQLSQL语句处理各个阶段16第16页/共55页第十六页，编辑于星期六：十一点 二十二分。SQLSQL语句处理必经阶段17 第第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 运行语句运行语句 第

8、第9 9步步: Close the Cursor : Close the Cursor 关闭游标关闭游标第17页/共55页第十七页，编辑于星期六：十一点 二十二分。SQLSQL语句处理可选阶段18 并行功能并行功能 第第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

9、 QueryDefine Output of a Query 定义查询的输出定义查询的输出数据数据 第第8 8步：步：Fetch Rows of a QueryFetch Rows of a Query 取查询出来的行取查询出来的行第18页/共55页第十八页，编辑于星期六：十一点 二十二分。DDLDDL语句处理过程19 不同于不同于DMLDML语句和查询语句的执行语句和查询语句的执行 DDLDDL成功后需要对数据字典表进行修改成功后需要对数据字典表进行修改 分析阶段还包括分析、查找数据字典分析阶段还包括分析、查找数据字典 信息和执行信息和执行第19页/共55页第十九页，编辑于星期六：十一点 二

10、十二分。OracleOracle优化器20 优化优化: : 选择最有效的执行计划来执行选择最有效的执行计划来执行 SQL SQL语句的过程语句的过程 优化器优化器: : 选择执行计划的数据库引擎选择执行计划的数据库引擎 基于规则（基于规则（RBORBO） 基于代价（基于代价（CBOCBO） 第20页/共55页第二十页，编辑于星期六：十一点 二十二分。OracleOracle统计信息21 影响影响CBOCBO执行计划最关键的因素执行计划最关键的因素 分析的方法分析的方法 analyzeanalyze命令命令 dbms_statdbms_stat包包 分析的频率分析的频率 Oracle 10gOr

11、acle 10g自动分析自动分析第21页/共55页第二十一页，编辑于星期六：十一点 二十二分。共享SQLSQL语句22 储存于共享池（储存于共享池（shared_poolshared_pool） 判断判断SQLSQL语句是否与共享池中某一语句是否与共享池中某一SQLSQL相相同的步骤（同的步骤（CURSOR_SHARINGCURSOR_SHARING） 对所发出语句的文本串进行对所发出语句的文本串进行hashedhashed 将所发出语句的文本串进行比较将所发出语句的文本串进行比较 将将SQLSQL中涉及的对象进行比较中涉及的对象进行比较第22页/共55页第二十二页，编辑于星期六：十一点 二十

12、二分。基本概念23 ROWIDROWID 伪列，唯一。定位数据的最快方法伪列，唯一。定位数据的最快方法 索引创建时会记录索引创建时会记录ROWIDROWID值值 Driving TableDriving Table（驱动表）（驱动表）: : 外层表，用于嵌套以及外层表，用于嵌套以及hashhash连接中连接中 可选择性可选择性: : “唯一键的数量唯一键的数量/ /表中的行数表中的行数”的比值的比值第23页/共55页第二十三页，编辑于星期六：十一点 二十二分。Oracle SQL Oracle SQL 执行计划24SQLSQL语句语句: :SELECT ename, job, sal, dna

13、meSELECT 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 emp.sal BETWEEN losal AND hisal ); BETWEEN losal AND hisal );第24页/共55页第二十四页，

14、编辑于星期六：十一点 二十二分。Oracle SQL Oracle SQL 执行计划图25第25页/共55页第二十五页，编辑于星期六：十一点 二十二分。Oracle SQL Oracle SQL 执行计划图26第26页/共55页第二十六页，编辑于星期六：十一点 二十二分。访问路径27 全表扫描（全表扫描（FTSFTS） 通过通过rowidrowid的表存取（的表存取（Table Access by rowidTable Access by rowid） 索引扫描（索引扫描（Index ScanIndex Scan） 索引唯一扫描索引唯一扫描(index unique scan)(index u

15、nique scan) 索引范围扫描索引范围扫描(index range scan)(index range scan) 索引全扫描索引全扫描(index full scan)(index full scan) 索引快速扫描索引快速扫描(index fast full scan)(index fast full scan)第27页/共55页第二十七页，编辑于星期六：十一点 二十二分。表连接28 表连接表连接JOIN:JOIN: 将两个表结合在一起，一次只能连接将两个表结合在一起，一次只能连接2 2个表，个表， 表连接也可以被称为表关联表连接也可以被称为表关联 排序排序合并连接（合并连接（Sor

16、t Merge JoinSort Merge Join） 嵌套循环（嵌套循环（Nested LoopsNested Loops） 哈希连接（哈希连接（Hash JoinHash Join）第28页/共55页第二十八页，编辑于星期六：十一点 二十二分。排序- -合并连接图29第29页/共55页第二十九页，编辑于星期六：十一点 二十二分。嵌套循环连接图30第30页/共55页第三十页，编辑于星期六：十一点 二十二分。连接适用情况31 排序排序合并连接合并连接 非等值连接、关联列都有索引非等值连接、关联列都有索引 嵌套连接嵌套连接 驱动表较小驱动表较小 哈希连接哈希连接 等值连接等值连接第31页/共5

17、5页第三十一页，编辑于星期六：十一点 二十二分。生成执行计划32 sql set autotrace onsql set autotrace on sql explain plan for select sql explain plan for select 用用dbms_systemdbms_system存储过程生成执行计划存储过程生成执行计划 用用PL/SQL DEVELOPERPL/SQL DEVELOPER（F5F5）第32页/共55页第三十二页，编辑于星期六：十一点 二十二分。干预执行计划_ _访问路径33 FULL FULL / /* *+ FULL ( table ) + FUL

18、L ( table ) * */ / 指定该表使用指定该表使用FTSFTS INDEX INDEX / /* *+ INDEX ( table index) + INDEX ( table index) * */ / 使用该表上指定的索引对表进行索引扫描使用该表上指定的索引对表进行索引扫描 INDEX_FFS / INDEX_FFS /* *+ INDEX_FFS ( table index) + INDEX_FFS ( table index) * */ / 使用快速全索引扫描使用快速全索引扫描 NO_INDEX / NO_INDEX /* *+ NO_INDEX ( table index

19、) + NO_INDEX ( table index) * */ / 不使用该表上指定的索引进行存取，仍然可以使用不使用该表上指定的索引进行存取，仍然可以使用 其它的索引进行索引扫描其它的索引进行索引扫描第33页/共55页第三十三页，编辑于星期六：十一点 二十二分。干预执行计划_ _表连接34 USE_NL / USE_NL /* *+ USE_NL ( tab ,tab, . ) + USE_NL ( tab ,tab, . ) * */ / 使用嵌套连接使用嵌套连接 USE_MERGE / USE_MERGE /* *+ USE_MERGE ( tab ,tab,.) + USE_MERG

20、E ( tab ,tab,.) * */ / 使用排序使用排序- - -合并连接合并连接 USE_HASH / USE_HASH /* *+ USE_HASH ( tab ,tab, .) + USE_HASH ( tab ,tab, .) * */ / 使用使用HASHHASH连接连接第34页/共55页第三十四页，编辑于星期六：十一点 二十二分。 二、二、Oracle 体系结构体系结构 五、五、Oracle SQL 编写规范编写规范 四、典型案例分析暨优化要点四、典型案例分析暨优化要点 三、三、Oracle SQL 优化基础优化基础 一、培训目的一、培训目的培训总目录培训总目录第35页/共5

21、5页第三十五页，编辑于星期六：十一点 二十二分。典型案例_1_136 系统名称：综合集中维护支撑系统系统名称：综合集中维护支撑系统 故障现象：压力测试系统故障现象：压力测试系统CPUCPU资源资源100%100% 故障分析：故障分析： 数据库实际的数据量仅为数据库实际的数据量仅为6GB6GB左右。但是每左右。但是每天却产生大概天却产生大概150GB150GB左右的左右的REDO LOGREDO LOG 使用使用LOGMNRLOGMNR分析分析ORACLEORACLE日志，发现基本上都日志，发现基本上都为为JMSSTOREJMSSTORE表的增、删操作。该表为表的增、删操作。该表为WEBLOGI

22、CWEBLOGIC的消息表的消息表（CLOBCLOB字段）字段）。第36页/共55页第三十六页，编辑于星期六：十一点 二十二分。典型案例_1_137 系统名称：综合集中维护支撑系统系统名称：综合集中维护支撑系统 故障处理：故障处理： 运行相关查询业务，在数据库中查询运行相关查询业务，在数据库中查询v$sqlareav$sqlarea，JMSSTOREJMSSTORE表相关操作的频率表相关操作的频率 对应用进行跟踪调试，查找每笔查询业务对应用进行跟踪调试，查找每笔查询业务所运行的所运行的SQLSQL 最终定位问题产生原因：应用程序在每笔查询最终定位问题产生原因：应用程序在每笔查询后面都附带了消息

23、传输（后面都附带了消息传输（1010次）次） 将该将该SQLSQL屏蔽，问题得到解决屏蔽，问题得到解决第37页/共55页第三十七页，编辑于星期六：十一点 二十二分。典型案例_2_238 系统名称：系统名称：OAOA系统系统 故障现象：故障现象：SQLSQL语句执行速度慢（语句执行速度慢（5S5S）select distinct b.fd_workflowname,a.fd_fileid,a.fd_c_filetitle,a.fd_c_enddateselect distinct b.fd_workflowname,a.fd_fileid,a.fd_c_filetitle,a.fd_c_endd

24、atefrom tb_model_workflow b,tb_document c,tb_engine_filecommon a,tb_engine_filetache gfrom tb_model_workflow b,tb_document c,tb_engine_filecommon a,tb_engine_filetache gwhere a.fd_c_workflowid=b.fd_workflowid and g.fd_fileid=a.fd_fileid and where a.fd_c_workflowid=b.fd_workflowid and g.fd_fileid=a.f

25、d_fileid and a.fd_c_fileno=c.ida.fd_c_fileno=c.idand 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=2 and g.fd_ft_staffid=2)or (c.security_level_code=3 and or(c.security_level_code=2 a

26、nd g.fd_ft_staffid=2)or (c.security_level_code=3 and g.fd_ft_staffid=2)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=1) and (pany_id=1 or c.send_company=1) order by a.fd_c_enddate desc;order by a.fd_c_en

27、ddate desc;第38页/共55页第三十八页，编辑于星期六：十一点 二十二分。典型案例_2_239 系统名称：系统名称：OAOA系统系统 故障处理：故障处理： 查看该查看该sqlsql的执行计划的执行计划 tb_engine_filecommontb_engine_filecommon表为表为FTSFTS（full full table scantable scan），而且），而且costcost大大第39页/共55页第三十九页，编辑于星期六：十一点 二十二分。典型案例_2_240 系统名称：系统名称：OAOA系统系统 故障处理：故障处理： 查询该表查询该表fd_c_enddatefd_

28、c_enddate的可选择性的可选择性，发现很发现很高，因此对该字段建索引，高，因此对该字段建索引，执行计划变为如下，执行计划变为如下，执行时间缩短为执行时间缩短为0.3S0.3S左右左右第40页/共55页第四十页，编辑于星期六：十一点 二十二分。典型案例_3_341 系统名称：商务领航系统系统名称：商务领航系统 故障现象：出账速度慢故障现象：出账速度慢 故障处理：故障处理： 查找查找V$SESSIONV$SESSION中正在长时间运行的中正在长时间运行的SQLSQLupdate zjxc.BIZ_CUST_BALANCE t set t.warefee = 0 where t.cusid i

29、nupdate 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字字段建有索引段建有索引第41页/共5

30、5页第四十一页，编辑于星期六：十一点 二十二分。典型案例_3_342 系统名称：商务领航系统系统名称：商务领航系统 故障处理：故障处理： 改写上述改写上述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

31、 t.cusid=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秒秒第42页/共55页第四十二页，编辑于星期六：十一点 二十二分。典型案例_4_443 系统名称：系统名称：CRMCRM系统系统 故障现象：过多的故障现象：过多的latch freelatch free锁影响性

32、能锁影响性能 故障处理：故障处理： 查找查找latch freelatch 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语句语句第43页/共55页第四十

33、三页，编辑于星期六：十一点 二十二分。典型案例_4_444 系统名称：系统名称：CRMCRM系统系统 故障处理：故障处理： 定位定位SQLSQL有相关子查询调用了该函数有相关子查询调用了该函数 对该对该SQLSQL进行进行tracetrace跟踪，发现该跟踪，发现该SQLSQL每执行每执行一次，将会调用该函数一次，将会调用该函数3835538355次，也就是说次，也就是说latch freelatch free相应的相应的SQLSQL将被执行将被执行3835538355次次 修改相关应用（修改相关应用（cachecache相关刷新机制）相关刷新机制）第44页/共55页第四十四页，编辑于星期六：

34、十一点 二十二分。Oracle SQLOracle SQL优化要点45 使用执行计划来分析使用执行计划来分析SQLSQL性能进而进行优化性能进而进行优化 使用使用HINTSHINTS来干预执行计划来干预执行计划 最大限度地避免全表扫描（最大限度地避免全表扫描（FTSFTS） 减少子查询的使用减少子查询的使用 规范规范SQLSQL语句的编写，使得语句的编写，使得SQLSQL语句能够被共享语句能够被共享 减少减少SQLSQL解析的次数：使用绑定变量解析的次数：使用绑定变量第45页/共55页第四十五页，编辑于星期六：十一点 二十二分。Oracle SQLOracle SQL优化要点46 用索引提高效

35、率用索引提高效率 避免在索引列上使用计算避免在索引列上使用计算 避免在索引列上使用函数转换避免在索引列上使用函数转换 避免改变索引列的类型避免改变索引列的类型 避免在索引列上使用避免在索引列上使用IS NULLIS NULL和和IS NOT IS NOT NULLNULL 避免在索引列上使用避免在索引列上使用like%AAAlike%AAA第46页/共55页第四十六页，编辑于星期六：十一点 二十二分。Oracle SQLOracle SQL优化要点47 尽量避免尽量避免SQLSQL语句不必要的排序操作语句不必要的排序操作 UNIONUNION操作要用操作要用UNION ALLUNION ALL

36、来代替来代替 ORDER BY ORDER BY 中的字段最好建立索引中的字段最好建立索引 尽量避免使用尽量避免使用DISTINCTDISTINCT语句语句尽量避免使用尽量避免使用(NOT) IN(NOT) IN，而使用（，而使用（NOTNOT） EXISTSEXISTS来代替来代替 对表超过对表超过10%10%以上的以上的deletedelete、updateupdate操作要重建操作要重建索引。索引。第47页/共55页第四十七页，编辑于星期六：十一点 二十二分。 五、五、Oracle SQL 编写规范编写规范 二、二、Oracle 体系结构体系结构 四、典型案例分析暨优化要点四、典型案例分

37、析暨优化要点 三、三、Oracle SQL 优化基础优化基础 一、培训目的一、培训目的培训总目录培训总目录第48页/共55页第四十八页，编辑于星期六：十一点 二十二分。SQLSQL编写规范_ _书写格式49 缩进缩进 对于存储过程文件，缩进为对于存储过程文件，缩进为8 8个空格个空格 对于对于Java SourceJava Source里的里的SQLSQL字符串，不可有缩字符串，不可有缩进，即每一行字符串不以空格开头进，即每一行字符串不以空格开头 空格空格 SQL SQL内算数运算符、逻辑运算符连接的两个内算数运算符、逻辑运算符连接的两个元素之间必须用空格分隔元素之间必须用空格分隔 逗号之后必须接一个空格逗号之后必须接一个空格 关键字、保留字和左括号间必有一个空格关键字、保留字和左括号间必有一个空格第49页/共55页第四十九页，编辑于星期六：十一点 二十二分。SQLSQL编写规范_ _书写格式50 换行换行_1_1 Select/From/Where/Order by/Group by Select/From/Where/Order by/Group by等等子句必须另起一行写子句必须