通過append hint來插入資料,演示它和普通插入資料的效能比較。

weixin_34119545發表於2019-01-08

一、實驗說明

   作業系統: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都能減少資料塊讀的數量和一致讀的數量。

相關文章