oracle执行计划学习文档

oracle执行计划学习文档

一、O racl e 执行SQL的步骤

1.1、SQL 语句的两种类型

DDL语句，不共享，每次执行硬解析；

DML语句，会共享，硬解析或者软解析。

1.2、SQL执行步骤

1、语法检测。判断一条SQL语句的语法是否符合SQL的规范；

2、语义检查。语法正确的SQL语句在解析的第二个步骤就是判断该SQL语句所访问的表及列是否准确？用户是否有权限访问或更改相应的表或列？

3、检查共享池中是否有相同的语句存在。假如执行的SQL语句已经在共享池中存在同样的副本，那么该SQL语句将会被软解析，也就是可以重用已解析过的语句的执行计划和优化方案，可以忽略语句解析过程中最耗费资源的步骤，这也是我们为什么一直强调避免硬解析的原因。这个步骤又可以分为两个步骤：

（1）验证SQL语句是否完全一致。

（2）验证SQL语句执行环境是否相同。比如同样一条SQL语句，一个查询会话加了/*+ first_rows */的HINT，另外一个用户加/*+ all_rows */的HINT，他们就会产生不同的执行计划，尽管他们是查询同样的数据。

通过如上三个步骤检查以后，如果SQL语句是一致的，那么就会重用原有SQL语句的执行计划和优化方案，也就是我们通常所说的软解析。如果SQL语句没有找到同样的副本，那么就需要进行硬解析了。

4、Oracle根据提交的SQL语句再查询相应的数据对象是否有统计信息。如果有统计信息的话，那么CBO将会使用这些统计信息产生所有可能的执行计划（可能多达成千上万个）和相应的Cost，最终选择Cost最低的那个执行计划。如果查询的数据对象无统计信息，则按RBO的默认规则选择相应的执行计划。这个步骤也是解析中最耗费资源的，因此我们应该极力避免硬解析的产生。至此，解析的步骤已经全部完成，Oracle将会根据解析产生的执行计划执行SQL语句和提取相应的数据。

二、优化器介绍

Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的。不同的情况,一条SQL 可能有多种执行计划,但在某一时点,一定只有一种执行计划是最优的,花费时间是最少的。

Oracle目前提供RBO和CBO两种优化器。

2.1 RBO（RULE-BASE Optimization）基于规则的优化器

RBO的执行路径和等级：

1、Single Row by Rowid(等级最高)

2、Single Row by Cluster Join

3、Single Row by Hash Cluster Key with Unique or Primary Key

4、Single Row by Unique or Primary Key

5、Clustered Join

6、Hash Cluster Key

7、Indexed Cluster Key

8、Composite Index

9、Single-Column Indexes

10、Bounded Range Search on Indexed Columns

11、Unbounded Range Search on Indexed Columns

12、Sort Merge Join

13、MAX or MIN of Indexed Column

14、ORDER BY on Indexed Column

15、Full Table Scan(等级最低)

优化器根据上述等级优先选择高效的执行路径，以上涉及到的概念在后面详细分析。2.2 CBO（COST-BASE Optimization）基于代价的优化器

Oracle把一个代价引擎集成在数据库内核，用来估计每个执行计划的代价，并量化执行计划所耗费资源，从而选择选择最优的执行计划，查询耗费资源分为以下三种。

I/0代价，即从磁盘读数据到内存的代价,从数据文件中数据块的内容读取到SGA数据高速缓存中，这是数据访问最主要的代价，故优化原则一般以降低查询产生的I/0次数为主；

CPU代价，即处理在内存中数据所需代价，如对数据进行排序(sort)或者连接(join)操作等；

NetWork代价，对访问跨服务器数据库的数据，需要花费的传输操作耗费的资源。

CBO 方式通过表和索引的统计数据计算出相对准确的代价，然后采用最佳的执行计划，所以定期对表和索引进行分析是非常必要的，否则得不偿失，关于数据分析技术详见第三章。

2.3 优化器模式

Optimization-mode 即优化器模式，可选值包括：

1、Rule ，采用的是RBO；

2、CHOOSE，根据实际情况，如果数据字典中包含了引用表的统计数据，则采用CBO优

化器，否则采用RBO；

3、ALL-Rows是CBO使用的第一种优化方法，以数据吞吐量为目标，以便可以使用最少

的资源完成查询；

4、FIRST-ROWS是CBO使用的第二种优化方法，以数据的响应时间为目标，以便快速查

询出开始的几行；

5、FIRST-ROWS_[1|10|100|1000] 是CBO使用的第三种优化方法，选择一个响应时间最

小的计划，迅速查询出结果。

2.4 查看执行计划

2.4.1、查看能执行计划方式

1、通过下面的sql查询：

explain plan for

SELECT * FROM bss_org WHERE bss_org_id=1;

SELECT * FROM table(dbms_xplan.display);

2、直接看pl/sql的explain Plan。

2.4.2 Estimator

共 3 种度量标准：

1、Selectivity

表示有多少 rows 可以通过谓词被选择出来，大小介于 0.0~1.0，0 表示没有 row 被

选择出来。

如果没有 statistics，estimator 会使用一个默认的 selectivity 值，这个值根据谓词的不同而异。比如 '=' 的 selectivity 小于 '<'。

如果有statistics，比如对于last_name = 'Smith'，estimator 使用last_name 列的 distinct 值的倒数（注：是指表中所有 last_name 的 distinct 值），作为 selectivity。

如果 last_name 列上有 histogram，则使用 histogram 根据 last_name 值的分布情况产生的 selectivity 作为 selectivity。Histogram 在当列有数据倾斜时可以大大帮助 CBO 产生好的 selectivity。

2. Cardinality

表示一个 row set 的行数。

Base cardinality：base table 的行数。如果表分析过了，则直接使用分析的统计信息。如果没有，则使用表 extents 的数量来估计。

Effective cardinality：有效行集，指从基表中选择出来的行数。是 Base cardinality 和表上所有谓词的组合 Selectivity 的乘积。如果表上没有谓词，那么 Effective cardinality = Base cardinality。

Join cardinality：两表 join 后产生的行数。是两表 cardinality 的乘积(Cartesian)乘以 Join 谓词的 selectivity。

Distinct cardinality：列上 distinct 值的行数。

Group cardinality：GROUP BY 操作之后 row set 的行数。由 grouping columns 的distinct cardinality 和整个 row set 的行数决定。

group cardinality lies between max ( dist. card. colx , dist. card. coly ) and min ( (dist. card. colx * dist. card. coly) , num rows in row set )

3. Cost

Cost 表现了 Disk I/O, CPU usage, Memory usage 资源单位的使用数量（units of work or resource used）。

Access path 决定从 base table 获得数据所需的 units of work 的数量。也就是说Access path 决定 Cost 的值。Access path 可以是 table scan, fast full index scan, index scan。

Oracle10G中,优化器默认为CBO,OPTIMIZER_MODE默认值为ALL_ROWS。不再使用古老的RBO模式,但RULE、CHOOSE并没有彻底消失,有些时候仍然可以作为我们调试的工具。另

DBMS_XPLAN.DISPLAY_CURSOR观察更为详细的执行计划。

2.5 Scan方式

2.5.1、Full T able Scan 全表扫描

优点：可以同时读多个数据块，减少了i/0访问次数，而且每个数据块只会被读一次。在查询一个表>5%~10%的时候，或者想用并行查询时，可以考虑使用。全表扫描的Hint: Full T able Scan Hints: /*+ FULL(table alias) */；

2.5.2、Rowid Scans

获得一行数据的最快方法。一般要先通过index scan 获得Rowid，如果需要的列不在index 中，再进行Rowid Scans 获得相应的行，如果在index 中，则不需要Rowid Scans。HINT（很少用到）：/*+ ROWID ( table ) */

2.5.3、Index Scans

1)、Index Unique Scans

最多返回一个rowid，用于Unique Index 且index cols 在条件中使用"等于"。如：SELECT * from serv where serv_id='518108574'。

2)、Index Range Scans

返回的数据按照index columns 升序排列，index column 为同一个值的多行按照行rowid 的升序排列。如果order by/group by 的顺序和Index Range Scans 返回的row set 的顺序相同就不需要再sort 了，否则还需要再对row set 进行sort。如: SELECT * from serv where prop_cust_id='518108574'.

Unique index 中的< > 条件，以及nonunique indexe 的< = > 条件，都会引起Index Range Scans。如果进行like查找，% 不能放最前面，否则不会进行Index Range Scans。如：SELECT * from serv where serv_id LIKE'518108574%'。

使用该表上指定的索引对表进行索引扫描HINT：/*+ INDEX ( table [index]) */；

不使用该表上指定的索引进行存取，仍然可以使用其它的索引进行索引扫描，HINT：

/*+ NO_INDEX ( table [index]) */

3)、Index Range Scans Descending

和Index Range Scans 相同，只是用于降序返回结果，或者返回小于某特定值的结果。

HINT：/*INDEX_DESC(table_alias index_name)*/

4)、Index Skip Scans

用于前导列没有出现在查询中（skiped）时使用索引。它将composite index 拆分成若干个小的逻辑子索引。子索引的个数由前导列的distinct 值决定。适用于前导列distinct 值

很少（子索引就少了），非前导列distinct 值很多的情况。

HINT: /*INDEX_SS(table_alias index_name)*/

5)、Full Scans ，当索引的列作为谓词，且不需要索引驱动时；或者没有谓词，并且a、查询中引用的列都在index 中，b、至少有一个索引列不为空时采用Full Scans。它是先定位到索引的root block，然后到branch block（如果有的话），再定位到第一个leaf block，然后根据leaf block 的双向链表顺序读取。它所读取的块都是有顺序的，也是经过排序的。

6)、Fast Full Index Scans ，和Full Scans 适用于：查询的所有列都在索引中出现，且至少有一个索引列具有NOT NULL 约束。区别在于它是从段头开始，读取包含位图块，root block，所有的branch block，leaf block，读取的顺序完全有物理存储位置决定，并采取多块读，没次读取db_file_multiblock_read_count个块。

Fast Full Index Scans 可以利用多块读和并行读，只能用于CBO，不能在bitmap indexes 上使用。

当需要排序时，Oracle 会使用Full Index Scans，因为他的结果已经排好序；当不排序时，会使用Fast Full Index Scans，因为能使用多块读，速度更快。

在rebuild index 时使用的就是Fast Full Index Scans，所以rebuild index 需要排序。

HINT：/*INDEX_FFS(table_alias index_name)*/

Fast Full Index Scan 限制：索引列中至少有一列有NOT NULL 约束

如果要用并行fast full index scan 必须在创建索引时单独指定parallel clause，不能从索引所在的表上继承，必须分析索引，否则优化器可能不会使用它

2.5.4、Cluster Scans

用于从存放于indexed cluster 中的表中获得相同cluster key 值的数据。具有相同cluster key 值的所有数据存放于同一个BLOCK。通过扫描cluster index 获得相应的rowid，再通过rowid 定位到所需的行。

2.5.5 、Hash Scans

用于从存放于hash cluster 中的表中获得相同hash value 值的数据。具有相同hash value 值的所有数据存放于同一个BLOCK。通过将hash function 应用于cluster key 值上，获得hash value，再通过hash value 定位到所需的行上。

2.5.6、Sample T able Scans

从表中获得a random sample of data。

SAMPLE clause：从表中随机获得指定百分比的行数据。

SAMPLE BLOCK clause：从表中随机获得指定百分比的块数据。

限制：查询不能包含a join or a remote table ，需要使用CBO

2.6 连接方式

优化器使用6种不同表的连接方式。

2.6.1嵌套循环连接（NESTED LOOP JOIN）

嵌套循环连接的内部处理的流程：

1） Oracle 优化器根据基于规则RBO或基于成本CBO的原则，选择两个表中的一个作为驱动表，并指定其为外部表。

2） Oracle 优化器再将另外一个表指定为内部表。

3） Oracle从外部表中读取第一行,然后和内部表中的数据逐一进行对比，所有匹配的记录放在结果集中。

4） Oracle读取外部表中的第二行，再和内部表中的数据逐一进行对比，所有匹配的记录添加到结果集中。

5）重复上述步骤，直到外部表中的所有纪录全部处理完。

6）最后产生满足要求的结果集。

使用嵌套循环连接是一种从结果集中提取第一批记录最快速的方法。在驱动行源表(就是正在查找的记录)较小、或者内部行源表已连接的列有惟一的索引或高度可选的非惟一索引时, 嵌套循环连接效果是比较理想的。嵌套循环连接比其他连接方法有优势,它可以快速地从结果集中提取第一批记录,而不用等待整个结果集完全确定下来。这样,在理想情况下,终端用户就可以通过查询屏幕查看第一批记录,而在同时读取其他记录。不管如何定义连接的条件或者模式,任何两行记录源可以使用嵌套循环连接,所以嵌套循环连接是非常灵活的。

然而,如果内部行源表(读取的第二张表)已连接的列上不包含索引,或者索引不是高度可选时, 嵌套循环连接效率是很低的。如果驱动表的记录非常庞大时,其他的连接方法可能更加有效。如下sql，查看执行计划，采用NESTED LOOP JOIN：

SELECT b.serv_type_name,https://www.wendangku.net/doc/3710551228.html,

from serv a,serv_type b

where a.serv_type_id = b.serv_type_id

AND a.serv_type_id='0PA144';

可以通过在SQL语句中添加HINTS,强制ORACLE优化器产生嵌套循环连接的执行计划。

HINT:/*+ use_nl(a b) */

2.6.2群集连接(CLUSTER JOIN)

群集连接实际上是嵌套循环连接的一种特例。如果所连接的两张源表是群集中的表，即两张表属于同一个段（SEGMENT），那么ORACLE能够使用群集连接。处理的过程是：ORACLE从第一张行源表中读取第一行，然后在第二张行源表中使用CLUSTER索引查找能够匹配到的纪录；继续上面的步骤处理行源表中的第二行，直到所有的记录全部处理完。

群集连接的效率极高，因为两个参加连接的行源表实际上处于同一个物理块上。但是，群集连接也有其限制，没有群集的两个表不可能用群集连接。所以，群集连接实际上很少使用。

2.6.3排序合并连接（SORT MERGE JOIN）

排序合并连接内部处理的流程：

1) 优化器判断第一个源表是否已经排序，如果已经排序，则到第3步，否则

到第2步。

2) 第一个源表排序

3) 优化器判断第二个源表是否已经排序，如果已经排序，则到第5步，否则

到第4步。

4) 第二个源表排序

5) 已经排过序的两个源表进行合并操作，并生成最终的结果集。

在缺乏数据的选择性或者可用的索引时,或者两个源表都过于庞大(所选的数据超过表记录数的5%)时,排序合并连接将比嵌套循环连更加高效。

排列合并连接需要比较大的临时内存块,以用于排序，这将导致在临时表空间占用更多的内存和磁盘I/O。

SELECT a.cust_name,https://www.wendangku.net/doc/3710551228.html,

from property_cust a, serv b

where a.prop_cust_id > b.prop_cust_id

Plan

--------------------------------------------------

可以通过在SQL语句中添加HINTS,强制ORACLE优化器产生排序合并连接的执行计划:

/*+ use_merge(a b) */ ,排序合并连接是基于RBO的。

2.6.4笛卡尔连接(CARTESIAN JOIN)

笛卡尔连接是指在sql语句中没有写出表连接的条件，优化器把第一个表的每一条记录和第二个表的所有纪录相连接。如果第一个表的纪录数为m, 第二个表的纪录数为m,则会产生m*n条纪录数。

下面的查询，未指名连接条件，就会产生笛卡尔连接。

SELECT a.cust_name,https://www.wendangku.net/doc/3710551228.html,

from property_cust a, serv b

由于笛卡尔连接会导致性能很差的SQL,因此一般也很少用到。

2.6.5 哈希连接(HASH JOIN)

当内存能够提供足够的空间时,哈希(HASH)连接是Oracle优化器通常的选择。哈希连接中，优化器根据统计信息，首先选择两个表中的小表，在内存中建立这张表的基于连接键的哈希表；优化器再扫描表连接中的大表，将大表中的数据与哈希表进行比较，如果有相关联的数据，则将数据添加到结果集中。

当表连接中的小表能够完全cache到可用内存的时候，哈希连接的效果最佳。哈希连接的成本只是两个表从硬盘读入到内存的成本。

但是，如果哈希表过大而不能全部cache到可用内存时，优化器将会把哈希表分成多个分区，再将分区逐一cache到内存中。当表的分区超过了可用内存时，分区的部分数据就会临时地写到磁盘上的临时表空间上。因此，分区的数据写磁盘时，比较大的区间（EXTENT）会提高I/O性能。ORACLE推荐的临时表空间的区间是1MB。临时表空间的区间大小由UNIFORM SIZE指定。

当哈希表构建完成后，进行下面的处理：

1）第二个大表进行扫描

2）如果大表不能完全cache到可用内存的时候，大表同样会分成很多分区

3）大表的第一个分区cache到内存

4）对大表第一个分区的数据进行扫描，并与哈希表进行比较，如果有匹配的纪录，添加到结果集里面

5）与第一个分区一样，其它的分区也类似处理。

6）所有的分区处理完后，ORACLE对产生的结果集进行归并，汇总，产生最终的结果。

当哈希表过大或可用内存有限，哈希表不能完全CACHE到内存。随着满足连接条件的结果集的增加，可用内存会随之下降，这时已经CACHE到内存的数据可能会重新写回到硬盘去。如果出现这种情况，系统的性能就会下降。

当连接的两个表是用等值连接并且表的数据量比较大时，优化器才可能采用哈希连接。哈希连接是基于CBO的。只有在数据库初始化参数HASH_JOIN_ENABLED设为True,并且为参数

PGA_AGGREGATE_TARGET设置了一个足够大的值的时候,Oracle才会使用哈希边连接。

HASH_AREA_SIZE是向下兼容的参数,但在Oracle9i之前的版本中应当使用HASH_AREA_SIZE。当使用ORDERED提示时,FROM子句中的第一张表将用于建立哈希表。

select a.cust_name,https://www.wendangku.net/doc/3710551228.html,

from property_cust a, serv b

where a.prop_cust_id = b.prop_cust_id

Plan

----------------------------------------------------------

可以通过在SQL语句中添加HINTS,强制ORACLE优化器产生哈希连接的执行计划：

/*+ use_hash(a b)*/。

当缺少有用的索引时，哈希连接比嵌套循环连接更加有效。哈希连接也可能比嵌套循环连接更快，因为处理内存中的哈希表比检索B_树索引更加迅速。

2.6.6索引连接(INDEX JOIN)

如果一组已存在的索引包含了查询所需要的所有信息，那么优化器将在索引中有选择地生成一组哈希表。可通过范围或者快速全局扫描访问到每一个索引，而选择何种扫描方式取决于WHERE子句中的可有条件。在一张表有大量的列，而您只想访问有限的列时，这种方法非常有效。WHERE子句约束条件越多，执行速度越快。因为优化器在评估执行查询的优化路径时，将把约束条件作为选项看待。您必须在合适的列（那些满足整个查询的列）上建立索引，这样可以确保优化器将索引连接作为可选项之一。这个任务通常牵涉到在没有索引，或者以前没有建立联合索引的列上增加索引。相对于快速全局扫描，连接索引的优势在于：快速全局扫描只有一个单一索引满足整个查询;索引连接可以有多个索引满足整个查询。

假设表SERV上有两个索（一个在dev_no,一个在dev_type 上）。

作如下的查询

select dev_no,dev_type

from user_info

where user_id = ‘U101010’

and dev_type = ‘1010’;

2.6.7几种连接方式比较

2.7优化器的执行操作

2.7.1 IN-List Iterators

当指定了特定的值，并且在该列上有索引，如SERV_id IN (子句) ，优化器选择IN-list iterator。

当有多个OR 子句使用相同的索引，那么优化器也选择更高效的IN-list iterator，而不使用连接or UNION ALL方式。

2.7.2 Concatenation

当不同的条件使用OR clause 连接起来，并且不同的条件都可以通过不同的索引生成较好的执行计划，那么Concatenation 是很有用的

2.7.3 Remote Operations 和Distributed Statements

远程数据库Network round trips 比物理和逻辑I/Os 昂贵几个数量级

1.如果SQL 语句中的所有表来自同一个远程数据库，Oracle 把语句发送给远程数据库，远程数据库执行完之后把结果发还给本地数据库。

2.如果表来自不同的数据库，Oracle 把语句拆分，每一个部分访问单个数据库上的表，把他们分别发送给各数据库，各数据库执行自己部分的语句，并把结果发还给本地数据库，本地数据库再执行语句的其余处理部分。

如果是CBO，优化器会考虑远程数据库上的索引，就像本地数据库一样，还会考虑远程的statistics，此外，在估计访问的cost 时，还会考虑数据的位置，比如远程的一个全表扫描比本地相同表的全表扫描估计的cost 要高。

对于RBO，优化器不会考虑远程数据库上的索引。

2.7.4 Sort

1、SORT UNIQUE

如果使用了DISTINCT clause 或者unique values 在下一步中需要，就会发生SORT UNIQUE

2、SORT AGGREGATE

SORT AGGREGATE 实际上不发生sort，他使用于对整个set of rows 进行聚合计算。

3、 SORT GROUP BY

SORT GROUP BY 用于对不同组上的数据进行聚合计算，这种情况下sort 是需要的，sort 用于将行拆分成不同的组。

4、SORT JOIN

在SORT MERGE JOIN 中，如果数据需要根据join key 排序，就会发生SORT JOIN。

6、S ORT ORDER BY

当语句中使用ORDER BY，并且没有任何索引适合这种排序方式，那么SORT ORDER BY 就需要

2.7.5常量

常量的计算只在语句被优化时执行一次，而不是每次语句被执行的时候。

比如：salary > 24000/12 会被优化器简化为salary > 2000

优化器不会跨过比较符简化表达式，salary*12 > 24000 不能被简化为salary > 2000，因此写语句时应尽量用常量和列作比较，而不要将列作计算之后再去比较。

2.7.6 UNION and UNION ALL

对于将OR clauses 组合为一个复合语句，或者将一个复杂语句分解为包含简单select 语句的复合语句很有用，他们更易于优化和理解。

就和concatenation 一样，如果UNION ALL 重复了昂贵的操作，就不应该使用。

2.7.7 LIKE

对于没有通配符的like 条件，优化器会将他简化为等于操作

cust_name LIKE 'SMITH' 等价于：cust_name = 'SMITH'

但这种简化只能用于变长的类型，对于固定长度的，比如CHAR(10) 就不能简化，因为等于操作遵循blank-padded semantics，而like 不是（此规则只适合9i 以上）。

2.7.8 ANY or SOME

1.列表优化器将其扩展为等价的comparison operators and OR logical operators 条件

salary > ANY (:first_sal, :second_sal) 等价于：

salary > :first_sal OR salary > :second_sal

2.子查询

优化器将其转化为等价的EXISTS operator and a correlated subquery 条件

x > ANY (SELECT salary FROM employees

WHERE job_id = 'IT_PROG')

变为

EXISTS (SELECT salary

FROM employees

WHERE job_id = 'IT_PROG'

AND x > salary)

2.7.9 ALL

1.列表

优化器将其扩展为等价的comparison operators and AND logical operators 条件

2.子查询

优化器将其转化为等价的ANY comparison operator and a complementary comparison operator 条件

x > ALL (SELECT salary

FROM employees

WHERE department_id = 50)

变为

NOT (x <= ANY (SELECT salary

FROM employees

WHERE department_id = 50) )

然后再进一步根据ANY Operator 的转换规则再将其转换：

NOT EXISTS (SELECT salary

FROM employees

WHERE department_id = 50

AND x <= salary)

2.7.10 BETWEEN

优化器总是将BETWEEN 条件用>= and <= 条件来代替

2.7.11NOT

优化器总是将BETWEEN 条件用>= and <= 条件来代替

2.7.12 Transitivity

如果两个条件涉及到同一个column，且这个column 的其中一个条件是和constant expressions 进行比较，那么有时候优化器会推断出一个条件，这个推断的条件可以使用index access path，而原始的条件却不能使用：

WHERE column1 comp_oper constant AND column1 = column

其中：comp_oper 为任何比较操作：=, !=, ^=, <, <>, >, <=, or >=

constant 为任何常量表达式（不能为其他column）：SQL functions, literals, bind variables, and correlation variables，

这时，优化器会推断一个条件：

column2 comp_oper constant

如果column2 上有索引，就能使用该索引

注：Transitivity 只用于CBO

2.7.13 表达式

公共的子表达式优化是一种启发式的优化，可以鉴别、移出、收集在各disjunctive (OR) branches 中的公共子表达式，绝大数情况下，可以减少join 的数量。

在满足一下情况时，可使用公共子表达式优化（从最顶层至最内层的顺序）：

1.顶层条件是一个disjunction（几个以or 连接的条件）

2.每个or 分支中是simple predicate 或者a conjunction（几个以and 连接的条件）

3.每个and 分支中是simple predicate 或者 a disjunction of simple predicates

4.表达式在每个or 分支中都出现，即公共子表达式

simple predicate 只不含有AND or OR 连接的条件

满足以上条件的公共子表达式，优化器会将其移到最顶层，去除重复，再和被移去公共子表达式的原disjunction 做conjunction，这样可以减少join 操作。

2.7.14 DETERMINISTIC Functions

某些情况，优化器不需要计算user-written function 的值，而用以前计算的值来

代替他。

这种function 必须有一定的限制：

1.Function 的返回值不能随着package variables、database、session parameters 的不同而改变

2.如果function 被重定义了，那么他的返回值和以前的要保持一致

3.使用预计算结果代替执行function 必须没有重大副作用

使用DETERMINISTIC 关键字创建的function 告诉Oracle 该function 满足以上限制，Oracle 不会去检查该function，即使function 很明显不满足以上限制，因此，程序员应负责检查以上限制，只有满足了才能加DETERMINISTIC 关键字。

2.8优化器怎样转换SQL句子

2.8.1把多个OR组合查询

如果一个查询包含多个用OR 连接的条件，优化器会将其转换为用UNION ALL 连接的混合查询，如果转换后的语句执行更加高效

1.如果每个条件都可以单独使用index access path，那么可以转换。优化器将转换后的语句生成执行计划，他们用不同的索引多次访问表，并把结果放到一起

2.如果任何一个条件使用full table scan，那么不会转换，因为一个full table scan 和几个index scan 结合的效率不如直接使用一个full table scan 效率高

3.对于CBO，优化器会估计并比较转换前后的语句所需的COST，并决定是否转

4.CBO 不会转换IN-lists 或者条在在同一列上的OR 操作，而使用INLIST iterator operator

2.8.2优化多个子句的负责语句

优化复杂语句，优化器选择下面两种方法的其中一种：

1.如果复杂语句可以转换为等同的join 语句，那么先转换，再优化join 语句，这样可以利用join 语句的优势

2.如果无法转换，那么优化复杂语句本身

子查询包含aggregate functions，比如AVG 的复杂语句，无法转换为join 语句三、数据分析

一般数据改变到足够影响执行计划了，就应该进行分析，尤其对分区表，以下是常用的分析方法：

1、GATHER_INDEX_STATS：分析索引信息

2、GATHER_TABLE_STATS：分析表信息，当cascade为true时，分析表、列（索引）信息

3、GATHER_SCHEMA_STATS：分析方案信息

4、GATHER_DATABASE_STATS：分析数据库信息

5、GATHER_SYSTEM_STATS：分析系统信息

常用脚本如：

dbms_stats.gather_index_stats(ownname=>'citictest', INDNAME=>rec.segment_name);

dbms_stats.gather_table_stats(OWNNAME=>'citictest',

TABNAME=>rec.segment_name,

ESTIMATE_PERCENT=>v_per,

METHOD_OPT=>'FOR ALL INDEXED COLUMNS');

四、参考文档：

1、SQL语句性能调整之ORACLE的执行计划

https://www.wendangku.net/doc/3710551228.html,/a2009/0423/273/000000273787.shtml

2、https://www.wendangku.net/doc/3710551228.html,/post/330/2970

ORACLE 执行计划介绍与测试

ORACLE 执行计划介绍与测试 (沈克勤) 2005-3-3

1.目的： 本文档的目的是通过介绍常用的HINT来了解ORACLE的优化器的工作原理及执行计划，以期望起到抛砖引玉的作用。在实际开发中有意识地控制SQL的执行计划，以达到SQL 执行性能的最优以及执行计划稳定。 为了减少枯燥的文档描述，使用了较多的图示。 2.如何查看执行计划 首先创建EXPLAIN_PLAN表 不同版本的ORACLE，该表结构可能会不同。请使用的ORACLE中 $ORACLE_HOME/rdbms/admin/utlxplan.sql去创建该表。 方法1：使用SQL*PLUS 的SET AUTOTRACE ： SQL>SET AUTOTRACE ON EXPLAIN 执行SQL，且仅显示执行计划 SQL>SET AUTOTRACE ON STATISTICS 执行SQL，且仅显示执行统计信息 SQL>SET AUTOTRACE ON 执行SQL，且显示执行计划与执行统计信息SQL>SET AUTOTRACE TRACEONLY 仅显示执行计划与统计信息，无执行结果SQL>SET AUTOTRACE OFF 关闭跟踪显示计划与统计

方法2：使用PL/SQL Developer工具

方法3：使用DBMS_XPLAN.DISPLAY() 方法4：直接查看表：EXPLAIN_TABLE SELECT lpad(' ',level-1)||operation||' '||options||' '|| object_name "Plan" FROM plan_table CONNECT BY prior id = parent_id AND prior statement_id = statement_id START WITH id = 0AND statement_id = '&1' ORDER BY id; 3.如何控制与改变执行计划 我并没有见过单独介绍ORACLE SQL优化器原理方面的资料。但可以从ORACLE的HINT这个侧面来了解ORACLE的优化器的原理，从而最有效地书写SQL。

利用Oracle执行计划机制提高查询性能

利用Oracle执行计划机制提高查询性能消耗在准备利用Oracle执行计划机制提高查询性能新的SQL语句的时间是Oracle SQL语句执行时间的最重要的组成部分。但是通过理解Oracle内部产生执行计划的机制，你能够控制Oracle花费在评估连接顺序的时间数量，并且能在大体上提高查询性能。 准备执行SQL语句 当SQL语句进入Oracle的库缓存后，在该语句准备执行之前，将执行下列步骤： 1) 语法检查：检查SQL语句拼写是否正确和词序。 2) 语义分析：核实所有的与数据字典不一致的表和列的名字。 3) 轮廓存储检查：检查数据字典，以确定该SQL语句的轮廓是否已经存在。 4) 生成执行计划：使用基于成本的优化规则和数据字典中的统计表来决定最佳执行计划。 5) 建立二进制代码：基于执行计划，Oracle生成二进制执行代码。 一旦为执行准备好了SQL语句，以后的执行将很快发生，因为Oracle认可同一个SQL语句，并且重用那些语句的执行。然而，对于生成特殊的SQL语句，或嵌入了文字变量的SQL语句的系统，SQL执行计划的生成时间就很重要了，并

且前一个执行计划通常不能够被重用。对那些连接了很多表的查询，Oracle需要花费大量的时间来检测连接这些表的适当顺序。 评估表的连接顺序 在SQL语句的准备过程中，花费最多的步骤是生成执行计划，特别是处理有多个表连接的查询。当Oracle评估表的连接顺序时，它必须考虑到表之间所有可能的连接。例如：六个表的之间连接有720（6的阶乘，或6 * 5 * 4 * 3 * 2 * 1 = 720）种可能的连接线路。当一个查询中含有超过10个表的连接时，排列的问题将变得更为显著。对于15个表之间的连接，需要评估的可能查询排列将超过1万亿（准确的数字是1,307,674,368,000）种。 使用optimizer_search_limit参数来设定限制 通过使用optimizer_search_limit参数，你能够指定被优化器用来评估的最大的连接组合数量。使用这个参数，我们将能够防止优化器消耗不定数量的时间来评估所有可能的连接组合。如果在查询中表的数目小于optimizer_search_limit的值，优化器将检查所有可能的连接组合。 例如：有五个表连接的查询将有120（5! = 5 * 4 * 3 * 2 * 1 = 120）种可能的连接组合，因此如果optimizer_search_limit等于5（默认值），则优化器将评

oracle执行计划解释

oracle执行计划解释 一.相关概念 1·rowid，伪列：就是系统自己给加上的，每个表都有一个伪列，并不是物理存在。它不能被修改，删除，和添加，rowid在该行的生命周期是唯一的，如果向数据库插入一列，只会引起行的变化，但是rowid并不会变。 2·recursive sql概念：当用户执行一些SQL语句时，会自动执行一些额外的语句，我们把这些额外的SQL语句称为“recursive calls” 或者是“recursive sql statement”,当在执行一个DDL语句时，Oracle总会隐含的发出一些Recursiv sql语句，用于修改数据字典，如果数据字典没有在共享内存中，则就执行“resursive calls”，它会把数据字典从物理读取到共享内存。当然DML和select语句都可能引起recursive SQL。 3·row source 行源：在查询中，由上一操作返回的符合条件的数据集，它可能是整个表，也可能是部分，当然也可以对2个表进行连接操作（join）最后得到的数据集4·predicate:一个查询中的where限制条件 5·driving table 驱动表：该表又成为外层表，这个感念用于内嵌和HASH连接中，如果返回数据较大，会有负面影响，返回行数据较小的适合做驱动表 6·probed table 被探查表：该表又称为内层表，我们在外层表中取得一条数据，在该表中寻找符合连接的条件的行。 7·组合索引（concatenated index）由多个列组成的索引，在组合索引中有一个重要的概念，就是引导索引， create index idx_tab on tab(col1,col2,col3), indx_tab则称为组合索引， col1则称为引导列 在查询条件where后，必须使用引导索引，才会使用该组合索引 8.可选择性（selectivity）比较一下列中唯一键的数量和表中的行数，就可以判断该列的可选择性。如果该列的“唯一键的数量/表中的行数”的比值越接近1，则该列的可选择性越高，该列就越适合创建索引，同样索引的可选择性也越高。在可选择性高的列上进行查询时，返回的数据就较少，比较适合使用索引查询。 二.Oracle访问数据的存取方法 1.全表扫描(Full tabel scans,FTS) 为了实现全表扫描，Oracle读取数据库中的每一行，并检查每一行是否满足语句的where 限制条件一个多块读操作，可以使io能读取多块数据块。减少了IO次数，提高了系统的吞吐量。在多块读的方法的使用下，可以高效的实现数据库全表扫描，而且，中有在全表扫描的情况下，在可以使用多块读的方法。在这个种访问模式下，数据块只读一次。 【注意】 使用FTS的前提是，在较大的表中，不建议使用FTS,除非取出的数据较多，超过总量的5%-10%，或者使用并行查询时 2.通过rowid的表存取 行的ROWID指向了该行的数据文件，数据块，以及在数据块中的位置，使用rowid能快速的定位到要取得数据的行上，在Oracle中，这是取得单行最快的方式。 【注意】 该存取方法，不会用到多块读操作，一次IO只能读取一个数据块。 3.索引扫描(index scan 和index lookup) 索引扫描时通过index查找到对应行的rowid，然后通过rowid从数据库中得到具体的数据。该方法分为两个步骤，

oracle执行计划学习文档

oracle执行计划学习文档 一、O racl e 执行SQL的步骤 1.1、SQL 语句的两种类型 DDL语句，不共享，每次执行硬解析； DML语句，会共享，硬解析或者软解析。 1.2、SQL执行步骤 1、语法检测。判断一条SQL语句的语法是否符合SQL的规范； 2、语义检查。语法正确的SQL语句在解析的第二个步骤就是判断该SQL语句所访问的表及列是否准确？用户是否有权限访问或更改相应的表或列？ 3、检查共享池中是否有相同的语句存在。假如执行的SQL语句已经在共享池中存在同样的副本，那么该SQL语句将会被软解析，也就是可以重用已解析过的语句的执行计划和优化方案，可以忽略语句解析过程中最耗费资源的步骤，这也是我们为什么一直强调避免硬解析的原因。这个步骤又可以分为两个步骤： （1）验证SQL语句是否完全一致。 （2）验证SQL语句执行环境是否相同。比如同样一条SQL语句，一个查询会话加了/*+ first_rows */的HINT，另外一个用户加/*+ all_rows */的HINT，他们就会产生不同的执行计划，尽管他们是查询同样的数据。 通过如上三个步骤检查以后，如果SQL语句是一致的，那么就会重用原有SQL语句的执行计划和优化方案，也就是我们通常所说的软解析。如果SQL语句没有找到同样的副本，那么就需要进行硬解析了。 4、Oracle根据提交的SQL语句再查询相应的数据对象是否有统计信息。如果有统计信息的话，那么CBO将会使用这些统计信息产生所有可能的执行计划（可能多达成千上万个）和相应的Cost，最终选择Cost最低的那个执行计划。如果查询的数据对象无统计信息，则按RBO的默认规则选择相应的执行计划。这个步骤也是解析中最耗费资源的，因此我们应该极力避免硬解析的产生。至此，解析的步骤已经全部完成，Oracle将会根据解析产生的执行计划执行SQL语句和提取相应的数据。

sqlplus中查看执行计划分析

sqlplus中查看执行计划分析 对于oracle9i，需要手工设置plustrace角色，步骤如下： 1、在SQL>connect sys/密码as sysdba （密码为：数据库所在的那台服务器的密码） 在sys用户下运行$ORACLE_HOME/sqlplus/admin/plustrce.sql SQL>@$ORACLE_HOME/sqlplus/admin/plustrce.sql 这段sql的实际内容如下： set echo on drop role plustrace; create role plustrace; grant select on v_$sesstat to plustrace; grant select on v_$statname to plustrace; grant select on v_$mystat to plustrace; grant plustrace to dba with admin option; set echo off 以上产生plustrace角色 2、在sys用户下把此角色赋予一般用户 SQL> grant PLUSTRACE to 用户名; （用户名为：当前你登陆数据库的用户名，如：bbass） 3、然后在当前用户下运行$ORACLE_HOME/rdbms/admin/utlxplan.sql SQL>@$ORACLE_HOME/rdbms/admin/utlxplan.sql 它会创建一个plan_table，用来存储分析SQL语句的结果。 4、SQL> set timing on 可查看SQL语句执行的用时 SQL> set autotrace on; 可查看SQL执行计划分析。 关于Autotrace几个常用选项的说明: SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式

oracle-SQL语句执行原理和完整过程详解

SQL语句执行过程详解 一条sql，plsql的执行到底是怎样执行的呢？ 一、SQL语句执行原理： 第一步:客户端把语句发给服务器端执行 当我们在客户端执行select 语句时,客户端会把这条SQL 语句发送给服务器端,让服务器端的进程来处理这语句。也就是说,Oracle 客户端是不会做任何的操作,他的主要任务就是把客户端产生的一些SQL 语句发送给服务器端。虽然在客户端也有一个数据库进程,但是,这个进程的作用跟服务器上的进程作用事不相同的。服务器上的数据库进程才会对SQL 语句进行相关的处理。不过,有个问题需要说明,就是客户端的进程跟服务器的进程是一一对应的。也就是说,在客户端连接上服务器后,在客户端与服务器端都会形成一个进程,客户端上的我们叫做客户端进程;而服务器上的我们叫做服务器进程。 第二步:语句解析 当客户端把SQL 语句传送到服务器后,服务器进程会对该语句进行解析。同理,这个解析的工作, 其会做很多小动作。 也是在服务器端所进行的。虽然这只是一个解析的动作,但是,“” 1. 查询高速缓存(library cache)。服务器进程在接到客户端传送过来的SQL 语句时,不 会直接去数据库查询。而是会先在数据库的高速缓存中去查找,是否存在相同语句的执行计划。如果在数据高速缓存中,则服务器进程就会直接执行这个SQL 语句,省去后续的工作。所以,采用高速数据缓存的话,可以提高SQL 语句的查询效率。一方面是从内存中读取数据要比从硬盘中的数据文件中读取数据效率要高,另一方面,也是因为这个语句解析的原因。 不过这里要注意一点,这个数据缓存跟有些客户端软件的数据缓存是两码事。有些客户端软件为了提高查询效率,会在应用软件的客户端设置数据缓存。由于这些数据缓存的存在,可以提高客户端应用软件的查询效率。但是,若其他人在服务器进行了相关的修改,由于应用软件数据缓存的存在,导致修改的数据不能及时反映到客户端上。从这也可以看出,应用软件的数据缓存跟数据库服务器的高速数据缓存不是一码事。 2. 语句合法性检查(data dict cache)。当在高速缓存中找不到对应的SQL 语句时,则服 务器进程就会开始检查这条语句的合法性。这里主要是对SQL 语句的语法进行检查,看看其是否合乎语法规则。如果服务器进程认为这条SQL 语句不符合语法规则的时候,就会把这个错误信息,反馈给客户端。在这个语法检查的过程中,不会对SQL 语句中所包含的表名、列名等等进行SQL 他只是语法上的检查。 3. 语言含义检查(data dict cache)。若SQL 语句符合语法上的定义的话,则服务器进程 接下去会对语句中的字段、表等内容进行检查。看看这些字段、表是否在数据库中。如果表名与列名不准确的话,则数据库会就会反馈错误信息给客户端。所以,有时候我们写select 语句的时候,若语法与表名或者列名同时写错的话,则系统是先提示说语法错误,等到语法完全正确后,再提示说列名或表名错误。 4. 获得对象解析锁(control structer)。当语法、语义都正确后,系统就会对我们需要查询的对象加锁。这主要是为了保障数据的一致性,防止我们在查询的过程中,其他用户对这个对象的结构发生改变。 5. 数据访问权限的核对(data dict cache)。当语法、语义通过检查之后,客户端还不一定 能够取得数据。服务器进程还会检查,你所连接的用户是否有这个数据访问的权限。若你连接上服务器

TOAD中查看SQL的执行计划

TOAD中查看SQL的执行计划 一、TOAD中查看SQL的执行计划： 1、点击工具栏上120救护车图标按钮 2、快捷键Ctrl+E 3、菜单View-Explain plan 二、如果是默认安装TOAD，在查看执行计划时会报一个错： ORA-02404: 未找到指定的计划表 稍微研究了一下，解决这个问题基本上有3个方案： 1、最直接的解决方案：直接创建TOAD所需要的计划表，该脚本在%oracle_home%\rdbms\admin\utlxplan.sql 中，不过该脚本是创建PLAN_TABLE表，表结构一样，改名为TOAD_PLAN_TABLE 即可。如下： CREATE TABLE TOAD_PLAN_TABLE ( STATEMENT_ID VARCHAR2 (32), TIMESTAMP DATE, REMARKS VARCHAR2 (80), OPERATION VARCHAR2 (30), OPTIONS VARCHAR2 (30), OBJECT_NODE VARCHAR2 (128), OBJECT_OWNER VARCHAR2 (30), OBJECT_NAME VARCHAR2 (30), OBJECT_INSTANCE NUMBER, OBJECT_TYPE VARCHAR2 (30), SEARCH_COLUMNS NUMBER, ID NUMBER, COST NUMBER, PARENT_ID NUMBER, POSITION NUMBER, CARDINALITY NUMBER, OPTIMIZER VARCHAR2 (255), BYTES NUMBER, OTHER_TAG VARCHAR2 (255), OTHER LONG, PARTITION NUMBER, PARTITION_START VARCHAR2 (255), PARTITION_STOP VARCHAR2 (255), DISTRIBUTION VARCHAR2 (30) ) ; 2、偷机取巧的处理方案：修改TOAD参数 将菜单View -> Options ->Oracle -> General -> Explain Plan Table name中的参数修改为PLAN_TABLE即可使用。 3、通过TOAD自带功能创建表结构： 在菜单tools -> server side objects wizard下运行，不过要建立一些对象，最好建议一个单独的表空间放这些对象。 在10g中带有plan_table这张表，但是将名字改为了plan_table$ 只需$ORACLE_HOME/sqlplus/admin/plustrce.sql创建plustrace角色 grant plustrace to public

Oracle定时执行计划任务.

Oracle 定时执行计划任务 Oracle 在 10g 版本以前, 计划任务用的是 DBMS_JOB包, 10g 版本引入 DBMS_SCHEDULER来替代先前的 DBMS_JOB,在功能方面 , 它比 DBMS_JOB提供了更强大的功能和更灵活的机制管理, 但 DBMS_JOB包的使用相对比较简单, 也基本能够满足定时执行计划任务的需求, 故接下来就先看看 DBMS_JOB包的使用方法。 1. DBMS_JOB 我们可以在命令窗口输入 show parameter job_queue_processes查看数据库中定时任务的最多并发数,一般设置为 10(设置方法:alter system set job_queue_processes=10 ,如果设为 0,那么数据库定时作业是不会运行的。 oracle 定时执行 job queue 的后台进程是 SNP , 要启动 snp, 首先看系统模式是否支持 sql> alter system enable restricted session;或 sql> alter system disenable restricted session; 利用上面的命令更改系统的会话方式为 disenable restricted,为 snp 的启动创建条件 . 接下来我们尝试实现以下功能:每隔一分钟自动向 job_test表中插入当前的系统时间。 1、创测试表 create table job_test(updatetime date; 2、创建 JOB variable v_job_no number; begin dbms_job.submit(:v_job_no, 'insert into job_test values(sysdate;', sysdate, 'sysdate+1/1440';

oracle名词解释

OLAP and OLTP OLTP （在线事务处理系统） OLAP （在线分析系统） 对于一个olap系统，大型的查询每天做几次，没有必要将大量的数据缓存到内存里，完全没有必要，所以一般buffer hit都比较低 对于一个olap系统，内存优化余地不大，增加cpu速度和磁盘io速度才是最直接的提高性能的方式。 oltp系统的用户并发数很多，而且多是小的操作，数据库侧重于对用户操作的快速响应，这是对数据库最重要的性能要求。 对于一个oltp系统来说，数据库内存设计显得很重要，如果数据都可以在内存处理，性能无疑会提高很多。 oltp系统是一个数据块变化非常频繁，sql语句提交非常频繁的系统。对于数据块来说，应尽可能让数据块保存在内存中；对于sql来说，应尽可能使用绑定变量来达到sql的重用，减少物理io和反复的sql解析。 oltp 热快问题：当一个块被多个用户同时读取的时候，oracle为了维护数据的一致性，需要使用一种称为latch的东西来串行化用户的操作。当一个用户获得了这个latch之后，其他的用户只能被迫等待，获取这个数据块的用户越多，等待就越明显，这就造成了热快问题。这种热快可能是数据块也可能是回滚段块。对于数据块来讲通常是数据块上的数据分布不均导致，如果是索引的数据块，可以考虑建反向索引来达到重新分布数据的目的；对于回滚段数据块，可以适当增加几个回滚段来避免争用。 SGA 系统全局区，是oracle用来为实例存储数据和控制信息的共享内存区。在实例启动时分配，关闭时释放。 数据库缓冲区高速缓存(buffer cache)：保存了最近使用过的数据块。最近最多使用算法(most-recently-used) 共享池： 存储共享内存结构的区域。如library cache中的sql区，以及数据字典的内部信息 library cache：SQL 和PL SQL 的文本，执行计划，编译数 data dictionary cache：oracle数据字典包含一组表和视图，oracle将他们作为数据库的引用，在其中存储了与数据库的逻辑和物理结构相关的信息 用户信息；如权限等 为数据库表定义的完整性约束

执行计划详解

简介： 本文全面详细介绍oracle执行计划的相关的概念，访问数据的存取方法，表之间的连接等内容。 并有总结和概述，便于理解与记忆! +++ 目录 --- 一．相关的概念 Rowid的概念 Recursive Sql概念 Predicate(谓词) DRiving Table(驱动表) Probed Table(被探查表) 组合索引(concatenated index) 可选择性(selectivity) 二．oracle访问数据的存取方法 1）全表扫描（Full Table Scans， FTS） 2）通过ROWID的表存取（Table Access by ROWID或rowid lookup） 3）索引扫描（Index Scan或index lookup）有4种类型的索引扫描：（1）索引唯一扫描（index unique scan） （2）索引范围扫描（index range scan） 在非唯一索引上都使用索引范围扫描。使用index rang scan的3种情况： （a）在唯一索引列上使用了range操作符（> < <> >= <= between） （b）在组合索引上，只使用部分列进行查询，导致查询出多行 （c）对非唯一索引列上进行的任何查询。 （3）索引全扫描（index full scan） （4）索引快速扫描（index fast full scan） 三、表之间的连接 1，排序 - - 合并连接（Sort Merge Join， SMJ） 2，嵌套循环（Nested Loops， NL） 3，哈希连接（Hash Join， HJ） 另外，笛卡儿乘积（Cartesian Product） 总结Oracle连接方法 Oracle执行计划总结概述 +++ 一．相关的概念

Oracle定时执行计划任务

Oracle定时执行计划任务 Oracle在10g版本以前，计划任务用的是DBMS_JOB包，10g版本引入DBMS_SCHEDULER 来替代先前的DBMS_JOB,在功能方面,它比DBMS_JOB提供了更强大的功能和更灵活的机制管理，但DBMS_JOB包的使用相对比较简单，也基本能够满足定时执行计划任务的需求，故接下来就先看看DBMS_JOB包的使用方法。 1.DBMS_JOB 我们可以在命令窗口输入show parameter job_queue_processes查看数据库中定时任务的最多并发数，一般设置为10（设置方法：alter system set job_queue_processes=10），如果设为0，那么数据库定时作业是不会运行的。 oracle定时执行job queue 的后台进程是SNP,要启动snp,首先看系统模式是否支持sql> alter system enable restricted session;或sql> alter system disenable restricted session; 利用上面的命令更改系统的会话方式为disenable restricted,为snp的启动创建条件. 接下来我们尝试实现以下功能：每隔一分钟自动向job_test表中插入当前的系统时间。 1、创测试表 create table job_test(updatetime date); 2、创建JOB variable v_job_no number; begin dbms_job.submit(:v_job_no, 'insert into job_test values(sysdate);', sysdate, 'sysdate+1/1440'); end; / 其中最后一个参数'sysdate+1/1440'表示时间间隔为每分钟。其它常用的时间间隔的设置如下： （1）如果想每天凌晨1点执行，则此参数可设置为'trunc(sysdate)+25/24'； （2）如果想每周一凌晨1点执行，则此参数可设置为 'trunc(next_day(sysdate,1))+25/24'； （3）如果想每月1号凌晨1点执行，则此参数可设置为 'trunc(last_day(sysdate))+25/24'； （4）如果想每季度执行一次，则此参数可设置为 'trunc(add_months(sysdate,3),'Q')+1/24'；

半小时看懂Oracle的执行计划

一、什么是执行计划 An explain plan is a representation of the access path that is taken when a query is executed within Oracle. 二、如何访问数据 At the physical level Oracle reads blocks of data. The smallest amount of data read is a single Oracle block, the largest is constrained by operating system limits (and multiblock i/o). Logically Oracle finds the data to read by using the following methods: Full Table Scan (FTS) --全表扫描 Index Lookup (unique & non-unique) --索引扫描（唯一和非唯一） Rowid --物理行id 三、执行计划层次关系 When looking at a plan, the rightmost (ie most inndented) uppermost operation is the first thing that is executed. --采用最右最上最先执行的原则看层次关系，在同一级如果某个动作没有子ID就最先执行 1、看一个简单的例子： Query Plan ----------------------------------------- SELECT STATEMENT [CHOOSE] Cost=1234 **TABLE ACCESS FULL LARGE [:Q65001] [ANALYZED]--[:Q65001]表示是并行方式，[ANALYZED]表示 该对象已经分析过了 优化模式是CHOOSE的情况下，看Cost参数是否有值来决定采用CBO还是RBO： SELECT STATEMENT [CHOOSE] Cost=1234 --Cost有值，采用CBO SELECT STATEMENT [CHOOSE] Cost= --Cost为空，采用RBO

Oracle 执行计划中的几种关联说明

Oracle 执行计划中的几种关联说明 NESTED LOOP 对于被连接的数据子集较小的情况，nested loop连接是个较好的选择。nested loop就是扫描一个表，每读到一条记录，就根据索引去另一个表里面查找，没有索引一般就不会是nested loops。 一般在nested loop中，驱动表满足条件结果集不大，被驱动表的连接字段要有索引，这样就走nstedloop。如果驱动表返回记录太多，就不适合nested loops了。如果连接字段没有索引，则适合走hash join，因为不需要索引。 可用ordered提示来改变CBO默认的驱动表，可用USE_NL(table_name1 table_name2)提示来强制使用nested loop。 oradered 表示根据from 后面表的顺序，从左到右join，左表做驱动表，3个或3个以上最有用 oracle 并没有指出use_nl(a b) 中哪个是驱动表，所以有时我们习惯使用ordered 或者full() 或者index() 来强化我们的目标 " HASH JOIN hash join是CBO 做大数据集连接时的常用方式。优化器扫描小表（或数据源），利用连接键（也就是根据连接字段计算hash 值）在内存中建立hash表，然后扫描大表，每读到一条记录就来探测hash表一次，找出与hash表匹配的行。 当小表可以全部放入内存中，其成本接近全表扫描两个表的成本之和。如果表很大不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O 的性能。临时段中的分区都需要换进内存做hash join。这时候成本接近于全表扫描小表+分区数*全表扫描大表的代价和。至于两个表都进行分区，其好处是可以使用parallel query，就是多个进程同时对不同的分区进行join，然后再合并。但是复杂。 使用hash join时，HASH_AREA_SIZE初始化参数必须足够的大，如果是9i，Oracle建议使用SQL工作区自动管理，设置WORKAREA_SIZE_POLICY 为AUTO，然后调整PGA_AGGREGATE_TARGET即可。 以下条件下hash join可能有优势： 两个巨大的表之间的连接。

ORACLE中的执行计划

Oracle 执行计划 1，什么是执行计划 所谓执行计划，顾名思义，就是对一个查询任务，做出一份怎样去完成任务的详细方案。举个生活中的例子，我从珠海要去英国，我可以 选择先去香港然后转机，也可以先去北京转机，或者去广州也可以。但是到底怎样去英国划算，也就是我的费用最少，这是一件值得考究 的事情。同样对于查询而言，我们提交的SQL仅仅是描述出了我们的目的地是英国，但至于怎么去，通常我们的SQL中是没有给出提示信息 的，是由数据库来决定的。 我们先简单的看一个执行计划的对比： SQL> set autotrace traceonly 执行计划一： SQL> select count(*) from t; COUNT(*) ---------- 24815 Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT (AGGREGATE) 2 1 TABLE Access (FULL) OF 'T' 执行计划二： SQL> select count(*) from t; COUNT(*) 24815 Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=1) 1 0 SORT (AGGREGATE) 2 1 INDEX (FULL SCAN) OF 'T_INDEX' (NON-UNIQUE) (Cost=26 Card=28180) 这两个执行计划中，第一个表示求和是通过进行全表扫描来做的，把整个表中数据读入内存来逐条累加;第二个表示根据表中索引，把 整个索引读进内存来逐条累加，而不用去读表中的数据。但是这两种方式到底哪种快呢?通常来说可能二比一快，但也不是绝对的。这是一 个很简单的例子演示执行计划的差异。对于复杂的SQL(表连接、嵌套子查询等)，执行计划可能几十种甚至上百种，但是到底那种最好呢?

Oracle执行计划 SQL语句执行效率问题查找与解决方法

Oracle的SQL语句执行效率问题查找与解决方法 一、识别占用资源较多的语句的方法（4种方法） 1.测试组和最终用户反馈的与反应缓慢有关的问题。 2.利用V_$SQLAREA视图提供了执行的细节。（执行、读取磁盘和读取缓冲区的次数） ?数据列 EXECUTIONS：执行次数 DISK_READS：读盘次数 COMMAND_TYPE：命令类型（3:select,2:insert;6:update;7delete;47:pl/sql程序 单元） OPTIMIZER_MODE：优化方式 SQL_TEXT：Sql语句 SHARABLE_MEM：占用shared pool的内存多少 BUFFER_GETS：读取缓冲区的次数 ?用途 1、帮忙找出性能较差的SQL语句 2、帮忙找出最高频率的SQL 3、帮忙分析是否需要索引或改善联接 3.监控当前Oracle的session，如出现时钟的标志，表示此进程中的sql运行时间较长。 4.Trace工具： a)查看数据库服务的初始参数：timed_statistics、user_dump_dest和 max_dump_file_size b)Step 1: alter session set sql_trace=true c)Step 2: run sql； d)Step 3: alter session set sql_trace=false e)Step 4:使用“TKPROF”转换跟踪文件 f)Parse，解析数量大通常表明需要增加数据库服务器的共享池大小， query或current提取数量大表明如果没有索引，语句可能会运行得更有效， disk提取数量表明索引有可能改进性能， library cache中多于一次的错过表明需要一个更大的共享池大小 二、如何管理语句处理和选项 ?基于成本（Cost Based）和基于规则（Rule Based）两种优化器，简称为CBO 和RBO ?Optimizer Mode参数值: Choose：如果存在访问过的任何表的统计数据，则使用基于成本的Optimizer,目标是获得最优的通过量。如果一些表没有统计数据，则使用估计值。如果没有可用的统计数据，则将使用基于规则的Optimizer All_rows：总是使用基于成本的Optimizer，目标是获得最优的通过量 First_rows_n：总是使用基于成本的Optimizer，目标是对返回前N行（“n”可以是1，10，100或者1000）获得最优的响应时间 First_rows：用于向后兼容。使用成本与试探性方法的结合，以便快速传递前几行 RULE：总是使用基于规则的Optimizer 三、使用数据库特性来获得有助于查看性能的处理统计信息（解释计划和AUTOTRACE） No1: Explain Plan A）使用Explain工具需要创建Explain_plan表，这必须先进入相关应用表、视图和索引

Oracle事务的完整流程的分析(精)

Oracle 事务的完整流程的分析 oracle 客户端与服务端的连接 要想登录数据库并在数据库中真正做事情, 一定要先连接数据库, 根据连接的原理不同, 分为专用服务器连接(dedicated server 和共享服务器连接(shared server 。TCP/IP是网络上连接 Oracle 所用的主要网络协议 什么是专用服务器? 在登录 oracle 时, Oracle 总会为我创建一个新的进程。这通常称为专用服务器配置 , 因为这个服务器进程会在我的会话生存期中专门为我服务。对于每个会话,都会出现一个新的专用服务器,会话与专用服务器之间存在一对一的映射。按照定义,这个专用服务器不是实例的一部分。我的客户进程 (也就是想要连接数据库的程序会通过某种网络通道 (如 TCP/IP socket 与这个专用服务器直接通信,并由这个服务器进程接收和执行我的 SQL 。如果必要, 它会读取数据文件,并在数据库的缓存中查找我要的数据。也许它会完成我的更新语句,也可能会运行我的 PL/SQL代码。这个服务器进程的主要目标就是对我提交的 SQL 调用做出响应。 什么是共享服务器?

Oracle 还可以接受另一种方式的连接,这称为共享服务器(shared server ,正式的说法是多线程服务器(Multi-ThreadedServer 或 MTS 。如果采用这种方式,就不会对每条用户连接创建另外的线程或新的 UNIX 进程。在共享服务器中, Oracle 使用一个“共享进程”池为大量用户提供服务。共享服务器实际上就是一种连接池机制 (例如程序的连接池。利用共享服务器,我们不必为 10000个数据库会话创建 10000个专用服务器(这样进程或线程就太多了,难于管理 ,而只需建立很少的一部分进程 /线程,顾名思义,这些进程 /线程将由所有会话共享。这样 Oracle 就能让更多的用户与数据库建立连接,否则很难连接更多用户。如果让我的机器管理 10000个进程,这个负载肯定会把它压垮,但是管理 100个或者 1 000个进程还是可以的。采用共享服务器模式, 共享进程通常与数据库一同启动, 使用 ps 命令可以看到这个进程。 共享服务器连接和专用服务器连接之间有一个重大区别, 与数据库连接的客户进程不会与共享服务器直接通信,但专用服务器则不然,客户进程会与专用服务器直接通信。之所以不能与共享服务器直接对话,原因就在于这个服务器进程是共享的。为了共享这些进程,还需要另外一种机制,通过这种机制才能与共享服务器进程“对话” 。为此, Oracle 使用了一个或一组称为调度器(dispatcher ,也称分派器的进程。客户进程通过网络与一个调度器进程通信。这个调度器进程将客户的请求放入 SGA 中 UGA 中的请求队列(这也是 SGA 的用途之一。第一个空闲的共享服务器会得到这个请求, 并进行处理 (例如, 请求可能是 UPDATE T SET X =X+5 WHERE Y = 2 。完成这个命令后,共享服务器会把响应放在原调度器(即接收请求的调度器的响应队列中。调度器进程一直在监听这个队列,发现有结果后,就会把结果传给客户。

ORACLE执行计划

1，什么是执行计划 所谓执行计划，顾名思义，就是对一个查询任务，做出一份怎样去完成任务的详细方案。举个生活中的例子，我从珠海要去英国，我可以 选择先去香港然后转机，也可以先去北京转机，或者去广州也可以。但是到底怎样去英国划算，也就是我的费用最少，这是一件值得考究 的事情。同样对于查询而言，我们提交的SQL仅仅是描述出了我们的目的地是英国，但至于怎么去，通常我们的SQL中是没有给出提示信息 的，是由数据库来决定的。 我们先简单的看一个执行计划的对比： SQL> set autotrace traceonly 执行计划一： SQL> select count(*) from t; COUNT(*) ---------- 24815 Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE Access (FULL) OF 'T' 执行计划二： SQL> select count(*) from t; COUNT(*) 24815 Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=1) 1 0 SORT (AGGREGATE) 2 1 INDEX (FULL SCAN) OF 'T_INDEX' (NON-UNIQUE) (Cost=26 C ard=28180) 这两个执行计划中，第一个表示求和是通过进行全表扫描来做的，把整个表中数据读入内存来逐条累加;第二个表示根据表中索引，把 整个索引读进内存来逐条累加，而不用去读表中的数据。但是这两种方式到底哪种快呢?通常来说可能二比一快，但也不是绝对的。这是一 个很简单的例子演示执行计划的差异。对于复杂的SQL(表连接、嵌套子查询等)，执行计划可能几十种甚至上百种，但是到底那种最好呢?

Oracle查看执行计划的几种方法

Oracle查看执行计划的几种方法 原创作者：lhrbest时间：2017-04-09 14:42:0123930 Oracle查看执行计划的几种方法 一般来说，有如下几种获取执行计划的方式： 1、AUTOTRACE方式 AUTOTRACE是Oracle自带的客户端工具SQL*Plus的一个特性。启用AUTOTRACE后，SQL*Plus会自动收集执行过的SQL语句的执行计划、性能统计数据等，并在语句执行结束后显示在SQL*Plus中。 DBA用户可以直接使用AUTOTRACE功能，但是如果用户没有DBA权限，那么需要在SYS用户下执行plustrce.sql脚本，自动创建PLUSTRACE角色，再把PLUSTRACE权限赋给普通用户即可。 $ORACLE_HOME/sqlplus/admin/plustrce.sql GRANT PLUSTRACE TO USER_LHR; 另外，若启用AUTOTRACE报“SP2-0611”的错误，则可以执行utlxplan.sql脚本来创建表PLAN_TABLE，如下所示： SQL> set autot on SP2-0613: 无法验证 PLAN_TABLE 格式或实体 SP2-0611: 启用EXPLAIN报告时出错 SQL> @?/rdbms/admin/utlxplan.sql

@?/rdbms/admin/utlxplan.sql CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE; GRANT ALL ON PLAN_TABLE TO PUBLIC; @?/sqlplus/admin/plustrce.sql GRANT PLUSTRACE TO PUBLIC; AUTOTRACE的语法如下所示： SET AUTOTRACE {OFF|ON|TRACEONLY} [EXPLAIN] [STATISTICS] 其中，AUTOTRACE可简写为AUTOT，TRACEONLY可简写为TRACE，EXPLAIN可简写为EXP，STATISTICS可简写为STAT。SQL> SET AUTOT ON SQL> SELECT COUNT(*) FROM PLAN_TABLE; COUNT(*) ---------- 68 Execution Plan ---------------------------------------------------------- Plan hash value: 1751138260 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |