Direct Path, Nologging, CTAS, and GTT: a comparison of undo and redo generated

To log or log not

Well of course, as an Oracle DBA, I was conscious of the usual “nologging question” – how to weigh the impact on your recovery procedures against the performance gain and space savings (redo log-wise) achieved by turning off logging on certain tables.
But honestly, when it came to nologging and direct path loads, I wouldn’t have been able to give any figures as to how big the difference in redo and undo generation really might be (let alone impact on performance).

So I’ve decided to set up a simple experiment, measuring undo and redo generation while varying the following factors:

  • table logging vs. nologging
  • conventional vs. direct path load
  • conventional table vs. global temporary table
  • insert vs. CTAS
  • small vs. big table

While it would have been nice to compare elapsed times, I would have had to perform an extensive series of measurements in order to guarantee reliable results (especially given the environment I used), which is why I deemed it more prudent not to relate any performance related data 🙂
Speaking of environment, the tests were conducted on an 11.2.0.3 2-node cluster comprised of virtual machines running RedHat 5.

The rules

To keep it simple, I’ve restricted myself to the – at least from my point of view – rather common (for a data warehouse) situation of having the database running in archivelog, but not force logging mode.
In this case, we expect the following (see http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9014.htm#i2163698, as well as http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables002.htm#i1106265):

  • conventional insert generates maximal redo and undo, regardless of logging setting
  • direct path insert does NOT generate any undo for data (as opposed to metadata) changes. Redo for data changes is generated ONLY when the table is not set to nologging
  • a temporary table (being nologging “per se”) has redo generated just for the undo that is being created, not for the inserts – unless direct path load is used, in which case any undo and consequent redo generation is skipped
  • CTAS, being a direct path operation, does NOT generate any undo for data (as opposed to metadata) changes. Redo for data changes is generated ONLY when the table is not set to nologging. Compared to an insert that is direct path as well as nologging, metadata-related redo and undo generated are of course expected to be much bigger
  • nologging setting should have bigger effect with big tables than with small ones (as stated by the documentation linked above,- but it should logically make sense too, the difference in amount of metadata between a big and a small table being, probably, smaller than the difference in data size…)

The figures

In order to see the effect of table size, I have performed two sets of tests, the first with a rather smallish table (inexplicably, quite precisely of dba_objects size ;-), ~ 60000 rows), the second of size ~ 8 million rows.

First, the results for the small table. More out of curiosity than expectancy of big differences, I’ve included the table size, as determined by dbms_stats.

Small table
direct path nologging redo (bytes) undo (bytes) blocks
insert into <table> select * from no no 7,300,176 285,530 1000
insert into <table> select * from nologging no yes 7,312,736 281,960 1000
insert /*+ append */ into <table> select * from yes no 7,365,808 8,804 909
insert /*+ append */ into <table> select * from nologging yes yes 48,684 8,804 909
insert into <GTT> select * from no yes 385,780 272,480 883
insert into <GTT> select * from nologging yes yes 140 80 883
create table <table> as select * from yes no 7,392,692 17,964 909
create table <table> nologging as select * from yes yes 82,848 17,844 909

Now, what do the figures say?

Firstly, as to the four combinations of logging on/off and conventional vs. direct path load, we see exactly what we’d expect to, from the Oracle documentation: it is only when combining direct path and nologging that we have any effects on redo. This effect, however, is quite impressive: redo is being reduced by a factor of 140!

How does insert append compare to CTAS? When performed logging, CTAS in fact generates an amount of redo similar to the respective version of insert append. Done nologging, redo amounts to nearly the double amount, compared to insert append – the same is the case for undo. Actually, not surprising when we take into account that metadata changes have to be logged always – and CTAS, of course, includes the metadata for table creation…

So, when there is insert append, can it get any better? Well yes, it can, with GTT. Now, while CTAS is direct path always and can be logging as well as nologging, GTTs are nologging always and can optionally be direct path. And in contrast to inserts into a normal table, with GTT we do see an effect of nologging alone: A GTT, even when populated by conventional insert, has redo reduced by a factor of about 20. But in combination with direct path load, the effect is, well, quite awesome: Redo (as well as undo) nearly vanishes completely!

So, I guess what this means is you need rather compelling reasons not to use GTTs in every situation where they are appropriate. And fortunately, some compelling reasons now go away with 12c: session-level statistics, inserts on the standby … for sure something worth to check out!

Big table

Now, we still have the question whether savings in redo generation will be bigger with bigger tables. Let’s see.

direct path nologging redo (bytes) undo (bytes) blocks
insert into <table> select * from no no 928,535,948 35,028,304 113117
insert into <table> select * from nologging no yes 928,664,192 35,027,486 113117
insert /*+ append */ into <table> select * from yes no 937,730,612 76,952 113789
insert /*+ append */ into <table> select * from nologging yes yes 772,916 76,644 113789
create table <table> as select * from yes no 938,164,896 157,080 113789
create table <table> as select * from nologging yes yes 962,696 121,376 113789
insert into <GTT> select * from no yes 49,459,284 34,941,516 112702
insert into <GTT> select * from nologging yes yes 420 80 112702

And they are: With the big table, redo gets reduced not by a factor of 140, but of 1200… and with the GTT, we get 8 million rows populated for virtually no (420 bytes of) redo.

Now, that’s it for now. If you’re impressed by the effects of nologging and are in a situation where its advantages do count, it might be time to check out the exact consequences on recoverability (possibly starting out with this blog post: http://oraclecomplete.blogspot.de/2013/01/benefits-and-consequences-of-nologging.html).

Advertisements

One thought on “Direct Path, Nologging, CTAS, and GTT: a comparison of undo and redo generated

Leave a Reply

Fill in your details below or click an icon to log in:

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