nologging的使用

来源：互联网发布：白金数据是谁杀了人编辑：程序博客网时间：2024/05/22 00:11

insert into "000".tbtcyang select * from dba_objects where rownum<=10000;

10000 rows created.

Elapsed: 00:00:00.14

Execution Plan
----------------------------------------------------------
Plan hash value: 2273262418

------------------------------------------------------------------------------------------------
| Id | Operation                        | Name        | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |             | 10000 | 2021K|   896   (5)| 00:00:06 |
|   1 | LOAD TABLE CONVENTIONAL         | TBTCYANG    |       |       |            |          |
|* 2 |   COUNT STOPKEY                  |             |       |       |            |          |
|   3 |    VIEW                          | DBA_OBJECTS | 88487 |    17M|   896   (5)| 00:00:06 |
|   4 |     UNION-ALL                    |             |       |       |            |          |
|* 5 |      TABLE ACCESS BY INDEX ROWID | SUM$        |     1 |    11 |     1   (0)| 00:00:01 |
|* 6 |       INDEX UNIQUE SCAN          | I_SUM$_1    |     1 |       |     0   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID | OBJ$        |     1 |    31 |     4   (0)| 00:00:01 |
|* 8 |       INDEX RANGE SCAN           | I_OBJ1      |     1 |       |     3   (0)| 00:00:01 |
|* 9 |      FILTER                      |             |       |       |            |          |
|* 10 |       HASH JOIN                  |             |   108K|    12M|   893   (5)| 00:00:06 |
| 11 |        TABLE ACCESS FULL         | USER$       |    89 | 1513 |     2   (0)| 00:00:01 |
|* 12 |        HASH JOIN                 |             |   108K|    11M|   888   (4)| 00:00:06 |
| 13 |         INDEX FULL SCAN          | I_USER2     |    89 | 2047 |     1   (0)| 00:00:01 |
|* 14 |         TABLE ACCESS FULL        | OBJ$        |   108K| 8824K|   884   (4)| 00:00:05 |
|* 15 |       TABLE ACCESS BY INDEX ROWID| IND$        |     1 |     9 |     2   (0)| 00:00:01 |
|* 16 |        INDEX UNIQUE SCAN         | I_IND1      |     1 |       |     1   (0)| 00:00:01 |
| 17 |       NESTED LOOPS               |             |     1 |    29 |     3   (0)| 00:00:01 |
|* 18 |        INDEX FULL SCAN           | I_USER2     |     1 |    20 |     1   (0)| 00:00:01 |
|* 19 |        INDEX RANGE SCAN          | I_OBJ4      |     1 |     9 |     2   (0)| 00:00:01 |
| 20 |      NESTED LOOPS                |             |     1 |    35 |     3   (0)| 00:00:01 |
| 21 |       TABLE ACCESS FULL          | LINK$       |     1 |    18 |     2   (0)| 00:00:01 |
| 22 |       TABLE ACCESS CLUSTER       | USER$       |     1 |    17 |     1   (0)| 00:00:01 |
|* 23 |        INDEX UNIQUE SCAN         | I_USER#     |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=10000)
   5 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
   6 - access("S"."OBJ#"=:B1)
   8 - access("EO"."OBJ#"=:B1)
   9 - 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"."TYPE#"=2 OR
              "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)
              AND ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
              "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
              "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
              "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
              "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR
              "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87)
              AND (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
              "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))
              OR EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE
              "O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND
              "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
              )))
10 - access("O"."SPARE3"="U"."USER#")
12 - access("O"."OWNER#"="U"."USER#")
14 - filter(BITAND("O"."FLAGS",128)=0 AND "O"."NAME"<>'_NEXT_OBJECT' AND
              "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL)
15 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
              "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
16 - access("I"."OBJ#"=:B1)
18 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_
              edition_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_
              edition_id')))
19 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
23 - access("L"."OWNER#"="U"."USER#")

Statistics
----------------------------------------------------------
        728 recursive calls
       1958 db block gets
        798 consistent gets
          1 physical reads
    1129628 redo size
       1119 bytes sent via SQL*Net to client
       1317 bytes received via SQL*Net from client
          4 SQL*Net roundtrips to/from client
          2 sorts (memory)
          0 sorts (disk)
      10000 rows processed

sys@SGERP5> alter table "000".tbtcyang nologging;

Table altered.

Elapsed: 00:00:00.01
sys@SGERP5> insert into "000".tbtcyang
2 select * from dba_objects where rownum<=10000;

10000 rows created.

Elapsed: 00:00:00.11

Execution Plan
----------------------------------------------------------
Plan hash value: 2273262418

Predicate Information (identified by operation id):
---------------------------------------------------

Statistics
----------------------------------------------------------
        225 recursive calls
       1186 db block gets
        623 consistent gets
          0 physical reads
    1080296 redo size
       1127 bytes sent via SQL*Net to client
       1317 bytes received via SQL*Net from client
          4 SQL*Net roundtrips to/from client
          7 sorts (memory)
          0 sorts (disk)
      10000 rows processed

sys@SGERP5> rollback;

Rollback complete.

Elapsed: 00:00:00.00
sys@SGERP5> select * from "000".tbtcyang;

10000 rows selected.

Elapsed: 00:00:00.23

Execution Plan
----------------------------------------------------------
Plan hash value: 3200461411

------------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          | 12956 | 2619K|    60   (4)| 00:00:01 |
|   1 | TABLE ACCESS FULL| TBTCYANG | 12956 | 2619K|    60   (4)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         29 recursive calls
          1 db block gets
       1068 consistent gets
          0 physical reads
        536 redo size              产生这个
    1075281 bytes sent via SQL*Net to client
       7846 bytes received via SQL*Net from client
        668 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      10000 rows processed

sys@SGERP5> rollback;

Rollback complete.

Elapsed: 00:00:00.00
sys@SGERP5> select * from "000".tbtcyang;

10000 rows selected.

Elapsed: 00:00:00.22

Execution Plan
----------------------------------------------------------
Plan hash value: 3200461411

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        986 consistent gets
          0 physical reads
          0 redo size
    1075281 bytes sent via SQL*Net to client
       7846 bytes received via SQL*Net from client
        668 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      10000 rows processed

sys@SGERP5> set autotrace traceonly;
sys@SGERP5> insert /*+ append */ into"000".tbtcyang select * from dba_objects where rownum<=10000;

10000 rows created.

Elapsed: 00:00:00.12

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel

SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
         81 recursive calls
        259 db block gets
        367 consistent gets
          0 physical reads
       9944 redo size
       1116 bytes sent via SQL*Net to client
       1331 bytes received via SQL*Net from client
          4 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
      10000 rows processed

sys@SGERP5> rollback;

Rollback complete.

Elapsed: 00:00:00.00

对比没有append:
sys@SGERP5> insert into "000".tbtcyang select * from dba_objects where rownum<=10000
2 ;

10000 rows created.

Elapsed: 00:00:00.12

Execution Plan
----------------------------------------------------------
Plan hash value: 2273262418

Predicate Information (identified by operation id):
---------------------------------------------------

Statistics
----------------------------------------------------------
         15 recursive calls
        997 db block gets
        614 consistent gets
          0 physical reads
    1067528 redo size
       1132 bytes sent via SQL*Net to client
       1324 bytes received via SQL*Net from client
          4 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
      10000 rows processed

sys@SGERP5>