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(表连接、嵌套子查询等),执行计划可能几十种甚至上百种,但是到底那种最好呢?
我们事前并不知道,数据库本身也不知道,但是数据库会根据一定的规则或者统计信息(statistics)去选择一个执行计划,通常来说选择的是比较优的,但也有选择失误的时候,这就是这次讨论的价值所在。
Oracle优化器模式
Oracle优化器有两大类,基于规则的和基于代价的,在SQLPLUS中我们可以查看init文件中定义的缺省的优化器模式。
SQL> show parameters optimizer_mode
NAME TYPE VALUE
optimizer_mode string CHOOSE
SQL>
这是Oracle8.1.7 企业版,我们可以看出,默认安装后数据库优化器模式为CHOOSE,我们还可以设置为 RULE、FIRST_ROWS,ALL_ROWS。可以在init文件中对整个instance的所有会话设置,也可以单独对某个会话设置:
SQL> ALTER SESSION SET optimizer_mode = RULE;
会话已更改。
SQL> ALTER SESSION SET optimizer_mode = FIRST_ROWS;
会话已更改。
SQL> ALTER SESSION SET optimizer_mode = ALL_ROWS;
会话已更改。
基于规则的查询,数据库根据表和索引等定义信息,按照一定的规则来产生执行计划;基于代价的查询,数据库根据搜集的表和索引的数据的统计信息(通过analyze 命令或者使用dbms_stats包来搜集)综合来决定选取一个数据库认为最优的执行计划(实际上不一定最优)。
RULE是基于规则的,CHOOSE表示如果查询的表存在搜集的统计信息则基于代价来执行(在CHOOSE模式下Oracle采用的是 FIRST_ROWS),否则基于规则来执行。在基于代价的两种方式中,FIRST_ROWS指执行计划采用最少资源尽快的返回部分结果给客户端,对于排序分页页显示这种查询尤其适用,ALL_ROWS指以总体消耗资源最少的方式返回结果给客户端。
基于规则的模式下,数据库的执行计划通常比较稳定。但在基于代价的模式下,我们才有更大的机会选择最优的执行计划。也由于Oracle的很多查询方面的特性必须在基于代价的模式下才能体现出来,所以我们通常不选择RULE(并且Oracle宣称从 Oracle 10i版本数据库开始将不再支持 RULE)。既然是基于代价的模式,也就是说执行计划的选择是根据表、索引等定义和数据的统计信息来决定的,这个统计信息是根据 analyze 命令或者dbms_stats包来定期搜集的。首先存在着一种可能,就是由于搜集信息是一个很消耗资源和时间的动作,尤其当表数据量很大的时候,因为搜集信息是对整个表数据进行重新的完全统计,所以这是我们必须慎重考虑的问题。我们只能在服务器空闲的时候定期的进行信息搜集。这说明我们在一段时期内,统计信息可能和数据库本身的数据并不吻合;另外就是Oracle的统计数据本身也存在着不精确部分(详细参考Oracle DOCUMENT),更重要的一个问题就是及时统计数据相对已经比较准确,但是Oracle的优化器的选择也并不是始终是最优的方案。这也倚赖于Oracle对不同执行计划的代价的计算规则(我们通常是无法知道具体的计算规则的)。这好比我们决定从香港还是从北京去英国,车票、机票等实际价格到底是怎么核算出来的我们并不知道,或者说我们现在了解的价格信息,在我们乘车前往的时候,真实价格跟我们的预算已经发生了变化。所有的因素,都将影响我们的整个开销。
执行计划稳定性能带给我们什么
Oracle存在着执行计划选择失误的可能。这也是我们经常遇见的一些现象,比如总有人说我的程序在测试数据库中跑的很好,但在产品数据库上就是跑的很差,甚至后者硬件条件比前者还好,这到底是为什么?硬件资源、统计信息、参数设置都可能对执行计划产生影响。
由于因素太多,我们总是对未来怀着一种莫名的恐惧,我的产品数据库上线后到底跑的好不好?于是Oracle提供了一种稳定执行计划的能力,也就是把在测试环境中的运行良好的执行计划所产生的OUTLINES移植到产品数据库,使得执行计划不会随着其他因素的变化而变化。
那么OUTLINES是什么呢?先要介绍一个内容,Oracle提供了在SQL中使用HINTS来引导优化器产生我们想要的执行计划的能力。这在多表连接、复杂查询中特别有效。HINTS的类型很多,可以设置优化器目标(RULE、CHOOSE、FIRST_ROWS、ALL_ROWS),可以指定表连接的顺序,可以指定使用哪个表的哪个索引等等,可以对SQL进行很多精细的控制。通过这种方式产生我们想要的执行计划的这些HINTS,Oracle可以存储这些HINTS,我们称之为OUTLINES。通过STORE OUTLINES可以使得我们拥有以后产生相同执行计划的能力,也就是使我们拥有了稳定执行计划的能力。
这里想给出一个附加的说明就是,实际上,我们通过工具改写SQL,比如使用SQL EXPERT改写后的SQL,这些不仅仅是加了HINTS而且文本都已经发生了变化的SQL,也可以存储OUTLINES,并可被应用到应用中。但这不是一定生效,我们必须测试检查是否生效。但由于就算给了错误的OUTLINES,数据库在执行的时候,也只是忽略过去重新生成执行计划而不会返回错误,所以我们才敢放心的这么使用。
当然在Oracle文档中并没有指明可以这样做,文档中只是说明,如果存在OUTLINES的同时又在SQL中加了HINTS,则会使用OUTLINES而忽略HINTS。这个功能在LECCO将发布的产品中会使用这一功能,这样可以将SQL EXPERT的改写SQL的能力和稳定执行计划的能力结合起来,那么我们就对不能更改源代码的应用具有了相当强大的SQL优化能力。
也许我们会有疑问,假如稳定了执行计划,那还搜集统计信息干吗?这是因为几个原因造成的,首先,现在的执行计划对于未来发生了变化的数据未必就是合适的,存在着当前的执行计划不满足未来数据的变化后的效率,而新的统计信息的情况下所产生的执行计划也并不是全部都合理的。那这个时候,我们可以采用新搜集的统计信息,但是却对新统计信息下不良的执行计划采用Oracle提供的执行计划稳定
性这个能力固定执行计划,这样结合起来我们可以建立满意的高效的数据库运行环境。
我们还需要关注的一个东西,Oracle提供的dbms_stats包除了具有搜集统计信息的能力,还具有把数据库中统计信息(statistics)export/import的能力,还具有只搜集统计信息而使得统计信息不应用于数据库的能力(把统计信息搜集到一个特定的表中而不是立即生效),在这个基础上我们就可以把统计信息export出来再import到一个测试环境中,再运行我们的应用,在测试环境中我们观察最新的统计信息会导致哪些执行计划发生变化(DB EXPERT的Plan Version Tracer是模拟不同环境并自动检查不同环境中执行计划变化的工具),是变好了还是变差了。我们可以把变差的这一部分在测试环境中使用hints或者利用工具(SQL EXPERT是在重写SQL这一领域目前最强有力的工具)产生良好的执行计划的SQL,利用这些SQL可以产生OUTLINES,然后在产品数据库应用最新的统计信息的同时移植进这些OUTLINES。
最后说一下我们不得不使用执行计划稳定性能力的场合。我们假定Oracle的优化器的选择都是准确的,但是优化器选择的基础就是我们的SQL,这些SQL才从根本上决定了运行效率,这是更重要的一个优化的环节。SQL是基础(当然数据库的设计是基础的基础),一个SQL写的好不好,就相当于我们同样是要想去英国,但是我的起点在珠海,你的起点却在西藏的最边缘偏僻的一个地方,那不管你做怎样的最优路线选择,你都不如我在珠海去英国所花费的代价小。
2.怎么生成的
1.Explain plan
explain plan for
select * from aa;
查看结果:
select * from table(dbms_xplan.display());
2.Autotrace Set timing on --记录所用时间
Set autot trace --自动记录执行计划
3.SQL_TRACE
ORACLE SQL_TRACE
“SQL TRACE”是Oracle提供的用于进行SQL跟踪的手段,是强有力的辅助诊断工具。在日常的数据库问题诊断和解决中,“SQL TRACE”是非常常用的方法。
一般,一次跟踪可以分为以下几步:
1、界定需要跟踪的目标范围,并使用适当的命令启用所需跟踪。
2、经过一段时间后,停止跟踪。此时应该产生了一个跟踪结果文件。
3、找到跟踪文件,并对其进行格式化,然后阅读或分析。
本文就“SQL TRACE”的这些使用作简单探讨,并通过具体案例对SQL_TRACE的使用进行说明。
3,怎么查看执行计划
从Oracle10g开始,可以通过EXPLAIN PLAN FOR查看DDL语句的执行计划了。
在9i及以前版本,Oracle只能看到DML的执行计划,不过从10g开始,通过EXPLAIN PLAN FOR的方式,已经可以看到DDL语句的执行计划了。
这对于研究CREATE TABLE AS SELECT、CREATE MATERIALIZED VIEW AS SELECT以及CREATE INDEX,ALTER INDEX REBUILD等语句有很大的帮助。
举个简单的例子,Oracle的文档上对于索引的建立有如下描述:
The optimizer can use an existing index to build another index. This results in a much faster index build.
如果看不到DDL的执行计划,只能根据执行时间的长短去猜测Oracle的具体执行计划,但是这种方法没有足够的说服力。但是通过DDL的执行计划,就使得结果一目了然了。
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
表已创建。
SQL> EXPLAIN PLAN FOR
2 CREATE INDEX IND_T_NAME ON T(OBJECT_NAME);
已解释。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 3035241083
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 57915 | 3732K| 75 (2)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| IND_T_NAME | | | | |
| 2 | SORT CREATE INDEX | | 57915 | 3732K| | |
| 3 | TABLE ACCESS FULL | T | 57915 | 3732K| 41 (3)| 00:00:01 |
-------------------------------------------------------------------------------------
Note
-----
- estimated index size: 5242K bytes
已选择14行。
SQL> CREATE INDEX IND_T_OWNER_NAME ON T(OWNER, OBJECT_NAME);
索引已创建。
SQL> EXPLAIN PLAN FOR
2 CREATE INDEX IND_T_NAME ON T(OBJECT_NAME);
已解释。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 517242163
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 57915 | 3732K| 75 (2)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| IND_T_NAME | | | | |
| 2 | SORT CREATE INDEX | | 57915 | 3732K| | |
| 3 | INDEX FAST FULL SCAN| IND_T_OWNER_NAME | | | | |
-------------------------------------------------------------------------------------------
Note
-----
- estimated index size: 5242K bytes
已选择14行。
SQL> SET AUTOT ON
SQL> CREATE INDEX IND_T_NAME ON T(OBJECT_NAME);
索引已创建。
注意,查看DDL的执行计划需要使用EXPLAIN PLAN FOR,AUTOTRACE对于DDL是无效的。
4,如何读懂执行计划:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'USER_INFO'
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'USER_NUM_TABLE'
5 3 INDEX (RANGE SCAN) OF 'PK_USER_INFO' (UNIQUE)
请问以上执行计划语句是如何看的?语句的执行顺序是什么?
让我们来解释一下怎么看吧,左边的两列数字,第一列表示这条计划的编号,第二列是这条计划的父计划的编号;如果一条计划有子计划,
那么先要执行其子计划;在这个例子中:从第一条编号为0的(SELECT STATEMENT ptimizer=CHOOSE)开始,他有个子计划1(SORT
(AGGREGATE)),然后1有个子计划2,2有子计划3, 3 有子计划4和5,4是3的第一个子计划,所以先执行4(TABLE ACCESS (FULL)
OF 'USER_NUM_TABLE'),再执行5(INDEX (RANGE SCAN) OF 'PK_USER_INFO' (UNIQUE)),4和5执行完返回到其父计划3(NESTED
LOOPS),3把4和5取到的rows进行nested loops,结果再返回到2,再到1排序,再到0select.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
如何看懂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.一个简单的例子:
SQL> select /*+parallel (e 4)*/ * from emp e;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=82 Bytes=7134)
1 0 TABLE ACCESS* (FULL) OF 'EMP' (Cost=1 Card=82 Bytes=7134):Q5000
--[:Q5000]表示是并行方式
1 PARALLEL_TO_SERIAL SELECT /*+ NO_EXPAND ROWID(A1) */ A1."EMPNO"
,A1."ENAME",A1."JOB",A1."MGR",A1."HI
优化模式是CHOOSE的情况下,看Cost参数是否有值来决定采用CBO还是RBO:
SELECT STATEMENT [CHOOSE] Cost=1234--Cost有值,采用CBO
SELECT STATEMENT [CHOOSE] --Cost为空,采用RBO(9I是如此显示的)
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 Optimizer=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(我觉得应该这样翻译:FTS扫描会使表使用上升到高水位(HWM),HWM标识了表最后写入数据的块,如果你用DELETE删除了所有的数据表仍然处于高水位(HWM),只有用TRUNCATE才能使表回归,FTS使用多IO从磁盘读取数据块).
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 --排序,很消耗资源
There are a number of different operations that promote sorts:
(1)order by clauses (2)group by (3)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,totfrom 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.
示例:假定A、B、C都是不是小表,且在A表上一个组合索引:A(a.col1,a.col2) ,注意a.col1列为索引的引导列。考虑下面的查询:
select A.col4 from A , B , C
where B.col3 = 10 and A.col1 = B.col1 and A.col2 = C.col2 and C.col3 = 5;
Execution Plan
------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'
Statistics(统计信息参数,参见另外个转载的文章)
--------------------------------------
0 recursive calls(归调用次数)
8 db block gets(从磁盘上读取的块数,即通过update/delete/select for update读的次数)
6 consistent gets(从内存里读取的块数,即通过不带for update的select 读的次数)
0 physical reads(物理读—从磁盘读到数据块数量,一般来说是'consistent gets' + 'db block gets')
0 redo size (重做数——执行SQL的过程中,产生的重做日志的大小)
551 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory) (在内存中发生的排序)
0 sorts (disk) (在硬盘中发生的排序)
6 rows processed
在表做连接时,只能2个表先做连接,然后将连接后的结果作为一个row source,与剩下的表做连接,在上面的例子中,连接顺序为B与A先连接,然后再与C连接:
B <---> A <---> C
col3=10 col3=5
如果没有执行计划,分析一下,上面的3个表应该拿哪一个作为第一个驱动表?从SQL语句看来,只有B表与C表上有限制条件,所以第一个驱动表应该为这2个表中的一个,到底是哪一个呢?
B表有谓词B.col3 = 10,这样在对B表做全表扫描的时候就将where子句中的限制条件(B.col3 = 10)用上,从而得到一个较小的row source, 所以B表应该作为第一个驱动表。而且这样的话,如果再与A表做关联,可以有效利用A表的索引(因为A表的col1列为leading column)。
上面的查询中C表上也有谓词(C.col3 = 5),有人可能认为C表作为第一个驱动表也能获得较好的性能。让我们再来分析一下:如果C表作为第一个驱动表,则能保证驱动表生成很小的row source,但是看看连接条件A.col2 = C.col2,此时就没有机会利用A表的索引,因为A表的col2列不为leading column,这样nested loop的效率很差,从而导致查询的效率很差。所以对于NL连接选择正确的驱动表很重要。
因此上面查询比较好的连接顺序为(B - - > A) - - > C。如果数据库是基于代价的优化器,它会利用计算出的代价来决定合适的驱动表与合适的连接顺序。一般来说,CBO都会选择正确的连接顺序,如果CBO选择了比较差的连接顺序,我们还可以使用ORACLE提供的hints来让CBO采用正确的连接顺序。如下所示
select /*+ ordered */ A.col4
from B,A,C
where B.col3 = 10 and A.col1 = B.col1 and A.col2 = C.col2 and C.col3 = 5
既然选择正确的驱动表这么重要,那么让我们来看一下执行计划,到底各个表之间是如何关联的,从而得到执行计划中哪个表应该为驱动表:
在执行计划中,需要知道哪个操作是先执行的,哪个操作是后执行的,这对于判断哪个表为驱动表有用处。判断之前,如果对表的访问是通过rowid,且该rowid的值是从索引扫描中得来得,则将该索引扫描先从执行计划中暂时去掉。然后在执行计划剩下的部分中,判断执行顺序的指导原则就是:最右、最上的操作先执行。具体解释如下:
得到去除妨碍判断的索引扫描后的执行计划:Execution Plan
-------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'
看执行计划的第3列,即字母部分,每列值的左面有空格作为缩进字符。在该列值左边的空格越多,说明该列值的缩进越多,该列值也越靠右。如上面的执行计划所示:第一列值为6的行的缩进最多,即该行最靠右;第一列值为4、5的行的缩进一样,其靠右的程度也一样,但是第一列值为4的行比第一列值为5的行靠上;谈论上下关系时,只对连续的、缩进一致的行有效。
从这个图中我们可以看到,对于NESTED LOOPS部分,最右、最上的操作是TABLE ACCESS (FULL) OF 'B',所以这一操作先执行,所以该操作对应的B表为第一个驱动表(外部表),自然,A表就为内部表了。从图中还可以看出,B与A表做嵌套循环后生成了新的row source ,对该row source进行来排序后,与C表对应的排序了的row source(应用了C.col3 = 5限制条件)进行SMJ连接操作。所以从上面可以得出如下事实:B表先与A表做嵌套循环,然后将生成的row source与C表做排序—合并连接。
通过分析上面的执行计划,我们不能说C表一定在B、A表之后才被读取,事实上,B表有可能与C表同时被读入内存,因为将表中的数据读入内存的操作可能为并行的。事实上许多操作可能为交叉进行的,因为ORACLE读取数据时,如果就是需要一行数据也是将该行所在的整个数据块读入内存,而且有可能为多块读。
看执行计划时,我们的关键不是看哪个操作先执行,哪个操作后执行,而是关键看表之间连接的顺序(如得知哪个为驱动表,这需要从操作的顺序进行判断)、使用了何种类型的关联及具体的存取路径(如判断是否利用了索引)
在从执行计划中判断出哪个表为驱动表后,根据我们的知识判断该表作为驱动表(就像上面判断ABC表那样)是否合适,如果不合适,对SQL语句进行更改,使优化器可以选择正确的驱动表。
相关推荐
"Oracle 执行计划详解" Oracle 执行计划是数据库性能优化的关键。为了更好地理解和优化 Oracle 数据库的执行计划,我们需要了解执行计划的生成过程、优化方法和执行计划的解读方法。 执行计划生成过程 执行计划的...
Oracle执行计划参数解释,Oracle SQL优化的基础是看懂Oracle的执行计划,本文当系统整理了Oracle执行计划里面的各种参数。
Oracle 执行计划解读 Oracle 执行计划是一种查询执行路径的表示形式,它展示了 Oracle 数据库在执行查询时访问数据的路径。下面是 Oracle 执行计划的详细解读,包括执行计划的定义、访问数据的方式、执行计划层次...
### Oracle执行计划分析 #### 一、概述 在Oracle数据库管理中,优化SQL查询性能是一项至关重要的任务。其中,理解并分析SQL执行计划是提升查询效率的关键步骤之一。执行计划是指Oracle数据库根据特定的SQL语句所...
Oracle执行计划是数据库管理系统在处理SQL语句时的预估工作流程,它是Oracle优化器根据当前数据分布、索引情况和系统资源等信息选择的最佳执行策略。了解和分析执行计划对于提升SQL语句的性能至关重要。 一、生成...
Oracle 执行计划是数据库管理系统在处理SQL查询时制定的一系列步骤,用于高效地检索和处理数据。它是Oracle数据库优化器(Optimizer)根据统计信息、成本估算和已存在的索引等信息生成的。优化器有两种主要的工作...
### Oracle执行计划深入解析 #### 引言 在数据库领域,尤其对于大型企业级应用,Oracle数据库因其强大的功能和稳定性而被广泛采用。在Oracle中,执行计划是数据库优化器根据SQL语句特性生成的一系列步骤,用于指导...
Oracle 执行计划详解是数据库管理系统中一个非常重要的概念。本文将详细介绍 Oracle 执行计划的相关概念、访问数据的存取方法、表之间的连接等内容,并对总结和概述,以便于理解和记忆。 一、相关的概念 1. Rowid ...
除了以上概念,Oracle执行计划还包括其他访问方法,如索引扫描(Index Scan)、索引唯一扫描(Index Unique Scan)、索引快速全扫描(Index Fast Full Scan)以及各种类型的连接操作,如嵌套循环(Nested Loop)、...
ORACLE 执行计划和 SQL 调优 ORACLE 执行计划和 SQL 调优是关系数据库管理系统中非常重要的概念。执行计划是 Oracle 优化器生成的,用于描述如何访问数据库中的数据的计划。execute plan 中包括了访问路径、表扫描...
Oracle执行计划详解,包括oracle执行顺序和索引详细介绍
《Oracle执行计划与SQL优化实例》这一主题深入探讨了数据库管理与优化的关键方面,尤其针对Oracle数据库环境。本文旨在解析并扩展此PPTX文件中提及的重要知识点,涵盖执行计划的概念、获取方法、解读技巧以及SQL优化...
ORACLE执行计划和SQL调优
oracle执行计划详细解释
Oracle 执行计划 Oracle 执行计划是 Oracle 数据库中的一种机制,用于确定如何访问存储器,得到需要的结果集。执行计划的主要内容包括访问方式和访问顺序。下面是 Oracle 执行计划的详细知识点: 一、执行计划的...
总之,Oracle执行计划的稳定性与数据库性能紧密相关,理解并掌握优化器的工作原理以及如何控制执行计划的选择,是优化数据库性能的关键。在基于代价的优化器模式下,虽然存在执行计划不稳定的可能,但通过合理管理和...
总之,Oracle执行计划的稳定性对于数据库的高效运行具有重要意义。通过合理管理统计信息、选择合适的优化器模式、利用SQL提示和监控工具,可以有效控制执行计划的稳定性,从而提升数据库的整体性能。
"Oracle 执行计划" Oracle 执行计划是 Oracle 数据库中的一种机制,用于优化 SQL 语句的执行过程。它决定了如何访问存储器,得到需要的结果集。执行计划的主要内容包括访问方式和访问顺序。 Oracle 执行计划的生成...