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

Resize should be a simple action. Never seen it taken more then 10 seconds. What can have big impact is the housekeeping of Oracle. I try to explain.

When segments (tables and indexes) grow, they allocate extents: a number of contiguous Oracle blocks in a datafile of it's tablespace as specified when the segment was created (next_extent size specifies the size in bytes). When segments are dropped or reorganized these extents keep registered as free (query the view dba_free_space to see this). Adjacent free pieces are not always coalesced to one free piece in the administration automatically.

So when you resize a file to a smaller size all registered free pieces between the new and the current size must be removed from the dictionary (Oracle's administration). Only when we talk of thousands and thousands free pieces this can take a while.
Lately I read a thread in this group where a DBA did "drop tablespace". He was adviced to check the number of free pieces in the tablespace and he saw that decreasing and decreasing. It took 10 hours(!) before the drop tablespace completed. Unfortunately he never reported back how big the number really was but it must haven been > 50.000.

When database space management is done properly there should be no more then about 1000 free pieces in a tablespace. So check the count of dba_free_space for the tablespace involved before resize, do a "alter tablespace coalesce" and check the count again. It should decrease as much as it can. Resize should be quick then: there is only 1 free piece to remove from the administration. But you have spent the time anyway: now on the "alter tablespace coalesce" statement.

Richard E Sgrignoli <Richard.Sgrignoli_at_highmark.com> schreef in berichtnieuws 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 - 14:21:38 CDT