Re: performance of backup degrade continuously

On 24 Jun 2006 09:48:34 -0700, "jshen.cad_at_gmail.com" <jshen.cad_at_gmail.com> wrote:

>I'm sorry that I did not explain my situation clearly.
>
>The amount of data to be backed up is increasing SLOWLY, because we
>only keep data for around 6 month ( one table for each month). Although
>the number of record increase time, the size of data to back up differs
>not so much. while the time for backup operation increase much faster
>than expected.
>
>

Hi Joe,

There are then 2 possible ways to explain the increasing backup time :

1. The physical size of the database is actually increasing, even though the amount of logical records is constant.
2. I/O workload, bandwidth, job priorities and other stuff "outside" the database.

Regarding 1) :

The physical DB size can grow independent of a constant logical data set for a number of reasons:

1. The rollback segments (since you run 8i) might have grown because of increased transactional load.
2. Temporary tablespaces are (incorrectly) included in your bacup and may have grown significantly due to e.g. heavy sorts. Solution : Use tempfiles for temp tablespaces and exclude them from the backup. In case of recovery, just drop and recreate.
3. Indexes may grow significantly though the number of rows in the base table is constant. If you have bitmap indexes on columns being heavily updated, they may even explode in size ( though in 10g bitmap indexes have become more robust to updates)
4. Data is moved between tables and/or partitions in a way that does not reclaim used space. e.g. if you do the following move of data :

create table tab2 as select * from tab1 where key <=1000; delete from tab1 where key <=1000;

tab2 takes up some space, but the space released in tab1 is not available for other segments until you reorganise it.

Solution : Check if the space usage percentage of the tables in your DB is continously decreasing.

etc...

• Kenneth Koenraadt