Oracle global temp tabs or nologging/append – saved redo in numbers

In an attempt to reduce redo entries (and archive space) generated for only temporarily computed contents of a couple of simple tables, I regarded employing Global Temporary Tables as well as Nologging Inserts as redesign options. This article, however, is not meant to dive into the details of these redesign options at all. There’s plenty of examples and discussions about the pros and cons of each option around the net. Most notably to mention, and a stimulus to post just my redo reduction test results, is Tim Hall’s compact articles on the subject like https://oracle-base.com/articles/misc/temporary-tables and https://oracle-base.com/articles/misc/append-hint that do include an assessment of the redo matter.

So, what’s the lineup for short? On startup, some data will be sourced throughout the database, losely computed and brutely written into a couple of simple tables. Then some third party will read this data for own purposes and will report completion such that the data can eventually be discarded completely. In making shure that an iteration step always starts unsoiled, any data left over from a crashed precursor will be rubbed out as well. Nobody cares about backups of this data, iteration management runs on other tables. You see, this is not really something that takes a database to complete successfully but…

For temp tables, stored with the pga or disk tmp, this is a on commit delete rows case. The nolog inserts, on the other hand, using an append hint for direct path, may also optimize the discard step by using the faster truncate instead of a delete. The following tables provide numbers for the three different constellations. The load for every test run, around ten iterations executed, just two shown, is aroung 50.000 rows including clobs > 4.000 characters.

original constellation

/	Insert #1	Delete #1	Insert #2	Delete #2
Redo this	22 mb	16 mb	20 mb	15 mb
Redo sum	22 mb	38 mb	58 mb	73 mb
Run time	00:06.06	00:02.24	00:05.06	00:02.11

temp approach

/	Insert #1	Delete #1	Insert #2	Delete #2
Redo this	8 mb	17 mb	8 mb	15 mb
Redo sum	9 mb	25 mb	33 mb	48 mb
Run time	00:05.92	00:01.92	00:05.39	00:02.47

nolog approach

/	Insert #1	Delete #1	Insert #2	Delete #2
Redo this	9 mb	3 mb	8 mb	3 mb
Redo sum	9 mb	12 mb	20 mb	23 mb
Run time	00:06.69	00:02.23	00:08.69	00:02.25

Lessons:

1. You don’t draw gain or loss in performance, whatever constellation being.
2. There’s always redo, point, at least for the system side operations.
3. The nolog approach wins, saves 2/2 of the redo, most obviously by selecting truncate (no space reuse for small or medium size tabs) over delete. Whoever knows why the temp approach spends so much redo in the delete step…
4. Interesting to learn btw, what it takes to see nolog inserts are really direct path: https://antognini.ch/2009/10/hints-for-direct-path-insert-statements.

Have fun, Peter