For those not familiar with Richard Foote’s extensive blog about indexes (and if you’re not you should be) – the title of this note is a blatant hi-jacking of his preferred naming mechanism.
It’s just a short note to remind myself (and my readers) that anything you know about Oracle, and anything published on the Internet – even by Oracle Corp. and its employees – is subject to change without notice (and sometimes without being noticed). I came across one such change today while read the Expert Oracle Exadata book by Kerry Osborne, Randy Johnson and Tanel Poder. It was just a little throwaway comment to the effect that:
In NOARCHIVELOG mode all bulk operations (such as INSERT, APPEND, index REBUILD and ALTER TABLE MOVE) are automatically nologging.
The obvious error there is the reference to “index REBUILD”. Although create table as select and alter table move default to nologging (when running in noarchivelog mode) the equivalent commands for indexes have always been logged. On the other hand, pausing for thought here, I wouldn’t expect such an obvious error to slip past all three authors and the technical reviewers so, before opening my mouth and putting my foot firmly into it, I decided to run a quick test and, almost inevitably, I have a handy test script that I’ve been running intermittently for years for exactly this test case.execute snap_redo.start_snap
create table t1
as
with generator as (
select --+ materialize
rownum id
from dual
connect by
level )
select
rownum id,
lpad(rownum,10,'0') small_vc,
rpad('x',100) padding
from
generator v1,
generator v2
where
rownum ;
execute snap_redo.end_snap
execute snap_redo.start_snap
create index t1_i1 on t1(padding);
execute snap_redo.end_snap
execute snap_redo.start_snap
alter index t1_i1 rebuild;
execute snap_redo.end_snap
The snap_redo package is a simple bit of pl/sql I wrote to report the changes in a few of the current session stats (view v$mystat) over time. Specfically it looks at the statistics containing the word redo (and, in a more sophisticated form, a few others related to transaction management). Here are the key results for a test run on 10.2.0.3 and 11.1.0.7 – first from 10g============ Create Table ============ redo synch writes 2 redo entries 410 redo size 71,792 redo wastage 1,836 redo writes 6 redo blocks written 133 redo ordering marks 3 redo subscn max counts 10 ============ Create Index ============ redo synch writes 3 redo synch time 2 redo entries 1,887 redo size 13,129,888 redo buffer allocation retries 4 redo wastage 2,144 redo writes 11 redo blocks written 26,428 redo write time 67 redo log space requests 3 redo log space wait time 31 redo ordering marks 3 redo subscn max counts 3 ============= Rebuild index ============= redo synch writes 2 redo entries 1,978 redo size 13,132,052 redo buffer allocation retries 2 redo wastage 4,084 redo writes 16 redo blocks written 26,453 redo write time 66 redo ordering marks 3 redo subscn max counts 4Now from 11g
============ Create Table ============ redo synch writes 1 redo synch time 1 redo entries 428 redo size 67,228 redo size for direct writes 2,912 redo wastage 1,072 redo writes 5 redo blocks written 138 redo write time 1 redo ordering marks 3 redo subscn max counts 5 ============ Create Index ============ redo synch writes 1 redo entries 601 redo size 71,092 redo size for direct writes 14,916 redo wastage 972 redo writes 4 redo blocks written 146 redo blocks checksummed by FG (exclusive) 6 redo ordering marks 3 redo subscn max counts 4 ============= Rebuild index ============= redo synch writes 1 redo entries 684 redo size 80,400 redo size for direct writes 14,916 redo wastage 1,988 redo writes 7 redo blocks written 167 redo write time 5 redo blocks checksummed by FG (exclusive) 6 redo ordering marks 4 redo subscn max counts 5As you can see, somewhere between 10.2.0.3 and 11.1.0.7 index creation and rebuild finally became consistent with table creation and move when running in noarchivelog mode. The book was right – it was, after all, talking about Exadata which means it’s implicitly talking about 11g and doesn’t really have to qualify the comment with references to earlier versions.
There’s a secondary moral to this story: instead of saying: “You’re wrong”, you might look a little wiser if you start with “Are you sure about that?” or even “Which version are you thinking of?”
There’s another corollary, of course – if you decide to test out the time and impact of rebuilding a very large index by using a backup copy of your production system, make sure that you are running in archivelog mode or you won’t be doing a test that is anything like valid for the production system (assuming your production systems are running in archivelog mode, of course).
DIGITAL JUICE
No comments:
Post a Comment
Thank's!