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;