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

asked 30 May '17, 19:52

elriba's gravatar image

elriba
51334
accept rate: 0%

1

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.

(01 Jun '17, 07:32) Justin Willey
Replies hidden

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

(01 Jun '17, 11:08) 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
permanent link

answered 31 May '17, 04:33

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

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.

permanent link

answered 31 May '17, 04:08

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 31 May '17, 04:11

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

(31 May '17, 04:37) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×52
×23

question asked: 30 May '17, 19:52

question was seen: 1,792 times

last updated: 01 Jun '17, 11:13