Re: Tablespace Usage and Performance

On Nov 7, 8:04 am, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> Oracle 10g 10.2.0.3
> Windows 2003 Server
>
> I have someone who made the following statement:
>
> "any time a tablespace is > 85% [ of capacity ] we experience performance
> degragation".
>
> Is there any validity in that or not?
>
> Thanks

(first, note that I'm not disagreeing with Mark, just with the implicit assumptions of such a statement).

It could be a valid observation, but assumes too much. So one needs to get more information to evaluate what it is actually saying.

It is quite possible the procedures involved in maintaining this tablespace (these tablespaces) are causing this issue, rather than an inherent limitation of tablespaces. For example, it could be that reorganizing the tablespace through exp/imp just happens to leave enough room for a certain amount of data to be added/updated, then starts splitting index leaves or chaining rows. The real answer to that would be to adjust how much free space is in blocks, and determine whether new data is added to newly expanded parts of the data and indices. My ERP data, for example, generally has compound keys (ie, company and division before the actual key), so index adds tend to be lumpily spread around the index, with some big lumps in the most active divisions. But it would be silly to make the entire TS have extra space in blocks when only a few recent ones will be updated. There was a time when I would calculate the needs of each of 1000's of tables, but not any more.

I note in my two main tablespaces, one tends to be well into the 90's with no problems. I've had to readjust the nearly worthless EM notifications to stop bothering me about it. The only thing I worry about is actually running out of space, so I tend to keep my (few dozen) data files at 2G noautoextend (unix, just out of habit), with the last one with autoextend maxsize 2G with a big next. Then I just watch that one. For my Windows instances, I just let them do their XE default thing, the only performance issue I see there is when I wake up the instance after some time, which I assume is a windows issue.

This may be of interest: http://jonathanlewis.wordpress.com/2008/05/14/index-efficiency/

I bet your system tablespace is 99.5% :-)

jg

--
@home.com is bogus.
"Unix was meant for users to communicate with each other: people just
don't understand that's what makes it such a cool system. I tell mac
and windows users (who think Facebook is a great way to communicate)
what I was doing fifteen years ago on Berkeley Unix, and they are
astonished. But then they go back to browsing Facebook." -
trashbird_1240