confused about temp tablespace in local extent - Page 3
DBAsupport.com Forums - Powered by vBulletin
Page 3 of 3 FirstFirst 123
Results 21 to 29 of 29

Thread: confused about temp tablespace in local extent

  1. #21
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    I can take that Q for Amar:

    That means no matter how big/small table you create, Oracle creates in multiple of distinct extent sizes mentioned(64K,1M,8M,64M).

    In practical terms:

    If you create 512K table in LMT with AUTOALLOCATE it uses 64K

    If you use 10M , it uses IM extent size and if you use 50M, it uses 8M size and so on.

    In first case 8 extents, second case 10 extents, third case 7 extents as opposed to
    6+partial extent for 2MB.
    Reddy,Sam

  2. #22
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by sreddy
    I can take that Q for Amar:

    That means no matter how big/small table you create, Oracle creates in multiple of distinct extent sizes mentioned(64K,1M,8M,64M).

    In practical terms:

    If you create 512K table in LMT with AUTOALLOCATE it uses 64K

    If you use 10M , it uses IM extent size and if you use 50M, it uses 8M size and so on.

    In first case 8 extents, second case 10 extents, third case 7 extents as opposed to
    6+partial extent for 2MB.
    Well, this is only the first (less important, IMHO) part of the autoallocate process. The basic idea is not about the initial extent sizes when table is created, but the adoption of next extent sizes when segment grows. Roughly, the basic idea is something like the following:

    - Untill your table/index is less than 1MB in size, oracle uses 64K extents for it.
    - Once the segment grows beyond 1MB, oracle will start allocating larger extents for it, namely 1MB. So suppose your table is occupying 16 extents of 64KB and all your extents are full, when it needs more space for additional data, it will allocate 17th extent, but this time it will be of size 1MB.
    - Once your segment grows beyong 64MB, the follwing extents allocated will be of size 8MB. So continuing from our previous example, once the table size reaches 64MB (16 extents of 64KB each plus 63 extents of 1MB), the size of next extents used will be 8MB.
    - Once that table grow beyongd 1GB, the remaining extents allocated will be of size 64MB. So continuing with our previos example, once the table grows to 1GB (16 extents of 64KB each plus 63 extents of 1MB plus 120 extents of 8MB), all the following extents allocated for that table will be of size 64MB.

    Now that is *the general theory*, but in praxis and with different releases you could notice some of the "distortions" from that theory. Here is the quote from Jonathan Lewis that discusses his own finding about how AUTOALLOCATE behaves in praxis (the original question was about how AUTOALLOCATE algorithm works and if it has been officialy published by Oracle Corp.):
    From: Jonathan Lewis 05-Feb-03 08:33
    Subject: Re : LMT Autoallocate Algorithm

    I doubt if an algorithm will ever be published.
    Every time someone asks the question the official
    reply is that it is internal. This does mean that
    it can change to address the possible honey-comb
    effect you describe.

    One minor detail - your experiments on sizes and rates
    look as if they were based largely on a nice clean
    tablespace. Oddities can happen (if in a clean start).
    For example, try creating a table with (initial 64M) -
    Oracle will create it with no 64K extents, and 64 x 1M
    extents. Do the same with (initial 65M) and 8.1.7.4
    and 9.2.0.2 will both create the first extent at 8M.
    the next 40 something at 1M, and a couple more at 8M.

    This makes it rather hard to guarantee that moving
    a table, or exp/imp on a table will manage to fill
    any funny little holes - possibly everything you
    move will start allocating at larger sizes.

    On the plus side - extents of size X are allocated on
    a boundary matching X - in other words, they always
    start in 'the right place' for their size. This does
    reduce the probability of excessive honey-combing if
    you have an early period of dropping and recreating
    objects in the tablespace before things stabilise.

    Jonathan Lewis
    Author: Practical Oracle 8i
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #23
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    A rectification from my side.

    You cannot create SYSTEM managed LMT ie cannot use autoallocate for a Temporary LMT it has to be uniform and will be uniform.

    HTH
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  4. #24
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    You can create system LMT in 9.2.0.1

  5. #25
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by pando
    You can create system LMT in 9.2.0.1
    No you cannot
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  6. #26
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Code:
    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.1.0 - Production
    
    lsc@LNX920-RAC1>
    lsc@LNX920-RAC1>select tablespace_name, extent_management from dba_tablespaces;
    
    
    TABLESPACE_NAME                EXTENT_MAN
    ------------------------------ ----------
    SYSTEM                         LOCAL
    UNDOTBS1                       LOCAL
    TEMP                           LOCAL
    DRSYS                          LOCAL
    EXAMPLE                        LOCAL
    INDX                           LOCAL
    TOOLS                          LOCAL
    USERS                          LOCAL
    XDB                            LOCAL

  7. #27
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by pando
    Code:
    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.1.0 - Production
    
    lsc@LNX920-RAC1>
    lsc@LNX920-RAC1>select tablespace_name, extent_management from dba_tablespaces;
    
    
    TABLESPACE_NAME                EXTENT_MAN
    ------------------------------ ----------
    SYSTEM                         LOCAL
    UNDOTBS1                       LOCAL
    TEMP                           LOCAL
    DRSYS                          LOCAL
    EXAMPLE                        LOCAL
    INDX                           LOCAL
    TOOLS                          LOCAL
    USERS                          LOCAL
    XDB                            LOCAL
    I was think in the same line before.

    Check the ALLOCATION_TYPE its uniform always and not system.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  8. #28
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well I think we have some missunderstandings, I thought you was saying SYSTEM tablespace but it seems that you meant AUTOALLOCATE?

  9. #29
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by pando
    well I think we have some missunderstandings, I thought you was saying SYSTEM tablespace but it seems that you meant AUTOALLOCATE?
    Yes, earlier i had suggested, if you read the thread, to use SYSTEM managed Temporary tablespaces ie autoallocate.

    But while testing i saw that each and every extent was 1M in size for the Teporary LMT even if uniform size is not mentioned.

    While for a permanent tablespace even if you do not mention uniform size or autoallocate clause in create tablespace command it creates a system managed LMT.

    So i thought i would rectify my suggestions
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width