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