cancel
Showing results for 
Search instead for 
Did you mean: 

Segments with many allocated extents

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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

Former Member
0 Kudos

Hi Vivek,

Thanks for you quick answer.

I have some more doubts:

  • is there any method to reduce the number (not size) of allocated extents eg: export/import, reorg etc.

  • will the number of extents have any impact on performance of the system

Regards,

Kiran

Former Member
0 Kudos

is there any method to reduce the number (not size) of allocated extents eg: export/import, reorg etc.

Yes, possible with BRtools online reorg.

will the number of extents have any impact on performance of the system

Not very likely, this was only an issue in the past when having dictionary managed tablespaces.

Cheers Michael

Former Member
0 Kudos

Hi Kiran,

Yeah there are some ways in which you can reduce your number of extents...

But its more into DBA job...

One way in which we can do it is:

You can copy that table to new table with new extent sizes , so that you can reduce the number of extent.

2. Number of extents will have little impact on performace when accessing that particular table . But you can plan the next extents correctly.

0 Kudos

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)

Answers (2)

Answers (2)

Former Member
0 Kudos

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

Former Member
0 Kudos

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