Re: Resizing of Data Files - How Long SHOULD It Take?

On 28 May 2002 06:19:03 -0700, Richard.Sgrignoli_at_highmark.com (Richard E Sgrignoli) wrote:

>I'm not an Oracle DBA, but am a UNIX (Solaris) Sys Admin.....recently,
>I have had a question come up from an Oracle DBA relating to time it
>took to resize a data file with an inference that there was a problem
>with a controller/disk on that particular server "because on other
>domains it would have taken SECONDS!"
>
>I do not know what all is involved with resizing a data file, but I
>will attempt to provide some data below which, hopefully, will allow
>someone to shed some light on what the norm is for resizing and how
>long it takes.
>
>Example 1:
> data file "p65.dbf"
> resized 1750m - took 1 minute 22 seconds
> current file size: 1,835,016,192 bytes
>
>Example 2:
> data file "23p.dbf"
> resized 1800m - took 45 seconds
> current file size: 2,097,160,192 bytes
>
>Example 3:
> data file "12p.dbf"
> resized 2400m - took 34 minutes 38 seconds
> current file size: 1,677,729,792 bytes
>
>As you can see, there is no firm correlation with larger resizes
>taking longer.
>In doing iostat comparisons between the disks involved in THESE
>resizes and those on other domains (which Oracle says takes "only
>SECONDS" to resize), I see no difference in service times or busy
>rates. I guess my main question here is: What are some other factors
>I should be considering in trying to come up with an explanation of
>why it is taking so long to resize? What is the norm time required to
>resize? I do not want to spin my wheels trying to come up with
>something that does not exist simply because our Oracle folks are TOO
>impatient, yet I want to be able to better investigate and fix a
>problem if, in fact, these times ARE too slow. Any feedback greatly
>appreciated.
>
>Richard

Hi Richard,

My experience is similar to what you have posted - it basically depends on the speed of the disks and how large the file being resized it. On a large production box with an EMC array attached, resizing the files takes only a few minutes, while on a smaller box with a simple scsi daisy chain resizing a similarly-sized file can take much longer.

However, personally, I have never seen a resize takes 'seconds' unless the datafile was completely empty and the resize was very small in size (a few megabytes being shaved or added).

Cheers,
GC Received on Tue May 28 2002 - 12:37:27 CDT