Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Location
    Shanghai , China
    Posts
    63

    Unanswered: why rollback tablespace is not coalesced

    Just recreate all the rollback segs with
    initial extend = 10M
    next extend = 10M
    optimal size = 20M
    min extend = 2
    max extend = unlimited.

    After 2 days , I checked dba_free_space_coalesced

    select Tablespace_name , percent_blocks_coalesced
    from dba_free_space_coalesced
    order by percent_blocks_coalesced

    I discover that the rollback tablespace again has around 50% fragment . How is possible ? As I already make all the extend same size ?
    Oracle is an ocean . I am just a little fish

  2. #2
    Join Date
    Sep 2002
    Location
    Montréal / Canada
    Posts
    2
    Your initial is to low, dont worry about fragmentation in rollback tablespace. Frag in rollback is normal, anyway the pct_increase should be 0.

    Your next_extent is also big.

    Here is what i use.

    Create public rollback segment rb1
    Tablespace RBS
    Storage (initial 1M next 1M maxextents 400 minextents 400 optimal 400M);

    You should be worry about oracle resizing your rollback instead.

    Here's a query to help:

    select a.name, b.extents, b.rssize, b.xacts, b.waits, b.gets,
    optsize, shrinks, status
    from v$rollname a, v$rollstat b
    where a.usn = b.usn;

  3. #3
    Join Date
    Sep 2002
    Location
    ITALY
    Posts
    53

    RBS configuration

    rollback segment tablespace is to be managed like a paging area, more or less as the TEMP area (like covering with tiles a floor).
    So do not worry about fragmentation IF you have configured the tablespace in this way:

    1. LOCALLY MANAGED (because you are not inflating the dictionary)
    2. pctincrease 0
    3. try to keep the "usual/optimal" number of extents between 10 and 50, I've found no reasons to configure out of that limits, even if sometimes i used as much as 50 extents.
    4. try to have a segment for each concurrent transaction
    5. the dimentin of extents depends on the workload of applciations and so it's up to you to observe the machine and keep a decision.

    Hope this can help you.
    Best regards.
    Franco Ceotto
    SIPTI srl
    OnSite Services
    9i OCP DBA, Performance Engineer

  4. #4
    Join Date
    Sep 2002
    Location
    Montréal / Canada
    Posts
    2
    ORA-02192: "PCTINCREASE not allowed for rollback segment storage clauses", its 0 by default and you cant change it

  5. #5
    Join Date
    Sep 2002
    Location
    ITALY
    Posts
    53

    undo tablespaces are only a recent feature

    oh, i know,
    but nothing states that an RBS tablespace MUST contain only undo segments (untill last days, but we call it undo tablespaces),
    the same thing apply to a TEMP tablespace, it can contain permanent or temporary other segments (and not only the sort ones) if you don't declare the content in a different way.

    Thanks for you kind reply.
    Franco Ceotto
    SIPTI srl
    OnSite Services
    9i OCP DBA, Performance Engineer

Posting Permissions

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