Oracle培训课件.ppt

二、二、Oracle 体系体系结构构 五、五、Oracle SQL编写写规范范 四、典型案例分析四、典型案例分析暨优化要点化要点 三、三、Oracle SQL优化基化基础 一、培一、培训目的目的培训总目录培训总目录培训目的培训目标培训内容课堂交互 本次课程大约需要3个小时，课后，我们会留半个小时与大家进行探讨，希望在 座各位在培训过程中记录下自己的问题；Oracle 体系结构；Oracle SQL优化及编写规范；了解Oracle 体系架构；掌握Oracle SQL优化基本方法；Oracle SQL优化典型案例分析；二、二、Oracle 体系体系结构构 五、五、Oracle SQL 编写写规范范 四、典型案例分析四、典型案例分析暨优化要点化要点 三、三、Oracle SQL 优化基化基础 一、培一、培训目的目的培训总目录培训总目录Oracle服务器架构Oracle内存体系架构Oracle进程体系架构 用户进程用户进程:在数据库用户或批处理进程连接到在数据库用户或批处理进程连接到 Oracle DB 时启动时启动 服务器进程服务器进程:连接到连接到Oracle 实例，在用户建立实例，在用户建立 会话时启动会话时启动 后台进程后台进程:在在Oracle 实例启动时启动实例启动时启动Oracle存储体系架构Oracle高可用性_双机热备优点优点 管理简单管理简单 相对较为稳定相对较为稳定缺点缺点 切换间存在停顿切换间存在停顿 备机空闲备机空闲 无扩展性无扩展性Computer BComputer BComputer BComputer AComputer AComputer AOracle高可用性_双机热备切换Oracle高可用性_RAC优点优点 良好的可伸缩性、可扩展性良好的可伸缩性、可扩展性 高可用性高可用性 新进的新进的Cache Fusion技术技术 负载均衡负载均衡缺点缺点 管理复杂管理复杂 对网络要求高对网络要求高Oracle高可用性_RAC将应用和用户自动而透明地重新连接到将应用和用户自动而透明地重新连接到另一个系统另一个系统登录的上下文可被保持登录的上下文可被保持应用查询不会中断应用查询不会中断节点节点节点节点A A节点节点节点节点B B节点节点节点节点A A节点节点节点节点B B当集群内节点当集群内节点 A 失败失败,用户被转移用户被转移Oracle RAC负载均衡 数据库启动时，向监听进程注册数据库启动时，向监听进程注册 节点向监听进程报告节点向监听进程报告CPU CPU 的使用情况的使用情况 当建立连接时，监听进程选择最空闲的节点当建立连接时，监听进程选择最空闲的节点 处理请求处理请求 二、二、Oracle 体系体系结构构 五、五、Oracle SQL 编写写规范范 四、典型案例分析四、典型案例分析暨优化要点化要点 三、三、Oracle SQL 优化基化基础 一、培一、培训目的目的培训总目录培训总目录性能优化概述14谁来调整系统谁来调整系统 应用设计人员、应用开发人员应用设计人员、应用开发人员 数据库管理人员数据库管理人员 什么时候调整什么时候调整 系统设计、系统开发过程系统设计、系统开发过程 系统运行过程系统运行过程 应用系统类型15联机事务处理（联机事务处理（OLTPOLTP）高吞吐量高吞吐量 增、删、改多增、删、改多 决策支持（决策支持（DSSDSS）数据量大数据量大 主要用于查询主要用于查询 SQL语句处理过程16 查询语句处理查询语句处理 DMLDML语句处理语句处理 （insertinsert、deletedelete、updateupdate）DDLDDL语句处理语句处理 （createcreate、dropdrop、alteralter）SQL语句处理各个阶段17SQL语句处理必经阶段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 关闭游标关闭游标SQL语句处理可选阶段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 QueryFetch Rows of a Query 取查询出来的行取查询出来的行DDL语句处理过程20 不同于不同于DMLDML语句和查询语句的执行语句和查询语句的执行 DDLDDL成功后需要对数据字典表进行修改成功后需要对数据字典表进行修改 分析阶段还包括分析、查找数据字典分析阶段还包括分析、查找数据字典 信息和执行信息和执行Oracle优化器21优化优化:选择最有效的执行计划来执行选择最有效的执行计划来执行 SQL SQL语句的过程语句的过程优化器优化器:选择执行计划的数据库引擎选择执行计划的数据库引擎 基于规则（基于规则（RBORBO）基于代价（基于代价（CBOCBO）Oracle统计信息22影响影响CBOCBO执行计划最关键的因素执行计划最关键的因素分析的方法分析的方法 analyzeanalyze命令命令 dbms_statdbms_stat包包 分析的频率分析的频率Oracle 10gOracle 10g自动分析自动分析共享SQL语句23储存于共享池（储存于共享池（shared_poolshared_pool）判断判断SQLSQL语句是否与共享池中某一语句是否与共享池中某一SQLSQL相相同的步骤（同的步骤（CURSOR_SHARINGCURSOR_SHARING）对所发出语句的文本串进行对所发出语句的文本串进行hashedhashed 将所发出语句的文本串进行比较将所发出语句的文本串进行比较 将将SQLSQL中涉及的对象进行比较中涉及的对象进行比较基本概念24ROWIDROWID 伪列，唯一。定位数据的最快方法伪列，唯一。定位数据的最快方法 索引创建时会记录索引创建时会记录ROWIDROWID值值Driving TableDriving Table（驱动表）（驱动表）:外层表，用于嵌套以及外层表，用于嵌套以及hashhash连接中连接中可选择性可选择性:“唯一键的数量唯一键的数量/表中的行数表中的行数”的比值的比值Oracle SQL 执行计划25SQLSQL语句语句: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 emp.sal BETWEEN losal AND hisal);BETWEEN losal AND hisal);Oracle SQL 执行计划图26Oracle SQL 执行计划图27访问路径28全表扫描（全表扫描（FTSFTS）通过通过rowidrowid的表存取（的表存取（Table Access by Table Access by rowidrowid）索引扫描（索引扫描（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:将两个表结合在一起，一次只能连接将两个表结合在一起，一次只能连接2 2个表，个表，表连接也可以被称为表关联表连接也可以被称为表关联 排序排序合并连接（合并连接（Sort Merge JoinSort Merge Join）嵌套循环（嵌套循环（Nested LoopsNested Loops）哈希连接（哈希连接（Hash JoinHash Join）排序-合并连接图30嵌套循环连接图31连接适用情况32排序排序合并连接合并连接 非等值连接、关联列都有索引非等值连接、关联列都有索引嵌套连接嵌套连接 驱动表较小驱动表较小哈希连接哈希连接 等值连接等值连接生成执行计划33sql set autotrace onsql set autotrace onsql 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 INDEX /*+INDEX(table index)*/*+INDEX(table index)*/使用该表上指定的索引对表进行索引扫描使用该表上指定的索引对表进行索引扫描 INDEX_FFS /*+INDEX_FFS(table index)*/INDEX_FFS /*+INDEX_FFS(table index)*/使用快速全索引扫描使用快速全索引扫描 NO_INDEX /*+NO_INDEX(table index)*/NO_INDEX /*+NO_INDEX(table index)*/不使用该表上指定的索引进行存取，仍然可以使用不使用该表上指定的索引进行存取，仍然可以使用 其它的索引进行索引扫描其它的索引进行索引扫描干预执行计划_表连接35 USE_NL /*+USE_NL(tab,tab,.)*/USE_NL /*+USE_NL(tab,tab,.)*/使用嵌套连接使用嵌套连接 USE_MERGE/*+USE_MERGE(tab,tab,.)*/USE_MERGE/*+USE_MERGE(tab,tab,.)*/使用排序使用排序-合并连接合并连接 USE_HASH/*+USE_HASH(tab,tab,.)*/USE_HASH/*+USE_HASH(tab,tab,.)*/使用使用HASHHASH连接连接 二、二、Oracle 体系体系结构构 五、五、Oracle SQL 编写写规范范 四、典型案例分析四、典型案例分析暨优化要点化要点 三、三、Oracle SQL 优化基化基础 一、培一、培训目的目的培训总目录培训总目录典型案例_137系统名称：综合集中维护支撑系统系统名称：综合集中维护支撑系统故障现象：压力测试系统故障现象：压力测试系统CPUCPU资源资源100%100%故障分析：故障分析：数据库实际的数据量仅为数据库实际的数据量仅为6GB6GB左右。但是每左右。但是每天却产生大概天却产生大概150GB150GB左右的左右的REDO LOGREDO LOG 使用使用LOGMNRLOGMNR分析分析ORACLEORACLE日志，发现基本上日志，发现基本上都为都为JMSSTOREJMSSTORE表的增、删操作。该表为表的增、删操作。该表为WEBLOGICWEBLOGIC的消息表的消息表（CLOBCLOB字段）字段）。典型案例_138系统名称：综合集中维护支撑系统系统名称：综合集中维护支撑系统故障处理：故障处理：运行相关查询业务，在数据库中查询运行相关查询业务，在数据库中查询v$sqlareav$sqlarea，JMSSTOREJMSSTORE表相关操作的频率表相关操作的频率 对应用进行跟踪调试，查找每笔查询业务对应用进行跟踪调试，查找每笔查询业务所运行的所运行的SQLSQL 最终定位问题产生原因：应用程序在每笔最终定位问题产生原因：应用程序在每笔查询后面都附带了消息传输（查询后面都附带了消息传输（1010次）次）将该将该SQLSQL屏蔽，问题得到解决屏蔽，问题得到解决典型案例_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 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 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_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 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 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_enddate desc;典型案例_240系统名称：系统名称：OAOA系统系统故障处理：故障处理：查看该查看该sqlsql的执行计划的执行计划 tb_engine_filecommontb_engine_filecommon表为表为FTSFTS（full full table scantable scan），而且），而且costcost大大典型案例_241系统名称：系统名称：OAOA系统系统故障处理：故障处理：查询该表查询该表fd_c_enddatefd_c_enddate的可选择性的可选择性，发现发现很高，因此对该字段建索引，很高，因此对该字段建索引，执行计划变为执行计划变为如下，执行时间缩短为如下，执行时间缩短为0.3S0.3S左右左右典型案例_342系统名称：商务领航系统系统名称：商务领航系统故障现象：出账速度慢故障现象：出账速度慢故障处理：故障处理：查找查找V$SESSIONV$SESSION中正在长时间运行的中正在长时间运行的SQLSQLupdate 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_temp_0401biz_cusid_temp_0401为为FTSFTS，但，但cusidcusid字字段建有索引段建有索引典型案例_343系统名称：商务领航系统系统名称：商务领航系统故障处理：故障处理：改写上述改写上述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(select 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秒秒典型案例_444系统名称：系统名称：CRMCRM系统系统故障现象：过多的故障现象：过多的latch freelatch 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语句语句典型案例_445系统名称：系统名称：CRMCRM系统系统故障处理：故障处理：定位定位SQLSQL有相关子查询调用了该函数有相关子查询调用了该函数 对该对该SQLSQL进行进行tracetrace跟踪，发现该跟踪，发现该SQLSQL每执行每执行一次，将会调用该函数一次，将会调用该函数3835538355次，也就是说次，也就是说latch freelatch free相应的相应的SQLSQL将被执行将被执行3835538355次次 修改相关应用（修改相关应用（cachecache相关刷新机制）相关刷新机制）Oracle SQL优化要点46使用执行计划来分析使用执行计划来分析SQLSQL性能进而进行优化性能进而进行优化使用使用HINTSHINTS来干预执行计划来干预执行计划最大限度地避免全表扫描（最大限度地避免全表扫描（FTSFTS）减少子查询的使用减少子查询的使用规范规范SQLSQL语句的编写，使得语句的编写，使得SQLSQL语句能够被共享语句能够被共享减少减少SQLSQL解析的次数：使用绑定变量解析的次数：使用绑定变量Oracle SQL优化要点47用索引提高效率用索引提高效率 避免在索引列上使用计算避免在索引列上使用计算 避免在索引列上使用函数转换避免在索引列上使用函数转换 避免改变索引列的类型避免改变索引列的类型 避免在索引列上使用避免在索引列上使用IS NULLIS NULL和和IS NOT IS NOT NULLNULL 避免在索引列上使用避免在索引列上使用likelike%AAA%AAAOracle SQL优化要点48尽量避免尽量避免SQLSQL语句不必要的排序操作语句不必要的排序操作 UNIONUNION操作要用操作要用UNION ALLUNION ALL来代替来代替 ORDER BY ORDER BY 中的字段最好建立索引中的字段最好建立索引 尽量避免使用尽量避免使用DISTINCTDISTINCT语句语句尽量避免使用尽量避免使用(NOT)IN(NOT)IN，而使用（，而使用（NOTNOT）EXISTSEXISTS来代替来代替对表超过对表超过10%10%以上的以上的deletedelete、updateupdate操作要重操作要重建索引。建索引。五、五、Oracle SQL 编写写规范范 二、二、Oracle 体系体系结构构 四、典型案例分析四、典型案例分析暨优化要点化要点 三、三、Oracle SQL 优化基化基础 一、培一、培训目的目的培训总目录培训总目录SQL编写规范_书写格式50缩进缩进 对于存储过程文件，缩进为对于存储过程文件，缩进为8 8个空格个空格 对于对于Java SourceJava Source里的里的SQLSQL字符串，不可有字符串，不可有缩进，即每一行字符串不以空格开头缩进，即每一行字符串不以空格开头 空格空格 SQL SQL内算数运算符、逻辑运算符连接的两个内算数运算符、逻辑运算符连接的两个元素之间必须用空格分隔元素之间必须用空格分隔 逗号之后必须接一个空格逗号之后必须接一个空格 关键字、保留字和左括号间必有一个空格关键字、保留字和左括号间必有一个空格SQL编写规范_书写格式51换行换行_1_1 Select/From/Where/Order by/Group by Select/From/Where/Order by/Group by等等子句必须另起一行写子句必须另起一行写 Select Select子句内容如果只有一项，与子句内容如果只有一项，与SelectSelect同行写同行写 Select Select子句内容如果多于一项，每一项单子句内容如果多于一项，每一项单独占一行，在对应独占一行，在对应SelectSelect的基础上向右缩进的基础上向右缩进8 8个空格（个空格（Java sourceJava source无缩进）无缩进）SQL编写规范_书写格式52换行换行_2_2 From From子句内容如果只有一项，与子句内容如果只有一项，与FromFrom同行同行 From From子句内容如果多于一项，每一项单独子句内容如果多于一项，每一项单独占一行，在对应占一行，在对应FromFrom的基础上向右缩进的基础上向右缩进8 8个个空格（空格（Java sourceJava source无缩进）无缩进）Where Where子句的条件如果有多项，每一个条件子句的条件如果有多项，每一个条件占一行，以占一行，以ANDAND开头，且无缩进开头，且无缩进SQL编写规范_书写格式53换行换行_3_3 Insert Insert子句内容每个表字段单独占一行，子句内容每个表字段单独占一行，无缩进；无缩进；valuesvalues每一项单独占一行，无缩进每一项单独占一行，无缩进(Update)Set(Update)Set子句内容每一项单独占一行，子句内容每一项单独占一行，无缩进无缩进 SQL SQL文中间不允许出现空行文中间不允许出现空行 Java source Java source里单引号必须跟所属的里单引号必须跟所属的SQLSQL子子句处在同一行，连接符（句处在同一行，连接符（+）必须在行首）必须在行首SQL编写规范54不等于统一使用不等于统一使用“”数据库查询时使用表的别名数据库查询时使用表的别名SQLSQL文本对表字段扩展的兼容性文本对表字段扩展的兼容性在在Java sourceJava source里使用里使用Select*Select*时，严禁通时，严禁通过过getString(1)getString(1)的形式得到查询结果，必须的形式得到查询结果，必须使用使用getString(getString(字段名字段名)的形式的形式使用使用InsertInsert时，必须指定插入的字段名，严时，必须指定插入的字段名，严禁不指定字段名直接插入禁不指定字段名直接插入valuesvaluesThanks