Oracle SQL语句优化心得

228次阅读

没有评论

共计 2158 个字符，预计需要花费 6 分钟才能阅读完成。

1.Oracle 优化器种类

自从 Oracle 10g 以来，Oracle 就存在了两种优化器 –CBO 和 RBO。

RBO: Rule-Based Optimization 基于规则的优化器

CBO: Cost-Based Optimization 基于代价的优化器

RBO 顾名思义，Oracle 在系统内部定义了一系列 sql 语句执行了规则，sql 严格按照规则来生成执行计划，并执行，对表的数据分布和变化不敏感，所以才有了 CBO 的出现。

CBO 是对每个查询所耗费的资源进行量化，从而可以根据这个量化的值选出最佳的执行计划，一个查询所耗费的资源可分为:I/O，CPU，network 三部分代价。

<1>I/ O 一般是将数据库文件中的数据库块读入内存 (磁盘读入内存) 所耗费的资源

<2>CPU 代价是内存中处理数据的代价，在这些数据上进行排序 sort, 表的 join 连接操作，这都需要 cpu 资源的耗费。

<3>network 是远程查询数据库表或者执行分布式连接的网络传输代价

注：Oracle 中数据库的概念是 Oracle 数据文件中的最小单位，由多个操作系统块组成。

数据库使用的优化器根据参数 optimizer_mode 决定，取值如下:

RULE 使用 RBO 优化器

choose 数据字典有被引用的对象的统计数据，则使用 CBO，否则使用 RBO

all_rows 以数据吞吐量为主要目标，以便使用最少的资源完成语句

first_rows 以数据响应时间为主要目标，以便快速查询开始的 n 行数据

first_rows[1|100|1000|n] 让优化器选一个能把响应时间减到最小的执行计划，以迅速产生查询结果的前 n 行

2. 执行计划中的概念

row_sources(行源) 根据 where 中条件限制后的结果集或者多表链接后的结果集，不单指 table

predicate(谓词)

access 谓词这个谓词的条件的值将会影响数据的访问路径（一般针对索引）

fileter 谓词起过滤作用

driving table(驱动表, 外表,outer table) 用于嵌套连接和哈希连接

probed table(被探查表, 内表,inner table)

access path(访问路径)

full table scans(全表扫描) Oracle 顺序读取分配给表的每个数据块，知道表的最高水位线。可以一次性读取多个块，block 的数量则由操作系统的 I / O 最大值和 multiblock(db_block_multiblock_read_count)参数共同决定

Table Access by ROWID(通过 ROWID 的表存取 /rowid lookup）

Index Scan（索引扫描 /index lookup）

……

sort-mergejoin(排序合并连接)

nested loop(嵌套连接)

hash join(哈希连接)

3.sql 语句执行过程

每种类型 sql 语句都要一下 n 个阶段:

<1>create a cursor

<2>parse the statement 解析语句

判断语法是否正确，权限是否充足，查找数据字典是否符合表，列的定义，锁分析，生成执行计划等，这一步骤比较耗费资源，一般都应该减少解析次数。但是也有下述这种情况: 当 sql 的基表发生的 dml 语句导致数据分布发生了较大的变化(可能影响的执行计划)，如果还采用之前的执行计划，有可能性能会不太好，这时候最好重新进行表分析，重新生成执行计划，所以这个还是要看具体情况决定。

<5>bind any variables

<7>run the statement

<9>close the cursor

若使用了并行功能

<6>parallelize the statement 并行执行语句

若是 select 语句

<3>describe result of a query 描述查询结果集

<4>define output of a query 定义查询输出

<8>fetch rows of a query 获取查询行

4.sql 中标的连接方式

排序合并连接

MERGE