Re: MONITORING

On Thu, 24 Jan 2002 21:53:27 GMT,
SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK (Brian Tkatch) wrote:

>On Thu, 24 Jan 2002 20:34:40 +0000, Connor McDonald
><connor_mcdonald_at_yahoo.com> wrote:
>
>>Brian Tkatch wrote:
>>>
>>> When is an item considered stale, so that GATHER STALE collects
>>> recollects statistics?
>>>
>>> Also, I hacked this together:
>>>
>>> SELECT UT.Table_Name, UT.Num_Rows,
>>> UTM.Inserts, UTM.Updates, UTM.Deletes,
>>> ROUND((UTM.Inserts + UTM.Deletes) / DECODE(UT.Num_Rows, 0, 100,
>>> UT.Num_Rows) * 100, 2) || '%' "Percent Added/Deleted",
>>> ROUND((UTM.Inserts + UTM.Updates + UTM.Deletes) /
>>> DECODE(UT.Num_Rows, 0, 100, UT.Num_Rows) * 100, 2) || '%' "Percent
>>> Changed",
>>> UT.Last_Analyzed, UTM.Timestamp "Modification time"
>>> FROM User_Tables UT, User_Tab_Modifications UTM
>>> WHERE UT.Table_Name = UTM.Table_Name
>>> ORDER BY (UTM.Inserts + UTM.Updates + UTM.Deletes) /
>>> DECODE(UT.Num_Rows, 0, 100, UT.Num_Rows) DESC
>>>
>>> Does it make sense?
>>>
>>> Brian
>>
>>I remember reading somehwere that gather_stale is based on 10% deltas,
>>but please don't take that as gospel.
>
>But 10% of INSERTs and DELETEs, or do UPDATEs count as well.
>
>I'm watching GATHER STALE. I just want to know what to watch.
>
>Brian

Just to answer my own question. It appears that the 10% rule is followed, using INSERTs and DELETEs, but ignoring UPDATEs. I have a table with, using the query above, has a Percent Added/Deleted of 24%, but Percent Changed of 32.35%. It was not reanalyzed by the GATHER STALE. Anything above 10% Added/Deleted was however.

Brian Received on Fri Jan 25 2002 - 12:44:16 CST