第二部分 ORACLE 高效SQL分析1(方法)

oracle教材

第二部分 ORACLE高效SQL分析亿阳信通 无线与交换MSN: tsingsong@http://www.77cn.com.cn Email:zhangjiahong@http://www.77cn.com.cn 张家宏 2009-12-02

oracle教材

培训大纲 SQL优化基础 – Oracle内存空间

– SQL的执行过程– 使SQL语句共享 – Oracle优化器 数据读取方式(全表，ROWID,索引) 表连接实现方式(SMJ,NL,HJ) 用提示调整执行计划 用OutLine固定执行计划2

oracle教材

Oracle 内存结构SGA+PGASGA(System Global Area )是Oracle各个进程共用的一块巨大的共享内存区域。

Shared pool 共 享 池 Data Buffer Cache: 缓存从磁盘读取的数 据，Oracle所有的数 据处理都在该内存区 完成之后再写入磁盘 Redo log buffer Java Pool

SQL Library Cache:缓存解析后的SQL 信息，供下次直接使用，避免再次解析 Data Dictionary Cache:缓冲解析SQL或 其他Oracle运作需要的数据字典

Large Pool3

oracle教材

SGA和高效SQL – SGA是Oracle 运作的核心区域，分析SGA的作用，不难得出以下

结论：当该次访问的SQL语句已经缓存在SGA中，那么该SQL就很可能不 用再解析，缩短了SQL的执行时间；

––

当该次访问的数据已经缓存在SGA中，那么很可能就不会再从磁盘读取了，减少了磁盘的I/O; 在SGA中的数据和SQL越多，I/O开销就越少，性能就越好；

oracle教材

SQL执行过程概述 方式一： – 硬解析：SQL初次执行 (1)语法检测(selec<>select) (2)语义检查(对象，权限？) – 优化器(Optimizer) (1)WHERE子句信息 (2)优化模式(CBO,RBO) (3)生成执行计划Plan – 执行Plan取得所需数据 方式二 – 软解析：SQL及Plan已在SGA中 – 直接执行Plan取得所需数据 SQL共享可以实现软解析！5

oracle教材

SQL共享实现高速软解析 SQL共享是实现软解析的方法，基本条件：– SQL语句文本的ASCII码值相同；比如：大小写，空格，换行等等； 例如: SELECT * FROM TB_EMP; 和下列每一个都不同 SELECT * from TB_EMP; (大小写) SELECT * FROM TB_EMP;(空格不对) SELECT * FROM TB_EMP; (换行)

–

SQL语句所指的对象必须完全相同；表TB_ABC:select count(1) from tb_abc 和同义词TB_ABC: select count(1) from tb_abc 是不相同的；6

oracle教材

SQL变量绑定实现高速软解析 绑定变量并且同名如有以下100条语句：

select name from tb_a where pin = 1; select name from tb_a where pin = 2; …… select name from tb_a where pin = 100;上述100语句仅仅只是WHER pin= 值不同，都需要硬解析，如果将pin 的值 1…100 先分别赋给变量v_pin,再用select name from tb_a where pin = :v_pin 就 可以使上述100条SQL语句共享的目的! 注意，变量需同名 如下变量绑定的sql之间不会共享：

select name from tb_a where pin = :v_pin select name from tb_a where pin = :t_pin7

oracle教材

JAVA中的变量绑定 oracle绑定变量的java 代码如下：

String v_id = 'xxxxx'; //嵌入绑定变量 Stri

ng v_sql = 'select name from table_a where id = ? '; stmt = con.prepareStatement( v_sql ); stmt.setString(1, v_id ); //为绑定变量赋值 stmt.executeQuery();

oracle教材

优化器及模式

优化器(Optimizer)的操作步骤：计算表达式和条件 语句转换：eg对View的访问转换为对基表的访问 选择优化模式：CBO还是RBO 选择访问路径：where子句，确定可用访问路径，索引扫描...... 选择join顺序：from,统计，驱动表，第1步，第2步...... 选择join方法，Hash Join/Nest Loop/Sort-Merge 生成执行计划(RBO),或者生成各执行计划并选成本最低的计划(CBO)

CBO(Cost-Based Optimizer)(where /from) Statistics 统计信息

RBO(Rule-Based Optimizer) (where /from) Oracl e 规则

执 行 计 划

执行 计划

oracle教材

优化模式CBO CBO(Cost Based Optimizer) ——依据统计分析估算成本并选择最低成本的执行计划 Oracle默认模式是CHOOSE ——CBO可用则用不然用RBO; 使用CBO必须有统计信息： analyze table abc compute statistics; analyze table abc estimate statistics sample 20 percent; 提示与CBO: (1)除了rule之外的提示都会会激发 CBO 优化器; (2)如果有提示(hints)存在，那么会依提示确定获取路径; (3)对有提示的语句中引用的表和索引进行统计; FIRST ROWS:CHOOSE+优先返回 ALL ROWS:CHOOSE+得到全部值再返回

oracle教材

查看SQL执行计划执行计划主要查看：访问路径，连接顺序，连接方法

OPTIMIZER

当前优化程序的模式 对具体执行步骤的描述

OBJECT_OWNER

相关表或索引的所有者

OBJECT_NAMECOST CARDINALITY BYTES

表或索引的名称CBO估算的操作开销(RBO为空),用于比较开销大小的权重值，无单位 CBO下对操作所访问行数的估计值 CBO对操作所访问字节的估计11

oracle教材

怎么看SqlPlan的执行顺序方法一：使用工具中的四个小箭头； 方法二：内上原则：内层先执行，同层次时上层先执行。

oracle教材

SQL访问基础 Oracle访问数据的存取方法

–– –

1、全表扫描(Full Table Scans, FTS)2、用ROWID访问表(Table Access by ROWID) 3、索引扫描(Index Scan) (3.1)索引唯一扫描(index unique scan) (3.2)索引范围扫描(index range scan) (3.3)索引全扫描(index full scan) (3.4)索引快速扫描(index fast full scan) 表之间的连接方法 排序合并连接(Sort Merge Join, SMJ) 嵌套循环(Nested Loops, NL)

– –

–

哈希连接(Hash Join, HJ)13

oracle教材

Oracle访问数据之全表扫描 全表扫描(Full Table Scans, FTS) (1)全表扫描： Oracle顺序地读取分配给表的每个数据块的每一行，并检 查每一行是否满足语句的WHERE限制条件，直到读到表的最高水线 处(high water mark, HWM,标识表的最后一个数据块)。 (2)可设定一次I/O读取多块数据块(db_file_multiblock_read_count参数),这

极大的减少了I/O次数，可以十分高效地完成全表扫描，而且只有在 全表扫描的情况下才能使用多块读操作。 (3)由于delete操作不影响HWM值，所以delete一个表后，其全表扫描的时 间不会有改善。 (4)缩减HWM的方法(a)truncate命令使HWM值归为0。(b)oracle 10G后，可 以Shrink收缩HWM的值，行级锁。(c)alter table MOVE,

由FTS模式读入的数据被放到高速缓存的Least Recently Used (LRU)列表的 尾部，这样可以使其快速交换出内存，不会影响内存中重要的数据。使用FTS的前提条件：大表上不建议使用全表扫描，除非取出数据的比 较多，超过总量的10%,或你想使用并行查询功能时。

oracle教材

使用全表扫描的场合 在必须使用全表扫描的场合：(1)无可用索引； (2)返回的数据量为大表的25%左右； (3)对小表的访问； (4)SQL中有全表扫描提示/*+ full (tab)*/;

提高全表扫描的方法：(1)db_file_multiblock_read (2)/*+ full (tab) parallel(tab,10)*/ 并行度(10):一般为CPU数和表所在的磁盘数的最小值…… 此处隐藏：2533字，全部文档内容请下载后查看。喜欢就下载吧 ……