【SQL 優化】異常的邏輯讀

實驗環境

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

但是從執行計劃的結果上看，走索引卻耗費更多的邏輯讀！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-676273/，如需轉載，請註明出處，否則將追究法律責任。