It’s always useful to collect baseline information – especially when it helps you notice that the baseline has moved in a way that might explain the next performance problem you see. Here’s an example demonstrating the benefit.
I have a table with a LOB column that is stored out of line. Many years ago I decided that I wanted to compare how the redo generation varied as I change the LOB from cached to nocache (with nologging). So here was one of my simplest test scripts (stripped to a minimum):
create table t1 ( id number, cached_lob clob ) lob (cached_lob) store as text_lob_cache( disable storage in row cache -- nocache logging ) ; insert into t1 select rownum, rpad('x',4000) from all_objects where rownum <= 1000 ;
When I first ran this test (twice, one for each option) I noticed that cached LOBs recorded just the redo needed to describe the data that had been written into them (so at 1,000 rows of 4KB each that would be in the ballpark of 4MB with some overheads), but the nocache option created redo for the entire block (so nearer 8MB, plus overheads). There were, of course, all sorts of other side effects of the change – some of them very nasty – but this was one of the more important differences to note.
A little while ago I was prompted to repeat the test – so I ran the whole thing three times on 10.2.0.5, 184.108.40.206, and 220.127.116.11; it’s fascinating to see the way the key redo stats change:
10.2.0.5 ======== CACHE Name Value ---- ----- redo entries 5,588 redo size 5,288,836 NOCACHE redo entries 5,578 redo size 9,407,824 18.104.22.168 ======== CACHE redo entries 5,223 redo size 5,277,520 NOCACHE redo entries 5,220 redo size 1,212,992 redo size for direct writes 52,000 22.214.171.124 ======== CACHE redo entries 5,231 redo size 9,343,444 NOCACHE redo entries 5,291 redo size 1,301,700 redo size for direct writes 60,000
As you can see the 10.2.0.5 results mirror the first results I got – the redo size is consistent with the volume of data when the LOBs are cached, and matches whole block dumps when they’re not cached – but something very clever seems to appear in the 126.96.36.199 results – the redo for the lob component of nocache LOBs seems to have disappeared – there’s a drop of (roughly) 8,192,000 bytes from the 10g figures: is this a clever trick (filesystem-like logging ?) or is it a reporting error. Then 12c is even stranger – the redo for the cached LOBs now matches block logging (8MB + overheads) instead of data logging – and the redo for the nocache LOBs is still “missing”.
To be investigated further – possibly by dumping redo log files, but initially simply by watching when log file switches take place – to see if the changes are reporting errors or genuine changes.