delete后为何物理读直接是0

最新推荐文章于 2025-04-06 14:23:19 发布

最新推荐文章于 2025-04-06 14:23:19 发布 · 146 阅读

oracle体系结构专栏收录该内容

7 篇文章

订阅专栏

本文详细记录了使用SQL进行插入、删除、清空、重新插入数据时的性能表现及执行计划，揭示了在不同操作后执行`select count(*) from m`时，物理读取次数并非总是为0的原因，并提供了通过清空缓存解决该问题的方法。

SQL> conn hr

Enter password:

Error accessing PRODUCT_USER_PROFILE

Warning: Product user profile information not loaded!

You may need to run PUPBLD.SQL as SYSTEM

Connected.

SQL> set autot on;

SQL> show user

USER is "HR"

SQL> select count(*) from m;

COUNT(*)

----------

Execution Plan

----------------------------------------------------------

Plan hash value: 96562055

-------------------------------------------------------------------

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| M | 1 | 2 (0)| 00:00:01 |

-------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

421 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> begin

2 for i in 1..801000

3 loop

4 insert into m values(i,'bbk');

5 end loop ;

6 commit;

7 end;

8 /

PL/SQL procedure successfully completed.

SQL> select count(*) from m;

COUNT(*)

----------

801000

Execution Plan

----------------------------------------------------------

Plan hash value: 96562055

-------------------------------------------------------------------

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 673 (7)| 00:00:09 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| M | 5898K| 673 (7)| 00:00:09 |

-------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

3972 consistent gets

632 physical reads

119148 redo size

423 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> delete from m;

801000 rows deleted.

---

Execution Plan

-------------------------------------------------------

---Plan hash value: 3418523296 ---------

| T ime |

-------------------------------------------------------------------

| ------------------------------------------------------- |

| 0| 0 | DELETE STATEMENT | | 1 | 629 (1)0:00:08 |

---| 1 | DELETE | M | | ---------

| 2 | TABLE ACCESS FULL| M | 1 | 629 (1)

-------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

1684 recursive calls

830790 db block gets

3543 consistent gets

2017 physical reads

209831216 redo size

682 bytes sent via SQL*Net to client

581 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

801000 rows processed

SQL> commit;

Commit complete.

SQL> select count(*) from m;

COUNT(*)

----------

Execution Plan

----------------------------------------------------------

Plan hash value: 96562055

-------------------------------------------------------------------

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 629 (1)| 00:00:08 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| M | 1 | 629 (1)| 00:00:08 |

-------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

3853 consistent gets

0 physical reads

110492 redo size

421 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> truncate table m;

Table truncated.

SQL> select count(*) from m;

COUNT(*)

----------

Execution Plan

----------------------------------------------------------

Plan hash value: 96562055

-------------------------------------------------------------------

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| M | 1 | 2 (0)| 00:00:01 |

-------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

20 recursive calls

1 db block gets

9 consistent gets

0 physical reads

96 redo size

421 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

在delete后select count(*) from m;得到的物理读应该不是0啊？？

网友提示说应该是从内存中直接读了。清空buffer cache就可以了

Alter system flush buffer_cache;