How RMAN Restores/Rebuilds Tables

[dnunknown]

Hey Tim,

10.2
We have a process that does bulk inserts into a table in our production enviroment to a table, this table is also subject to mass deletes, we will call it table X

Now for development we take the RMAN backup and restore it into a different enviroment, we run the same bulk inserts and the time it takes to run the job is about 1/3 the time. The enviroments are identical. So I am thinking that table X in production when doing the insert is being scanned to try to utilize the empty space given back to the table by the mass deletes. And if this is true then the only thing that would be logical is that when RMAN restores the entire DB the rows are written without these 'holes' of empty space so it simply just appends it to the end of the table causing a much quicker insert

Am I right in my logic here?

Thanks,
Steve

[Tim...]

Hi.

1) Is the hardware any different? If you have better hardware in the second environment, you would expect a different result.
2) What type of backups are you doing? Image copies are literally a complete image copy, so no "extra" changes can happen. If you are doing backupsets, then RMAN uses unused block compression so it only backs up blocks that are listed as being used in the tablespace bitmap. This is effectively compacting the backup and in some ways compacting the recovered data file. It doesn;t compact data within blocks though.

Cheers

Tim...

[dnunknown]

1) HW is the same, if not a bit less powerfull than production since it is for development
2) The backup is the standard RMAN backup performed via;

Code: Select all

run {
backup database;
backup archivelog all;
}

Then in development enviroment it is restored pretty much in the same manner;

Code: Select all

run {
restore database;
recover database;
}

[Tim...]

Hi.

The you will have empty block compression and null compression, as they are the default in 10gR2 and above. You should check the extent map on both installation to confirm this has resulted in compression and reduction of the HWM. That would explain quicker FTS.

Cheers

Tim...