eric> set autotrace on eric> -- create an empty table from emp eric> create table emp2 as select 2 * from emp where rownum < 1; Table created. Elapsed: 00:00:00.04 eric> -- round one of tests, where parsing overhead exists eric> select empno,ename from emp2; no rows selected Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 2941272003 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMP2 | 1 | 20 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 44 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 336 bytes sent via SQL*Net to client 405 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed eric> insert into emp2 select * from emp; 14 rows created. Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------------- - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- - | 0 | INSERT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 | | 1 | LOAD TABLE CONVENTIONAL | EMP2 | | | | | | 2 | TABLE ACCESS FULL | EMP | 14 | 1218 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------- - Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 129 recursive calls 19 db block gets 107 consistent gets 0 physical reads 1672 redo size 677 bytes sent via SQL*Net to client 603 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 14 rows processed eric> select empno,ename from emp2; EMPNO ENAME ---------- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER 14 rows selected. Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 2941272003 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 280 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMP2 | 14 | 280 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 14 consistent gets 0 physical reads 0 redo size 1438 bytes sent via SQL*Net to client 482 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed eric> delete from emp2; 14 rows deleted. Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 2796997851 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | DELETE STATEMENT | | 1 | 3 (0)| 00:00:01 | | 1 | DELETE | EMP2 | | | | | 2 | TABLE ACCESS FULL| EMP2 | 1 | 3 (0)| 00:00:01 | ------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 4 recursive calls 15 db block gets 15 consistent gets 0 physical reads 4036 redo size 677 bytes sent via SQL*Net to client 585 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 14 rows processed eric> -- round two, just the statements eric> insert into emp2 select * from emp; 14 rows created. Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------------- - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- - | 0 | INSERT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 | | 1 | LOAD TABLE CONVENTIONAL | EMP2 | | | | | | 2 | TABLE ACCESS FULL | EMP | 14 | 1218 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------- - Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 0 recursive calls 3 db block gets 8 consistent gets 0 physical reads 864 redo size 677 bytes sent via SQL*Net to client 603 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 14 rows processed eric> select empno,ename from emp2; EMPNO ENAME ---------- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER 14 rows selected. Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 2941272003 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 280 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMP2 | 14 | 280 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 14 consistent gets 0 physical reads 0 redo size 1438 bytes sent via SQL*Net to client 482 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed eric> delete from emp2; 14 rows deleted. Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 2796997851 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | DELETE STATEMENT | | 1 | 3 (0)| 00:00:01 | | 1 | DELETE | EMP2 | | | | | 2 | TABLE ACCESS FULL| EMP2 | 1 | 3 (0)| 00:00:01 | ------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 0 recursive calls 15 db block gets 7 consistent gets 0 physical reads 4036 redo size 677 bytes sent via SQL*Net to client 585 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 14 rows processed eric> commit; Commit complete. Elapsed: 00:00:00.04 eric> select empno,ename from emp2; no rows selected Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 2941272003 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMP2 | 1 | 20 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 336 bytes sent via SQL*Net to client 405 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed eric> spool off