Question Re: ALTER DBSPACE question when unloading/reloading in Technology Q&A

ALTER DBSPACE question when unloading/reloading

Former Member — Tue, 30 May 2017 19:52:51 GMT

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

Re: ALTER DBSPACE question when unloading/reloading

VolkerBarth — Wed, 31 May 2017 04:08:50 GMT

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.

Re: ALTER DBSPACE question when unloading/reloading

Breck_Carter — Wed, 31 May 2017 04:33:04 GMT

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

Re: ALTER DBSPACE question when unloading/reloading

Breck_Carter — Wed, 31 May 2017 04:37:36 GMT

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

Re: ALTER DBSPACE question when unloading/reloading

VolkerBarth — Wed, 31 May 2017 16:29:38 GMT

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).

Re: ALTER DBSPACE question when unloading/reloading

Breck_Carter — Thu, 01 Jun 2017 04:17:47 GMT

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.

Re: ALTER DBSPACE question when unloading/reloading

justin_willey — Thu, 01 Jun 2017 07:32:50 GMT

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.

Re: ALTER DBSPACE question when unloading/reloading

Breck_Carter — Thu, 01 Jun 2017 11:08:48 GMT

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." 🙂