|
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 |
|
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 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).
(31 May '17, 16:29)
Volker Barth
Replies hidden
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.
(01 Jun '17, 04:17)
Breck Carter
|
|
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. CREATE DATABASE ... DATABASE SIZE was added in Version 10.
(31 May '17, 04:37)
Breck Carter
|
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.
Yes... in olden times, before giant RAM caches... like dbeng50 "By default, the database server usess (sic) 2 megabytes of memory for caching." :)