---Original Message---
Subject: BASIS: Tablespace sizes in large databases -Reply [5]
From: mark kochanski
Robert and others following the thread,
First a little background on extents in our production system. We created the instance about 4 months prior to going live. As man of you know, getting down time during the last few months is nearly
impossible, so we saw and let extents grow. In fact, by the time we went live, we had 2 objects over 450, 5 objects over 300, about 50 objects (tables and indices) that were over 100 extents, and we had hundreds of objects over 10 extents.
I agree to doing both planning for growth and monitoring growth. And the earlier in your SAP implementation you do this the better - which is something we did not do until after we created our productive instance.
We were very concerned about this situation and spoke to 3 or 4 different SAP consultants. We got the same answer from each - objects in high extents will have little or no performance impact. Like Sanjay mentioned, the consultants had no specific reason for this.
I do not believe you will find an SAP employed person who will say you should keep extents below a specific value. Also, I cannot definitively give that advice either.
Over the months we have all our objects below 100 extents. We have not seen a significant change in database response time. Our goal is to have all objects below 20 extents - which is a corporate
standard.
But we will not ask for extra down time to reach this goal.
Good luck trying to keep objects below 10 extents. While data is "pumped" into the system during the weeks before going live, whatch the extents, they will take off. This also occurs after performing a SAP version upgrade.
Mark A. Kochanski ---Reply Message---
Subject: Re: BASIS: Tablespace sizes in large databases -Reply [3]
From: "Robert A. Simard"
Gentleman,
Why not do both? Planning for growth is critical. Monitoring daily can be automated via CCMS can it not? With proper alert thresholds, a system freeze can be thwarted long before extents reach 300 (max extents in my version of Oracle).
My question to you both is, how many extents are to many? I have heard from consultants that SAP says that, for performance reasons 10 is the limit. I do not understand the logic in this. Unless There is alot of fragmentation throughout the tables, why not 50 or 100? I just completed a Client Copy and have 4 tables in the BTABD tablespace that are over 17
extents. Is this to many? and should I lose the uptime for a reorg for 17 versus 10 extents?
I guess what I am asking is, since both of you seem to have put some thought into this, is there a hard-and fast number when in comes to an acceptable amount of extents? SAP seems to be overly
conservative most of the time - was wondering if anyone has good numbers?
Thanks and have a great day.
~Bob
--- Robert A Simard SAP-Basis Support, NT Sys. Admin.
"Whoever is first in the field and awaits the coming of the enemy, will be fresh for the fight; whoever is second in the field and has to hasten to battle will arrive exhausted."
Sun Tzu - The Art of War
--- ---Reply Message---
Subject: Re: BASIS: Tablespace sizes in large databases -Reply [3] -Reply From: Sanjay Shastri
Good suggestion and well received ;-)
Now to try and answer your question, looking at just Oracle ( or any DB ), you would think that too many extents would cause problems with your performance ( and that is quite true in most cases ).
However, I believe I read on this list that SAP ignores the extent growth ( no explanation provided ) and that it 'really does not matter how large the number gets'...
We have several tables that are over 150 extents and don't see too much of a performance glitch ( on an overall level ) but in practice, I do not let any table go beyond 100 extents in an SAP environment.
Letting indices grow too much seems to have a much greater impact.
Any comments / insights?
- Sanjay
---Reply Message---
Subject: BASIS: Tablespace sizes in large databases -Reply -Reply From: Sanjay Shastri <[email protected]>
Mark,
would you be willing to risk a system freeze, even if it happens once? I happen to believe in the saying 'prevention is better than cure' !
You are correct that keeping up with extent growth and increasing the size of the next extent via SAPDBA controls the extent problem but, resizing the tables offers one advantage in that you plan better for growth and you are not bogged down by too much of an maintenance effort. System availability IS critical and minimizing downtime doesn't hurt... ;-)
- Sanjay
---Reply Message---
Subject: BASIS: Tablespace sizes in large databases -Reply -Reply From: Mark Kochanski
Sanjay,
Tablespaces will grow and you can add space as needed but if you run out of extents on tables....tough luck!
Why Tough luck? Sure, if a table or index reaches max extents your system will freeze or go down, or
BV_SR_TCOD BC_A Sales Returns - Authorization Check at Transaction Level
C_DML BC_A MDF Object Type
S_ASAP_RF BC_A Implementation Assistant: Roadmap (Type and Flavor) S_ASAP_TF BC_A Separate Roadmap Type and Flavor
S_BDC_MONI BC_A Batch Input Authorizations
S_BPE_CONF BC_A Configuration Inbound Processing for Integration Processes S_BRAN_ADM BC_A Industry management
S_BTCH_ADM BC_A Background Processing: Background Administrator S_BTCH_EXT BC_A External Scheduler
S_BTCH_JOB BC_A Background Processing: Operations on Background Jobs S_BTCH_NAM BC_A Background Processing: Background User Name
S_CCMS_CMC BC_A CCMS Monitoring Console
S_CCM_RECV BC_A Authorizations for Transferring Central System Repos. Data S_CLNT_IMP BC_A Data Import for Client Copy
S_CPIC BC_A CPIC Calls from ABAP Programs
S_CTS_ADMI BC_A Administration Functions in Change and Transport System S_CTS_LANG BC_A Language Transport Administration Functions
S_C_FUNCT BC_A C calls in ABAP programs S_DATASET BC_A Authorization for file access S_DB2_ADM BC_A DB2/390: Database Administration
S_DB2_COMM BC_A DB2 OS/390 commands: in Database Performance Monitor (ST04) S_DBCON BC_A Database Multiconnect
S_DDSTCAUT BC_A Structure Changes in Support Package Systems S_ENQUE BC_A Enqueue: Display and Delete Lock Entries S_ESH_ADM BC_A Administration Enterprise Search Appliance