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

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s