DBA Hub

📋Steps in this guide1/4

APPEND Hint

Use the APPEND hint to improve the performance of load operations.

oracle miscconfigurationintermediate
by OracleDba
21 views
1

How the APPEND Hint Affects Performance

The hint tells the optimizer to perform a direct-path insert, which improves the performance of operations for a number of reasons: - Data is appended to the end of the table, rather than attempting to use existing free space within the table. - Data is written directly to the data files, by-passing the buffer cache. - Referential integrity constraints are not considered. * - No trigger processing is performed. * The combination of these features make direct-path inserts significantly quicker than conventional-path inserts. * These two points could leave data logically corrupt, so Oracle ignores the hint and the data is loaded using conventional path if enabled referential integrity constraints and enabled triggers are present on a table. The hint can still be used if referential integrity constraints and triggers on the table are disabled. There are a number of additional restrictions mentioned here .
2

How the APPEND Hint Affects the Table Size (High Water Mark)

As direct-path inserts append data to the end of the table, they constantly increase the table high water mark, even if there is lots of free space within the table. In tables that regularly have rows deleted, the use of the the hint can result in large tables containing lots of sparsely populated blocks. These will need to be managed by one of the following types of shrink operation. - Export the data, truncate the table and import the data. - Use a " " (CTAS) operation to build a new table with the data compacted, drop the original table and rename the new table to replace the original. - Use and online table redefinition operation to recreate the table. - Use an online segment shrink operation to compact the data.
3

How the APPEND Hint Affects Redo Generation

If the database is running on mode, using just the hint will reduce redo generation. In reality, you will rarely run OLTP databases in mode, so what happens in mode? In mode, using the hint will not reduce redo generation unless the table is set to . The examples below step through this process to show it in action. The following example is run against a database running in mode. The redo generation is displayed in bold. As suggested, with the database running on mode, the addition of the hint did reduce the amount of redo generated. The next example performs the same test, but this time on a database running in mode. Notice how the addition of the hint no longer has an impact on the amount of redo generated. To allow the hint to have an impact on redo generation again, we must set the table to . We can see that altering the table to makes the behavior return. So, except in the case of a mode database, the hint will only reduce redo generation if the table[space] is set to .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
SQL> create table t1 as select * from all_objects where 1=2;

Table created.

SQL> set autotrace on statistics
SQL> insert into t1 select * from all_objects;

72512 rows created.


Statistics
----------------------------------------------------------
        634  recursive calls
       9946  db block gets
      50116  consistent gets
          2  physical reads
8464520  redo size
830  bytes sent via SQL*Net to client
        796  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       1508  sorts (memory)
          0  sorts (disk)
      72512  rows processed

SQL> truncate table t1;

Table truncated.

SQL> insert /*+ append */ into t1 select * from all_objects;

72512 rows created.


Statistics
----------------------------------------------------------
        369  recursive calls
       1689  db block gets
      48194  consistent gets
          2  physical reads
46048  redo size
822  bytes sent via SQL*Net to client
        810  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       1500  sorts (memory)
          0  sorts (disk)
      72512  rows processed

SQL> commit;

Commit complete.

SQL>

SQL> create table t1 as select * from all_objects where 1=2;

Table created.

SQL> set autotrace on statistics
SQL> insert into t1 select * from all_objects;

88773 rows created.


Statistics
----------------------------------------------------------
        613  recursive calls
      11792  db block gets
     116808  consistent gets
          2  physical reads
10222352  redo size
370  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       3142  sorts (memory)
          0  sorts (disk)
      88773  rows processed

SQL> truncate table t1;

Table truncated.

SQL> insert /*+ append */ into t1 select * from all_objects;

88773 rows created.


Statistics
----------------------------------------------------------
        307  recursive calls
       1573  db block gets
     114486  consistent gets
          0  physical reads
10222864  redo size
366  bytes sent via SQL*Net to client
        566  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       3138  sorts (memory)
          0  sorts (disk)
      88773  rows processed

SQL> commit;

Commit complete.

SQL>

SQL> alter table t1 nologging;

Table altered.

SQL> truncate table t1;

Table truncated.

SQL> set autotrace on statistics
SQL> insert into t1 select * from all_objects;

88773 rows created.


Statistics
----------------------------------------------------------
        506  recursive calls
      11790  db block gets
     116652  consistent gets
          0  physical reads
10222328  redo size
373  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       3139  sorts (memory)
          0  sorts (disk)
      88773  rows processed

SQL> truncate table t1;

Table truncated.

SQL> insert /*+ append */ into t1 select * from all_objects;

88773 rows created.


Statistics
----------------------------------------------------------
        307  recursive calls
       1573  db block gets
     114486  consistent gets
          0  physical reads
25968  redo size
366  bytes sent via SQL*Net to client
        566  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       3138  sorts (memory)
          0  sorts (disk)
      88773  rows processed

SQL> commit;

Commit complete.

SQL> drop table t1 purge;

Table dropped.

SQL>
4

Using NOLOGGING

In the previous section we demonstrated the reduction in redo generation by combining the hint with in a database running in mode. From a performance perspective that may sound appealing, but remember the impact this has on data recovery. When we use we are no longer protecting the data, making it impossible to do point-in-time-recovery (PITR) of that data. The table structure will be protected, but the contents will not. As a result, any PITR may need the table to be truncated and repopulated. This is fine for transient tables, like staging tables in an ETL process, but it is a bad idea to any data you care about. I prefer to keep transient staging tables in separate tablespaces, so everyone understands the contents of those tablespaces are not protected like "real tables". It should also be noted, in some situations like data guard environments using forced logging, the use of is overridden, and will not result in the desired effect. Connor McDonald made the following comment, which is worth remembering. > "Just to clarify, the tablespace setting of only applies if you have set it before you create the table. Table settings override tablespace settings." For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!