测试目的:初步测试CBO与RBO,以及全表扫描比索引快的情况。
1. 创建测试环境
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> create table test1 as select * from dba_objects where rownum < 1001;
表已创建。
SQL> insert into test1 select * from test1;
已创建1000行。
SQL> /
已创建2000行。
...
SQL> /
已创建4096000行。
SQL> update test1 set object_id = 3 where rownum < 4096000;
已更新4095999行。
SQL> commit;
提交完成。
|
2. 比较对于不同的数据分布,CBO与RBO执行计划的不同
2.1 测试CBO:自动根据统计数据选用不同的执行计划
-- 1. 因为object_id值为1的数据量占的比例很小,所以当object_id为1时,CBO选则使用索引:
SQL> explain plan for
2 select count(object_name) from test1 where object_id=1;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 4180766814
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 79 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 79 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_TEST1_OBJECT_ID | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=1)
Note
-----
- dynamic sampling used for this statement
已选择19行。
-- 2. 因为object_id值为3的数据量占1半的比例,所以当object_id为3时,CBO选择使用全表扫描:
SQL> explain plan for
2 select count(object_name) from test1 where object_id=3;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 3896847026
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 21846 (3)| 00:04:23 |
| 1 | SORT AGGREGATE | | 1 | 79 | | |
|* 2 | TABLE ACCESS FULL| TEST1 | 3662K| 275M| 21846 (3)| 00:04:23 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
2 - filter("OBJECT_ID"=3)
Note
-----
- dynamic sampling used for this statement
已选择18行。
|
2.2 测试RBO:根据规则直接使用索引,而不考虑实际的数据。
-- 1. 当object_id值为1时,使用索引,此时是一个不错的执行计划:
SQL> explain plan for
2 select /*+rule*/ count(object_name) from test1 where object_id=1;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
Plan hash value: 4180766814
------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST1 |
|* 3 | INDEX RANGE SCAN | IND_TEST1_OBJECT_ID |
------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=1)
Note
-----
- rule based optimizer used (consider using cbo)
已选择19行。
-- 2. 当object_id值为3时,对于RBO,仍然会使用索引,不会考虑数据的分布:
SQL> explain plan for
2 select /*+rule*/ count(object_name) from test1 where object_id=3;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
Plan hash value: 4180766814
------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST1 |
|* 3 | INDEX RANGE SCAN | IND_TEST1_OBJECT_ID |
------------------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=3)
Note
-----
- rule based optimizer used (consider using cbo)
已选择19行。
|
3. 比较当object_id为3时,CBO的全表扫描与RBO的使用索引的执行时间:
-- 1. CBO:执行时间不到11秒
SQL> set timing on
SQL> select count(object_name) from test1 where object_id=3;
COUNT(OBJECT_NAME)
------------------
4100095
已用时间: 00: 00: 10.60
SQL> select count(object_name) from test1 where object_id=3;
COUNT(OBJECT_NAME)
------------------
4100095
已用时间: 00: 00: 10.95
SQL> select count(object_name) from test1 where object_id=3;
COUNT(OBJECT_NAME)
------------------
4100095
已用时间: 00: 00: 10.76
SQL> select count(object_name) from test1 where object_id=3;
COUNT(OBJECT_NAME)
------------------
4100095
已用时间: 00: 00: 10.51
-- 2. RBO:执行时间在18秒左右
SQL> select /*+rule*/ count(object_name) from test1 where object_id=3;
COUNT(OBJECT_NAME)
------------------
4100095
已用时间: 00: 00: 16.75
SQL> select /*+rule*/ count(object_name) from test1 where object_id=3;
COUNT(OBJECT_NAME)
------------------
4100095
已用时间: 00: 00: 17.00
SQL> select /*+rule*/ count(object_name) from test1 where object_id=3;
COUNT(OBJECT_NAME)
------------------
4100095
已用时间: 00: 00: 18.45
SQL> select /*+rule*/ count(object_name) from test1 where object_id=3;
COUNT(OBJECT_NAME)
------------------
4100095
已用时间: 00: 00: 18.50
|
4. 结论
1)CBO会自动根据统计数据来选择它认为更优化的执行计划,而RBO只是根据既定规则选择相应的执行计划。
2)索引并不是加速系统性能的开关,有时候使用全表扫描要比使用索引更快。
5. 一点补充
当可以直接在索引中查询出结果集时,Oracle也不会再执行全表扫描的。比如在上面的测试中,当object_id=3时,统计 count(object_name),Oracle会使用全表扫描,但如果统计count(object_id)或count(*),Oracle就直 接可以在索引中完成操作了。再看一下此时的执行计划:
SQL> explain plan for
2 select count(object_id) from test1 where object_id=3;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 1676651115
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 7155 (4)| 00:01:26 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX FAST FULL SCAN| IND_TEST1_OBJECT_ID | 3662K| 45M| 7155 (4)| 00:01:26 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
2 - filter("OBJECT_ID"=3)
Note
-----
- dynamic sampling used for this statement
已选择18行。
SQL> explain plan for
2 select count(*) from test1 where object_id=3;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 1676651115
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 7155 (4)| 00:01:26 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX FAST FULL SCAN| IND_TEST1_OBJECT_ID | 3662K| 45M| 7155 (4)| 00:01:26 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
2 - filter("OBJECT_ID"=3)
Note
-----
- dynamic sampling used for this statement
已选择18行。