Someone sent me the following question recently:
I am the DBA for a 4 TB datawarehouse. It has about a thousand plus datafiles. I use the EMC bcv establish process for backing up the database. When splitting the database I place all the tablespaces in begin backup mode, suspend (alter system), split, resume (alter system) and end backup of all tablespace.
In 9i and prior to 9i it used to take about 30 minutes to place all tablespaces in begin backup. Ever since I upgraded to 10gr2, it only takes one minute to place all these tablespaces in begin backup. Could you please throw some light on this ?
The answer is, as so often, I don’t know. (Partly because I don’t own an EMC, and partly because no-one has asked me before). I am, however, prepared to float a hypothesis.
All other things being equal – and they rarely are if you’re comparing 9i with 10g – the change may relate to tablespace checkpoints and write optimisations.
- When you issue “alter tablespace xxx begin backup” Oracle issues a tablespace checkpoint, which means it writes to disc all dirty blocks in the db cache that belong to that tablespace – and those writes must complete before the command returns. (That’s not a new feature, by the way – it’s been like that for years).
In 10g when dbwr finds several adjacent blocks to write it can “coalesce” them into a single large write – which can make a difference to the way the I/O subsystem has to work. This feature is controlled by the hidden parameter _db_writer_coalesce_area_size and becomes visible in v$sgastat under the shared pool names: dbwriter coalesce bitmap, dbwriter coalesce struct, dbwriter coalesce buffer.
In normal checkpointing activity dbwr walks the checkpoint queue in order (see this link for an old, but reasonably accurate, note on the checkpoint queue) and writes a few blocks from the tail end of the queue; but for a tablespace checkpoint there may be a lot of blocks from one tablespace to write, and if you take them out of queue order you may be able to use the coalesce trick quite frequently to make a significant dent in the I/O overhead.
I’m not going to claim that this is the correct answer to the original question – but it does seem a reasonable hypothesis. As a test, the person asking the question could check v$filestat just before and just after doing the ‘begin backup’ to see if the statistics for the relevant file suggest that a number of multiblock writes have taken place.
Historically, a multiblock write was a sign of a direct write (usually on the temporary tablespace, but also visible for index rebuilds, direct loads etc.) From 10g, a multiblock write could appear any place, any time, thanks to the dbwriter coalesce feature.
Footnote: on a more mundane note, it is possible that the 9i system had an explicit setting for log_checkpoint_timeout (typically 1800 seconds) whilst the 10g system has been set to use the self-tuning fast_start_mttr_target – which could have reduced the notional log_checkpoint_timeout to a much smaller value, thus keeping the number of dirty blocks in the buffer to a much lower volume. A check on v$instance_recovery (particularly the target_redo_blks, and log_chkpt_interval_redo_blks) would confirm this hypothesis.