on 06-22-2011 1:03 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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)
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.