Oracle 体系结构以及编写规范

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

2、acle SQL 优化基础 一、培训目的培训总目录Oracle服务器架构Oracle内存体系架构Oracle进程体系架构 用户进程:在数据库用户或批处理进程连接到 Oracle DB 时启动 服务器进程:连接到Oracle 实例，在用户建立 会话时启动 后台进程:在Oracle 实例启动时启动Oracle存储体系架构Oracle高可用性_双机热备优点 管理简单 相对较为稳定缺点 切换间存在停顿 备机空闲 无扩展性Computer BComputer AOracle高可用性_双机热备切换Oracle高可用性_RAC优点 良好的可伸缩性、可扩展性 高可用性 新进的Cache Fusion技术 负载

3、均衡缺点 管理复杂 对网络要求高Oracle高可用性_RAC将应用和用户自动而透明地重新连接到另一个系统登录的上下文可被保持应用查询不会中断节点A节点B节点A节点B当集群内节点 A 失败, 用户被转移Oracle RAC负载均衡 数据库启动时，向监听进程注册 节点向监听进程报告CPU 的使用情况 当建立连接时，监听进程选择最空闲的节点 处理请求 二、Oracle 体系结构 五、Oracle SQL 编写规范 四、典型案例分析暨优化要点 三、Oracle SQL 优化基础 一、培训目的培训总目录性能优化概述15谁来调整系统 应用设计人员、应用开发人员 数据库管理人员 什么时候调整 系统设计、系统

4、开发过程 系统运行过程 应用系统类型16联机事务处理（OLTP） 高吞吐量 增、删、改多 决策支持（DSS） 数据量大 主要用于查询 SQL语句处理过程17 查询语句处理 DML语句处理 （insert、delete、update） DDL语句处理 （create、drop、alter）SQL语句处理各个阶段18SQL语句处理必经阶段19 第1步: Create a Cursor 创建游标 第2步: Parse the Statement 分析语句 第5步: Bind Any Variables 绑定变量 第7步: Run the Statement 运行语句 第9步: Close the C

5、ursor 关闭游标SQL语句处理可选阶段20并行功能 第6步：Parallelize the Statement 并行执行语句查询语句 第3步：Describe Results of a Query 描述查询的结果集 第4步：Define Output of a Query 定义查询的输出数据 第8步：Fetch Rows of a Query 取查询出来的行DDL语句处理过程21 不同于DML语句和查询语句的执行 DDL成功后需要对数据字典表进行修改 分析阶段还包括分析、查找数据字典 信息和执行Oracle优化器22优化: 选择最有效的执行计划来执行 SQL语句的过程优化器: 选择执行计划

6、的数据库引擎 基于规则（RBO） 基于代价（CBO） Oracle统计信息23影响CBO执行计划最关键的因素分析的方法 analyze命令 dbms_stat包 分析的频率Oracle 10g自动分析共享SQL语句24储存于共享池（shared_pool）判断SQL语句是否与共享池中某一SQL相同的步骤（CURSOR_SHARING） 对所发出语句的文本串进行hashed 将所发出语句的文本串进行比较 将SQL中涉及的对象进行比较基本概念25ROWID 伪列，唯一。定位数据的最快方法 索引创建时会记录ROWID值Driving Table（驱动表）: 外层表，用于嵌套以及hash连接中可选择性

7、:“唯一键的数量/表中的行数”的比值Oracle SQL 执行计划26SQL语句:SELECT ename, job, sal, dname FROM emp, dept AND NOT EXISTS ( SELECT * FROM salgrade WHERE emp.sal BETWEEN losal AND hisal );Oracle SQL 执行计划图27Oracle SQL 执行计划图28访问路径29全表扫描（FTS）通过rowid的表存取（Table Access by rowid）索引扫描（Index Scan） 索引唯一扫描(index unique scan) 索引范围扫描

8、(index range scan) 索引全扫描(index full scan) 索引快速扫描(index fast full scan)表连接30表连接JOIN: 将两个表结合在一起，一次只能连接2个表， 表连接也可以被称为表关联 排序合并连接（Sort Merge Join） 嵌套循环（Nested Loops） 哈希连接（Hash Join）排序-合并连接图31嵌套循环连接图32连接适用情况33排序合并连接 非等值连接、关联列都有索引嵌套连接 驱动表较小哈希连接 等值连接生成执行计划34sql set autotrace onsql explain plan for select 用d

9、bms_system存储过程生成执行计划用PL/SQL DEVELOPER（F5）干预执行计划_访问路径35 FULL /*+ FULL ( table ) */ 指定该表使用FTS INDEX /*+ INDEX ( table index) */ 使用该表上指定的索引对表进行索引扫描 INDEX_FFS /*+ INDEX_FFS ( table index) */ 使用快速全索引扫描 NO_INDEX /*+ NO_INDEX ( table index) */ 不使用该表上指定的索引进行存取，仍然可以使用 其它的索引进行索引扫描干预执行计划_表连接36 USE_NL /*+ USE_N

10、L ( tab ,tab, . ) */ 使用嵌套连接 USE_MERGE /*+ USE_MERGE ( tab ,tab,.) */ 使用排序- -合并连接 USE_HASH /*+ USE_HASH ( tab ,tab, .) */ 使用HASH连接 二、Oracle 体系结构 五、Oracle SQL 编写规范 四、典型案例分析暨优化要点 三、Oracle SQL 优化基础 一、培训目的培训总目录典型案例_138系统名称：综合集中维护支撑系统故障现象：压力测试系统CPU资源100%故障分析： 数据库实际的数据量仅为6GB左右。但是每天却产生大概150GB左右的REDO LOG 使用L

11、OGMNR分析ORACLE日志，发现基本上都为JMSSTORE表的增、删操作。该表为WEBLOGIC的消息表（CLOB字段）。典型案例_139系统名称：综合集中维护支撑系统故障处理： 运行相关查询业务，在数据库中查询v$sqlarea，JMSSTORE表相关操作的频率 对应用进行跟踪调试，查找每笔查询业务所运行的SQL 最终定位问题产生原因：应用程序在每笔查询后面都附带了消息传输（10次） 将该SQL屏蔽，问题得到解决典型案例_240系统名称：OA系统故障现象：SQL语句执行速度慢（5S）from tb_model_workflow b,tb_document c,tb_engine_file

12、common a,tb_engine_filetache gand 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 and g.fd_ft_staffid=2)and a.fd_c_enddate=(sysdate-30)and a.fd_c_enddate=(sysdate+1)and (c pany_id=1 or c.send_company=1) o

13、rder by a.fd_c_enddate desc;典型案例_241系统名称：OA系统故障处理： 查看该sql的执行计划 tb_engine_filecommon表为FTS（full table scan），而且cost大典型案例_242系统名称：OA系统故障处理： 查询该表fd_c_enddate的可选择性，发现很高，因此对该字段建索引，执行计划变为如下，执行时间缩短为左右典型案例_343系统名称：商务领航系统故障现象：出账速度慢故障处理： 查找V$SESSION中正在长时间运行的SQLupdate zjxc.BIZ_CUST_BALANCE t set t.warefee = 0 wh

14、ere t.cusid in (select b.cusid from _cusid_temp_0401 b) and t.year_month = 201003; 表biz_cusid_temp_0401为FTS，但cusid字段建有索引典型案例_344系统名称：商务领航系统故障处理： 改写上述SQL语句update _cust_balance t set t.warefee = 0 where exists (select 1 from _cusid_temp_0401 b where t.cusid=b.cusid and t.year_m

15、onth = 201003) ; 表biz_cusid_temp_0401的访问从FTS改变为索引唯一扫描 SQL执行时间缩短为12秒典型案例_445系统名称：CRM系统故障现象：过多的latch free锁影响性能故障处理： 查找latch free对应的相关语句，都为SELECT COL_VALUE FROM PUB_COLUMN_REFERENCE WHERE REFER_ID = :B1 而该SQL语句都为函数GET_COLUMN_VALUE调用所产生 查询调用该函数的相关可疑SQL语句典型案例_446系统名称：CRM系统故障处理： 定位SQL有相关子查询调用了该函数 对该SQL进行t

16、race跟踪，发现该SQL每执行一次，将会调用该函数38355次，也就是说latch free相应的SQL将被执行38355次 修改相关应用（cache相关刷新机制）Oracle SQL优化要点47使用执行计划来分析SQL性能进而进行优化使用HINTS来干预执行计划最大限度地避免全表扫描（FTS）减少子查询的使用规范SQL语句的编写，使得SQL语句能够被共享减少SQL解析的次数：使用绑定变量Oracle SQL优化要点48用索引提高效率 避免在索引列上使用计算 避免在索引列上使用函数转换 避免改变索引列的类型 避免在索引列上使用IS NULL和IS NOT NULL 避免在索引列上使用like

17、%AAAOracle SQL优化要点49尽量避免SQL语句不必要的排序操作 UNION操作要用UNION ALL来代替 ORDER BY 中的字段最好建立索引 尽量避免使用DISTINCT语句尽量避免使用(NOT) IN，而使用（NOT） EXISTS来代替对表超过10%以上的delete、update操作要重建索引。 五、Oracle SQL 编写规范 二、Oracle 体系结构 四、典型案例分析暨优化要点 三、Oracle SQL 优化基础 一、培训目的培训总目录SQL编写规范_书写格式51缩进 对于存储过程文件，缩进为8个空格 对于Java Source里的SQL字符串，不可有缩进，即每

18、一行字符串不以空格开头 空格 SQL内算数运算符、逻辑运算符连接的两个元素之间必须用空格分隔 逗号之后必须接一个空格 关键字、保留字和左括号间必有一个空格SQL编写规范_书写格式52换行_1 Select/From/Where/Order by/Group by等子句必须另起一行写 Select子句内容如果只有一项，与Select同行写 Select子句内容如果多于一项，每一项单独占一行，在对应Select的基础上向右缩进8个空格（Java source无缩进） SQL编写规范_书写格式53换行_2 From子句内容如果只有一项，与From同行 From子句内容如果多于一项，每一项单独占一行，在对应From的基础上向右缩进8个空格（Java source无缩进） Where子句的条件如果有多项，每一个条件占一行，以AND开头，且无缩进SQL编写规范_书写格式54换行