delete后为何物理读直接是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(*)

----------

         0

 

 

Execution Plan

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

Plan hash value: 96562055

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

| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |

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

|   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

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

| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |

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

|   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| Id  | Operation          | Name | Rows  | Cost (%CPU)0:00:08 |

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

| 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(*)

----------

         0

 

Execution Plan

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

Plan hash value: 96562055

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

| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |

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

|   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(*)

----------

         0

 

Execution Plan

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

Plan hash value: 96562055

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

| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |

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

|   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;

 

 

 

 

 

 

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

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

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值