SEO

What influences the time taken for the 'alter tablespace begin backup'
statement?
Until a couple of weeks ago, I could get my entire database (50
tablespaces, 1TB) into backup mode in about 10-15 minutes, but then
all of a sudden it started taking 45-50 minutes to do the same action.
The only change I am aware of which coincided, was an increase by 4GB
(from 12GB to 16GB) in the data cache (db_block_buffers). I am
interested to understand what actually happens as the 'begin backup'
statement is executed. What does it wait for?

There are two possible waits with setting a
tablespace into backup mode. The first
is that it can't be done until all current
transactions on the database have completed
(although new ones can start). If you are waiting
for a transaction to complete, your session will
be reporting a TX lock request in mode 4.

The other is the tablespace checkpoint. When
you issue 'begin backup' all the dirty blocks in
the tablespace have to be written to disc - and
these can only be found by walking the entire
checkpoint queue (and it is possible that Oracle
still finds them by scanning the entire buffer rather
than walking the queue). If this is happening,
you will see your session holding (or possibly
waiting for) a TC enqueue in mode 6 while the
flush takes place.

The increase in the size of the block cache is likely
to make the tablespace checkpoint take longer,
whether or not Oracle was doing it the right way
or the wrong way. You could reduce the time by
changing your log_checkpoint_timeout, or the
fast_start_mttr_target to make Oracle write
dirty blocks more aggressively between checkpoints,
but this might have other effects on the I/O subsystem
that you don't want to put up with.


--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Andy Pennington" <andy_pennington@yahoo.com> wrote in message
news:60313e96.0312172149.32ba7799@posting.google.c om...
> What influences the time taken for the 'alter tablespace begin backup'
> statement?
> Until a couple of weeks ago, I could get my entire database (50
> tablespaces, 1TB) into backup mode in about 10-15 minutes, but then
> all of a sudden it started taking 45-50 minutes to do the same action.
> The only change I am aware of which coincided, was an increase by 4GB
> (from 12GB to 16GB) in the data cache (db_block_buffers). I am
> interested to understand what actually happens as the 'begin backup'
> statement is executed. What does it wait for?

andy_pennington@yahoo.com (Andy Pennington) wrote in message news:<60313e96.0312172149.32ba7799@posting.google. com>...
> What influences the time taken for the 'alter tablespace begin backup'
> statement?
> Until a couple of weeks ago, I could get my entire database (50
> tablespaces, 1TB) into backup mode in about 10-15 minutes, but then
> all of a sudden it started taking 45-50 minutes to do the same action.
> The only change I am aware of which coincided, was an increase by 4GB
> (from 12GB to 16GB) in the data cache (db_block_buffers). I am
> interested to understand what actually happens as the 'begin backup'
> statement is executed. What does it wait for?

It has to be sure there aren't any "split blocks," that is, Oracle
blocks that are only half updated because the copy spans two OS blocks
while Oracle is updating them. RMAN knows about making consistent
blocks so it doesn't have this problem.

It also has to checkpoint, flush dirty blocks to disk, so that might
explain some of the difference (especially since there might be
proportionally more dirty blocks after increasing the SGA).

Also, any blocks being changed during the hot backup need to be
entirely written to the redo logs, rather than just changes vectors,
which can of course impact performance.

jg
--
@home.com is bogus
http://msnbc.msn.com/Default.aspx?id=3540652&p1=0

"Jonathan Lewis" <jonathan@jlcomp.demon.co.uk> wrote in message news:<brt18i$koq$1$8302bc10@news.demon.co.uk>...
> There are two possible waits with setting a
> tablespace into backup mode. The first
> is that it can't be done until all current
> transactions on the database have completed
> (although new ones can start).

Are you sure about this? It would be very difficult to make hot
backups if any long running transaction could block 'begin backup'.

I just tried in 8.1.7:
- in session1 started a transaction (deleted a record from a table)
- in session2 'alter tablespace begin backup' and it worked straight
away.

> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk

Regards,
Igor

Whoops, Sorry,

Absolutely right - the TX lock is relevant to
alter tablespace read only
not to begin backup.

It's only the tablespace checkpoint that applies
to begin backup.


--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Igor Laletin" <ilaletin@usa.net> wrote in message
news:f9226414.0312181858.618e12f6@posting.google.c om...
>
> Are you sure about this? It would be very difficult to make hot
> backups if any long running transaction could block 'begin backup'.
>
> I just tried in 8.1.7:
> - in session1 started a transaction (deleted a record from a table)
> - in session2 'alter tablespace begin backup' and it worked straight
> away.
>
> > --
> > Regards
> >
> > Jonathan Lewis
> > http://www.jlcomp.demon.co.uk
>
> Regards,
> Igor

So, to replay this: its the checkpointing which is taking longer
(approximately 1 minute for each tablespace).


The thing that confuses me is that immediately prior to the 'begin
backup' statements, there is a log switch, so I would have thought
this would have already checkpointed the database, flushing all the
dirty blocks etc, leaving only a few to flush when the begin backup
statements start. Is this not the case?

Can I shorten this checkpointing period, perhaps by forcing a
checkpoint earlier, or by somehow otherwise flushing the dirty blocks?

By the way, many thanks for your responses so far, guys.

Andy.

joel-garry@home.com (Joel Garry) wrote in message news:<91884734.0312181652.5fb18236@posting.google. com>...
> andy_pennington@yahoo.com (Andy Pennington) wrote in message news:<60313e96.0312172149.32ba7799@posting.google. com>...
> > What influences the time taken for the 'alter tablespace begin backup'
> > statement?
> > Until a couple of weeks ago, I could get my entire database (50
> > tablespaces, 1TB) into backup mode in about 10-15 minutes, but then
> > all of a sudden it started taking 45-50 minutes to do the same action.
> > The only change I am aware of which coincided, was an increase by 4GB
> > (from 12GB to 16GB) in the data cache (db_block_buffers). I am
> > interested to understand what actually happens as the 'begin backup'
> > statement is executed. What does it wait for?
>
> It has to be sure there aren't any "split blocks," that is, Oracle
> blocks that are only half updated because the copy spans two OS blocks
> while Oracle is updating them. RMAN knows about making consistent
> blocks so it doesn't have this problem.
>
> It also has to checkpoint, flush dirty blocks to disk, so that might
> explain some of the difference (especially since there might be
> proportionally more dirty blocks after increasing the SGA).
>
> Also, any blocks being changed during the hot backup need to be
> entirely written to the redo logs, rather than just changes vectors,
> which can of course impact performance.
>
> jg

A log switch only initiates a slow checkpoint. The checkpoint that is
initiated should be completed by the time the log file is overwritten.
As Jonathan has said, you can turn on incremental checkpointing by
setting LOG_CHECKPOINT_INTERVAL or LOG_CHECKPOINT_TIMEOUT or
fast_start_mttr_target in 9i.
regards
Srivenu