Oracle SQL语句优化心得

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

row_source1按照连接列进行排序,row_source2按照连接列进行排序

row_source1,row_source2一起执行合并操作,即将两个row_source按照连接条件连接起来

嵌套循环

一般原则是选择驱动表是较小的row_source

优点:可以快速返回已经连接的行,不必等所有行连接操作处理完才返回数据,可实时响应

hash连接

较小的row_source用来构建hash table的bitmap,第二个row_source被用来hansed,并与第一个row_source生成的hash table匹配,以便进一步连接,比bitmap用来check hash table中是否有匹配的行。

三种连接方式比较:

smj:第一,对于非等值连接效率较高。关联列上有索引更好。对于两个较大的row_source比nl效率高

nl:第一快速响应。外部表较小,内部表上有唯一索引/高效的非唯一索引

hj:hash_area_size参数要合适。只能用于等值连接。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/59d9f8efb29a2327327ad46e2e8c4ba0.html