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
Leave a Reply