Re: segment growth

I think tracking at the segment level is answering a fundamentally different question than at a tablespace level.  Without segment size tracking there's no way to understand where tablespace growth originated.  In my experience it's been common to have developers ask where their space has gone, or I'll notice a jump myself and want to investigate.  Having segment stats makes answering that question much easier.  It's one thing to tell someone that table x is the biggest.  It's entirely different to show them that in the last few months table y has grown 5x with proof of an upward trend.  It's possible that they're unaware of this trend and it's something they need to get in front of quickly.

I've also had edge-case scenarios where an enormous table was temporarily created as a part of an application upgrade.  This ballooned the tablespace before being truncated the next day.  In this situation we were able to determine what object caused the growth.

In the end I wrote a very simple package that maintains these sizes independent of AWR.  I do nightly size snapshots; keeping 30 days of daily, 12 months of monthly, and a persistent end of year. 

I don't need this data daily, but it's easy to gather and has been fantastic to have when these questions do come up.

On 04/07/2014 02:04 PM, Iotzov, Iordan wrote:

Why do you care about segment growth?

You can add/remove disk at tablespaces level, so I think that forecasting at tablespace level makes sense. 

I do not use AWR for disk forecasting because the typical retention period is way too short – usually a couple of weeks. I am aware that it could be changed, but I the disk storage associated with a retention increase can be significant if the retention is to be changed to a few months or a couple of years.

 

You can source your data from the OEM repository (if licensed and used) and/or your own custom repository for capacity and performance data.

While there are many ways/techniques for trend analysis, a simple linear regression is a good first step.

It is important (as Mark already mentioned) that you do confidence calculations, such as confidence intervals and coefficient of determination (R squared), to insure that the model (linear regression in our case) fits your data.

My guess is that for many regular tablespaces, linear regression would be OK.

 

If the model gives too wide confident interval, dues to lack or data points or significant non-linear patterns, or too low R square, you need to accept that the linear model is not good enough and either do “manual” estimates or employ more powerful trend analysis methods.

 

 

Iordan Iotzov | Senior Database Administrator, Information Services | News America Marketing
20 Westport Road, 1^st floor, Wilton CT 06897 | P 203.563.6472 | C 203.423.9269

iiotzov_at_newsamerica.com| newsamerica.com | smartsource.com

 

 

------------------------------------------------------------------
This email is intended solely for the use of the addressee and may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email..
------------------------------------------------------------------

-- 
Pole na kazi

----------------------------------------------------------------------
Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com>
pos   : Senior DBA at DIiUSI
comp  : Nordea IT Polska Sp. z o.o.
addr  : Luzycka 6A st, 81-537 Gdynia, Poland
phone : +48 58 667 17 43
mobile: +48 602 42 42 77
geo   : 54.4952N 18.4332E

Nordea IT Polska Sp.z o.o. z siedzibą w 81-537 Gdynia, ul. Łużycka 6A wpisaną do Rejestru Przedsiębiorców Krajowego Rejestru Sądowego pod numerem: 0000429783, dla której dokumentację przechowuje Sąd Rejonowy dla M.St. Warszawy w Warszawie, XII Wydział Gospodarczy Krajowego Rejestru Sądowego, o kapitale zakładowym i opłaconym w wysokości: 40.000.000,00 złotych, NIP: 527-268-29-50, REGON: 146278110

--
http://www.freelists.org/webpage/oracle-l