Re: Tablespace Usage and Performance

On Nov 12, 9:02 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Nov 9, 1:25 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:
>
>
>
>
>
> > 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- Hide quoted text -
>
> > - Show quoted text -
> >> " [EM pops up] "tablespace is full, query slow."  <<
>
> What this example proves is that too many of the programmers Oracle
> hires to write its applications do not understand how Oracle works.
>
> HTH -- Mark D Powell --

While it may be true, I think "proof" isn't there. The real problem is jumping to unwarranted conclusions, like "EM complains my TS is 85% full, I see some vaguely defined slowdown, therefore there is a causal relationship." We probably all agree that more research would need to be done to make such a causation claim, and probably all enjoy the nitpicking involved :-)

In this case, however, I kind of see how the product managers of EM would have to come up with some arbitrary default figure, the lameness comes in describing the use to users. Perhaps a case of good intentions gone to hell, oversimplification changing interpretation to wrong conclusions. In that context, Daniel's answer becomes quite correct, as does your initial answer.

Of course, implicit in the OP is a desire to show the someone who made the statement the error of his ways. That's what I think needs to be addressed. A simple appeal to authority is counterproductive, it even helps myths to propagate as some wrong self-described authorities (including Oracle's own products/docs) are quoted.

jg

--
@home.com is bogus.
“It was really exciting. It’s…it was such a…It’s an amazing feeling.
It’s our first, you know, colored president...” Lindsay Lohan