[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资源.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2153337/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2153337/