通过append hint来插入数据，演示它和普通插入数据的性能比较。

灰太狼 2022-03-29 06:08 188阅读 0赞

**一、实验说明**

操作系统：redhat 5.4

数据库:oracle 11g r2

**二、在Noarchivelog模式中的比较：**

1 SQL> conn jack/jack
     2 Connected.
     3 SQL> drop table t purge;
     4 
     5 Table dropped.
     6 
     7 SQL> conn /as sysdba
     8 Connected.
     9 SQL> archive log list;
    10 Database log mode           No Archive Mode              --Noarchivelog模式 11 Automatic archival           Disabled
    12 Archive destination           USE_DB_RECOVERY_FILE_DEST
    13 Oldest online log sequence     5
    14 Current log sequence           7
    15 SQL> conn jack/jack
    16 Connected.
    17 SQL> create table t as select * from dba_objects;
    18 
    19 Table created.
    20 
    21 SQL> set autotrace traceonly;
    22 SQL> insert into t select * from t;
    23 
    24 72544 rows created.
    25 
    26 
    27 Execution Plan
    28 ----------------------------------------------------------
    29 Plan hash value: 1601196873
    30 
    31 ---------------------------------------------------------------------------------
    32 | Id  | Operation         | Name | Rows    | Bytes | Cost (%CPU)| Time    |
    33 ---------------------------------------------------------------------------------
    34 |   0 | INSERT STATEMENT     |    |   285K|    56M|   590   (1)| 00:00:08 |
    35 |   1 |  LOAD TABLE CONVENTIONAL | T    |    |    |         |        |
    36 |   2 |   TABLE ACCESS FULL     | T    |   285K|    56M|   590   (1)| 00:00:08 |
    37 ---------------------------------------------------------------------------------
    38 
    39 Note
    40 -----
    41    - dynamic sampling used for this statement (level=2)
    42 
    43 
    44 Statistics
    45 ----------------------------------------------------------
    46     586      recursive calls
    47     9343     db block gets
    48     2449     consistent gets
    49     1033     physical reads
    50     8428664  redo size
    51     673      bytes sent via SQL*Net to client
    52     601      bytes received via SQL*Net from client
    53      3       SQL*Net roundtrips to/from client
    54      2       sorts (memory)
    55      0       sorts (disk)
    56     72544    rows processed
    57 
    58 SQL> set autotrace off;
    59 SQL> drop table t purge;
    60 
    61 Table dropped.
    62 
    63 SQL> create table t as select * from dba_objects;
    64 
    65 Table created.
    66 
    67 SQL> set autotrace traceonly;
    68 SQL> insert /*+ append */ into t select * from t;
    69 
    70 72544 rows created.
    71 
    72 
    73 Execution Plan
    74 ----------------------------------------------------------
    75 ERROR:
    76 ORA-12838: cannot read/modify an object after modifying it in parallel
    77 
    78 
    79 SP2-0612: Error generating AUTOTRACE EXPLAIN report
    80 
    81 Statistics
    82 ----------------------------------------------------------
    83     594     recursive calls
    84     1292    db block gets
    85     1218    consistent gets
    86     1033    physical reads
    87     22472   redo size
    88     662     bytes sent via SQL*Net to client
    89     615     bytes received via SQL*Net from client
    90      3      SQL*Net roundtrips to/from client
    91      2      sorts (memory)
    92      0      sorts (disk)
    93     72544   rows processed

小结：

2.1、append减少数据块读的数量，普通插入db block gets为9343，加/\*+ append \*/为1292；

2.2、append减少一致读的数量，普通插入consistent gets为2449，加/\*+ append \*/为1218；

2.3、append减少回滚的写入数量，普通插入redo size为8428664，加/\*+ append \*/为22472。

**三、在Arachivelog模式下比较(前提是不使用alter table t nologging;)**

1 SQL> conn /as sysdba
      2 Connected.
      3 SQL> shutdown immediate;
      4 Database closed.
      5 Database dismounted.
      6 ORACLE instance shut down.
      7 SQL> startup mount;
      8 ORACLE instance started.
      9 
     10 Total System Global Area  372449280 bytes
     11 Fixed Size            1336624 bytes
     12 Variable Size          146803408 bytes
     13 Database Buffers      218103808 bytes
     14 Redo Buffers            6205440 bytes
     15 Database mounted.
     16 SQL> alter database archivelog;
     17 
     18 Database altered.
     19 
     20 SQL> alter database open;
     21 
     22 Database altered.
     23 
     24 SQL> archive log list;
     25 Database log mode           Archive Mode
     26 Automatic archival           Enabled
     27 Archive destination           USE_DB_RECOVERY_FILE_DEST
     28 Oldest online log sequence     5
     29 Next log sequence to archive   7
     30 Current log sequence           7
     31 SQL> conn jack/jack
     32 Connected.
     33 SQL> drop table t purge;
     34 
     35 Table dropped.
     36 
     37 SQL> create table t as select * from dba_objects;
     38 
     39 Table created.
     40 
     41 SQL> set linesize 160;
     42 SQL> set autotrace traceonly;
     43 SQL> insert into t select * from t;
     44 
     45 72544 rows created.
     46 
     47 
     48 Execution Plan
     49 ----------------------------------------------------------
     50 Plan hash value: 1601196873
     51 
     52 ---------------------------------------------------------------------------------
     53 | Id  | Operation         | Name | Rows    | Bytes | Cost (%CPU)| Time    |
     54 ---------------------------------------------------------------------------------
     55 |   0 | INSERT STATEMENT     |    |   285K|    56M|   590   (1)| 00:00:08 |
     56 |   1 |  LOAD TABLE CONVENTIONAL | T    |    |    |         |        |
     57 |   2 |   TABLE ACCESS FULL     | T    |   285K|    56M|   590   (1)| 00:00:08 |
     58 ---------------------------------------------------------------------------------
     59 
     60 Note
     61 -----
     62    - dynamic sampling used for this statement (level=2)
     63 
     64 
     65 Statistics
     66 ----------------------------------------------------------
     67     586      recursive calls
     68     9341     db block gets
     69     2395     consistent gets
     7      1033     physical reads
     71     8428488  redo size
     72     679      bytes sent via SQL*Net to client
     73     601      bytes received via SQL*Net from client
     74      3       SQL*Net roundtrips to/from client
     75      2       sorts (memory)
     76      0       sorts (disk)
     77     72544    rows processed
     78 
     79 SQL> set autotrace off;
     80 SQL> drop table t purge;
     81 
     82 Table dropped.
     83 
     84 SQL> create table t as select * from dba_objects;
     85 
     86 Table created.
     87 
     88 SQL> set autotrace traceonly;
     89 SQL> insert /*+ append */ into t select * from t;
     90 
     91 72544 rows created.
     92 
     93 
     94 Execution Plan
     95 ----------------------------------------------------------
     96 ERROR:
     97 ORA-12838: cannot read/modify an object after modifying it in parallel
     98 
     99 
    100 SP2-0612: Error generating AUTOTRACE EXPLAIN report
    101 
    102 Statistics
    103 ----------------------------------------------------------
    104     594      recursive calls
    105     1292     db block gets
    106     1218     consistent gets
    107     1033     physical reads
    108     8536868  redo size
    109     665      bytes sent via SQL*Net to client
    110     615      bytes received via SQL*Net from client
    111      3       SQL*Net roundtrips to/from client
    112      2       sorts (memory)
    113      0       sorts (disk)
    114    72544     rows processed

小结：

3.1、append减少数据块读的数量，普通插入db block gets为9341，加append为1292；

3.2、append减少一致读的数量，普通插入consistents gets为2395，加append为1218；

3.3、回滚段的数量没有什么变化。

**四、在Arachivelog模式下比较(前提是使用alter table t nologging;)**

1 SQL> conn /as sysdba
     2 Connected.
     3 SQL> archive log list;
     4 Database log mode           Archive Mode
     5 Automatic archival           Enabled
     6 Archive destination           USE_DB_RECOVERY_FILE_DEST
     7 Oldest online log sequence     7
     8 Next log sequence to archive   9
     9 Current log sequence           9
    10 
    11 SQL> drop table t purge;
    12 
    13 Table dropped.
    14 
    15 SQL> create table t as select * from dba_objects;
    16 
    17 Table created.
    18 
    19 SQL> alter table t nologging;
    20 
    21 Table altered.
    22 
    23 SQL> set autotrace traceonly;
    24 SQL> insert into t select * from t;
    25 
    26 72544 rows created.
    27 
    28 
    29 Execution Plan
    30 ----------------------------------------------------------
    31 Plan hash value: 1601196873
    32 
    33 ---------------------------------------------------------------------------------
    34 | Id  | Operation         | Name | Rows    | Bytes | Cost (%CPU)| Time    |
    35 ---------------------------------------------------------------------------------
    36 |   0 | INSERT STATEMENT     |    |   285K|    56M|   590   (1)| 00:00:08 |
    37 |   1 |  LOAD TABLE CONVENTIONAL | T    |    |    |         |        |
    38 |   2 |   TABLE ACCESS FULL     | T    |   285K|    56M|   590   (1)| 00:00:08 |
    39 ---------------------------------------------------------------------------------
    40 
    41 Note
    42 -----
    43    - dynamic sampling used for this statement (level=2)
    44 
    45 
    46 Statistics
    47 ----------------------------------------------------------
    48     766      recursive calls
    49     9347     db block gets
    5      2352     consistent gets
    51     1033     physical reads
    52     8434036  redo size
    53     681      bytes sent via SQL*Net to client
    54     601      bytes received via SQL*Net from client
    55      3       SQL*Net roundtrips to/from client
    56      6       sorts (memory)
    57      0       sorts (disk)
    58     72544    rows processed
    59 
    60 SQL> drop table t purge;
    61 
    62 Table dropped.
    63 
    64 SQL> set autotrace off;
    65 SQL> create table t as select * from dba_objects;
    66 
    67 Table created.
    68 
    69 SQL> alter table t nologging;
    70 
    71 Table altered.
    72 
    73 SQL> set autotrace traceonly;
    74 SQL> insert /*+ append */ into t select * from t;
    75 
    76 72544 rows created.
    77 
    78 
    79 Execution Plan
    80 ----------------------------------------------------------
    81 ERROR:
    82 ORA-12838: cannot read/modify an object after modifying it in parallel
    83 
    84 
    85 SP2-0612: Error generating AUTOTRACE EXPLAIN report
    86 
    87 Statistics
    88 ----------------------------------------------------------
    89     774    recursive calls
    90     1292   db block gets
    91     1237   consistent gets
    92     1033   physical reads
    93     22472  redo size
    94     666    bytes sent via SQL*Net to client
    95     615    bytes received via SQL*Net from client
    96      3     SQL*Net roundtrips to/from client
    97      6     sorts (memory)
    98      0     sorts (disk)
    99     72544  rows processed

小结：

4.1、append减少数据块的数量，普通插入db block gets为9347，加append为1292；

4.2、append减少一致读的数量，普通插入consistent gets为2352，加append为1237；

4.3、append减少回滚段的写入数量，普通插入redo size为8434036，加append为22472。

**五、总结**

5.1、只有在归档模式下，对Logging表插入时，加不加append提示，产生的回滚段数量都差不多，其他情况下，加append能显著减少回滚段的产生。

5.2、不论是在什么情况下，加append都能减少数据块读的数量和一致读的数量。