对ORACLE中访问路径的摘要,备忘。摘录自徐玉金的《SQL性能的调整》,重做了实验部分并根据个人理解有所增删。
关键字: access path CBO[@more@]优化器在生成执行计划时需要选择如何从数据库查询出需要的数据。由于可能存在许多访问路径(access path)可以访问到需要的数据,优化器将选择其中自认为是最优的路径。
怎样才算是最优?CBO(Cost Based Optimizer)顾名思义是根据执行成本最小即cost最小,RBO(Rule Based Optimizer)则是根据规则,即尽量符合等级高的预定义规则。下文如无特殊说明即是默认为CBO。
在物理层,oracle读取数据,一次读取的最小单位为数据库块(由多个连续的OS块组成),一次读取的最大值由操作系统一次I/O的最大值与multiblock参数共同决定,所以即使只需要一行数据,也是将该行所在的数据库块读入内存。
下文说明Oracle的各种访问路径。
实验环境:
·OS
Microsoft Winsows XP Professional 版本 2002 Service Pack 3
·DB
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
需要使用的测试表脚本如下:
create table JOE_MAIN as select object_id,object_name,last_ddl_time from dba_objects where rownum < 100;
alter table JOE_MAIN add constraint UN_1 unique (OBJECT_ID);
create index IDX_JOE_MAIN_12 on JOE_MAIN (OBJECT_ID, OBJECT_NAME);
create table big_table as select object_id serialno,object_name col_val,last_ddl_time CT from dba_objects;
alter table big_table add constraint PK_BIG_TABLE primary key (serialno);
create index IDX_BIG_12 on big_table (serialno, col_val);
全表扫描(Full Table Scans, FTS)
为实现全表扫描,Oracle读取表中所有的行,并检查每一行是否满足SQL语句的WHERE条件。Oracle顺序地读取分配给表的每个数据块,直到读到表的最高水位线(HWM,标识表的最后一个数据块)。FTS会使用多块读操作,可以使一次I/O能读取连续的多个数据块(具体数量受DB_FILE_MULTIBLOCK_READ_COUNT参数设定的影响),而不是只读取一个数据块,这极大的减少了I/O总次数,提高了系统的吞吐量,所以利用多块读的方法可以十分高效地实现全表扫描。在这种访问模式下,每个数据块只被读一次。
由FTS模式读入的数据被放到高速缓存的Least Recently Used (LRU)列表的尾部,这样可以使其快速交换出内存,从而不使内存重要的数据被交换出内存。
在我初学Oracle时认为凡是FTS就是不好的,都会造成SQL性能低下,实际上并非如此。有时使用FTS也是合理的,那么何时应该使用FTS?
·表很小
这里只数据量和占用的segment空间都很小。比如某小表1次IO即可全部加载到内存中(体现多块读的高效率)。这时走FTS的代价仅1次物理IO。如果走索引,则首先至少1次IO(无法多块读可能需要更多次的IO)将索引加载到内存,通过索引找到需要的记录后跟据rowid再通过至少1次IO(所需的多条数据可能不在一个块上)将数据加载到内存。
·查询的数据比较多
结果集占比较大,超过表数据总量的5% -- 10%。这时FTS的多块读充分发挥优势,尤其是表的数据块连续性好,离散度低的时候。
·使用并行查询功能时。
以下条件使CBO更倾向于使用FTS:
·DB_FILE_MULTIBLOCK_READ_COUNT参数值较大
ORACLE官方文档中建议,在OLTP系统中该值为4-16;在DSS、DW系统中建议设为最大值((max I/O size)/DB_BLOCK_SIZE)。
·OPTIMIZER_INDEX_COST_ADJ参数值较大
该参数表示使用索引和使用FTS的代价比,取值范围1-10000,默认100,表示使用索引和使用FTS的代价比为100%。
以下条件使FTS的效率更高(和FTS本身比):
·表的数据块连续性好碎片少
最理想的情况是表的数据块全部连续,没有任何碎片,每次读入的都是数据而没有碎片,获取数据效率最高。在某些只读表上可以实现或接近实现。
·HWM低
由于HWM标识最后一块被读入的数据,而delete操作不影响HWM值,所以一个表的所有数据被delete后,其全表扫描的时间不会有改善。所以降低HWM,可以减少FTS的处理量。一般我们需要使用truncate命令来使HWM值归为0。oracle 10G后,可以使用alter table shrink space降低HWM的值。和上一种方法思路不同,但殊途同归。
FTS的例子:
SQL> select * from dual;
执行计划
----------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
通过ROWID访问(Table Access by ROWID或rowid lookup)
数据记录的ROWID指出了该记录所在的数据文件、数据块以及行在该块中的位置,所以通过ROWID来存取数据可以快速定位到目标数据上,是Oracle存取单行数据的最快方法。
为了通过ROWID存取表,Oracle 首先要获取被选择行的ROWID,或者从语句的WHERE子句中得到,或者通过表的一个或多个索引中得到。
这种存取方法不会用到多块读操作,一次I/O只能读取一个数据块。我们会经常在执行计划中看到该访问方法,如通过索引查询数据。
通过ROWID访问的例子:
SQL> select * from joe_main where rowid='AAAM2WAAGAAAAAMAAE';
执行计划
----------------------------------------------------------
Plan hash value: 3247578292
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| JOE_MAIN | 1 | 26 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
索引扫描(Index Scan或index lookup)
索引扫描是先通过index查找到数据对应的rowid(对于非唯一索引可能返回多个rowid),然后根据rowid直接访问数据。一个rowid唯一的表示一行数据(记录的地址),该行对应的数据块是通过一次IO得到的,在此情况下该次IO只会读取一个数据库块。 索引扫描中只有Index Fast Full Scan(FFS)可以多块读。
在索引中,除了存储每个索引的值外,索引还存储具有此值的数据记录对应的ROWID。索引扫描可以由2步组成:(1) 扫描索引得到对应的rowid。 (2) 通过rowid访问数据。每步都是单独的一次I/O,但是对于索引,由于经常使用,绝大多数都已经CACHE到内存中,所以第1步的I/O经常是逻辑I/O,即数据可以从内存中得到。但是对于第2步来说,如果表比较大,则其数据不可能全在内存中,所以其I/O很有可能是物理I/O,这是一个机械操作,相对逻辑I/O来说,是极其费时间的。所以如果多大表进行索引扫描,取出的数据如果大于总量的5% -- 10%,使用索引扫描,效率下降很多。
以下例子通过索引找到rowid,再通过rowid访问数据:
SQL> select object_id,object_name,last_ddl_time from joe.joe_main where object_id=6;
执行计划
----------------------------------------------------------
Plan hash value: 209614598
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| JOE_MAIN| 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | UN_1 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
有一种情况只用执行第1步(访问索引),第2步(通过rowid访问数据)不用执行:所需的列在全在索引中。在实际中,这样的情况比较少见,但是一旦出现,我们可以加以利用,提高SQL效率。下面是例子:
SQL> select object_id from joe_main where object_id=6;
执行计划
----------------------------------------------------------
Plan hash value: 106044739
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| UN_1 | 1 | 4 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
索引是已排序的数据结构,那么如果sql语句中按索引列进行排序(order by),在执行计划中不需要再对索引列进行排序。注意下面两个SQL的不同。
SQL> select object_id,object_name,last_ddl_time from joe.joe_main where object_id<=100 order by object_id;
执行计划
----------------------------------------------------------
Plan hash value: 2292514407
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 95 | 2470 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID|JOE_MAIN| 95 | 2470 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | UN_1 | 95 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
SQL> select object_id,object_name,last_ddl_time from joe.joe_main where object_id<=100 order by last_ddl_time;
执行计划
----------------------------------------------------------
Plan hash value: 2968746428
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 95 | 2470 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 95 | 2470 | 4 (25)| 00:00:01 |
| 2| TABLE ACCESS BY INDEX ROWID|JOE_MAIN| 95 | 2470 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | UN_1 | 95 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
根据索引的类型与where限制条件的不同,有4种类型的索引扫描:
·索引唯一扫描(index unique scan)
·索引范围扫描(index range scan)
·索引全扫描(index full scan)
·索引快速扫描(index fast full scan)
索引唯一扫描(index unique scan)
通过唯一索引查找一个数值通常返回单个ROWID,以下是使用索引唯一扫描的例子:
SQL> select object_id from joe.joe_main where object_id=6;
执行计划
----------------------------------------------------------
Plan hash value: 106044739
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| UN_1 | 1 | 4 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
如果唯一索引为复合索引,为了使用该索引,则至少要有该索引的引导列参与到该查询中。如下面的例子,object_id为索引IDX_JOE_MAIN_12的前导列。
SQL> select object_id,object_name from joe.joe_main where object_id=6;
执行计划
----------------------------------------------------------
Plan hash value: 3355573836
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_JOE_MAIN_12 | 1 | 19 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Object_name列虽然也在索引IDX_JOE_MAIN_12中,但是不是前导列,情况就如下例子:
SQL> select object_id,object_name from joe.joe_main where object_name='C_TS#';
执行计划
----------------------------------------------------------
Plan hash value: 4109758151
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 3 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_JOE_MAIN_12| 1 | 19 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
如果存在UNIQUE 或PRIMARY KEY 约束,并且使用等值查询时,可以保证语句只访问单行数据,Oracle经常使用索引唯一扫描。
索引范围扫描(index range scan)
使用索引访问数据,如果返回多行数据,此时的访问路径称为索引范围扫描。在唯一索引上使用索引范围扫描的典型情况是在谓词中使用了范围操作符(如>、<、<>、>=、<=、between)
使用索引范围扫描的例子:
SQL> select object_id,object_name from joe.joe_main where object_id<100;
执行计划
----------------------------------------------------------
Plan hash value: 3355573836
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 94 | 1786 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_JOE_MAIN_12 | 94 | 1786 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
当使用非唯一索引访问数据时,等值条件也可能返回多行数据,所以在非唯一索引上都使用索引范围扫描。
使用index rang scan的3种情况:
(1) 在唯一索引列上使用了range操作符(> < <> >= <= between)
(2) 在组合索引上,只使用部分列进行查询,导致查询出多行
(3) 对非唯一索引列上进行的任何查询。
索引全扫描(index full scan)
与全表扫描对应,也有相应的全索引扫描。在某些情况下,可能进行全索引扫描而不是范围扫描,需要注意的是全索引扫描只在CBO模式下才有效。CBO根据统计数值得知进行全索引扫描比进行全表扫描更有效时,才进行全索引扫描。
全索引扫描的例子:
SQL> select serialno,col_val from joe.big_table order by serialno,col_val;
执行计划
----------------------------------------------------------
Plan hash value: 1647518611
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 61123 | 4715K| 301 (1)| 00:00:04 |
| 1 | INDEX FULL SCAN | IDX_BIG_12 | 61123 | 4715K| 301 (1)| 00:00:04 |
-------------------------------------------------------------------------------
我学习的这篇文档上提到“(使用全索引扫描需要)此时查询出的数据都必须从索引中可以直接得到”,从我的实验中来看这一点已经不正确了。毕竟ORACLE是不断发展的,任何以往的经验都只能作参考,遇到具体问题时还是应该主动分析,不能完全照搬经验,再相似的问题也会有细微的差别。以下是例子,ct列不在索引IDX_BIG_12中。
SQL> select serialno,col_val,CT from joe.big_table order by serialno,col_val;
执行计划
----------------------------------------------------------
Plan hash value: 157955444
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 61123 | 5252K| 601 (1)| 00:00:08 |
| 1 | TABLE ACCESS BY INDEX ROWID|BIG_TABLE|61123| 5252K| 601 (1)| 00:00:08 |
| 2 | INDEX FULL SCAN | IDX_BIG_12 | 61123 | | 286 (1)| 00:00:04 |
------------------------------------------------------------------------------------------
索引快速扫描(index fast full scan,FFS)
FFS扫描索引中的所有的数据块,与 index full scan很类似,但是一个显著的区别就是它不对查询出的数据进行排序,即数据不是以排序顺序被返回。FFS是另一个可以使用多块读功能的访问路径,也可以使用并行读入,以便获得最大吞吐量与缩短执行时间。
索引快速扫描的例子:
SQL> select serialno,col_val from joe.big_table;
执行计划
----------------------------------------------------------
Plan hash value: 2264247822
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 61123 | 4715K| 68 (2)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| IDX_BIG_12 | 61123 | 4715K| 68 (2)| 00:00:01 |
-----------------------------------------------------------------------------------
和索引唯一扫描不同,在没有选择索引的前导列时依然可以使用FFS。下面是例子:
SQL> select col_val from joe.big_table;
执行计划
----------------------------------------------------------
Plan hash value: 2264247822
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50582 | 1234K| 65 (2)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| IDX_BIG_12| 50582 | 1234K| 65 (2)| 00:00:01 |
-----------------------------------------------------------------------------------
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21129591/viewspace-1052311/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21129591/viewspace-1052311/