Oracle培训课件.ppt

《Oracle培训课件.ppt》由会员分享，可在线阅读，更多相关《Oracle培训课件.ppt（56页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

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

2、训总目录培训总目录OracleOracle服务器架构服务器架构OracleOracle内存体系架构内存体系架构OracleOracle进程体系架构进程体系架构 用户进程用户进程:在数据库用户或批处理进程连接到在数据库用户或批处理进程连接到 Oracle DB 时启动时启动 服务器进程服务器进程:连接到连接到Oracle 实例，在用户建立实例，在用户建立 会话时启动会话时启动 后台进程后台进程:在在Oracle 实例启动时启动实例启动时启动OracleOracle存储体系架构存储体系架构OracleOracle高可用性高可用性_ _双机热备双机热备优点优点 管理简单管理简单 相对较为稳定相对较为

3、稳定缺点缺点 切换间存在停顿切换间存在停顿 备机空闲备机空闲 无扩展性无扩展性OracleOracle高可用性高可用性_ _双机热备切换双机热备切换OracleOracle高可用性高可用性_RAC_RAC优点优点 良好的可伸缩性、可扩展性良好的可伸缩性、可扩展性 高可用性高可用性 新进的新进的Cache Fusion技术技术 负载均衡负载均衡缺点缺点 管理复杂管理复杂 对网络要求高对网络要求高OracleOracle高可用性高可用性_RAC_RAC将应用和用户自动而透明地重新连接到将应用和用户自动而透明地重新连接到另一个系统另一个系统登录的上下文可被保持登录的上下文可被保持应用查询不会中断应用

4、查询不会中断当集群内节点当集群内节点 A A 失失败败, , 用户被转移用户被转移OracleOracle RACRAC负载均衡负载均衡 数据库启动时，向监听进程注册数据库启动时，向监听进程注册 节点向监听进程报告节点向监听进程报告CPU 的使用情况的使用情况 当建立连接时，监听进程选择最空闲的节点当建立连接时，监听进程选择最空闲的节点 处理请求处理请求 二、二、Oracle Oracle 体系结构体系结构 五、五、Oracle SQL Oracle SQL 编写规范编写规范 四、典型案例分析暨优化要点四、典型案例分析暨优化要点 三、三、Oracle SQL Oracle SQL 优化基础优化

5、基础 一、培训目的一、培训目的培训总目录培训总目录14性能优化概述性能优化概述 谁来调整系统谁来调整系统 应用设计人员、应用开发人员应用设计人员、应用开发人员 数据库管理人员数据库管理人员 什么时候调整什么时候调整 系统设计、系统开发过程系统设计、系统开发过程 系统运行过程系统运行过程 15应用系统类型应用系统类型 联机事务处理（联机事务处理（OLTPOLTP） 高吞吐量高吞吐量 增、删、改多增、删、改多 决策支持（决策支持（DSSDSS） 数据量大数据量大 主要用于查询主要用于查询 16SQLSQL语句处理过程语句处理过程 查询语句处理查询语句处理 DMLDML语句处理语句处理 （inser

6、tinsert、deletedelete、updateupdate） DDLDDL语句处理语句处理 （createcreate、dropdrop、alteralter）17SQLSQL语句处理各个阶段语句处理各个阶段18SQLSQL语句处理必经阶段语句处理必经阶段 第第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 7步步: Run the Statement : Run the Statement 运行语句运行语句 第第9 9步步: Close the Cursor : Close the Cursor 关闭游标关闭游标19SQLSQL语句处理可选阶段语句处理可选阶段 并行功能并行功能 第第6 6步：步：Parallelize the Statement Parallelize the Statement 并行执行语并行执行语句句 查询语句查询语句 第第3 3步：步：Describe Results of a Query Describe Results of a Query 描述查描述查

8、询的结果集询的结果集 第第4 4步：步：Define Output of a QueryDefine Output of a Query 定义查询的定义查询的输出数据输出数据 第第8 8步：步：Fetch Rows of a QueryFetch Rows of a Query 取查询出来的行取查询出来的行20DDLDDL语句处理过程语句处理过程 不同于不同于DMLDML语句和查询语句的执行语句和查询语句的执行 DDLDDL成功后需要对数据字典表进行修改成功后需要对数据字典表进行修改 分析阶段还包括分析、查找数据字典分析阶段还包括分析、查找数据字典 信息和执行信息和执行21OracleOrac

9、le优化器优化器 优化优化: : 选择最有效的执行计划来执行选择最有效的执行计划来执行 SQL SQL语句的过程语句的过程 优化器优化器: : 选择执行计划的数据库引擎选择执行计划的数据库引擎 基于规则（基于规则（RBORBO） 基于代价（基于代价（CBOCBO） 22OracleOracle统计信息统计信息 影响影响CBOCBO执行计划最关键的因素执行计划最关键的因素 分析的方法分析的方法 analyzeanalyze命令命令 dbms_statdbms_stat包包 分析的频率分析的频率 Oracle 10gOracle 10g自动分析自动分析23共享共享SQLSQL语句语句 储存于共享池

10、（储存于共享池（shared_poolshared_pool） 判断判断SQLSQL语句是否与共享池中某一语句是否与共享池中某一SQLSQL相相同的步骤（同的步骤（CURSOR_SHARINGCURSOR_SHARING） 对所发出语句的文本串进行对所发出语句的文本串进行hashedhashed 将所发出语句的文本串进行比较将所发出语句的文本串进行比较 将将SQLSQL中涉及的对象进行比较中涉及的对象进行比较24基本概念基本概念 ROWIDROWID 伪列，唯一。定位数据的最快方法伪列，唯一。定位数据的最快方法 索引创建时会记录索引创建时会记录ROWIDROWID值值 Driving Tabl

11、eDriving Table（驱动表）（驱动表）: : 外层表，用于嵌套以及外层表，用于嵌套以及hashhash连接中连接中 可选择性可选择性: :“唯一键的数量唯一键的数量/ /表中的行数表中的行数”的比值的比值25Oracle SQL Oracle SQL 执行计划执行计划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.dept

12、no 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 );26Oracle SQL Oracle SQL 执行计划图执行计划图27Oracle SQL Oracle SQL 执行计划图执行计划图28访问路径访问路径 全表扫描（全表扫描（FTSFTS） 通过通过rowidrowid的表存取（的表存取（Table Access by

13、 rowidTable Access by rowid） 索引扫描（索引扫描（Index ScanIndex Scan） 索引唯一扫描索引唯一扫描(index unique scan)(index 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: 将两个表结合在一起，一次

14、只能连接将两个表结合在一起，一次只能连接2 2个表，个表， 表连接也可以被称为表关联表连接也可以被称为表关联 排序排序合并连接（合并连接（Sort Merge JoinSort Merge Join） 嵌套循环（嵌套循环（Nested LoopsNested Loops） 哈希连接（哈希连接（Hash JoinHash Join）30排序排序- -合并连接图合并连接图31嵌套循环连接图嵌套循环连接图32连接适用情况连接适用情况 排序排序合并连接合并连接 非等值连接、关联列都有索引非等值连接、关联列都有索引 嵌套连接嵌套连接 驱动表较小驱动表较小 哈希连接哈希连接 等值连接等值连接33生成执行计

15、划生成执行计划 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）34干预执行计划干预执行计划_ _访问路径访问路径 FULL FULL / /* *+ FULL ( table ) + FULL ( table ) * */ / 指定该表使用指定该表使用FTSFTS INDEX IN

16、DEX / /* *+ 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) + NO_INDEX ( table index) * */ / 不使用该表上指定的索引

17、进行存取，仍然可以使用不使用该表上指定的索引进行存取，仍然可以使用 其它的索引进行索引扫描其它的索引进行索引扫描35干预执行计划干预执行计划_ _表连接表连接 USE_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 /*

18、*+ USE_HASH ( tab ,tab, .) + USE_HASH ( tab ,tab, .) * */ / 使用使用HASHHASH连接连接 二、二、Oracle Oracle 体系结构体系结构 五、五、Oracle SQL Oracle SQL 编写规范编写规范 四、典型案例分析暨优化要点四、典型案例分析暨优化要点 三、三、Oracle SQL Oracle SQL 优化基础优化基础 一、培训目的一、培训目的培训总目录培训总目录37典型案例典型案例_1_1 系统名称：综合集中维护支撑系统系统名称：综合集中维护支撑系统 故障现象：压力测试系统故障现象：压力测试系统CPUCPU资源资

19、源100%100% 故障分析：故障分析： 数据库实际的数据量仅为数据库实际的数据量仅为6GB6GB左右。但是每左右。但是每天却产生大概天却产生大概150GB150GB左右的左右的REDO LOGREDO LOG 使用使用LOGMNRLOGMNR分析分析ORACLEORACLE日志，发现基本上日志，发现基本上都为都为JMSSTOREJMSSTORE表的增、删操作。该表为表的增、删操作。该表为WEBLOGICWEBLOGIC的消息表的消息表（CLOBCLOB字段）字段）。38典型案例典型案例_1_1 系统名称：综合集中维护支撑系统系统名称：综合集中维护支撑系统 故障处理：故障处理： 运行相关查询业

20、务，在数据库中查询运行相关查询业务，在数据库中查询v$sqlareav$sqlarea，JMSSTOREJMSSTORE表相关操作的频率表相关操作的频率 对应用进行跟踪调试，查找每笔查询业务对应用进行跟踪调试，查找每笔查询业务所运行的所运行的SQLSQL 最终定位问题产生原因：应用程序在每笔最终定位问题产生原因：应用程序在每笔查询后面都附带了消息传输（查询后面都附带了消息传输（1010次）次） 将该将该SQLSQL屏蔽，问题得到解决屏蔽，问题得到解决39典型案例典型案例_2_2 系统名称：系统名称：OAOA系统系统 故障现象：故障现象：SQLSQL语句执行速度慢（语句执行速度慢（5S5S）se

21、lect 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 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_

22、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.fd_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_work

23、flowtype=1 and (c.security_level_code=1 or(c.security_level_code=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)

24、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_enddate desc;40典型案例典型案例_2_2 系统名称：系统名称：OAOA系统系统 故障处理：故障处理： 查看该查看该sqlsql的执行计划的执行计划 tb_engine_filecommontb_engine_filecommon表为表为FTSFTS（full full table scant

25、able scan），而且），而且costcost大大41典型案例典型案例_2_2 系统名称：系统名称：OAOA系统系统 故障处理：故障处理： 查询该表查询该表fd_c_enddatefd_c_enddate的可选择性的可选择性，发现发现很高，因此对该字段建索引，很高，因此对该字段建索引，执行计划变为执行计划变为如下，执行时间缩短为如下，执行时间缩短为0.3S0.3S左右左右42典型案例典型案例_3_3 系统名称：商务领航系统系统名称：商务领航系统 故障现象：出账速度慢故障现象：出账速度慢 故障处理：故障处理： 查找查找V$SESSIONV$SESSION中正在长时间运行的中正在长时间运行的S

26、QLSQLupdate zjxc.BIZ_CUST_BALANCE t set t.warefee = 0 where t.cusid inupdate zjxc.BIZ_CUST_BALANCE t set t.warefee = 0 where t.cusid in (select b.cusid from zjxc.biz_cusid_temp_0401 b) and (select b.cusid from zjxc.biz_cusid_temp_0401 b) and t.year_month = 201003; t.year_month = 201003; 表表biz_cusid_

27、temp_0401biz_cusid_temp_0401为为FTSFTS，但，但cusidcusid字段建有索引字段建有索引43典型案例典型案例_3_3 系统名称：商务领航系统系统名称：商务领航系统 故障处理：故障处理： 改写上述改写上述SQLSQL语句语句update zjxc.biz_cust_balance t set t.warefee = 0 where update zjxc.biz_cust_balance t set t.warefee = 0 where exists (select 1 from zjxc.biz_cusid_temp_0401 b exists (sele

28、ct 1 from zjxc.biz_cusid_temp_0401 b where 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秒秒44典型案例典型案例_4_4 系统名称：系统名称：CRMCRM系统系统 故障现象：过多的故障现象：过多的latch fre

29、elatch free锁影响性能锁影响性能 故障处理：故障处理： 查找查找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语

30、句语句45典型案例典型案例_4_4 系统名称：系统名称：CRMCRM系统系统 故障处理：故障处理： 定位定位SQLSQL有相关子查询调用了该函数有相关子查询调用了该函数 对该对该SQLSQL进行进行tracetrace跟踪，发现该跟踪，发现该SQLSQL每执行每执行一次，将会调用该函数一次，将会调用该函数3835538355次，也就是说次，也就是说latch freelatch free相应的相应的SQLSQL将被执行将被执行3835538355次次 修改相关应用（修改相关应用（cachecache相关刷新机制）相关刷新机制）46Oracle SQLOracle SQL优化要点优化要点 使用执

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

32、的类型避免改变索引列的类型 避免在索引列上使用避免在索引列上使用IS NULLIS NULL和和IS NOT IS NOT NULLNULL 避免在索引列上使用避免在索引列上使用like%AAAlike%AAA48Oracle SQLOracle SQL优化要点优化要点 尽量避免尽量避免SQLSQL语句不必要的排序操作语句不必要的排序操作 UNIONUNION操作要用操作要用UNION ALLUNION ALL来代替来代替 ORDER BY ORDER BY 中的字段最好建立索引中的字段最好建立索引 尽量避免使用尽量避免使用DISTINCTDISTINCT语句语句 尽量避免使用尽量避免使用(N

33、OT) IN(NOT) IN，而使用（，而使用（NOTNOT） EXISTSEXISTS来代替来代替 对表超过对表超过10%10%以上的以上的deletedelete、updateupdate操作要重操作要重建索引。建索引。 五、五、Oracle SQL Oracle SQL 编写规范编写规范 二、二、Oracle Oracle 体系结构体系结构 四、典型案例分析暨优化要点四、典型案例分析暨优化要点 三、三、Oracle SQL Oracle SQL 优化基础优化基础 一、培训目的一、培训目的培训总目录培训总目录50SQLSQL编写规范编写规范_ _书写格式书写格式 缩进缩进 对于存储过程文件

34、，缩进为对于存储过程文件，缩进为8 8个空格个空格 对于对于Java SourceJava Source里的里的SQLSQL字符串，不可有字符串，不可有缩进，即每一行字符串不以空格开头缩进，即每一行字符串不以空格开头 空格空格 SQL SQL内算数运算符、逻辑运算符连接的两个内算数运算符、逻辑运算符连接的两个元素之间必须用空格分隔元素之间必须用空格分隔 逗号之后必须接一个空格逗号之后必须接一个空格 关键字、保留字和左括号间必有一个空格关键字、保留字和左括号间必有一个空格51SQLSQL编写规范编写规范_ _书写格式书写格式 换行换行_1_1 Select/From/Where/Order by

35、/Group by Select/From/Where/Order by/Group by等子句必须另起一行写等子句必须另起一行写 Select Select子句内容如果只有一项，与子句内容如果只有一项，与SelectSelect同行写同行写 Select Select子句内容如果多于一项，每一项单子句内容如果多于一项，每一项单独占一行，在对应独占一行，在对应SelectSelect的基础上向右缩进的基础上向右缩进8 8个空格（个空格（Java sourceJava source无缩进）无缩进） 52SQLSQL编写规范编写规范_ _书写格式书写格式 换行换行_2_2 From From子句内

36、容如果只有一项，与子句内容如果只有一项，与FromFrom同行同行 From From子句内容如果多于一项，每一项单独子句内容如果多于一项，每一项单独占一行，在对应占一行，在对应FromFrom的基础上向右缩进的基础上向右缩进8 8个个空格（空格（Java sourceJava source无缩进）无缩进） Where Where子句的条件如果有多项，每一个条件子句的条件如果有多项，每一个条件占一行，以占一行，以ANDAND开头，且无缩进开头，且无缩进53SQLSQL编写规范编写规范_ _书写格式书写格式 换行换行_3_3 Insert Insert子句内容每个表字段单独占一行，子句内容每个表

37、字段单独占一行，无缩进；无缩进；valuesvalues每一项单独占一行，无缩进每一项单独占一行，无缩进 (Update)Set (Update)Set子句内容每一项单独占一行，子句内容每一项单独占一行，无缩进无缩进 SQL SQL文中间不允许出现空行文中间不允许出现空行 Java source Java source里单引号必须跟所属的里单引号必须跟所属的SQLSQL子子句处在同一行，连接符（句处在同一行，连接符（+）必须在行首）必须在行首54SQLSQL编写规范编写规范 不等于统一使用不等于统一使用“” 数据库查询时使用表的别名数据库查询时使用表的别名 SQLSQL文本对表字段扩展的兼容性文本对表字段扩展的兼容性 在在Java sourceJava source里使用里使用Select Select * *时，严禁通时，严禁通过过getString(1)getString(1)的形式得到查询结果，必须的形式得到查询结果，必须使用使用getString(getString(字段名字段名)的形式的形式 使用使用InsertInsert时，必须指定插入的字段名，严时，必须指定插入的字段名，严禁不指定字段名直接插入禁不指定字段名直接插入valuesvaluesThanks结束结束