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;