Skip to Content
avatar image
Former Member

Segments with many allocated extents

Dear all,

This is regarding the number of allocated extents in any segments.

Our environment is SAP ECC 6.0 in ORACLE (11.2.0.2.0) in AIX.

We are having 2218 allocated extents for table BSIS in the tablespace PSAP<SID>.

The table is 114GB in size and have the default value 2147483645 as max.extents.

Like the same we have around 30 to 40 tables having more than 200 allocated extents.

The CCMS monitoring templates have all these tables in RED for the node 'Most allocated extents in any segment'.

I have tried online reorg for some of these tables and the number of extents gets increased at times.

How can we decrease the number of extents for such tables.

Regards,

Kiran

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Jun 22, 2011 at 12:15 PM

    Hi Kiran,

    1. Oracle recommendation for max extents is "unlimited"

    2. Check the size of the extent for the table in which you have this issue

    3. If you find the size of the extent is too small , then increase the size of the next extent

    And we cannot decrease the size of the extent once it is allocated. We can only plan our next extent sizes.

    Regards,

    Vivek S

    Add comment
    10|10000 characters needed characters exceeded

    • Hi,

      I'll put my 2c here.

      I'm quite sure your table is located in a Locally Managed Tablespace (if you do not know what that is, please, check the oracle documentation)

      In a LMTS, Oracle can decide the size of the extent (autoallocated) or you create a tablespace where ALL objects will have the same size (UNIFORM)

      SAP uses the autoallocated, therefore Oracle makes the decision of the extent size and will change it if the table growth.

      For that reason

      1) reorganization of the table may or may not end with different number of extents, it is a decision taken by oracle, not by the DBA

      2) having a lot of extents is not a problem (unless you are in an old Dictionary Managed Tablespace, which should not be the case)

      see note 825653 Oracle: Common misconceptions, point 19

      IMHO, trying to reduce the number of extents in a LMTS tablespace is a "waste" of time as there is problem to be solved

      I forgot:

      But you can plan the next extents correctly.

      Not, you can't if you use the standard SAP ( autoallocate, Oracle decision)

      You can plan to make different tablespaces with different UNIFORM sizes, but how do you decide for the "correct" size? a lot of work to solve a non existing problem in LMTS (too many extents)

  • avatar image
    Former Member
    Jun 22, 2011 at 12:40 PM

    I think Now a days this can be ignored. Only we can do to increase the size of extend.

    check note Note 483856 - Description of the alerts for Oracle database monitoring Point 1.2.3

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 25, 2011 at 06:02 PM

    Kiran,

    As others have stated, you do NOT want to waste your time reorganizing a table or index due to the number of extents (most of the type anyway).

    First, let's determine what type of tablespaces you have:

    sqlplus "/as sysdba"

    set lines 132 pages 100

    select TABLESPACE_NAME, NEXT_EXTENT, EXTENT_MANAGEMENT, ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT from dba_tablespaces order by 1;

    For example here are my tablespaces I have created:

    TABLESPACE_NAME NEXT_EXTENT EXTENT_MAN ALLOCATIO SEGMEN

    PSAPGLPCAD 524,288,000 LOCAL UNIFORM AUTO

    PSAPGLPCAI2 104,857,600 LOCAL UNIFORM AUTO

    PSAPMEDD 524,288,000 LOCAL UNIFORM AUTO

    PSAPMEDI2 104,857,600 LOCAL UNIFORM AUTO

    PSAPSR3 LOCAL SYSTEM AUTO

    PSAPSR3701 LOCAL SYSTEM AUTO

    PSAPSR3USR LOCAL SYSTEM AUTO

    PSAPTEMP 104,857,600 LOCAL UNIFORM MANUAL

    PSAPUNDO2 LOCAL SYSTEM MANUAL

    PSAPVBFSD2 524,288,000 LOCAL UNIFORM AUTO

    PSAPVBFSI2 524,288,000 LOCAL UNIFORM AUTO

    PSAPVBOXD 524,288,000 LOCAL UNIFORM AUTO

    PSAPVBOXI2 524,288,000 LOCAL UNIFORM AUTO

    SYSAUX LOCAL SYSTEM AUTO

    SYSTEM LOCAL SYSTEM MANUAL

    If your main tablespace PSAPSR3 is LOCALLY managed as shown by the "EXTENT_MANAGEMENT" and the ALLOCATION_TYPE is SYSTEM, then Oracle will determine the NEXT extent sizes and you do not have to worry about it.

    Now the reason you don't really have to worry about extents is that when SAP sends a SQL statement, Oracle determines the execution plan and then Oracle will read date by Oracle blocks NOT by extents. So in R/3 the CBO almost always picks an index as the leader and that means we read the root -> branch -> leaf -> table blocks. We do NOT read by this extent and then that extent. Even FULL TABLESCANS are reading block ranges based on the db_file_multiblock_read_count value.

    Usually the only time we have EXTENT issues is if the tablespace is of type DICTIONARY instead of LOCAL. A DICTIONARY managed tablespace has a different extent management process (UET$ and FET$). Having too many extents in a DICTIONARY managed tablespace "can" cause performance problems but usually only when you are DBA tasks like reorgs because of how Oracle manages the extents and the locking process for the UET$ and FET$. This does not mean the DICTIONARY managed tablespace are bad, just that we have to keep an eye on the extents.

    If your tablespaces are LOCAL then your system is configured properly. If you like, you reorg very large objects into their own tablespaces as I have done, but I do this because of archiving and frequent reorgs to seperate tablespaces so I can just drop the old tablespace. Plus I planned this during a unicode conversion so I don't have to actively manage this over time.

    And as a little big more information, when we use LOCALLY MANAGED AUTOALLOCATE type tablespaces, Oracle uses a formula for the extent sizes. That way, this frees up the DBA (usually) from having to maintain extents and such. What Oracle does is create the 1st 15 extents as 64K, then the batch of extents will be at 1 Mb, then 8 Mb, then 64 Mb. That's why the AUTOALLOCATE makes it "easier" for administration. When we create LOCALLY MANGED UNIFORM tablespace, we decide how large each extent will be an every object that is put in that tablespace will have the same extent sizes (good for very large objects).

    You can test this with:

    create table sapsr3.kiran (t1 number) tablespace psapsr3;

    alter table sapsr3.kiran allocate extent;

    ........ keep adding extents to see how they change over time.

    col segment_name format a20

    set lines 132 pages 100

    select segment_name, extent_id, bytes from dba_extents where segment_name = 'KIRAN' order by extent_id;

    So that's the rational behind extents. I hope it helps.

    Good luck.

    Mike Kennedy

    Add comment
    10|10000 characters needed characters exceeded