目前有个客户SQL存在一些性能问题，在自己环境测试时，发现SQL执行计划存在索引全扫描，索引快速全扫描？
然后观察如下链接
https://www.cnblogs.com/xqzt/p/4467038.html
疑问,index fast full scan是多快读,index full scan是顺序读？ 证明一下！
说明对比TIME_WAITED_MICRO 非次数，而是时间！
只是证明索引读取的方式执行之前执行脚本
SQL> host cat f.sql
alter system flush SHARED_POOL;
alter system flush BUFFER_CACHE;
SQL> select distinct sid from v$mystat; SID ---------- 32 SQL>select event,total_waits,time_waited,time_waited_micro from v$session_event where sid=32 and event like '%file%'; EVENT TOTAL_WAITS TIME_WAITED TIME_WAITED_MICRO ---------------------------- ----------- ----------- ----------------- Disk file operations I/O 136 2 22808 Data file init write 423 21 210902 control file sequential read 860 1 10284 control file parallel write 129 4 36160 log file switch completion 13 7 65160 log file sync 33 3 30294 db file sequential read 6278 53 528732 db file scattered read 731 19 188570 db file single write 43 1 7543 db file parallel read 8 2 24867 10 rows selected. set autotrace on select count(dcno) from b where b.b_type=2 and b.ss_date<sysdate-2; ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 15 | 904 (3)| 00:00:11 | | 1 | SORT AGGREGATE | | 1 | 15 | | | |* 2 | INDEX FAST FULL SCAN| B_IND | 510K| 7476K| 904 (3)| 00:00:11 | ------------------------------------------------------------------------------- 之前SQL前,执行f.sql脚本 EVENT TOTAL_WAITS TIME_WAITED TIME_WAITED_MICRO --------------------------------------------- ----------- ----------- ----------------- db file sequential read 7463 62 616266 db file scattered read 798 24 239660 db file single write 43 1 7543 db file parallel read 8 2 24867 ``````执行SQL后，再次查询 db file sequential read 7768 62 622263 差异3 db file scattered read 842 25 245366 差异5706 db file single write 43 1 7543 db file parallel read 8 2 24867 等待时间可以说明,快速全扫描是离散读,当然有少量的顺序读。 SQL>select /*+ index(c,c_ind) */ count(*) from c where c.available=0; -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 516 (1)| 00:00:07 | | 1 | SORT AGGREGATE | | 1 | 3 | | | |* 2 | INDEX FULL SCAN| C_IND | 99110 | 290K| 516 (1)| 00:00:07 | -------------------------------------------------------------------------- EVENT TOTAL_WAITS TIME_WAITED TIME_WAITED_MICRO --------------------------------------------- ----------- ----------- ----------------- db file sequential read 7858 62 624783 db file scattered read 843 25 245378 db file single write 43 1 7543 db file parallel read 12 4 40532 `````` db file sequential read 8156 63 632161 差异7378 db file scattered read 846 25 245446 差异68 db file single write 43 1 7543 db file parallel read 16 5 47315