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).