Re: Tablespace Usage and Performance
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Sun, 09 Nov 2008 19:25:41 +0100
Message-ID: <6noo98FlfpU1@mid.individual.net>
>> On 08.11.2008 02:49, DA Morgan wrote:
>>> Dereck L. Dietz 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
>>>
>>> Not!
>>
>> Hmm, I lean more towards Joel. After all, the statement is just an
>> observation, which we can't really question. The interesting bit is
>> to find out what caused this observation. There may be a common root
>> for both phenomena (for example, heavy insert activity at end of month
>> just before old data is purged, leading to tablespaces filling up AND
>> queries being slow). But of course, there is by far too many detail
>> to only start guessing about something like that. We do not even know
>> what "performance degradation" means. Is it queries, inserts, backup
>> etc.? Just one statement or multiple? ...
Date: Sun, 09 Nov 2008 19:25:41 +0100
Message-ID: <6noo98FlfpU1@mid.individual.net>
On 08.11.2008 21:48, DA Morgan wrote:
> Robert Klemme wrote:
>> On 08.11.2008 02:49, DA Morgan wrote:
>>> Dereck L. Dietz 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
>>>
>>> Not!
>>
>> Hmm, I lean more towards Joel. After all, the statement is just an
>> observation, which we can't really question. The interesting bit is
>> to find out what caused this observation. There may be a common root
>> for both phenomena (for example, heavy insert activity at end of month
>> just before old data is purged, leading to tablespaces filling up AND
>> queries being slow). But of course, there is by far too many detail
>> to only start guessing about something like that. We do not even know
>> what "performance degradation" means. Is it queries, inserts, backup
>> etc.? Just one statement or multiple? ...
> > My point in the simplistic answer is that there may be other things > going on but there is no 1:1 relationship between available space > and performance. Could a lack of space mean that there are other > issues related to performance? Of course. But you can not draw a > straight line that corresponds with the question asked by the OP.
I agree with regard to the straight line. I just felt the answer was probably a bit too simplistic in this case and was trying to avoid subsequent miscommunications e.g.
"any time a tablespace is > 85% [ of capacity ] we experience performance degragation".
"Can't be."
"Are you kidding me? Look at these figures: " [EM pops up] "tablespace is full, query slow."
...
And yes, I confess, I did enjoy the nitpicking. But I felt it could help speak out the unspoken so I did not resist the "temptation". :-)
Kind regards
robert Received on Sun Nov 09 2008 - 12:25:41 CST