访问路径:通过哪种方式对数据进行访问. 全表扫描、索引扫描或ROWID获取数据。
常见访问路径
1.TABLE ACCESS FULL(全表扫描)
- 多块读
- HINT:FULL(表名/别名)
- 等待事件为db file scattered read.
- 若为并行全表扫描,等待事件为direct path read.
2.TABLE ACCESS BY USER ROWID
- 直接用ROWID获取数据,单块读。
- 该访问路径在Oracle所有的访问路径中性能是最好的。
3.TABLE ACCESS BY ROWID RANGE
- 表示ROWID 范围扫描,多块读。
- 因为同一个块里面的ROWID是连续的,同一个EXTENT里面的ROWID也是连续的,所以可以多块读。
4.TABLE ACCESS BY INDEX ROWID
- 回表,单块读
5.INDEX UNIQUE SCAN
- 索引唯一扫描,单块读。
- 对唯一索引或者主键列进行等值查询,就会走INDEX UNIQUE SCAN
- 其性能仅次于TABLE ACCESS BY USER ROWID
scott@orclpdb1:orclcdb> set autot trace
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> select * from emp where empno=7369;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7369)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
961 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
scott@orclpdb1:orclcdb>
6.INDEX RANGE SCAN
- 索引范围扫描,单块读,返回的数据是有序的,
- HINT:INDEX(表名/别名 索引名)
- 对唯一索引或者主键索引进行范围查找(INDEX RANGE SCAN,等待事件db file sequential read)。对非唯一索引进行等值查找.
scott@orclpdb1:orclcdb> select * from test where object_id=100;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3297604684
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 132 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 1 | 132 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=100)
Statistics
----------------------------------------------------------
136 recursive calls
0 db block gets
408 consistent gets
33 physical reads
0 redo size
2686 bytes sent via SQL*Net to client
621 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
74 sorts (memory)
0 sorts (disk)
1 rows processed
scott@orclpdb1:orclcdb>
索引范围扫描默认是从索引中最左边的叶子块开始,然后往右边的叶子块扫描(从小到大),当检查到不匹配数据的时候,就停止扫描。
scott@orclpdb1:orclcdb> select * from test where object_id<100 order by object_id desc;
98 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1069979465
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 94 | 12408 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST | 94 | 12408 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| IDX_ID | 94 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
18 consistent gets
1 physical reads
0 redo size
14379 bytes sent via SQL*Net to client
711 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
98 rows processed
scott@orclpdb1:orclcdb>
INDEX RANGE SCAN DESCENDING 表示索引降序范围扫描,从右往左扫描,返回的数据是降序显示的。
7.INDEX SKIP SCAN
- 索引跳跃扫描,单块读。
- 返回的数据是有序的
- HINT:INDEX_SS(表名/别名 索引名)
- 当组合索引的引导列(第一个列)没有在where条件中,并且组合索引的引导列/前几个列的基数很低,where过滤条件对组合索引中非引导列进行过滤的时候就会发生索引跳跃扫描。
- 等待事件为 db file sequential read.
scott@orclpdb1:orclcdb> create index idx_ownerid on test(owner,object_id);
Index created.
scott@orclpdb1:orclcdb> drop index idx_id;
Index dropped.
scott@orclpdb1:orclcdb> select * from test where object_id<100;
98 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 607061290
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 94 | 12408 | 42 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 94 | 12408 | 42 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_OWNERID | 94 | | 39 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<100)
filter("OBJECT_ID"<100)
Statistics
----------------------------------------------------------
59 recursive calls
0 db block gets
92 consistent gets
17 physical reads
0 redo size
14379 bytes sent via SQL*Net to client
466 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
98 rows processed
scott@orclpdb1:orclcdb>
8.INDEX FULL SCAN
- INDEX FULL SCAN 表示索引全扫描,单块读,返回的数据是有序的。
- HINT:INDEX(表名/别名 索引名)
- 索引全扫描会扫描索引中所有的叶子块(从左往右扫描)
- 若索引很大,会产生严重的性能问题(因为是单块读)
- 等待事件: db file sequential read
scott@orclpdb1:orclcdb> select * from test order by object_id,owner;
73516 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2007178810
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 73516 | 9476K| | 2570 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 73516 | 9476K| 13M| 2570 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST | 73516 | 9476K| | 399 (1)| 00:00:01 |
-----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1430 consistent gets
0 physical reads
0 redo size
4868592 bytes sent via SQL*Net to client
54537 bytes received via SQL*Net from client
4903 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
73516 rows processed
scott@orclpdb1:orclcdb> create index idx_idowner on test(object_id,owner,0);
Index created.
scott@orclpdb1:orclcdb> select * from test order by object_id,owner;
73516 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3870803568
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 73516 | 9476K| 1865 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 73516 | 9476K| 1865 (1)| 00:00:01 |
| 2 | INDEX FULL SCAN | IDX_IDOWNER | 73516 | | 234 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
11531 consistent gets
232 physical reads
0 redo size
4868592 bytes sent via SQL*Net to client
54316 bytes received via SQL*Net from client
4903 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
73516 rows processed
scott@orclpdb1:orclcdb>
9.INDEX FAST FULL SCAN
- 索引快速全扫描,多块读。
- HINT:INDEX_FFS(表名/别名 索引名)
- 等待事件 db file scattered read
scott@orclpdb1:orclcdb> drop index idx_ownername;
Index dropped.
scott@orclpdb1:orclcdb> create index idx_ownername on test(owner,object_name,0);
Index created.
scott@orclpdb1:orclcdb> select owner,object_name from test;
73516 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3888663772
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 73516 | 2871K| 146 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| IDX_OWNERNAME | 73516 | 2871K| 146 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5405 consistent gets
537 physical reads
0 redo size
3773365 bytes sent via SQL*Net to client
54307 bytes received via SQL*Net from client
4903 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
73516 rows processed
scott@orclpdb1:orclcdb> create index idx_id on test(object_id);
Index created.
scott@orclpdb1:orclcdb> select object_name from test where object_id<100;
98 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3297604684
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 94 | 3760 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 94 | 3760 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ID | 94 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
18 consistent gets
1 physical reads
0 redo size
2851 bytes sent via SQL*Net to client
476 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
98 rows processed
scott@orclpdb1:orclcdb> create index idx_idname on test(object_id,object_name);
Index created.
scott@orclpdb1:orclcdb> select object_name from test where object_id<100;
98 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3678957952
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 94 | 3760 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_IDNAME | 94 | 3760 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"<100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
1 physical reads
0 redo size
2851 bytes sent via SQL*Net to client
476 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
98 rows processed
scott@orclpdb1:orclcdb> select object_name from test where object_id>100;
73415 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 252646278
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 73420 | 2867K| 142 (1)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_IDNAME | 73420 | 2867K| 142 (1)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID">100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5386 consistent gets
513 physical reads
0 redo size
3530801 bytes sent via SQL*Net to client
54686 bytes received via SQL*Net from client
4896 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
73415 rows processed
scott@orclpdb1:orclcdb>
10.INDEX FULL SCAN(MIN/MAX)
- 索引最小、最大值扫描、单块读
- INDEX FULL SCAN(MIN/MAX) 只会访问“索引高度”个索引块,其性能与INDEX UNIQUE SCAN一样,其性能仅次于TABLE ACCESS BY USER ROWID
11.MAT_VIEW REWRITE ACCESS FULL
- MAT_VIEW REWRITE ACCESS FULL 表示物化视图全表扫描、多块读。
- 因为物化视图本质上也是一个表,所以其扫描方式与全表扫描一样。
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> create materialized view test_mv build immediate enable query rewrite
2 as select object_id,object_name from test;
Materialized view created.
scott@orclpdb1:orclcdb> select object_id,object_name from test;
73516 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1627509066
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 73516 | 2799K| 129 (1)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| TEST_MV | 73516 | 2799K| 129 (1)| 00:00:01 |
----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
36 recursive calls
12 db block gets
5370 consistent gets
453 physical reads
1980 redo size
4184006 bytes sent via SQL*Net to client
54311 bytes received via SQL*Net from client
4903 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
73516 rows processed
scott@orclpdb1:orclcdb>
因为物化视图TEST_MV 已经包含查询需要的字段,所以该SQL会直接访问物化视图TEST_MV.
2单块单读与多块读
- 单块读:从磁盘1次读取1个块到buffer cache.
- 多块读:从磁盘1次读取多个块到buffer cache.
- 如果数据块都已经缓存在buffer cache中,那就不需要物理I/O了,没有物理I/O也就不存在单块读与多块读。
- 绝大数的平台,一次I/O最多只能读取或者写入1MB数据。
- Oracle的块大小默认是8K,那么一次I/O最多只能写入128个块到磁盘,最多只能读取128个块到buffer cache.
- 在判断哪个访问路径性能好的时候,通常是估算每次访问的I/O次数,谁的I/O次数少,谁的性能就好。在估算I/O次数的时候,我们只需要算个大概即可。