Oracle 各种删除操作对空间返还的说明

SQL> insert into   abc select * from dba_objects;
insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;
86977 rows created.

Elapsed: 00:00:00.33
SQL> 
86977 rows created.

Elapsed: 00:00:00.16
SQL> 

Commit complete.

Elapsed: 00:00:00.01
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;
173954 rows created.

Elapsed: 00:00:00.26
SQL> 

Commit complete.

Elapsed: 00:00:00.00
SQL> 
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;
347908 rows created.

Elapsed: 00:00:00.40
SQL> 

Commit complete.

Elapsed: 00:00:00.01
SQL> 
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;

695816 rows created.

Elapsed: 00:00:00.87
SQL> 
Commit complete.

Elapsed: 00:00:00.00
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;

1391632 rows created.

Elapsed: 00:00:01.40
SQL> 
Commit complete.

Elapsed: 00:00:00.00
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;

2783264 rows created.

Elapsed: 00:00:02.63
SQL> 
Commit complete.

Elapsed: 00:00:00.00
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;

5566528 rows created.

Elapsed: 00:00:05.40
SQL> 
Commit complete.

Elapsed: 00:00:00.01
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;

11133056 rows created.

Elapsed: 00:00:10.87
SQL> 
Commit complete.

Elapsed: 00:00:00.00
SQL> insert /*+ append parallel */ into abc select /*+ parallel */ * from abc;
commit;

22266112 rows created.

Elapsed: 00:00:19.88
SQL> 
Commit complete.

Elapsed: 00:00:00.02
SQL> 
SQL> 
SQL> 
SQL> desc dba_objects;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER						    VARCHAR2(30)
 OBJECT_NAME					    VARCHAR2(128)
 SUBOBJECT_NAME 				    VARCHAR2(30)
 OBJECT_ID					    NUMBER
 DATA_OBJECT_ID 				    NUMBER
 OBJECT_TYPE					    VARCHAR2(19)
 CREATED					    DATE
 LAST_DDL_TIME					    DATE
 TIMESTAMP					    VARCHAR2(19)
 STATUS 					    VARCHAR2(7)
 TEMPORARY					    VARCHAR2(1)
 GENERATED					    VARCHAR2(1)
 SECONDARY					    VARCHAR2(1)
 NAMESPACE					    NUMBER
 EDITION_NAME					    VARCHAR2(30)






SQL> set linesize 300 pagesize 2000 
SQL> 
SQL> select count(*) from abc;

  COUNT(*)
----------
  44532224

Elapsed: 00:00:04.26

Execution Plan
----------------------------------------------------------
Plan hash value: 1045519631

-------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Cost (%CPU)| Time	  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |   172K  (1)| 00:34:34 |
|   1 |  SORT AGGREGATE    |	  |	1 |	       |	  |
|   2 |   TABLE ACCESS FULL| ABC  |    44M|   172K  (1)| 00:34:34 |
-------------------------------------------------------------------


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
     635793  consistent gets
     635777  physical reads
	  0  redo size
	529  bytes sent via SQL*Net to client
	519  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

SQL> select /*+ parallel */ count(*) from abc;

  COUNT(*)
----------
  44532224

Elapsed: 00:00:03.19

Execution Plan
----------------------------------------------------------
Plan hash value: 2285262752

--------------------------------------------------------------------------------------------------------
| Id  | Operation	       | Name	  | Rows  | Cost (%CPU)| Time	  |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |	  |	1 | 31976   (1)| 00:06:24 |	   |	  |	       |
|   1 |  SORT AGGREGATE        |	  |	1 |	       |	  |	   |	  |	       |
|   2 |   PX COORDINATOR       |	  |	  |	       |	  |	   |	  |	       |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |	1 |	       |	  |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |	  |	1 |	       |	  |  Q1,00 | PCWP |	       |
|   5 |      PX BLOCK ITERATOR |	  |    44M| 31976   (1)| 00:06:24 |  Q1,00 | PCWC |	       |
|   6 |       TABLE ACCESS FULL| ABC	  |    44M| 31976   (1)| 00:06:24 |  Q1,00 | PCWP |	       |
--------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing


Statistics
----------------------------------------------------------
	 19  recursive calls
	  0  db block gets
     636281  consistent gets
     635777  physical reads
	  0  redo size
	529  bytes sent via SQL*Net to client
	519  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

22266112 rows inserted in 19.88s. PC server virtual box , about 150MB/s disk space written. physical read 635777 blocks( 4.85g) in 3.19s, 1.52GB/s.

[oracle@ocp ~]$ cat /proc/cpuinfo 
processor	: 0
vendor_id	: GenuineIntel
cpu family	: 6
model		: 158
model name	: Intel(R) Core(TM) i7-7700 CPU @ 3.60GHz
stepping	: 9
cpu MHz		: 3599.883
cache size	: 8192 KB
physical id	: 0
siblings	: 3
core id		: 0
cpu cores	: 3
apicid		: 0
initial apicid	: 0
fpu		: yes
fpu_exception	: yes
cpuid level	: 22
wp		: yes
flags		: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx rdtscp lm constant_tsc rep_good nopl xtopology nonstop_tsc pni pclmulqdq ssse3 cx16 pcid sse4_1 sse4_2 movbe popcnt aes xsave avx rdrand lahf_lm abm 3dnowprefetch fsgsbase avx2 invpcid rdseed
bogomips	: 7199.76
clflush size	: 64
cache_alignment	: 64
address sizes	: 39 bits physical, 48 bits virtual
power management:

processor	: 1
vendor_id	: GenuineIntel
cpu family	: 6
model		: 158
model name	: Intel(R) Core(TM) i7-7700 CPU @ 3.60GHz
stepping	: 9
cpu MHz		: 3599.883
cache size	: 8192 KB
physical id	: 0
siblings	: 3
core id		: 1
cpu cores	: 3
apicid		: 1
initial apicid	: 1
fpu		: yes
fpu_exception	: yes
cpuid level	: 22
wp		: yes
flags		: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx rdtscp lm constant_tsc rep_good nopl xtopology nonstop_tsc pni pclmulqdq ssse3 cx16 pcid sse4_1 sse4_2 movbe popcnt aes xsave avx rdrand lahf_lm abm 3dnowprefetch fsgsbase avx2 invpcid rdseed
bogomips	: 7199.76
clflush size	: 64
cache_alignment	: 64
address sizes	: 39 bits physical, 48 bits virtual
power management:

processor	: 2
vendor_id	: GenuineIntel
cpu family	: 6
model		: 158
model name	: Intel(R) Core(TM) i7-7700 CPU @ 3.60GHz
stepping	: 9
cpu MHz		: 3599.883
cache size	: 8192 KB
physical id	: 0
siblings	: 3
core id		: 2
cpu cores	: 3
apicid		: 2
initial apicid	: 2
fpu		: yes
fpu_exception	: yes
cpuid level	: 22
wp		: yes
flags		: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx rdtscp lm constant_tsc rep_good nopl xtopology nonstop_tsc pni pclmulqdq ssse3 cx16 pcid sse4_1 sse4_2 movbe popcnt aes xsave avx rdrand lahf_lm abm 3dnowprefetch fsgsbase avx2 invpcid rdseed
bogomips	: 7199.76
clflush size	: 64
cache_alignment	: 64
address sizes	: 39 bits physical, 48 bits virtual
power management:

[oracle@ocp ~]$ cat /proc/meminfo 
MemTotal:       15238052 kB
MemFree:         1185404 kB
Buffers:           36968 kB
Cached:         13501972 kB
SwapCached:            0 kB
Active:          9674364 kB
Inactive:        4176152 kB
Active(anon):    9564472 kB
Inactive(anon):  1782020 kB
Active(file):     109892 kB
Inactive(file):  2394132 kB
Unevictable:           0 kB
Mlocked:               0 kB
SwapTotal:       4128764 kB
SwapFree:        4128764 kB
Dirty:                 4 kB
Writeback:             0 kB
AnonPages:        311584 kB
Mapped:           439612 kB
Shmem:          11034924 kB
Slab:              67436 kB
SReclaimable:      48416 kB
SUnreclaim:        19020 kB
KernelStack:        2192 kB
PageTables:        28060 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:    11747788 kB
Committed_AS:   12092868 kB
VmallocTotal:   34359738367 kB
VmallocUsed:      107616 kB
VmallocChunk:   34359622135 kB
HardwareCorrupted:     0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:       13248 kB
DirectMap2M:    15548416 kB