实验环境
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
SQL> create table t as select * from all_objects where 1=0;
表已创建。
SQL> set timing on;
SQL> insert into t select * from all_objects ;
已创建67670行。
已用时间: 00: 00: 21.00
SQL> commit;
提交完成。
已用时间: 00: 00: 00.00
SQL> insert into t select * from all_objects ;
已创建67670行。
已用时间: 00: 00: 13.53
SQL> commit;
提交完成。
已用时间: 00: 00: 00.00
SQL> insert into t select * from all_objects ;
已创建67670行。
已用时间: 00: 00: 13.68
SQL> commit;
提交完成。
已用时间: 00: 00: 00.00
SQL> begin
2 dbms_stats.gather_table_stats(user,'T');--信息统计
3 end;
4 /
PL/SQL 过程已成功完成。
已用时间: 00: 00: 03.07
SQL> set autotrace on
SQL> set linesize 120
SQL> set autot traceonly stat
SQL> select owner,object_name,object_id, count(*)
2 from t group by owner ,object_name,object_id;
已选择67670行。
已用时间: 00: 00: 01.76
统计信息
---------------------------------
0 recursive calls
0 db block gets
2979 consistent gets ----全表扫描时的逻辑读
792 physical reads
0 redo size
2964477 bytes sent via SQL*Net to client
50037 bytes received via SQL*Net from client
4513 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
67670 rows processed
SQL> create index idx_t on t (owner,object_name,object_id);--建立索引
索引已创建。
已用时间: 00: 00: 01.43
SQL> begin
2 dbms_stats.gather_table_stats(user,'T',cascade => true);--信息统计
3 end;
4 /
PL/SQL 过程已成功完成。
已用时间: 00: 00: 03.46
SQL> set autot traceonly
SQL> select owner,object_name,object_id, count(*)
2 from t group by owner ,object_name,object_id;
已选择67670行。
已用时间: 00: 00: 01.62
执行计划
----------------------------------------------------------
Plan hash value: 3184476542
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 67670 | 2379K| 1310 (1)| 00:00:16 |
| 1 | SORT GROUP BY NOSORT| | 67670 | 2379K| 1310 (1)| 00:00:16 |
| 2 | INDEX FULL SCAN | IDX_T | 203K| 7137K| 1310 (1)| 00:00:16 |
------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
5795 consistent gets---几乎是FTS 的两倍的逻辑读。
0 physical reads
0 redo size
2866263 bytes sent via SQL*Net to client
50037 bytes received via SQL*Net from client
4513 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
67670 rows processed
已用时间: 00: 00: 00.06
做一个10053 事件看看
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T Alias: T
#Rows: 203010 #Blks: 2973 AvgRowLen: 101.00
Index Stats::
Index: IDX_T Col#: 1 2 4
LVLS: 2 #LB: 1306 #DK: 67670 LB/K: 1.00 DB/K: 3.00 CLUF: 203010.00
Access path analysis for T --路径选择
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
Table: T Alias: T
Card: Original: 203010.000000 Rounded: 203010 Computed: 203010.00 Non Adjusted: 203010.00
Access Path: TableScan
Cost: 809.88 Resp: 809.88 Degree: 0
Cost_io: 807.00 Cost_cpu: 63804141
Resp_io: 807.00 Resp_cpu: 63804141
Access Path: index (index (FFS))
Index: IDX_T
resc_io: 355.00 resc_cpu: 33661801
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 356.52 Resp: 356.52 Degree: 1
Cost_io: 355.00 Cost_cpu: 33661801
Resp_io: 355.00 Resp_cpu: 33661801
Access Path: index (FullScan)
Index: IDX_T
resc_io: 1308.00 resc_cpu: 49916844
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 1310.25 Resp: 1310.25 Degree: 1
Best:: AccessPath: IndexFFS --最佳路径
Index: IDX_T
Cost: 356.52 Degree: 1 Resp: 356.52 Card: 203010.00 Bytes: 0
但是从执行计划的结果上看，走索引却耗费更多的逻辑读！