This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Jan 3, 2009 9:32 PM by 581825 RSS

space management issue

user640001 Newbie
Currently Being Moderated
Hi,


Our one of the TS is having three datafiles, two of them having size of 1000 mb and one 512 mb.

File 1) 1000 mb ---- 74 % used
File 2) 1000 mb ---- 74 % used
File 3) 512 mb ---- 80 % used

Now, i have dropped the user and again imported the same through exp / imp.

After doing that one of the file 3) got empty and File 1) got full, File 2) 88 % used

Why the file 3) got empty ???

does the extents have been released ???

also the fragmentation in the TS got removed ??? will this improve the performance at some level ???

With Regards
  • 1. Re: space management issue
    153119 Pro
    Currently Being Moderated
    This is the typical OTN post
    No information about version.
    No information about type of tablespace being used.
    Vague notions like 'fragmentation' being used.
    A locally managed tablespace can not fragment ever.
    Drop user will just drop the user, and -obviously- release all extents.
    Import will begin allocating extents in file 1, until it is full, proceed to file 2 until it is full, etc.

    You don't have an 'issue'
    Everything is working as designed.

    -----
    Sybrand Bakker
    Senior Oracle DBA
  • 2. Re: space management issue
    646531 Newbie
    Currently Being Moderated
    Hi,

    it is normal after dropping a user that all user's objects will be dropped and then all extents willl be released. so that's why file 3) is empty.

    and then after importing, all objects are placed in file 1) and 2), and there is no issue.

    mohamed.
  • 3. Re: space management issue
    EdStevens Guru
    Currently Being Moderated
    sybrandb wrote:
    <snip>
    Import will begin allocating extents in file 1, until it is full, proceed to file 2 until it is full, etc.
    Unless there are other factors I didn't include in my tests, that is not true.

    I recently posted to another thread results demonstrating that extents are allocated in round-robin fashion, attempting to keep file usage in multi-file TS even. I just now re-worked that to see if importing with impdp had a different effect, and found none. Extents and rows continued to be inserted into the files in a round-robin fashion.

    Now, this was a simple test, only one table in a fresh tablespace. It did not factor in other segments in the TS (segments that were not dropped/truncated/recreated), nor did it factor adding files to the TS over a period of time, as other activity continues on the TS.

    So, based on my tests, I can explain everything the OP reported, except the 'front-loading' of the files. However, I agree that the OP doesn't have an issue. It really doesn't matter if the file-load in a multi-file TS isn't level.
    You don't have an 'issue'
    Everything is working as designed.

    -----
    Sybrand Bakker
    Senior Oracle DBA
  • 4. Re: space management issue
    EdStevens Guru
    Currently Being Moderated
    user640001 wrote:
    Hi,


    Our one of the TS is having three datafiles, two of them having size of 1000 mb and one 512 mb.

    File 1) 1000 mb ---- 74 % used
    File 2) 1000 mb ---- 74 % used
    File 3) 512 mb ---- 80 % used

    Now, i have dropped the user and again imported the same through exp / imp.

    After doing that one of the file 3) got empty and File 1) got full, File 2) 88 % used

    Why the file 3) got empty ???

    does the extents have been released ???

    also the fragmentation in the TS got removed ??? will this improve the performance at some level ???

    With Regards
    As others have pointed up - of course when you drop a user:
    1) all of that user's objects have to go away ... you can't have 'orphan' objects.
    2) if an object goes away, its extents will be released. All extents belong to an object, so if the object no longer exists ...

    I can't explain why your TS seems to have been 'front-loaded' into file1 instead of balanced, as all of my tests (and one recently posted to another thread) indicated it should be balanced. However, I can't see where it really matters. We can specify which TS a segment goes in, but we have no control over which file a particular extent of that segment goes to. It really doesn't matter. So, other than the academic question of why the extents got distributed in the fashion they did, you really have no problem.
  • 5. Re: space management issue
    26741 Oracle ACE
    Currently Being Moderated
    As EdStevens has pointed out, Oracle's correct behaviour is to allocate extents in a Round Robin fashion.

    However, there is a caveat : Extent Size v Free Extents.

    It is possible that
    a you have DictionaryManagedTablespace
    b the PCTINCREASE for the Tablespace or for the Table in question is not 0 (eg could be the old default of 50)
    c when you export, the default option of COMPRESS=Y was used

    This resulted in the import attempting to allocate a large Initial Extent (you can query USER_SEGMENTS for the INITIAL that has resulted in the import) and that meant that it took multiple free extents in the first datafile and coalesced them together (an Extent cannot span datafiles).
    The Next Extent may also have been large such that multiple free extents in the second datafile were coalesced.

    I am speculating as to a cause. Without knowing the Tablespace storage parameters, the CREATE TABLE ... INITIAL value and a "map" of Free Extents (FILE_ID, BLOCK_ID, BLOCKS from DBA_FREE_SPACE order by FILE_ID, BLOCK_ID) that existed before the import.

    However, for some information you can still now look at the Tablespace storage parameters, the Table's INITIAL, NEXT and PCTINCREASE value and a map of Free Extents.
  • 6. Re: space management issue
    581825 Journeyer
    Currently Being Moderated
    As EdStevens has pointed out, Oracle's correct behaviour is to allocate extents in a Round Robin fashion.

    Well, Ed may have pointed it out, but he was wrong to do so since that hasn't been Oracle's "correct" behaviour since about 8i days. And you were wrong to repeat his obviously untested assertion without, er, testing it for yourself, because that way you just propagate myths. (Correction: having just re-read Ed's post, it's clear he did make an effort to test this behaviour -but, evidently, his tests were not very exhaustive. A poorly-constructed test can be worse than just making things up, to be honest!)

    Stating things generally, if you have a multi-file DICTIONARY managed tablespace, then you will indeed have extents allocated in a round robin fashion. If you have LOCALLY-MANAGED tablespace with a uniform size extent allocation policy, it is also true that you will have extents allocated in a round robin fashion across all the tablespace's datafiles. But that is NOT the behaviour in LOCALLY MANAGED tablespace which is set to the AUTOALLOCATE extent management policy, which has been the default for a long, long time and which I suspect most of us will be using by now. In LMTs with Autoallocate, file 1 of any tablespace is loaded until it is full and only then are second and subsequent files brought in to play.

    I was going to cut-and-paste a demo of this fairly basic point, but decided it might be quicker in the end simply to refer you to a post dated 2004. That's five years ago. This behaviour is not exactly new, in other words! [http://www.orafaq.com/usenet/comp.databases.oracle.server/2004/09/17/1219.htm|http://www.orafaq.com/usenet/comp.databases.oracle.server/2004/09/17/1219.htm]
  • 7. Re: space management issue
    581825 Journeyer
    Currently Being Moderated
    Just in case anyone was thinking that 9.2.0.x was very old and mightn't apply to 10.2.0.x and above (a very valid line of thinking!), I decided to do the 10.2.0.1 test on Linux after all:
    SQL> create tablespace LMTA datafile '/u01/app/oracle/oradata/lin10/lmta01.dbf' size 10m;
    Tablespace created.
    SQL> alter tablespace LMTA add datafile '/u01/app/oracle/oradata/lin10/lmta02.dbf' size 10m;
    Tablespace altered.
    
    SQL> create table lmtatest (col1 char(5)) tablespace lmta;
    Table created.
    SQL> alter table lmtatest allocate extent;
    Table altered.
    SQL> alter table lmtatest allocate extent;
    Table altered.
    
    SQL> select file_id from dba_extents where segment_name='LMTATEST';
    
       FILE_ID
    ----------
          7
          7
          7
    (So, Locally Managed Autoallocate -everything happens to file 7, as the old post I linked to said it would).
    SQL> create tablespace lmtu datafile '/u01/app/oracle/oradata/lin10/lmtu01.dbf' size 10m extent management local uniform size 64k;
    Tablespace created.
    SQL> alter tablespace lmtu add datafile '/u01/app/oracle/oradata/lin10/lmtu02.dbf' size 10m;
    Tablespace altered.
    
    SQL> create table lmtutest (col1 char(5)) tablespace lmtu;
    Table created.
    SQL> alter table lmtutest allocate extent;
    Table altered.
    SQL> alter table lmtutest allocate extent; 
    Table altered.
    
    SQL>  select file_id from dba_extents where segment_name='LMTUTEST';
    
       FILE_ID
    ----------
          9
          8
          9
    (So, Locally Managed with a Uniform Extent size... files 8 and 9 are used in a round-robin fasion, also as the old post said it would).

    One thing I was not expecting, however: my first test showed File 7 being hammered. No problem there, in a sense: Autoallocate means only one file at a time gets loaded. However, file 7 wasn't the file I was expecting to get hammered to death! Here's my complete set of datafiles:
    SQL> select file#, name from v$datafile;
    
         FILE# NAME
    ---------- -------------------------------------------------------
          1 /u01/app/oracle/oradata/lin10/system01.dbf
          2 /u01/app/oracle/oradata/lin10/undotbs01.dbf
          3 /u01/app/oracle/oradata/lin10/sysaux01.dbf
          4 /u01/app/oracle/oradata/lin10/users01.dbf
          5 /u01/app/oracle/oradata/lin10/example01.dbf
          6 /u01/app/oracle/oradata/lin10/lmta01.dbf
          7 /u01/app/oracle/oradata/lin10/lmta02.dbf
          8 /u01/app/oracle/oradata/lin10/lmtu01.dbf
          9 /u01/app/oracle/oradata/lin10/lmtu02.dbf
    
    9 rows selected.
    My LMTA tablespace consists of files 6 and 7 -and yet it's the second of those that is being used as the store for new extents, not the first. So when I posted earlier that "file 1 of any tablespace is loaded until it is full and only then are second and subsequent files brought in to play", I was being correct in the generalities but incorrect in the specifics. It pays all of us to re-test things from time to time, in other words! Which file in an autoallocate tablespace gets used as the target for all storage until it is full is, in other words, not deterministic. But whichever one it is that gets picked for the first extent is the one that will stay picked for all subsequent extents until it is full. My apologies for over-stating the case earlier.
  • 8. Re: space management issue
    26741 Oracle ACE
    Currently Being Moderated
    Hmm. I wonder if that is a Design Spec or a limitation of the algorithm in AutoAllocate.

    We'd need to test what happens after the table has grown large enough to span 2 or more datafiles -- I guess that by your observations, the Next would always keep going into the same datafile until it can't get an extent in the same datafile.

    We could have 2 datafiles that contain 2 tables and grow the tables gradually to see where the extents get allocated. Later, we drop one of the two tables and keep growing the remaining one to see which datafiles's extents the remainining table uses. AutoAllocate's complication, of course, being that extent sizes grow as the table grows, so the smaller extents from a dropped table would not be reused by the larger table (unless they are contiguous).


    Also, as you point out, it may not be right to repeat something that did hold true in earlier versions, unless testing it in new versions, with different algorithms ! Else, we might end up propagating "fact turned myth" as I have done ?
  • 9. Re: space management issue
    581825 Journeyer
    Currently Being Moderated
    Yeah, well, you can obfuscate it like that if you like.

    Personally, I'd put it this way: the behaviour of space allocation hasn't changed since 8i was released. Which was about 1999 if memory serves. By all means keep testing to see if it changes: it's better than posting out-of-date factual errors, I suppose. But this particular behaviour has been stable for about 10 years now. It ought not to be the subject of major factual errors this far down the track, I'd have said. But whatever... if you feel you need to test it every new version, properly, then be my guest, of course.

    If you have datafiles a, b and c, then in autoallocated lmts, one of them (let us say b) will be the sole target for new extent allocations until it is full. Then another file will be picked (let us say, 'c') and it will be the sole target for new allocations until it is full, and so on. Auto extension of datafiles gets slightly more interesting and a little bit more complex, but not much.

    Your statement about autoallocate ("unless they are contiguous") is also factually incorrect. Everything in autoallocated tablespace is actually mostly 64K under the hood, regardless of the extent sizes allocated. It is extremely hard (though not, I will grant, completely impossible) to fragment it in such a way that a large extent allocated to Table A which has now been dropped couldn't be reused by Table B which is acquiring smaller extents. Let's not add to the myths, shall we?
  • 10. Re: space management issue
    26741 Oracle ACE
    Currently Being Moderated
    It is extremely hard (though not, I will grant, completely impossible) to fragment it in such a way that a large extent allocated to Table A which has now been dropped couldn't be reused by Table B which is acquiring smaller extents
    I am talking of table A having dropped smaller (64KB extents in AutoAllocate) and table B growing with larger extents (8MB and then 64MB).
  • 11. Re: space management issue
    26741 Oracle ACE
    Currently Being Moderated
    See http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:713425138056

    Tom Kyte's tests of April 22, 2005 - 10am US/Eastern :
    Test "(3)System-Managed LMT Allocates from Files Differently"
    where the question is :
    ""How can you achieve 'poor-man's striping' of a table. That is, without using striping software at
    the operating-system level and without using partitioning, how would you stripe a table across
    multiple devices?""

    Quoting from his findings :
    "The output shows the first 16 extents for the system-allocated tablespace all came from the same
    file (FILE_ID 22 in this case). Oracle did this in order to keep all of the small extents together,
    to avoid allocating a lot of small extents in many different files in an attempt to reduce any
    potential fragmentation that might occur if we dropped an object from this tablespace. Only when
    the extents jumped from 8 blocks (64KB) to 128 blocks (1MB) did the round-robin algorithm come into
    play. Once we hit 1MB extents, the allocation went to file 23, 24, 21, 22, 23, and so on."
    and
    "Most of the time, this difference in allocation doesn't really matter. The round-robin technique
    will kick in when the object gets large enough to mandate striping. I point it out mostly to show
    that the round-robin allocation will happen, but a simple test might not make that clear to you. If
    you allocate fewer than 16 extents in a test, you might be under the impression that it will never
    round-robin. (In fact, I was under that impression myself; it was only when making a larger test
    that I discovered the truth.)"
  • 12. Re: space management issue
    581825 Journeyer
    Currently Being Moderated
    Good to see you researching the topic, even if we are both now trading documents four and five years old!

    There was actually a reason I wrote "Stating things generally..." in my first post: autoallocate is a complicated beast and a lot of subtleties arise when its in use that could take all day to discuss. Try this one, for example:
    SQL> create table LMTA2 (col1 char(5)) storage (initial 16M next 16M) tablespace LMTA;
    Table created.
    
    SQL>  select file_id from dba_extents where segment_name='LMTA2';
    
       FILE_ID
    ----------
          7
          6
          7
          6
          7
          6
          7
          6
          7
          6
          7
          6
          7
          6
          7
          6
    
    16 rows selected.
    That's round-robin in auto-allocated LMTs from extent 1 onwards, not extent 16 as stated in Tom's piece. Why? Because when you specify a large initial extent size when creating a table in autoallocated LMTs, Oracle skips allocating small 16K and 64K extents and goes straight to 1MB ones (or even 8MB or 64MB if the initial setting is large enough) -at which point the round robin technique kicks in from the get-go. It was a detail that I didn't bother to mention because it wasn't relevant to the point being made, I thought, though I will cheerfully confess to (and apologise for) having completely forgotten about round-robin kicking in after 1MB for small extent allocations. Your reminder from Tom's work is therefore a useful reminder for me.

    Of course, re-looking at the OP's first post, I also see that he had 740MB of data in each of two datafiles, and he re-created his table via export/import. That amount of data means that the discussion about "less than 1MB of data in small extents doesn't round robin" is utterly irrelevant to the OP. Much more likely as an explanation, therefore, is that his export was done with COMPRESSION=Y, so that the import tried to create a single extent on re-import. Create a table in two-file autoallocate tablespace with INITIAL=1480M and you will (or might!) find the following extent distribution:
       FILE_ID BYTES/1024/1024
    ---------- ---------------
          7          64
          6          64
          7          64
          6          64
          7          64
          6          64
          7          64
          6          64
          7          64
          6          64
          7          64
          6          64
          7          64
          6          64
          7          64
          6          64
          7          64
          6          64
          6          64
          6          64
          6          64
          6          64
          6          64
          7           8
    Autoallocate has switched straight to 64MB extents, so we're immediately into round-robin and file 6 is mostly full (896MB) with the residual coming from file 7 (576MB plus an extra 8MB). So that more or less explains why the OP's file 1 is full (or nearly so) and file 2 is not.

    But an even more interesting point arises: that listing of extents is in extent ID order and therefore represents the order in which they were allocated by the database. That chunk of six repeated allocations to file 6 after a bit of round-robin (extents 18 to 23) is another example of why I spoke "in general terms" the first time I posted: this thing is not straightforward and even when you think you're into round robin mode, Oracle can switch out of that when it feels it appropriate to do so! There's a reason why this thing is "automatic", after all!

    Nevertheless, if it proves impossible to describe every last nuance of autoallocate behaviour in a short post, it is possible to say, clearly, that writing that "Oracle's correct behaviour is to allocate extents in a Round Robin fashion" is extremely untrue. The behaviour is a lot more subtle and complex than that.
  • 13. Re: space management issue
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    Nevertheless, if it proves impossible to describe every last nuance of autoallocate behaviour in a short post, it is possible to say, clearly, that writing that "Oracle's correct behaviour is to allocate extents in a Round Robin fashion" is extremely untrue. The behaviour is a lot more subtle and complex than that.

    Let's that put this in this way ORACLE Behavior is a lot depends on the Extent Size of data going to be inserted, Since based on that, as per the undocumented Algorithm of oracle it checks the Extents size with respect to LMT autoallocate and selects the Candidate Data file (Extents can not span across the file) across the tablespace.

    - Pavan Kumar N
  • 14. Re: space management issue
    581825 Journeyer
    Currently Being Moderated
    This one I agree with completely:

    a lot depends on the Extent Size of data going to be inserted

    But the rest of your description is just a fancy way of saying that "auto" in "autoallocate" means Oracle works stuff out for you, I think! True, but not an exactly Earth-shattering revelation!

    We are left not quite knowing whether the OP's question has been answered, because we've assumed a lot. I've assumed, for example, that his export was done with COMPRESS=Y. I've assumed, too, that he's using autoallocated tablespace. And so on... but we don't actually know. The main points to conclude with, however, are that the behaviour seen by the OP is perfectly normal and nothing at all to be surprised at. And that 'simple round robin' is definitely not the way Oracle can behave -and it's been that way for over 8 years now. Hopefully, too, a few of the subtleties have been teased out along the way.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points