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

It might have been useful to know the point from which the resizings took place!

"resize 1750M" -what does that mean? Resized to 1750M from what? 1K? 1650M? 320000M?

Presuming we are resizing UP, and not down, then yes.... these are not excessive sizes that you are resizing TO, therefore I would indeed have expected them to complete in seconds. Therefore, something is wrong, and quite big time, judging by the last example you cite.

If you are resizing DOWN, then there could be a lot of Free/Used Extent work to do on FET$/UET$. Could take a while (but you'd have to have thousands of extents for it to take 34 minutes).

That said, this a curious array of sizes to be setting your data files to. I immediately think 'weird sizes... doesn't know how to manage his tablespaces, or size them in the first place'... which could be an awful calumny, or something approaching the truth, who knows?

My usual rule of thumb: stick to 2Gb tablespaces, and increment as needed by adding in 2Gb datafiles. Never resize a thing.

Regards
HJR "Richard E Sgrignoli" <Richard.Sgrignoli_at_highmark.com> wrote in message news:9a3c69df.0205280519.3a101d52_at_posting.google.com...
> 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
Received on Tue May 28 2002 - 16:32:24 CDT