ORACLE 表分析对CBO执行计划的影响示例

一、创建测试表
SQL> Create Table Testsample As Select Object_Id, Object_Name From Dba_Objects Where 1=2;
 
Table created
 二、创建索引
SQL> Create Index i_Idx_Testsample On Testsample(Object_Id);
 
Index created
 三、插入数据
SQL>  Insert Into Testsample Select Object_Id, Object_Name From Dba_Objects;
 
72535 rows inserted
 
SQL> Commit;
 
Commit complete
 四、查询行数，平均长度，数据块等信息
SQL> select num_rows, avg_row_len, blocks, last_analyzed from user_tables where table_name='TESTSAMPLE';
 
  NUM_ROWS AVG_ROW_LEN     BLOCKS LAST_ANALYZED
---------- ----------- ---------- -------------
 五、查询索引层级，索引叶块数，非重复键值，最后分析时间
SQL> select blevel, leaf_blocks, distinct_keys, last_analyzed from user_indexes where table_name='TESTSAMPLE';
 
    BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED
---------- ----------- ------------- -------------
         0           0             0 2014-7-8 10:1
此时，表和索引都没有被分析， 表的行数，平均长度，占用的数据块数及最后分析时间为空，索引相关信息为空。
SQL> set linesize 800
五、查看非动态采样下的执行计划（ORALCE 10 以后，默认会采用动态采样，此处禁止使用动态采样 。）
SQL> select /*+ dynamic_sampling(testsample 0) */ * from testsample where object_id > 50;


已选择72486行。




执行计划
----------------------------------------------------------
Plan hash value: 37301494


------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     4 |   316 |     0   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TESTSAMPLE       |     4 |   316 |     0   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_IDX_TESTSAMPLE |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("OBJECT_ID">50)




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      10263  consistent gets
          0  physical reads
          0  redo size
    3114305  bytes sent via SQL*Net to client
      53567  bytes received via SQL*Net from client
       4834  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72486  rows processed


没有进行分析，CBO预估表中数据为4行，因此走索引扫描。此处的一致性读为10263

六、收集统计信息。
SQL> exec dbms_stats.gather_table_stats(user,'TESTSAMPLE',cascade => true);
 
PL/SQL procedure successfully completed


七、查询行数，平均长度，数据块等信息
SQL> select num_rows, avg_row_len, blocks, last_analyzed from user_tables where table_name='TESTSAMPLE';
 
  NUM_ROWS AVG_ROW_LEN     BLOCKS LAST_ANALYZED
---------- ----------- ---------- -------------
     72535          29        370 2014-7-8 11:2
 八、查询索引层级，索引叶块数，非重复键值，最后分析时间
SQL> select blevel, leaf_blocks, distinct_keys, last_analyzed from user_indexes where table_name='TESTSAMPLE';
 
    BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED
---------- ----------- ------------- -------------
         1         262         72535 2014-7-8 11:2
九、查看表分析后的执行计划
SQL> SELECT * FROM TESTSAMPLE WHERE OBJECT_ID > 50;


已选择72486行。




执行计划
----------------------------------------------------------
Plan hash value: 122634852


--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            | 72489 |  2052K|   103   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TESTSAMPLE | 72489 |  2052K|   103   (1)| 00:00:02 |
--------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("OBJECT_ID">50)




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5191  consistent gets
          0  physical reads
          0  redo size
    2824417  bytes sent via SQL*Net to client
      53567  bytes received via SQL*Net from client
       4834  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72486  rows processed
 
经过表分析以后，CBO预估表中的行数为72489条，和实际差距很小。此时一致性读为5191


十、查看表中总行数
SQL> select count(*) from testsample;
 
  COUNT(*)
----------
     72535  
 
和表中总数据量72535比较，本例使用全表扫描效果更好一些，此种情况，如果走扫描索引，再根据索引键值查询数据块，则要先查询索引数据块，再查询表数据块，因此要扫描更多的数据块,所以全表扫描更优一些。