一、说明1、IFS (index full scan) 单块读,IFFS(index fast full scan)多块读。2、在同时对表中某一列进行全扫描的时候看,多块读的速度明显要比单块读要快,性能要更
一、说明
1、IFS (index full scan) 单块读,IFFS(index fast full scan)多块读。
2、在同时对表中某一列进行全扫描的时候看,多块读的速度明显要比单块读要快,性能要更好。
3、FTS(full table scan)和IFFS(index fast full scan)都为多块读。
4、IFFS(index fast full scan)为多块读,可并行,非排序
5、IFS(index full scan)为单块读、有序。
二、测试过程
sql> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.17
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.30
SQL> select count(object_id) from tt;
COUNT(OBJECT_ID)
----------------
5524288
Elapsed: 00:00:05.72
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.17
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.07
SQL> select count(object_id) from tt;
COUNT(OBJECT_ID)
----------------
5524288
Elapsed: 00:00:01.35
SQL> explain plan for select count(object_id) from tt;
Explained.
Elapsed: 00:00:00.07
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3277332215
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 12269 (1)| 00:02:28 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INDEX FULL SCAN| IDX_OBJECT_ID | 2762K| 13M| 12269 (1)| 00:02:28 |
----------------------------------------------------------------------------------
9 rows selected.
Elapsed: 00:00:00.33
SQL> explain plan for select count(object_id) from tt;
Explained.
Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1131838604
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3335 (1)| 00:00:41 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 2762K| 13M| 3335 (1)| 00:00:41 |
---------------------------------------------------------------------------------------
9 rows selected.
Elapsed: 00:00:00.01
SQL>
--结束END--
本文标题: Oracle 11g IFS VS IFFS 性能对比
本文链接: https://www.lsjlt.com/news/36661.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-13
2024-05-13
2024-05-13
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0