怎样看懂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

2、层次的父子关系，看比较复杂的例子：

PARENT1

**FIRST CHILD

****FIRST GRANDCHILD

**SECOND CHILD

Here the same principles apply, the FIRST GRANDCHILD is the initial operation then the FIRST CHILD followed by the SECOND CHILD and finally the PARENT collates the output.

四、例子解说

Execution Plan

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

0 **SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=8 Bytes=248)

1 0 **HASH JOIN (Cost=3 Card=8 Bytes=248)

2 1 ****TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=

3 Bytes=36)

3 1 ****TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=304)

左侧的两排数据，前面的是序列号ID，后面的是对应的PID（父ID）。

A shortened summary of this is:

Execution starts with ID=0: SELECT STATEMENT；but this is dependand on it's child objects；

So it executes its first child step: ID=1 PID=0 HASH JOIN；but this is dependand on it's child objects；

So it executes its first child step: ID=2 PID=1 TABLE ACCESS (FULL) OF 'DEPT'；Then the second child step: ID=3 PID=2 TABLE ACCESS (FULL) OF 'EMP'；

Rows are returned to the parent step(s) until finished

五、表访问方式

1、Full Table Scan (FTS)全表扫描

In a FTS operation, the whole table is read up to the high water mark (HWM). The HWM marks the last block in the table that has ever had data written to it. If you have deleted all the rows then you will still read up to the HWM. Truncate resets the HWM back to the start of the table. FTS uses multiblock i/o to read the blocks from disk. --全表扫描模式下会读数据到表的高水位线（HWM即表示表曾经扩展的最后一个数据块），读取速度依赖于Oracle初始化参数

db_block_multiblock_read_count

Query Plan

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

SELECT STATEMENT [CHOOSE] Cost=1

**INDEX UNIQUE SCAN EMP_I1 --如果索引里就找到了所要的数据，就不会再去访问表了2、Index Lookup索引扫描

There are 5 methods of index lookup:

index unique scan --索引唯一扫描

Method for looking up a single key value via a unique index. always returns a single value, You must supply AT LEAST the leading column of the index to access data via the index.

eg:

SQL>explain plan for select empno,ename from emp where empno=10;

index range scan --索引局部扫描

Index range scan is a method for accessing a range values of a particular column.

AT LEAST the leading column of the index must be supplied to access data via the index. Can be used for range operations (e.g. > < <> >= <= between) .

eg:

SQL> explain plan for select mgr from emp where mgr = 5;

index full scan --索引全局扫描

Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort. For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order.

eg:

SQL> explain plan for select empno,ename from big_emp order by empno,ename; index fast full scan --索引快速全局扫描，不带order by情况下常发生Scans all the block in the index, Rows are not returned in sorted order, Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO, may be hinted using INDEX_FFS hint, uses multiblock i/o, can be executed in parallel, can be used to access second column of concatenated indexes. This is because we are selecting all of the index.

eg:

SQL> explain plan for select empno,ename from big_emp;

index skip scan --索引跳跃扫描，where条件列是非索引的前导列情况下常发生Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column(s) during the search.

eg:

SQL> create index i_emp on emp(empno, ename);

SQL> select /*+ index_ss(emp i_emp)*/ job from emp where ename='SMITH';

3.Rowid物理ID扫描

This is the quickest access method available.Oracle retrieves the specified block and extracts the rows it is interested in. --Rowid扫描是最快的访问数据方式

六、表连接方式

有三种连接方式：

1、Sort Merge Join (SMJ) --由于sort是非常耗资源的，所以这种连接方式要避免；Rows are produced by Row Source 1 and are then sorted Rows from Row Source 2 are then produced and sorted by the same sort key as Row Source 1. Row Source 1 and 2 are NOT accessed concurrently.

SQL> explain plan for

select /*+ ordered */ e.deptno,d.deptno

from emp e,dept d

where e.deptno = d.deptno

order by e.deptno,d.deptno;

Query Plan

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

SELECT STATEMENT [CHOOSE] Cost=17

**MERGE JOIN

****SORT JOIN

******TABLE ACCESS FULL EMP [ANALYZED]

****SORT JOIN

******TABLE ACCESS FULL DEPT [ANALYZED]

Sorting is an expensive operation, especially with large tables. Because of this, SMJ is often not a particularly efficient join method.

2.Nested Loops (NL) --比较高效的一种连接方式

Fetches the first batch of rows from row source 1, Then we probe row source 2 once for each row returned from row source 1.

For nested loops to be efficient it is important that the first row source returns as few rows as possible as this directly controls the number of probes of the second row source. Also it helps if the access method for row source 2 is efficient as this operation is being repeated once for every row returned by row source 1.

SQL> explain plan for

select a.dname,b.sql

from dept a,emp b

where a.deptno = b.deptno;

Query Plan

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

SELECT STATEMENT [CHOOSE] Cost=5

**NESTED LOOPS

****TABLE ACCESS FULL DEPT [ANALYZED]

****TABLE ACCESS FULL EMP [ANALYZED]

3.Hash Join --最为高效的一种连接方式

New join type introduced in 7.3, More efficient in theory than NL & SMJ,Only accessible via the CBO. Smallest row source is chosen and used to build a hash table and a bitmap The second row source is hashed and checked against the hash table looking for joins. The bitmap is used as a quick lookup to check if rows are in the hash table and are especially useful when the hash table is too large to fit in memory.

SQL> explain plan for

select /*+ use_hash(emp) */ empno

from emp,dept

where emp.deptno = dept.deptno;

Query Plan

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

SELECT STATEMENT [CHOOSE] Cost=3

**HASH JOIN

****TABLE ACCESS FULL DEPT

****TABLE ACCESS FULL EMP

Hash joins are enabled by the parameter HASH_JOIN_ENABLED=TRUE in the init.ora or session. TRUE is the default in 7.3.

4、Cartesian Product --卡迪尔积，不算真正的连接方式，sql肯定写的有问题

A Cartesian Product is done where they are no join conditions between 2 row sources and there is no alternative method of accessing the data. Not really a join as such as there is no join! Typically this is caused by a coding mistake where a join has been left out. It can be useful in some circumstances - Star joins uses cartesian products.Notice that there is no join between the 2 tables:

SQL> explain plan for

select emp.deptno,dept,deptno

from emp,dept

Query Plan

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

SLECT STATEMENT [CHOOSE] Cost=5

**MERGE JOIN CARTESIAN

****TABLE ACCESS FULL DEPT

****SORT JOIN

******TABLE ACCESS FULL EMP

The CARTESIAN keyword indicate that we are doing a cartesian product.

七、运算符

1、sort --排序，很消耗资源

There are a number of different operations that promote sorts:

order by clauses

group by

sort merge join

2、filter --过滤，如not in、min函数等容易产生

Has a number of different meanings, used to indicate partition elimination, may also indicate an actual filter step where one row source is filtering, another, functions such as min may introduce filter steps into query plans.

3、view --视图，大都由内联视图产生

When a view cannot be merged into the main query you will often see a projection view operation. This indicates that the 'view' will be selected from directly as opposed to being broken down into joins on the base tables. A number of constructs make a view non mergeable. Inline views are also non mergeable.

eg:

SQL> explain plan for

select ename,tot

from emp,(select empno,sum(empno) tot from big_emp group by empno) tmp

where emp.empno = tmp.empno;

Query Plan

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

SELECT STATEMENT [CHOOSE]

**HASH JOIN

**TABLE ACCESS FULL EMP [ANALYZED]

**VIEW

****SORT GROUP BY

******INDEX FULL SCAN BE_IX

4、partition view --分区视图

Partition views are a legacy technology that were superceded by the partitioning option. This section of the article is provided as reference for such legacy systems.

===================================★====================================

日常开发活动中，有时候需要对oracle执行计划进行监控，以此来调优程序和数据库方面的性能。

常用方法有以下几种：

一、通过PL/SQL Dev工具

1、直接File->New->Explain Plan Window，在窗口中执行sql可以查看计划结果。其中，Cost表示cpu的消耗，单位为n%，Cardinality表示执行的行数，等价Rows。

2、先执行 EXPLAIN PLAN FOR select * from tableA where paraA=1，再 select * from table(DBMS_XPLAN.DISPLAY)便可以看到oracle的执行计划了，看到的结果和1中的一样，所以使用工具的时候推荐使用1方法。

注意：PL/SQL Dev工具的Command window中不支持set autotrance on的命令。还有使用工具方法查看计划看到的信息不全，有些时候我们需要sqlplus的支持。

二、通过sqlplus

1、一般情况都是本机链接远程服务器，所以命令如下：

sqlplus user/pwd@serviceName

此处的serviceName为tnsnames.ora中定义的命名空间。

2、执行set autotrace on，然后执行sql语句，会列出以下信息：

。。。（省略一些信息）

统计信息

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

1 recursive calls （归调用次数）

0 db block gets

2 consistent gets

0 physical reads （物理读——执行SQL的过程中，从硬盘上读取的数据块个数）

0 redo size (重做数——执行SQL的过程中，产生的重做日志的大小)

358 bytes sent via SQL*Net to client

366 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory) 在内存中发生的排序

0 sorts (disk) 在硬盘中发生的排序

1 rows processed

省略部分信息和通过PL/SQL Dev工具查看执行计划的信息一样，下面的统计信息是更详细的。

判断SQL效率高低不关通过时间来衡量，还应该通过执行SQL执行状态里面的逻

辑读的数量

逻辑读=（db block gets+ consistent gets）

1、SET AUTOTRACE ON EXPLAIN

（set autot on exp)

SQLPLUS的命令，在执行SQL语句的同时显示执行计划，设置EXP(LAIN)的目的是只显示执行计划而不显示统计信息.。

2、SQL>explain plan for select ````````;

SQL>select * from table(dbms_xplan.display);

执行了set autotrace on explain语句之后，接下来的查询、插入、更新、删除语句就会显示执行计划，直到执行“set autotrace off;”语句。如果是设置了set autotrace on，除了会显示执行计划之外，还会显示一些有用的统计信息。

执行EXPLAIN PLAN FOR 可以只显示执行计划,然后执行如下查询

SQL> select * from table(dbms_xplan.display);

如：

SQL> explain plan for select * from emp where deptno='20';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 3956160932

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 5 | 150 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| EMP | 5 | 150 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

1 - filter("DEPTNO"=20)

13 rows selected.

3、SQL>exec dbms_stats.delete_table_stats(USER,'表');(删除表的统计信息）

SQL>exec dbms_stats.gather_table_stats(USER,'表',METHOD_OPT=>'FOR ALL COLUMNS SIZE 100')（收集表的统计信息）

4、AUTOTRACE的几个常用选项

set autotrace off ---------------- 不生成autotrace 报告，这是缺省模式

set autotrace on explain ------ autotrace只显示优化器执行路径报告

set autotrace on statistics -- 只显示执行统计信息

set autotrace on ----------------- 包含执行计划和统计信息

set autotrace traceonly ------ 同set autotrace on，但是不显示查询输

(1). set autotrace on explain; --只显示执行计划

SQL> set autotrace on explain;

SQL>

select count(*) from dba_objects;

COUNT(*)

----------

31820

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT (AGGREGATE)

2 1 VIEW OF 'DBA_OBJECTS'

4 3 FILTER

5 4 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'

6 5 NESTED LOOPS

7 6 TABLE ACCESS (FULL) OF 'USER$'

8 6 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)

9 4 TABLE ACCESS (BY INDEX ROWID) OF 'IND$'

10 9 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)

11 3 NESTED LOOPS

12 11 TABLE ACCESS (FULL) OF 'USER$'

13 11 INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)

(2). set autotrace on statistics;--只显示统计信息

SQL> set autotrace on statistics;

SQL> select count(*) from dba_objects;

COUNT(*)

----------

31820

Statistics

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

0 recursive calls

0 db block gets

25754 consistent gets

0 physical reads

0 redo size

383 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

(3). set autotrace traceonly;--同set autotrace on 只是不显示查询输出SQL> set autotrace traceonly;

SQL> select count(*) from dba_objects;

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT (AGGREGATE)

2 1 VIEW OF 'DBA_OBJECTS'

3 2 UNION-ALL

5 4 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'

6 5 NESTED LOOPS

7 6 TABLE ACCESS (FULL) OF 'USER$'

8 6 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)

9 4 TABLE ACCESS (BY INDEX ROWID) OF 'IND$'

10 9 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)

11 3 NESTED LOOPS

12 11 TABLE ACCESS (FULL) OF 'USER$'

13 11 INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)

Statistics

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

0 recursive calls

0 db block gets

25754 consistent gets

0 physical reads

0 redo size

383 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

(4).set autotrace traceonly explain;--比较实用的选项，只显示执行计划，但是与set autotrace on explain;相比不会执行语句，对于仅仅查看大表的Explain Plan非常管用。

SQL> set autotrace traceonly explain;

SQL> select * from dba_objects;

已用时间: 00: 00: 00.00

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 VIEW OF 'DBA_OBJECTS'

2 1 UNION-ALL

3 2 FILTER

4 3 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'

5 4 NESTED LOOPS

6 5 TABLE ACCESS (FULL) OF 'USER$'

7 5 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)

8 3 TABLE ACCESS (BY INDEX ROWID) OF 'IND$'

9 8 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)

10 2 TABLE ACCESS (BY INDEX ROWID) OF 'LINK$'

11 10 NESTED LOOPS

12 11 TABLE ACCESS (FULL) OF 'USER$'

13 11 INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)

5、analyze

analyze table hr.employees compute(estimate) statistics;(compute收集每一行数据的统计信息，比较耗时；estimate收集一部分数据行的统计信息）

select

t.owner,t.table_name,t.tablespace_name,t.blocks,t.empty_blocks,t.avg_ space

from dba_tables t

where t.owner='HR';

如何生成explain plan?

解答:运行utlxplan.sql. 建立plan 表

针对特定SQL语句，使用 explain plan set statement_id = 'tst1' into plan_table for sql statement

运行utlxplp.sql 或 utlxpls.sql察看explain plan

（select * from table(dbms_xplan.display());）

EXPLAIN PLAN 是一个很好的分析SQL语句的工具,它甚至可以在不执行SQL的情况下分析语句. 通过分析,我们就可以知道ORACLE是怎么样连接表,使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称.

你需要按照从里到外,从上到下的次序解读分析的结果. EXPLAIN PLAN分析的结果是用缩进的格式排列的, 最内部的操作将被最先解读, 如果两个操作处于同一层中,带有最小操作号的将被首先执行.

NESTED LOOP是少数不按照上述规则处理的操作, 正确的执行路径是检查对NESTED LOOP提供数据的操作,其中操作号最小的将被最先处理.

译者按:

通过实践, 感到还是用SQLPLUS中的SET TRACE 功能比较方便.

举例:

SQL> list

1 SELECT *

2 FROM dept, emp

3* WHERE emp.deptno = dept.deptno

SQL> set autotrace traceonly /*traceonly 可以不显示执行结果*/ SQL> /

14 rows selected.

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 NESTED LOOPS

2 1 TABLE ACCESS (FULL) OF 'EMP'

3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

Statistics

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

0 recursive calls

2 db block gets

30 consistent gets

0 physical reads

0 redo size

2598 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

通过以上分析,可以得出实际的执行步骤是:

1. TABLE ACCESS (FULL) OF 'EMP'

2. INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

3. TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

4. NESTED LOOPS (JOINING 1 AND 3)

注: 目前许多第三方的工具如TOAD和ORACLE本身提供的工具如OMS的SQL Analyze都提供了极其方便的EXPLAIN PLAN工具.也许喜欢图形化界面的朋友们可以选用它们.

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

对于sql执行的小量高低.我们可以通过执行计划的信息基本上可以进行分析查看该SQL语句执行的时间.连接顺序及浪费的数据库资源等信息,从而判断该SQL 语句执行的效率如何,下面就简单的介绍一下执行计划的使用

2. Explain使用

Oracle RDBMS执行每一条SQL语句，都必须经过Oracle优化器的评估。所以，了解优化器是如何选择(搜索)路径以及索引是如何被使用的，对优化SQL语句有很大的帮助。Explain可以用来迅速方便地查出对于给定SQL语句中的查询数据是如何得到的即搜索路径(我们通常称为Access Path)。从而使我们选择最优的查询方式达到最大的优化效果。

2.1. 安装

要使用执行计划首先需要执行相应的脚本。

使用Explain工具需要创建Explain_plan表，这必须先进入相关应用表、视图和索引的所有者的帐户内。Oracle的介质中包含有执行此项工作的SQL源程序，例如：

ORA_RDBMS: XPLAINPL.SQL (VMS)

$ORACLE_HOME/rdbms/admin/utlxplan.sql (UNIX)

该脚本后会生成一个表这个程序会创建一个名为plan_table的表，表结构如下：

我们简单的介绍一下主要的字段含义：

字段名字段类型含义

STATEMENT_ID VARCHAR2(30) explain PLAN 语句中所指定的最优STATEMENT_ID 参数值, 如果在EXPLAN PLAN语句中没有使用SET STATEMENT_ID，那么此值会被设为NULL。

REMARKS VARCHAR2(80) 与被解释规划的各步骤相关联的注释最长可达80 字节OPERATION VARCHAR2(30) 各步骤所执行内部操作的名称在某条语句所产生的第一行中该列的可能取值如下DELETE STATEMENT INSERT STATEMENT SELECT STATEMENT UPDATE STATEMENT

OPTIONS VARCHAR2(30) 对OPERATION 列中所描述操作的变种

OBJECT_NODE VARCHAR2(128) 用于访问对象的数据库链接database link 的名称对于使用并行执行的本地查询该列能够描述操作中输出的次序

OBJECT_OWNER VARCHAR2(30) 对于包含有表或索引的架构schema 给出其所有者的名称

OBJECT_NAME VARCHAR2(30) 表或索引的名称

OBJECT_INSTANCE INTEGER 根据对象出现在原始original 语句中的次序所给出的相应次序编号就原始的语句文本而论其处理顺序为自左至右自外向内景象扩张view

OBJECT_TYPE VARCHAR2(30) 用于提供对象描述性信息的修饰符例如索引的NON-UNIQUE

OPTIMIZER VARCHAR2(255) 当前优化程序的模式

ID INTEGER 分配给执行规划各步骤的编号

PARENT_ID INTEGER 对ID 步骤的输出进行操作的下一个执行步骤的ID POSITION INTEGER 对于具有相同PARENT_ID 的步骤其相应的处理次序

COST INTEGER 根据优化程序的基于开销的方法所估计出的操作开销值对于使用基于规则方法的语句该列为空该列值没有特定的测量单位它只是一个用于比较执行规划开销大小的权重值

CARDINALITY INTEGER 根据基于开销的方法对操作所访问行数的估计值

BYTES INTEGER 根据基于开销的方法对操作所访问字节的估计

2.2. 使用

2.2.1. 常规使用

常规使用语法：

explain PLAN [ SET STATEMENT_ID [=] ]

[ INTO

FOR

其中：

STATEMENT_ID是一个唯一的字符串，把当前执行计划与存储在同一PLAN表中的其它执行计划区别开来。

TABLE_NAME是plan表名，它结构如前所示，你可以任意设定这个名称。

SQL_STATEMENT是真正的SQL语句。

如：

SQL> explain plan set statement_id='test1' for

2 SELECT a.soctermbegin,

3 a.soctermend,

4 a.dealserialno,

5 a.levydataid,

6 a.dealtotal,

7 e.categoryitemcode,

8 row_number() over(PARTITION BY a.levydataid ORDER BY 1) AS theRow

9 FROM tb_soc_packdealdata a,

10 tb_Lvy_TaxDataBillMap c,

11 Tb_lvy_BillData d,

12 tb_soc_levydetaildata e

13 WHERE a.levydataid = c.datafrompointer(+)

14 AND c.billdataid = d.billdataid(+)

15 AND a.levydataid = e.levydataid

16 AND a.packdealstatuscode = '10'

17 AND (a.datastatus '9' OR a.datastatus is NULL)

18 AND (d.billstatus IS NULL OR

19 (d.billstatus '2' AND d.billstatus '8'))

20 AND a.Insurcode = '6010952'

21 ;

Explained

执行下面语句就可以查看该语句执行的执行计划:

SQL> SELECT

A.OPERATION,OPTIONS,OBJECT_NAME,OBJECT_TYPE,ID,PARENT_ID

2 FROM PLAN_TABLE a

3 WHERE STATEMENT_ID='test1'

4 ORDER BY Id;

OPERATION OPTIONS OBJECT_NAME OBJECT_TYPEID PARENT_ID

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

SELECT STATEMENT 0

WINDOW SORT 1 0

FILTER 2 1

NESTED LOOPS OUTER 3 2

NESTED LOOPS OUTER 4 3

NESTED LOOPS 5 4

TABLE ACCESS FULL TB_SOC_PACKDEALDATA 6 5

TABLE ACCESS BY INDEX ROWID TB_SOC_LEVYDETAILDATA 7 5

INDEX RANGE SCAN IND_DATAID_LEVSOC NON-UNIQUE 8 7

TABLE ACCESS BY INDEX ROWID TB_LVY_TAXDATABILLMAP 9 4

INDEX RANGE SCAN TBLVYTAXDATABIL_DATAFROMPOINTE NON-UNIQUE 10 9 TABLE ACCESS BY INDEX ROWID TB_LVY_BILLDATA 11 3

INDEX UNIQUE SCAN TBLVYBILLDATA_BILLDATAID UNIQUE

2.2.2. 自动显示使用

在SQLPLUS中自动跟踪显示执行计划及相关信息

SQL>set timing on --显示执行时间

SQL>set autorace on ?C显示执行计划

SQL>set autorace on ?C显示执行计划

SQL>set autotrace traceonly ?C只显示执行计划即不显示查询出来的数据

设置完毕后执行SQL语句就会显示执行计划信息及相应的统计信息(需要设置显示该选项)

SQL> select nvl(sum(t.taxdue), 0)

2 from tb_lvy_sbzs100 t, tb_lvy_declaredoc a, tb_lvy_declaredoc b

3 where a.dossiercode = 'SB02041108'

4 and a.pages = 123

5 and a.remarkid = b.remarkid

6 AND A.REMARKID IS NOT NULL

7 and b.declaredocid = t.declaredocid;

NVL(SUM(T.TAXDUE),0)

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

已用时间: 00: 00: 04.07

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=110)

1 0 SORT (AGGREGATE)

2 1 NESTED LOOPS (Cost=6 Card=1 Bytes=110)

3 2 MERGE JOIN (CARTESIAN) (Cost=

4 Card=1 Bytes=74)

4 3 TABLE ACCESS (FULL) OF 'TB_LVY_SBZS100' (Cost=2 Card =1 Bytes=31)

5 3 BUFFER (SORT) (Cost=2 Card=1 Bytes=43)

6 5 TABLE ACCESS (FULL) OF 'TB_LVY_DECLAREDOC' (Cost=2 Card=1 Bytes=43)

7 2 TABLE ACCESS (BY INDEX ROWID) OF 'TB_LVY_DECLAREDOC' (Cost=2 Card=1 Bytes=36)

8 7 INDEX (UNIQUE SCAN) OF 'TBLVYDECLAREDOC_DECLAREDOCID' (UNIQUE) Statistics

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

0 recursive calls --循环递归次数

0 db block gets―请求的数据块在buffer能满足的个数

6675 consistent gets --逻辑IO用于读表并计算行数, 数据请求总数在回滚段Buffer中

45 physical reads ?C从磁盘读到Buffer Cache数据块数量

0 redo size ?C产生的redo日志大小

217 bytes sent via SQL*Net to client

276 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

如果6675 consistent gets --逻辑IO用于读表并计算行数, 数据请求总数在回滚段Buffer中

45 physical reads ?C从磁盘读到Buffer Cache数据块数量

的数值比较小则该语句对对数据库的性能比较高。

2.2.

3. PL/SQL和TOAD中使用

如果在PL/SQL中使用选择要查询语句显示执行计划，则只需要SQL WINDOWS 窗口里面输入要查询的SQL语句，然后选择按键F5或者在菜单TOOLS?D?D>Explain Plan 菜单按键就可以在执行计划窗口查看该语句的执行计划。

在TOAD语句中在执行当前的SQL窗口中选择下方的Explain PlanTAB页即可以查看要执行语句的执行计划信息。

2.3. 限制

虽然任何SQL语句都可以用explain解释，但对于没有查询的INSERT，UPDA TE，DELETE 操作来说，这个工具并没有太大的用处。没有子查询的INSERT操作不会创建执行计划，但没有WHERE子句或子查询的UPDATE和DELETE操作会创建执行计划，因为这些操作必须先找出所要的记录。

另外，如果你在SQL语句中使用其它类型如sequence等，explain也能揭示它的用法。explain真正的唯一的限制是用户不能去解释其它用户的表，视图，索引或其它类型，用户必须是所有被解释事物的所有者，如果不是所有者而只有select权限，explain会返回一个错误。

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 |