优化高手都需要深入的访问路径(ACCESS PATH)

17 篇文章 1 订阅

访问路径:通过哪种方式对数据进行访问. 全表扫描、索引扫描或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次数的时候,我们只需要算个大概即可。
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值