Oracle培训课件.ppt

二 Oracle体系结构 五 OracleSQL编写规范 四 典型案例分析暨优化要点 三 OracleSQL优化基础 一 培训目的 培训总目录 培训目的 培训目标 培训内容 课堂交互 本次课程大约需要3个小时 课后 我们会留半个小时与大家进行探讨 希望在座各位在培训过程中记录下自己的问题 Oracle体系结构 OracleSQL优化及编写规范 了解Oracle体系架构 掌握OracleSQL优化基本方法 OracleSQL优化典型案例分析 二 Oracle体系结构 五 OracleSQL编写规范 四 典型案例分析暨优化要点 三 OracleSQL优化基础 一 培训目的 培训总目录 Oracle服务器架构 Oracle内存体系架构 Oracle进程体系架构 用户进程 在数据库用户或批处理进程连接到OracleDB时启动服务器进程 连接到Oracle实例 在用户建立会话时启动后台进程 在Oracle实例启动时启动 Oracle存储体系架构 Oracle高可用性 双机热备 优点管理简单相对较为稳定缺点切换间存在停顿备机空闲无扩展性 Oracle高可用性 双机热备切换 Oracle高可用性 RAC 优点良好的可伸缩性 可扩展性高可用性新进的CacheFusion技术负载均衡缺点管理复杂对网络要求高 Oracle高可用性 RAC 将应用和用户自动而透明地重新连接到另一个系统登录的上下文可被保持应用查询不会中断 OracleRAC负载均衡 数据库启动时 向监听进程注册节点向监听进程报告CPU的使用情况当建立连接时 监听进程选择最空闲的节点处理请求 二 Oracle体系结构 五 OracleSQL编写规范 四 典型案例分析暨优化要点 三 OracleSQL优化基础 一 培训目的 培训总目录 性能优化概述 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 sql setautotraceonsql explainplanforselect 用dbms system存储过程生成执行计划用PL SQLDEVELOPER F5 干预执行计划 访问路径 34 FULL FULL table 指定该表使用FTSINDEX INDEX table index 使用该表上指定的索引对表进行索引扫描INDEX FFS INDEX FFS table index 使用快速全索引扫描NO INDEX NO INDEX table index 不使用该表上指定的索引进行存取 仍然可以使用其它的索引进行索引扫描 干预执行计划 表连接 35 USE NL USE NL tab tab 使用嵌套连接USE MERGE USE MERGE tab tab 使用排序 合并连接USE HASH USE HASH tab tab 使用HASH连接 二 Oracle体系结构 五 OracleSQL编写规范 四 典型案例分析暨优化要点 三 OracleSQL优化基础 一 培训目的 培训总目录 典型案例 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 1 andb fd workflowtype 1 and 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 selectb cusidfromzjxc biz cusid temp 0401b andt year month 201003 表biz cusid temp 0401为FTS 但cusid字段建有索引 典型案例 3 43 系统名称 商务领航系统故障处理 改写上述SQL语句updatezjxc biz cust balancetsett warefee 0whereexists select1fromzjxc biz 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优化基础 一 培训目的 培训总目录 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子句