Script started on Mon 26 Feb 2007 07:57:38 AM EST ]0;oracle@ebony:~/r+d/insert_costs[oracle@ebony insert_costs]$ . test_script.txt SQL*Plus: Release 9.2.0.8.0 - Production on Mon Feb 26 07:57:45 2007 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production With the Partitioning option JServer Release 9.2.0.8.0 - Production SQL> shutdown Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 286855644 bytes Fixed Size 451036 bytes Variable Size 167772160 bytes Database Buffers 117440512 bytes Redo Buffers 1191936 bytes Database mounted. SQL> alter database noarchivelog; Database altered. SQL> alter database open; Database altered. SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production With the Partitioning option JServer Release 9.2.0.8.0 - Production SQL*Plus: Release 9.2.0.8.0 - Production on Mon Feb 26 07:57:57 2007 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production With the Partitioning option JServer Release 9.2.0.8.0 - Production SQL> SQL> create table perm as select * from all_objects where 1=0; Table created. SQL> create global temporary table temp on commit preserve rows as select * from all_objects where 1=0; Table created. SQL> SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production With the Partitioning option JServer Release 9.2.0.8.0 - Production SQL*Plus: Release 9.2.0.8.0 - Production on Mon Feb 26 07:57:57 2007 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production With the Partitioning option JServer Release 9.2.0.8.0 - Production SQL> SQL> select name,log_mode from v$database; NAME LOG_MODE --------- ------------ E9IUTF8 NOARCHIVELOG SQL> show parameter db_block_size NAME TYPE ------------------------------------ -------------------------------- VALUE ------------------------------ db_block_size integer 8192 SQL> SQL> set autotrace on statistics SQL> SQL> -- regular table logging SQL> alter table perm logging; Table altered. SQL> insert into perm select * from all_objects; 3916 rows created. Statistics ---------------------------------------------------------- 791 recursive calls 386 db block gets 26897 consistent gets 29 physical reads 400344 redo size 616 bytes sent via SQL*Net to client 542 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 3916 rows processed SQL> truncate table perm; Table truncated. SQL> insert /*+ append */ into perm select * from all_objects; 3916 rows created. Statistics ---------------------------------------------------------- 259 recursive calls 87 db block gets 26654 consistent gets 0 physical reads 11940 redo size 603 bytes sent via SQL*Net to client 556 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 3916 rows processed SQL> truncate table perm; Table truncated. SQL> SQL> -- regular table nologging SQL> alter table perm nologging; Table altered. SQL> insert into perm select * from all_objects; 3916 rows created. Statistics ---------------------------------------------------------- 459 recursive calls 382 db block gets 26741 consistent gets 0 physical reads 400228 redo size 619 bytes sent via SQL*Net to client 542 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 3916 rows processed SQL> truncate table perm; Table truncated. SQL> insert /*+ append */ into perm select * from all_objects; 3916 rows created. Statistics ---------------------------------------------------------- 259 recursive calls 87 db block gets 26654 consistent gets 0 physical reads 11956 redo size 603 bytes sent via SQL*Net to client 556 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 3916 rows processed SQL> truncate table perm; Table truncated. SQL> SQL> -- temporary table SQL> insert into temp select * from all_objects; 3916 rows created. Statistics ---------------------------------------------------------- 9 recursive calls 281 db block gets 26631 consistent gets 0 physical reads 19212 redo size 620 bytes sent via SQL*Net to client 542 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 3916 rows processed SQL> truncate table temp; Table truncated. SQL> insert /*+ append */ into temp select * from all_objects; 3916 rows created. Statistics ---------------------------------------------------------- 7 recursive calls 3 db block gets 26582 consistent gets 0 physical reads 224 redo size 604 bytes sent via SQL*Net to client 556 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 3916 rows processed SQL> truncate table temp; Table truncated. SQL> SQL> set autotrace off SQL> SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production With the Partitioning option JServer Release 9.2.0.8.0 - Production SQL*Plus: Release 9.2.0.8.0 - Production on Mon Feb 26 07:58:03 2007 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production With the Partitioning option JServer Release 9.2.0.8.0 - Production SQL> shutdown Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 286855644 bytes Fixed Size 451036 bytes Variable Size 167772160 bytes Database Buffers 117440512 bytes Redo Buffers 1191936 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production With the Partitioning option JServer Release 9.2.0.8.0 - Production SQL*Plus: Release 9.2.0.8.0 - Production on Mon Feb 26 07:58:15 2007 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production With the Partitioning option JServer Release 9.2.0.8.0 - Production SQL> SQL> select name,log_mode from v$database; NAME LOG_MODE --------- ------------ E9IUTF8 ARCHIVELOG SQL> show parameter db_block_size NAME TYPE ------------------------------------ -------------------------------- VALUE ------------------------------ db_block_size integer 8192 SQL> SQL> set autotrace on statistics SQL> SQL> -- regular table logging SQL> alter table perm logging; Table altered. SQL> insert into perm select * from all_objects; 3916 rows created. Statistics ---------------------------------------------------------- 1572 recursive calls 382 db block gets 27126 consistent gets 47 physical reads 400224 redo size 618 bytes sent via SQL*Net to client 542 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 23 sorts (memory) 0 sorts (disk) 3916 rows processed SQL> truncate table perm; Table truncated. SQL> insert /*+ append */ into perm select * from all_objects; 3916 rows created. Statistics ---------------------------------------------------------- 259 recursive calls 87 db block gets 26654 consistent gets 0 physical reads 408452 redo size 605 bytes sent via SQL*Net to client 556 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 3916 rows processed SQL> truncate table perm; Table truncated. SQL> SQL> -- regular table nologging SQL> alter table perm nologging; Table altered. SQL> insert into perm select * from all_objects; 3916 rows created. Statistics ---------------------------------------------------------- 459 recursive calls 382 db block gets 26741 consistent gets 1 physical reads 400244 redo size 621 bytes sent via SQL*Net to client 542 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 3916 rows processed SQL> truncate table perm; Table truncated. SQL> insert /*+ append */ into perm select * from all_objects; 3916 rows created. Statistics ---------------------------------------------------------- 259 recursive calls 87 db block gets 26654 consistent gets 0 physical reads 11956 redo size 606 bytes sent via SQL*Net to client 556 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 3916 rows processed SQL> truncate table perm; Table truncated. SQL> SQL> -- temporary table SQL> insert into temp select * from all_objects; 3916 rows created. Statistics ---------------------------------------------------------- 232 recursive calls 281 db block gets 26674 consistent gets 0 physical reads 19212 redo size 623 bytes sent via SQL*Net to client 542 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 3916 rows processed SQL> truncate table temp; Table truncated. SQL> insert /*+ append */ into temp select * from all_objects; 3916 rows created. Statistics ---------------------------------------------------------- 7 recursive calls 3 db block gets 26582 consistent gets 0 physical reads 224 redo size 608 bytes sent via SQL*Net to client 556 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 3916 rows processed SQL> truncate table temp; Table truncated. SQL> SQL> set autotrace off SQL> SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production With the Partitioning option JServer Release 9.2.0.8.0 - Production SQL*Plus: Release 9.2.0.8.0 - Production on Mon Feb 26 07:58:21 2007 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production With the Partitioning option JServer Release 9.2.0.8.0 - Production SQL> SQL> drop table perm; Table dropped. SQL> drop table temp; Table dropped. SQL> SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production With the Partitioning option JServer Release 9.2.0.8.0 - Production ]0;oracle@ebony:~/r+d/insert_costs[oracle@ebony insert_costs]$ exit Script done on Mon 26 Feb 2007 07:58:27 AM EST