SAP Tablespace sizes in large databases

-----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

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 certain transactions will have errors. .....

-----End of Reply Message-----