梦想有多远

nologging 和append

今天没事看测试了下表在nologging和logging模式下,使用APPEND产生REDO多少?注数据库是归档模式,非归档模式有时间我在试下.

SQL> set autotrace traceonly;
SQL> set time on;
15:43:35 SQL> insert into t2 select * from dba_objects; 这里表T2是处于nologging模式的..

51534 rows created.
Execution Plan
———————————————————-
Plan hash value: 2960989055

——————————————————————————–
————–

——————————————————————————–
————–

| 0 | INSERT STATEMENT | | 49398 | 8538K| 186 (
4)| 00:00:03 |

| 1 | VIEW | DBA_OBJECTS | 49398 | 8538K| 186 (
4)| 00:00:03 |

| 2 | UNION-ALL | | | |
| |

|* 3 | FILTER | | | |
| |

|* 4 | HASH JOIN | | 52766 | 4843K| 183 (
4)| 00:00:03 |

| 5 | TABLE ACCESS FULL | USER$ | 66 | 990 | 3 (
0)| 00:00:01 |

|* 6 | TABLE ACCESS FULL | OBJ$ | 52766 | 4070K| 178 (
3)| 00:00:03 |

|* 7 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 8 | 2 (
0)| 00:00:01 |

|* 8 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (
0)| 00:00:01 |

| 9 | NESTED LOOPS | | 1 | 103 | 3 (
0)| 00:00:01 |

| 10 | TABLE ACCESS FULL | LINK$ | 1 | 88 | 2 (
0)| 00:00:01 |

| 11 | TABLE ACCESS CLUSTER | USER$ | 1 | 15 | 1 (
0)| 00:00:01 |

|* 12 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (
0)| 00:00:01 |

——————————————————————————–
————–
Predicate Information (identified by operation id):
—————————————————

3 - filter(”O”.”TYPE#”<>1 AND “O”.”TYPE#”<>10 OR “O”.”TYPE#”=1 AND (SELECT 1
FROM

“SYS”.”IND$” “I” WHERE “I”.”OBJ#”=:B1 AND (”I”.”TYPE#”=1 OR “I”.”T
YPE#”=2 OR

“I”.”TYPE#”=3 OR “I”.”TYPE#”=4 OR “I”.”TYPE#”=6 OR “I”.”TYPE#”=7 O
R “I”.”TYPE#”=9))=1)

4 - access(”O”.”OWNER#”=”U”.”USER#”)
6 - filter(”O”.”NAME”<>’_NEXT_OBJECT’ AND “O”.”NAME”<>’_default_auditing_opti
ons_’

AND “O”.”LINKNAME” IS NULL)
7 - filter(”I”.”TYPE#”=1 OR “I”.”TYPE#”=2 OR “I”.”TYPE#”=3 OR “I”.”TYPE#”=4 O
R

              “I”.”TYPE#”=6 OR “I”.”TYPE#”=7 OR “I”.”TYPE#”=9)
   8 - access(”I”.”OBJ#”=:B1)
12 - access(”L”.”OWNER#”=”U”.”USER#”)
Statistics
———————————————————-
        464 recursive calls
       7300 db block gets
      10588 consistent gets
        739 physical reads
    5794872 redo size
        666 bytes sent via SQL*Net to client
        576 bytes received via SQL*Net from client
          4 SQL*Net roundtrips to/from client
         11 sorts (memory)
          0 sorts (disk)
      51534 rows processed

15:44:01 SQL> insert /*+ APPEND */ into t2 select * from dba_objects;

51534 rows created.
Execution Plan
———————————————————-
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel
SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
———————————————————-
        472 recursive calls
       1129 db block gets
       9274 consistent gets
        730 physical reads
      28496 redo size
        660 bytes sent via SQL*Net to client
        590 bytes received via SQL*Net from client
          4 SQL*Net roundtrips to/from client
         11 sorts (memory)
          0 sorts (disk)
      51534 rows processed

15:45:25 SQL> alter table t2 logging;

Table altered.

15:51:11 SQL> insert into t2 select * from dba_objects;