Little correction: resize TO A SMALLER SIZE should be a simple action. Why
is explained.
When increasing the size, the blocks are initialized with all zeroes so that
depends on the disk performance and the volume you add.
Anton Buijs <aammbuijs_at_xs4all.nl> schreef in berichtnieuws
ad0lah$qpf$1_at_news1.xs4all.nl...
| 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 - 17:07:39 CDT
