Oracle培训课件

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

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

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

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

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

6、5 联机事务处理（联机事务处理（OLTPOLTP） 高吞吐量高吞吐量 增、删、改多增、删、改多 决策支持（决策支持（DSSDSS） 数据量大数据量大 主要用于查询主要用于查询 SQLSQL语句处理过程语句处理过程16 查询语句处理查询语句处理 DMLDML语句处理语句处理 （insertinsert、deletedelete、updateupdate） DDLDDL语句处理语句处理 （createcreate、dropdrop、alteralter）SQLSQL语句处理各个阶段语句处理各个阶段17SQLSQL语句处理必经阶段语句处理必经阶段18 第第1 1步步: Create a Cursor

7、 : 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语句处理可选阶段语句处理可选阶段19 并行功能并行功能 第第6 6步：步：Pa

8、rallelize 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 QueryFetch Rows of a Query 取查询出来的行取查询出来的行DDL

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

10、响影响CBOCBO执行计划最关键的因素执行计划最关键的因素 分析的方法分析的方法 analyzeanalyze命令命令 dbms_statdbms_stat包包 分析的频率分析的频率 Oracle 10gOracle 10g自动分析自动分析共享共享SQLSQL语句语句23 储存于共享池（储存于共享池（shared_poolshared_pool） 判断判断SQLSQL语句是否与共享池中某一语句是否与共享池中某一SQLSQL相相同的步骤（同的步骤（CURSOR_SHARINGCURSOR_SHARING） 对所发出语句的文本串进行对所发出语句的文本串进行hashedhashed 将所发出语句的文

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

12、ECT SELECT enameename, job, , job, salsal, , dnamedname FROM FROM empemp, , deptdept WHERE WHERE emp.deptnoemp.deptno = = dept.deptnodept.deptno AND NOT EXISTS AND NOT EXISTS ( SELECT ( SELECT * * FROM FROM salgradesalgrade WHERE WHERE emp.salemp.sal BETWEEN BETWEEN losallosal AND AND hisalhisal );

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

14、引全扫描索引全扫描(index full scan)(index full scan) 索引快速扫描索引快速扫描(index fast full scan)(index fast full scan)表连接表连接29 表连接表连接JOIN:JOIN: 将两个表结合在一起，一次只能连接将两个表结合在一起，一次只能连接2 2个表，个表， 表连接也可以被称为表关联表连接也可以被称为表关联 排序排序合并连接（合并连接（Sort Merge JoinSort Merge Join） 嵌套循环（嵌套循环（Nested LoopsNested Loops） 哈希连接（哈希连接（Hash JoinHash J

15、oin）排序排序- -合并连接图合并连接图30嵌套循环连接图嵌套循环连接图31连接适用情况连接适用情况32 排序排序合并连接合并连接 非等值连接、关联列都有索引非等值连接、关联列都有索引 嵌套连接嵌套连接 驱动表较小驱动表较小 哈希连接哈希连接 等值连接等值连接生成执行计划生成执行计划33 sql set autotrace onsql set autotrace on sql explain plan for select sql explain plan for select 用用dbms_systemdbms_system存储过程生成执行计划存储过程生成执行计划 用用PL/SQL DEV

16、ELOPERPL/SQL DEVELOPER（F5F5）干预执行计划干预执行计划_ _访问路径访问路径34 FULL FULL / /* *+ FULL ( table ) + FULL ( table ) * */ / 指定该表使用指定该表使用FTSFTS INDEX INDEX / /* *+ INDEX ( table index) + INDEX ( table index) * */ / 使用该表上指定的索引对表进行索引扫描使用该表上指定的索引对表进行索引扫描 INDEX_FFS / INDEX_FFS /* *+ INDEX_FFS ( table index) + INDEX_F

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

18、接使用嵌套连接 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连接连接 二、二、Oracle Oracle 体系结构体系结构 五、五、Oracle SQL Oracle SQL 编写规范编写规范 四、典型案例分析暨优化要点四、典型案例分析暨优

19、化要点 三、三、Oracle SQL Oracle SQL 优化基础优化基础 一、培训目的一、培训目的培训总目录培训总目录典型案例典型案例_1_137 系统名称：综合集中维护支撑系统系统名称：综合集中维护支撑系统 故障现象：压力测试系统故障现象：压力测试系统CPUCPU资源资源100%100% 故障分析：故障分析： 数据库实际的数据量仅为数据库实际的数据量仅为6GB6GB左右。但是每左右。但是每天却产生大概天却产生大概150GB150GB左右的左右的REDO LOGREDO LOG 使用使用LOGMNRLOGMNR分析分析ORACLEORACLE日志，发现基本上日志，发现基本上都为都为JMSS

20、TOREJMSSTORE表的增、删操作。该表为表的增、删操作。该表为WEBLOGICWEBLOGIC的消息表的消息表（CLOBCLOB字段）字段）。典型案例典型案例_1_138 系统名称：综合集中维护支撑系统系统名称：综合集中维护支撑系统 故障处理：故障处理： 运行相关查询业务，在数据库中查询运行相关查询业务，在数据库中查询v$sqlareav$sqlarea，JMSSTOREJMSSTORE表相关操作的频率表相关操作的频率 对应用进行跟踪调试，查找每笔查询业务对应用进行跟踪调试，查找每笔查询业务所运行的所运行的SQLSQL 最终定位问题产生原因：应用程序在每笔最终定位问题产生原因：应用程序在

21、每笔查询后面都附带了消息传输（查询后面都附带了消息传输（1010次）次） 将该将该SQLSQL屏蔽，问题得到解决屏蔽，问题得到解决典型案例典型案例_2_239 系统名称：系统名称：OAOA系统系统 故障现象：故障现象：SQLSQL语句执行速度慢（语句执行速度慢（5S5S）select distinct select distinct b.fd_workflowname,a.fd_fileid,a.fd_c_filetitle,a.fd_c_enddateb.fd_workflowname,a.fd_fileid,a.fd_c_filetitle,a.fd_c_enddatefrom from

22、tb_model_workflowtb_model_workflow b,tb_documentb,tb_document c,tb_engine_filecommonc,tb_engine_filecommon a,tb_engine_filetachea,tb_engine_filetache g gwhere where a.fd_c_workflowida.fd_c_workflowid= =b.fd_workflowidb.fd_workflowid and and g.fd_fileidg.fd_fileid= =a.fd_fileida.fd_fileid and and a.f

23、d_c_fileno=c.ida.fd_c_fileno=c.idand and a.fd_c_isenda.fd_c_isend=1and =1and b.fd_workflowtypeb.fd_workflowtype=1 and (=1 and (c.security_level_codec.security_level_code=1 =1 or(or(c.security_level_codec.security_level_code=2 and =2 and g.fd_ft_staffidg.fd_ft_staffid=2)or =2)or ( (c.security_level_c

24、odec.security_level_code=3 and =3 and g.fd_ft_staffidg.fd_ft_staffid=2)=2)and and a.fd_c_enddatea.fd_c_enddate=(sysdate-30)and =(sysdate-30)and a.fd_c_enddatea.fd_c_enddate=(sysdate+1)=(sysdate+1)and (and (pany_pany_id=1 or =1 or c.send_companyc.send_company=1) =1) order by order by a.fd_c_enddatea.

25、fd_c_enddate descdesc; ;典型案例典型案例_2_240 系统名称：系统名称：OAOA系统系统 故障处理：故障处理： 查看该查看该sqlsql的执行计划的执行计划 tb_engine_filecommontb_engine_filecommon表为表为FTSFTS（full full table scantable scan），而且），而且costcost大大典型案例典型案例_2_241 系统名称：系统名称：OAOA系统系统 故障处理：故障处理： 查询该表查询该表fd_c_enddatefd_c_enddate的可选择性的可选择性，发现发现很高，因此对该字段建索引，很高，因

26、此对该字段建索引，执行计划变为执行计划变为如下，执行时间缩短为如下，执行时间缩短为0.3S0.3S左右左右典型案例典型案例_3_342 系统名称：商务领航系统系统名称：商务领航系统 故障现象：出账速度慢故障现象：出账速度慢 故障处理：故障处理： 查找查找V$SESSIONV$SESSION中正在长时间运行的中正在长时间运行的SQLSQLupdate update zjxc.BIZ_CUST_BALANCEzjxc.BIZ_CUST_BALANCE t set t set t.warefeet.warefee = 0 where = 0 where t.cusidt.cusid in in (s

27、elect (select b.cusidb.cusid from _cusid_temp_0401 b) and from _cusid_temp_0401 b) and t.year_montht.year_month = 201003; = 201003; 表表biz_cusid_temp_0401biz_cusid_temp_0401为为FTSFTS，但，但cusidcusid字段建有索引字段建有索引典型案例典型案例_3_343 系统名称：商务领航系统系统名称：商务领航系统 故障处理：故障处理： 改写上述改写上述SQLSQL语句语句update upda

28、te _cust__cust_balance t set t set t.warefeet.warefee = 0 where = 0 where exists (select 1 from _cusid_temp_0401 b exists (select 1 from _cusid_temp_0401 b where where t.cusidt.cusid= =b.cusidb.cusid and and t.year_montht.year_month = 201003) ; = 201003) ; 表表bi

29、z_cusid_temp_0401biz_cusid_temp_0401的访问从的访问从FTSFTS改变改变为索引唯一扫描为索引唯一扫描 SQL SQL执行时间缩短为执行时间缩短为1212秒秒典型案例典型案例_4_444 系统名称：系统名称：CRMCRM系统系统 故障现象：过多的故障现象：过多的latch freelatch free锁影响性能锁影响性能 故障处理：故障处理： 查找查找latch freelatch free对应的相关语句，都为对应的相关语句，都为SELECT COL_VALUE FROM PUB_COLUMN_REFERENCE WHERE REFER_ID = :B1SEL

30、ECT COL_VALUE FROM PUB_COLUMN_REFERENCE WHERE REFER_ID = :B1 而该而该SQLSQL语句都为函数语句都为函数GET_COLUMN_VALUEGET_COLUMN_VALUE调用所产生调用所产生 查询调用该函数的相关可疑查询调用该函数的相关可疑SQLSQL语句语句典型案例典型案例_4_445 系统名称：系统名称：CRMCRM系统系统 故障处理：故障处理： 定位定位SQLSQL有相关子查询调用了该函数有相关子查询调用了该函数 对该对该SQLSQL进行进行tracetrace跟踪，发现该跟踪，发现该SQLSQL每执行每执行一次，将会调用该函数

31、一次，将会调用该函数3835538355次，也就是说次，也就是说latch freelatch free相应的相应的SQLSQL将被执行将被执行3835538355次次 修改相关应用（修改相关应用（cachecache相关刷新机制）相关刷新机制）Oracle SQLOracle SQL优化要点优化要点46 使用执行计划来分析使用执行计划来分析SQLSQL性能进而进行优化性能进而进行优化 使用使用HINTSHINTS来干预执行计划来干预执行计划 最大限度地避免全表扫描（最大限度地避免全表扫描（FTSFTS） 减少子查询的使用减少子查询的使用 规范规范SQLSQL语句的编写，使得语句的编写，使得S

32、QLSQL语句能够被共享语句能够被共享 减少减少SQLSQL解析的次数：使用绑定变量解析的次数：使用绑定变量Oracle SQLOracle SQL优化要点优化要点47 用索引提高效率用索引提高效率 避免在索引列上使用计算避免在索引列上使用计算 避免在索引列上使用函数转换避免在索引列上使用函数转换 避免改变索引列的类型避免改变索引列的类型 避免在索引列上使用避免在索引列上使用IS NULLIS NULL和和IS NOT IS NOT NULLNULL 避免在索引列上使用避免在索引列上使用like%AAAlike%AAAOracle SQLOracle SQL优化要点优化要点48 尽量避免尽量避

33、免SQLSQL语句不必要的排序操作语句不必要的排序操作 UNIONUNION操作要用操作要用UNION ALLUNION ALL来代替来代替 ORDER BY ORDER BY 中的字段最好建立索引中的字段最好建立索引 尽量避免使用尽量避免使用DISTINCTDISTINCT语句语句 尽量避免使用尽量避免使用(NOT) IN(NOT) IN，而使用（，而使用（NOTNOT） EXISTSEXISTS来代替来代替 对表超过对表超过10%10%以上的以上的deletedelete、updateupdate操作要重操作要重建索引。建索引。 五、五、Oracle SQL Oracle SQL 编写规范

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