[20180425]為什麼走索引邏輯讀反而高.txt

[20180425]為什麼走索引邏輯讀反而高.txt

--//別人問的問題,自己測試看看,開始以為array設定太小.還是透過例子說明問題.

1.環境:

SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table t as select * from all_objects ;
Table created.

SCOTT@book> create index i_t_owner_object_name_id on t (owner,object_name,object_id);
Index created.

--//分析略.

2.測試:
SCOTT@book> alter session set statistics_level=all ;
Session altered.

$ cat a.sql
set array &1
set term off
select /*+ full(t) */ owner,object_name,object_id, count(*)  from t group by owner ,object_name,object_id;
set term on
@ &r/dpc '' ''
set term off
select  owner,object_name,object_id, count(*)  from t group by owner ,object_name,object_id;
set term on
@ &r/dpc '' ''

SCOTT@book> @ a.sql 100
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7wftkxux5qswj, child number 0
-------------------------------------
select /*+ full(t) */ owner,object_name,object_id, count(*)  from t
group by owner ,object_name,object_id
Plan hash value: 47235625
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |       |  1142 (100)|          |  84770 |00:00:00.13 |    1213 |   1211 |       |       |          |
|   1 |  HASH GROUP BY     |      |      1 |  84770 |  2980K|  4000K|  1142   (1)| 00:00:14 |  84770 |00:00:00.13 |    1213 |   1211 |  9903K|  2517K| 7503K (0)|
|   2 |   TABLE ACCESS FULL| T    |      1 |  84770 |  2980K|       |   338   (1)| 00:00:05 |  84770 |00:00:00.02 |    1213 |   1211 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
21 rows selected.

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9gtrqw4ar2y5y, child number 0
-------------------------------------
select  owner,object_name,object_id, count(*)  from t group by owner
,object_name,object_id
Plan hash value: 2986604141
-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                          |      1 |        |       |   553 (100)|          |  84770 |00:00:00.14 |    1394 |
|   1 |  SORT GROUP BY NOSORT|                          |      1 |  84770 |  2980K|   553   (1)| 00:00:07 |  84770 |00:00:00.14 |    1394 |
|   2 |   INDEX FULL SCAN    | I_T_OWNER_OBJECT_NAME_ID |      1 |  84770 |  2980K|   553   (1)| 00:00:07 |  84770 |00:00:00.05 |    1394 |
-------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
21 rows selected.

--//你可以發現在array=100的情況下,走索引反而邏輯讀高,實際上你設定array=200可以發現走索引邏輯讀低,並且全部掃描的邏輯讀不變都是1213.
--//實際上這個問題跟http://blog.itpub.net/267265/viewspace-2152739/相似.
--//如果設定array=200,就不會出現上面的情況.

SCOTT@book> select blocks from dba_tables where owner=user and table_name='T';
    BLOCKS
----------
      1239

--//走全表掃描時一次取出然後走hash group by.邏輯讀接近掃描表資料塊的數量.
--//而走索引,注意看id=1的Operation,SORT GROUP BY NOSORT,這樣的操作就是把索引當作表,fetch到array=100就一次邏輯讀.
--//如果走索引,加入一個order by限制邏輯讀更小.修改如下:
select  owner,object_name,object_id, count(*)  from t group by owner ,object_name,object_id order by object_id ;
--//注如果修改order by owner ;無效.因為這個是索引的第一個欄位.

$ cat a.sql
set array &1
set term off
select /*+ full(t) */ owner,object_name,object_id, count(*)  from t group by owner ,object_name,object_id;
set term on
@ &r/dpc '' ''
set term off
select  owner,object_name,object_id, count(*)  from t group by owner ,object_name,object_id;
set term on
@ &r/dpc '' ''
set term off
select  owner,object_name,object_id, count(*)  from t group by owner ,object_name,object_id order by object_id ;
set term on
@ &r/dpc '' ''

SCOTT@book> @ a.sql 100
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  42vqa70a6d39v, child number 0
-------------------------------------
select  owner,object_name,object_id, count(*)  from t group by owner
,object_name,object_id order by object_id

Plan hash value: 1346372488

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                     | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                          |      1 |        |       |       |   955 (100)|          |  84770 |00:00:00.18 |     559 |       |       |          |
|   1 |  SORT GROUP BY        |                          |      1 |  84770 |  2980K|  4000K|   955   (1)| 00:00:12 |  84770 |00:00:00.18 |     559 |  8345K|  1132K| 7417K (0)|
|   2 |   INDEX FAST FULL SCAN| I_T_OWNER_OBJECT_NAME_ID |      1 |  84770 |  2980K|       |   151   (0)| 00:00:02 |  84770 |00:00:00.02 |     559 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1

SCOTT@book> validate index I_T_OWNER_OBJECT_NAME_ID;
Index analyzed.

SCOTT@book> @ &r/i
    HEIGHT     BLOCKS NAME          LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS
---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- -------------
         3        640 I_T_OWNER_      84770        550     3929913       7996        549          3       12796       8028           0               0         84770
                      OBJECT_NAM
                      E_ID

MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
                1     4421884    3942709         90            1                    4          0            0              1               12

SCOTT@book> select leaf_blocks from dba_indexes where owner=user and table_name='T';
LEAF_BLOCKS
-----------
        550
               
--//這樣邏輯讀接近索引佔用的塊數.
--//當然任何問題都給辯證的看待,最後1個排序導致cost成本上升,使用到排序.消耗一定的pga資源.