Oracle培训课件PPT学习课件

二、Oracle体系结构,五、OracleSQL编写规范,四、典型案例分析暨优化要点,三、OracleSQL优化基础,一、培训目的,培训总目录,1,培训目的,培训目标,培训内容,课堂交互,本次课程大约需要3个小时，课后，我们会留半个小时与大家进行探讨，希望在座各位在培训过程中记录下自己的问题；,Oracle体系结构；,OracleSQL优化及编写规范；,了解Oracle体系架构；掌握OracleSQL优化基本方法；,OracleSQL优化典型案例分析；,2,二、Oracle体系结构,五、OracleSQL编写规范,四、典型案例分析暨优化要点,三、OracleSQL优化基础,一、培训目的,培训总目录,3,Oracle服务器架构,4,Oracle内存体系架构,5,Oracle进程体系架构,用户进程:在数据库用户或批处理进程连接到OracleDB时启动服务器进程:连接到Oracle实例，在用户建立会话时启动后台进程:在Oracle实例启动时启动,6,Oracle存储体系架构,7,Oracle高可用性_双机热备,优点管理简单相对较为稳定缺点切换间存在停顿备机空闲无扩展性,8,Oracle高可用性_双机热备切换,9,Oracle高可用性_RAC,优点良好的可伸缩性、可扩展性高可用性新进的CacheFusion技术负载均衡缺点管理复杂对网络要求高,10,Oracle高可用性_RAC,将应用和用户自动而透明地重新连接到另一个系统登录的上下文可被保持应用查询不会中断,11,OracleRAC负载均衡,数据库启动时，向监听进程注册节点向监听进程报告CPU的使用情况当建立连接时，监听进程选择最空闲的节点处理请求,12,二、Oracle体系结构,五、OracleSQL编写规范,四、典型案例分析暨优化要点,三、OracleSQL优化基础,一、培训目的,培训总目录,13,性能优化概述,14,谁来调整系统应用设计人员、应用开发人员数据库管理人员什么时候调整系统设计、系统开发过程系统运行过程,应用系统类型,15,联机事务处理（OLTP）高吞吐量增、删、改多决策支持（DSS）数据量大主要用于查询,SQL语句处理过程,16,查询语句处理DML语句处理（insert、delete、update）DDL语句处理（create、drop、alter）,SQL语句处理各个阶段,17,SQL语句处理必经阶段,18,第1步:CreateaCursor创建游标第2步:ParsetheStatement分析语句第5步:BindAnyVariables绑定变量第7步:RuntheStatement运行语句第9步:ClosetheCursor关闭游标,SQL语句处理可选阶段,19,并行功能第6步：ParallelizetheStatement并行执行语句查询语句第3步：DescribeResultsofaQuery描述查询的结果集第4步：DefineOutputofaQuery定义查询的输出数据第8步：FetchRowsofaQuery取查询出来的行,DDL语句处理过程,20,不同于DML语句和查询语句的执行DDL成功后需要对数据字典表进行修改分析阶段还包括分析、查找数据字典信息和执行,Oracle优化器,21,优化:选择最有效的执行计划来执行SQL语句的过程优化器:选择执行计划的数据库引擎基于规则（RBO）基于代价（CBO）,Oracle统计信息,22,影响CBO执行计划最关键的因素分析的方法analyze命令dbms_stat包分析的频率Oracle10g自动分析,共享SQL语句,23,储存于共享池（shared_pool）判断SQL语句是否与共享池中某一SQL相同的步骤（CURSOR_SHARING）对所发出语句的文本串进行hashed将所发出语句的文本串进行比较将SQL中涉及的对象进行比较,基本概念,24,ROWID伪列，唯一。定位数据的最快方法索引创建时会记录ROWID值DrivingTable（驱动表）:外层表，用于嵌套以及hash连接中可选择性:“唯一键的数量/表中的行数”的比值,OracleSQL执行计划,25,SQL语句:SELECTename,job,sal,dnameFROMemp,deptWHEREemp.deptno=dept.deptnoANDNOTEXISTS(SELECT*FROMsalgradeWHEREemp.salBETWEENlosalANDhisal);,OracleSQL执行计划图,26,OracleSQL执行计划图,27,访问路径,28,全表扫描（FTS）通过rowid的表存取（TableAccessbyrowid）索引扫描（IndexScan）索引唯一扫描(indexuniquescan)索引范围扫描(indexrangescan)索引全扫描(indexfullscan)索引快速扫描(indexfastfullscan),表连接,29,表连接JOIN:将两个表结合在一起，一次只能连接2个表，表连接也可以被称为表关联排序合并连接（SortMergeJoin）嵌套循环（NestedLoops）哈希连接（HashJoin）,排序-合并连接图,30,嵌套循环连接图,31,连接适用情况,32,排序合并连接非等值连接、关联列都有索引嵌套连接驱动表较小哈希连接等值连接,生成执行计划,33,sqlsetautotraceonsqlexplainplanforselect用dbms_system存储过程生成执行计划用PL/SQLDEVELOPER（F5）,干预执行计划_访问路径,34,FULL/*+FULL(table)*/指定该表使用FTSINDEX/*+INDEX(tableindex)*/使用该表上指定的索引对表进行索引扫描INDEX_FFS/*+INDEX_FFS(tableindex)*/使用快速全索引扫描NO_INDEX/*+NO_INDEX(tableindex)*/不使用该表上指定的索引进行存取，仍然可以使用其它的索引进行索引扫描,干预执行计划_表连接,35,USE_NL/*+USE_NL(tab,tab,.)*/使用嵌套连接USE_MERGE/*+USE_MERGE(tab,tab,.)*/使用排序-合并连接USE_HASH/*+USE_HASH(tab,tab,.)*/使用HASH连接,二、Oracle体系结构,五、OracleSQL编写规范,四、典型案例分析暨优化要点,三、OracleSQL优化基础,一、培训目的,培训总目录,36,典型案例_1,37,系统名称：综合集中维护支撑系统故障现象：压力测试系统CPU资源100%故障分析：数据库实际的数据量仅为6GB左右。但是每天却产生大概150GB左右的REDOLOG使用LOGMNR分析ORACLE日志，发现基本上都为JMSSTORE表的增、删操作。该表为WEBLOGIC的消息表（CLOB字段）。,典型案例_1,38,系统名称：综合集中维护支撑系统故障处理：运行相关查询业务，在数据库中查询v$sqlarea，JMSSTORE表相关操作的频率对应用进行跟踪调试，查找每笔查询业务所运行的SQL最终定位问题产生原因：应用程序在每笔查询后面都附带了消息传输（10次）将该SQL屏蔽，问题得到解决,典型案例_2,39,系统名称：OA系统故障现象：SQL语句执行速度慢（5S）selectdistinctb.fd_workflowname,a.fd_fileid,a.fd_c_filetitle,a.fd_c_enddatefromtb_model_workflowb,tb_documentc,tb_engine_filecommona,tb_engine_filetachegwherea.fd_c_workflowid=b.fd_workflowidandg.fd_fileid=a.fd_fileidanda.fd_c_fileno=c.idanda.fd_c_isend=1andb.fd_workflowtype=1and(c.security_level_code=1or(c.security_level_code=2andg.fd_ft_staffid=2)or(c.security_level_code=3andg.fd_ft_staffid=2)anda.fd_c_enddate=(sysdate-30)anda.fd_c_enddate=(sysdate+1)and(pany_id=1orc.send_company=1)orderbya.fd_c_enddatedesc;,典型案例_2,40,系统名称：OA系统故障处理：查看该sql的执行计划tb_engine_filecommon表为FTS（fulltablescan），而且cost大,典型案例_2,41,系统名称：OA系统故障处理：查询该表fd_c_enddate的可选择性，发现很高，因此对该字段建索引，执行计划变为如下，执行时间缩短为0.3S左右,典型案例_3,42,系统名称：商务领航系统故障现象：出账速度慢故障处理：查找V$SESSION中正在长时间运行的SQLupdatezjxc.BIZ_CUST_BALANCEtsett.warefee=0wheret.cusidin(_cusid_temp_0401b)andt.year_month=201003;表biz_cusid_temp_0401为FTS，但cusid字段建有索引,典型案例_3,43,系统名称：商务领航系统故障处理：改写上述SQL语句_cust_balancetsett.warefee=0whereexists(_cusid_temp_0401bwheret.cusid=b.cusidandt.year_month=201003);表biz_cusid_temp_0401的访问从FTS改变为索引唯一扫描SQL执行时间缩短为12秒,典型案例_4,44,系统名称：CRM系统故障现象：过多的latchfree锁影响性能故障处理：查找latchfree对应的相关语句，都为SELECTCOL_VALUEFROMPUB_COLUMN_REFERENCEWHEREREFER_ID=:B1而该SQL语句都为函数GET_COLUMN_VALUE调用所产生查询调用该函数的相关可疑SQL语句,典型案例_4,45,系统名称：CRM系统故障处理：定位SQL有相关子查询调用了该函数对该SQL进行trace跟踪，发现该SQL每执行一次，将会调用该函数38355次，也就是说latchfree相应的SQL将被执行38355次修改相关应用（cache相关刷新机制）,OracleSQL优化要点,46,使用执行计划来分析SQL性能进而进行优化使用HINTS来干预执行计划最大限度地避免全表扫描（FTS）减少子查询的使用规范SQL语句的编写，使得SQL语句能够被共享减少SQL解析的次数：使用绑定变量,OracleSQL优化要点,47,用索引提高效率避免在索引列上使用计算避免在索引列上使用函数转换避免改变索引列的类型避免在索引列上使用ISNULL和ISNOTNULL避免在索引列上使用like%AAA,OracleSQL优化要点,48,尽量避免SQL语句不必要的排序操作UNION操作要用UNIONALL来代替ORDERBY中的字段最好建立索引尽量避免使用DISTINCT语句尽量避免使用(NOT)IN，而使用（NOT）EXISTS来代替对表超过10%以上的delete、update操作要重建索引。,五、OracleSQL编写规范,二、Oracle体系结构,四、典型案例分析暨优化要点,三、OracleSQL优化基础,一、培训目的,培训总目录,49,SQL编写规范_书写格式,50,缩进对于存储过程文件，缩进为8个空格对于JavaSource里的SQL字符串，不可有缩进，即每一行字符串不以空格开头空格SQL内算数运算符、逻辑运算符连接的两个元素之间必须用空格分隔逗号之后必须接一个空格关键字、保留字和左括号间必有一个空格,SQL编写规范_书写格式,51,换行_1Select/From/Where/Orderby/Groupby等子句必须另起一行写Select子句内容如果只有一项，与Select同行写Select子句内容如果多于一项，每一项单独占一行，在对应Select的基础上向右缩进8个空格（Javasource无缩进）,SQL编写规范_书写格式,52,换行_2From子句内容如果只有一项，与From同行From子句内容如果多于一项，每一项单独占一行，在对应From的基础上向右缩进8个空格（Javasource无缩进）Where子句的条件如果有多项，每一个条件占一行，以AND开头，且无缩进,SQL编写规范_书写格式,53,换行_3Insert子句内容