SQL> conn system/yang as sysdba
已連線。
SQL> deac t
SP2-0042: 未知命令 "deac t" - 其餘行忽略。
SQL> desc t
名稱                                      是否為空? 型別
----------------------------------------- -------- ----------------------------
OWNER                                     NOT NULL VARCHAR2(30)
OBJECT_NAME                               NOT NULL VARCHAR2(30)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                 NOT NULL NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                        VARCHAR2(19)
CREATED                                   NOT NULL DATE
LAST_DDL_TIME                             NOT NULL DATE
TIMESTAMP                                          VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                          VARCHAR2(1)
GENERATED                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)
NAMESPACE                                 NOT NULL NUMBER
EDITION_NAME                                       VARCHAR2(30)

SQL> select count(owner) from t;
COUNT(OWNER)
------------
       68272
SQL> show arraysize
arraysize 15
SQL> set autot traceonly
SQL> create index i_t_id on t(object_id);
索引已建立。
SQL> set timing on
SQL> update t set object_id=1 where object_id is null;
已更新0行。
已用時間: 00: 00: 00.01
執行計劃
----------------------------------------------------------
Plan hash value: 1357257176

----------------------------------------------------------------------------
| Id | Operation         | Name   | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT |        |     1 |     4 |     0   (0)|          |
|   1 | UPDATE           | T      |       |       |            |          |
|* 2 |   FILTER          |        |       |       |            |          |
|   3 |    INDEX FULL SCAN| I_T_ID | 68272 |   266K|   153   (1)| 00:00:02 |
----------------------------------------------------------------------------

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

   2 - filter(NULL IS NOT NULL)

統計資訊
----------------------------------------------------------
         89 recursive calls
          0 db block gets
         40 consistent gets
          0 physical reads
          0 redo size
        675 bytes sent via SQL*Net to client
        618 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          2 sorts (memory)
          0 sorts (disk)
          0 rows processed

SQL> set autot traceonly
SQL> select /*+ index(t)*/* from t;
已選擇68272行。
已用時間: 00: 00: 03.46
執行計劃
----------------------------------------------------------
Plan hash value: 4247898483

--------------------------------------------------------------------------------
------

| Id | Operation                   | Name   | Rows | Bytes | Cost (%CPU)| Time
     |

--------------------------------------------------------------------------------
------

|   0 | SELECT STATEMENT            |        | 68272 | 6200K| 1196   (1)| 00:0
0:15 |

|   1 | TABLE ACCESS BY INDEX ROWID| T      | 68272 | 6200K| 1196   (1)| 00:0
0:15 |

|   2 |   INDEX FULL SCAN           | I_T_ID | 68272 |       |   153   (1)| 00:0
0:02 |

--------------------------------------------------------------------------------
------

統計資訊
----------------------------------------------------------
        284 recursive calls
          0 db block gets
      10257 consistent gets
        151 physical reads
          0 redo size
    7804835 bytes sent via SQL*Net to client
      50477 bytes received via SQL*Net from client
       4553 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      68272 rows processed
SQL> select a.index_name,
2 a.index_type,
3 a.blevel,
4 a.leaf_blocks,
5 a.distinct_keys,
6 a.clustering_factor,
7 a.num_rows
8 from dba_indexes a
9 where a.table_name='T';

INDEX_NAME                     INDEX_TYPE                      BLEVEL
------------------------------ --------------------------- ----------
LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR   NUM_ROWS
----------- ------------- ----------------- ----------
SYS_C0010490                   NORMAL                               0
          1             2                 2          2

SYS_C0010487                   NORMAL                               0
          1             2                 2          2

T_ENAME                        NORMAL                               0
          1            14                 1         14

INDEX_NAME                     INDEX_TYPE                      BLEVEL
------------------------------ --------------------------- ----------
LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR   NUM_ROWS
----------- ------------- ----------------- ----------
EMPNO_I                        NORMAL                               0
          1            14                 1         14

I_T1                           NORMAL                               2
        392         66333             34935      68272

I_T_ID                         NORMAL                               1
        151         68272              1042      68272

已選擇6行。

已用時間: 00: 00: 00.00
SQL> select blocks,table_name from user_tables where table_name='T';

    BLOCKS TABLE_NAME
---------- ------------------------------
      1004 T
已用時間: 00: 00: 00.03
SQL> select (68272/15)*2+1004+151 from dual;
(68272/15)*2+1004+151
---------------------
           10257.9333    -------------- 10257 consistent gets 基本一致
已用時間: 00: 00: 00.00

1004是表的塊數，151是leaf block數，index full scan是掃描全部的葉塊。
總行數/sqlplus一次返回的行數，算一次邏輯讀

【一致性讀的計算】

相關文章