ALTER DBSPACE question when unloading/reloading

[Former Member]

Hi, I'm unloading/reloading a more or less large database (75GB). I understand that to optimize the reloading time I should do an ALTER DBSPACE on the new database file.

I was thinking on doing:

ALTER DBSPACE 60GB

But I was wondering whether this is going to take a very long time, and maybe it was more optimal to use a smaller size....

Do you guys have any comments about this?

This is an ASA9 database. At this moment I'm unloading/reloading in version 9, but hopefully in a couple of months I'm doing this again for ASA17.

Thanks, Edgard

[justin_willey]

This might be too obvious a point, but the biggest single factor in the speed is probably to avoid reading and writing to the same physical disk at the same time. So if you have two local disks available, have the new database on one and the old one on the other.

[Breck_Carter]

biggest single factor

Yes... in olden times, before giant RAM caches... like dbeng50 "By default, the database server usess (sic) 2 megabytes of memory for caching." 🙂

[Breck_Carter]

The main reason to ALTER ADD space is (was?) to give you a chance to defragment the allocated disk space before the loading the data.

I'm not sure defragmentation is a big concern any more, at least not with Windows 7... I just ran ALTER DBSPACE SYSTEM ADD 60 GB and the resulting file only had 21 fragments; in olden days it would have had thousands.

Anyway, to answer your question about how long it would take, here are some test numbers...

SELECT @@VERSION;

@@VERSION
'9.0.2.3951'

ALTER DBSPACE SYSTEM ADD 60 GB;

Execution time: 2333.266 seconds 

...which is 39 minutes

Windows 7 on Dell XPS 8700 Intel i7-4790 3.60 GHz 16.0 GB RAM 1 TB drive

[VolkerBarth]

Nevertheless, doing a reload against an initially small database that will expand to 60 GB during the reload will take at least that time for the file growth operations, so it's no advantage in my book. And when the new database is already "pre-grown", the downtime for the whole unload/reload should be noticeably shorter (in case that matters).

[Breck_Carter]

The downtime argument is a good one. Sometimes it's not "how long will it take?" but "can I spend the time offline?"

Another example is backup and validation. Validation is a brutal process that takes a very long time but can be done on a backup copy, offline, on a separate computer so it doesn't affect online operations.

[VolkerBarth]

If the database file will become that large, it will be most efficient to grow the database file in one step. I would use the CREATE DATABASE DATABASE SIZE option for that.

Note that an unloaded database might be smaller than the original one (because of less free pages and morecompact row storage) so be sure the initial size is not "over-sized". It's often helpful to do a test unload to find out about the typical reload database size.

[Breck_Carter]

CREATE DATABASE ... DATABASE SIZE was added in Version 10.