Re: segment growth

I'm going to agree with Ryan that segment-level statistics on growth tells a better tale; the DBA can see which segments are growing the most in a given period of time and can plan accordingly.  Simply monitoring the tablespace doesn't provide enough information for me; I want/need to know which segments are 'at fault' for 'eating up' the space.  It's then possible to track down an errant process or piece of code doing things it probably shouldn't, knowing which table or tables it's using.  It's also possible to see when extremely large tables are created 'on-the-fly', tables that may not be around very long, that extend a tablespace or consume most of the free space you had yesterday.  Such knowledge allows the DBA to plan for such occurrences and possibly create tablespaces for such objects ('temporary' tables that aren't global temporary tables).


 
David Fitzjarrell
Primary author, "Oracle Exadata Survival Guide"

On Monday, April 7, 2014 1:06 PM, "Iotzov, Iordan" <IIotzov_at_newsamerica.com> 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, 1st floor, Wilton CT 06897 | P 203.563.6472203.563.6472| C 203.423.9269203.423.9269
iiotzov_at_newsamerica.com| newsamerica.com | smartsource.com
 
 
From:oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Remigiusz Sokolowski
Sent: Wednesday, April 02, 2014 2:18 AM
To: oracle-l
Subject: segment growth
 
hi,

I have just tried to realize how to count a segment growth trend based on the dba_hits_seg_stat rows. 
And now I am puzzled:
1. on the metalink I have found the article 1395195.1, which suggests to simply sum the db_block_changes_delta
2. I displayed the data for some particular segment to find out the possible relations between numbers and I would say this is rather the value, which indicates literally the delta of block changes i.e. all the deletes, inserts, updates and not only new blocks
 - for example because the db_block_changes_delta is always positive and db_block_changes_total always grows
3. So I look at space_used_total assuming this is the total space used by the segment in the moment a snap is taken - and here longer example

current size of the object
-----------------
object_name | blocks | bytes
ZABBIX.ITEMS        4992    40894464

excerpt from snap values - few subsequent snaps 
-----------------
OBJ_SNAP, SPACE_USED_TOTAL, SPACE_USED_DELTA,
13356    933731    9503
13357    937827    4096
13358    940940    3113
13359    -3277    -3277
13360    737    4014
13361    6062    5325
13362    7045    983

until 13358 all is fine I see the logic ( space_used_total(n)=space_used_total(n-1)+space_used_delta(n) )
but then negative total value (and the logic's gone) - no idea what to think about that.
And the main question - how to calculate the segment growth trend in a reliable manner?

Best regards
Remigiusz


-- 
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+48 58 667 17 43
mobile: +48 602 42 42 77+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
________________________________
 This message and its attachments may contain legally privileged or confidential information. It is intended solely for the named addressee. If you are not the addressee indicated in this message (or responsible for delivery of the message to the addressee), you may not copy or deliver this message or its attachments to anyone. Rather, you should permanently delete this message and its attachments and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of News America Incorporated or its subsidiaries must be taken not to have been sent or endorsed by any of them. No warranty is made that the e-mail or attachment(s) are free from computer virus or other defect.

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