全索引扫描 我以为是这样的情况 通过索引的叶节点的双向链表,走完全不索引叶块后,再去回表读数据!
SELECT id
FROM (SELECT MIN(BV_ID) id
FROM RBS.RBS_FIRST_FAILURE_BLACKLIST
GROUP BY BV_BE_ID, UPPER(BV_VALUE)
HAVING COUNT(1) > 1)
WHERE rownum <= 500;
执行计划
----------------------------------------------------------
Plan hash value: 1883400118
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 6500 | 511 (0)| 00:00:07 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 3014K| 37M| 511 (0)| 00:00:07 |
|* 3 | FILTER | | | | | |
| 4 | SORT GROUP BY NOSORT | | 3014K| 2955M| 511 (0)| 00:00:07 |
| 5 | TABLE ACCESS BY INDEX ROWID| RBS_FIRST_FAILURE_BLACKLIST | 3014K| 2955M| 511 (0)| 00:00:07 |
| 6 | INDEX FULL SCAN | IX_FIRSBFAIL_BEID | 500 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=500)
3 - filter(COUNT(*)>1)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
3060089 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
select object_name,object_id,data_object_id
from USER_objects
where object_name in ('RBS_FIRST_FAILURE_BLACKLIST','IX_FIRSBFAIL_BEID');
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
IX_FIRSBFAIL_BEID 75247 75247
RBS_FIRST_FAILURE_BLACKLIST 75243 75244
75247=>125EF
75244=>125EC
ORADEBUG出来的TRC居然达到1.7GB
[oracle@132-MRBS-SZ trace]more 8580_stared_block.txt
ktrgtc2(): started for block <0x0007 : 0x018006c3> objd: 0x000125ef
ktrgtc2(): started for block <0x0007 : 0x01800ce1> objd: 0x000125ef
ktrget2(): started for block <0x0007 : 0x018006c4> objd: 0x000125ef
ktrget2(): started for block <0x0006 : 0x01446946> objd: 0x000125ec
.....................................................................
ktrget2(): started for block <0x0007 : 0x018006c5> objd: 0x000125ef
从上面内容可知它依旧是读叶块,再读数据块,再读叶块
叶块读了6405次
[oracle@132-MRBS-SZ trace]cat 8580_stared_block.txt |grep 0x000125ef |wc -l
6405
数据块读了3053656次
[oracle@132-MRBS-SZ trace]cat 8580_stared_block.txt |grep 0x000125ec |wc -l
3053656
如果设置array 5000是否会改变呢?
[oracle@132-MRBS-SZ trace]cat 9520_stared_block.txt |grep 0x000125ef|wc -l
6405
[oracle@132-MRBS-SZ trace]cat 9520_stared_block.txt |grep 0x000125ec|wc -l
3053532
看样子次数不会减少很多
