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 220.127.116.11 2-node cluster comprised of virtual machines running RedHat 5.
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…)
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.
|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!
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).