Resolved How Far Into A Variable Extent

BigSlick

Member
Hi All,

Me again! :)

OE10.2BSp05 Windows 64-bit.

I'm racking my brain trying to work out why this doesn't make sense...

My aim is to monitor database extents and report on anything heading towards the variable extent or anything already in there.

I have the following code:

Code:
DEFINE VARIABLE dAreaTotalSize AS DECIMAL     NO-UNDO.
DEFINE VARIABLE dAreaUsedSpace AS DECIMAL     NO-UNDO.
DEFINE VARIABLE dEmptySpace    AS DECIMAL     NO-UNDO.
DEFINE VARIABLE iSize          AS INTEGER     NO-UNDO.

FOR EACH _AreaStatus NO-LOCK:

  FIND _Area WHERE _Area._Area-Number = _AreaStatus._AreaStatus-AreaNum NO-ERROR.

  DISPLAY _AreaStatus WITH WIDTH 320.

  ASSIGN dAreaTotalSize = DECIMAL ( _Areastatus._areaStatus-TotBlocks ) * _Area._Area-blocksize / 1024 / 1024 / 1024
         dAreaUsedSpace = DECIMAL ( _Areastatus._areaStatus-HiWater ) * _Area._Area-blocksize / 1024 / 1024 / 1024
         dEmptySpace    = DECIMAL ( _AreaStatus-Totblocks - _AreaStatus-Hiwater - _AreaStatus-Extents ) * _Area._Area-blocksize / 1024 / 1024 / 1024.

  DISPLAY dAreaTotalSize dAreaUsedSpace dEmptySpace.

  ASSIGN iSize = 0.
  FOR EACH _AreaExtent WHERE _AreaExtent._Area-Number = _AreaStatus._AreaStatus-AreaNum :

    ASSIGN iSize = iSize + _AreaExtent._Extent-Size.
    DISPLAY _AreaExtent WITH WIDTH 320.
  END.
  DISPLAY iSize.

END.

My main issue is that:
Total Blocks is 63328648
Hi-Water Mark is 63328255
Total Size GB is 241.58

Now, the size of all the extents is 243,712,000

243,712,000 / 4096 [blocksize] * 1024 * 1024 * 1024 = 3.5GB

Therefore the extent should be 3.5gb in size (give or take) but its 9GB.

Am i going wrong somewhere?

P.s. I've also read somewhere that variable extents aren't necessarily bad and won't slow down DB performance. Although I've experienced poor performance with variable extents in the past.

Are variable extents slowing the database?

Thanks
 

TheMadDBA

Active Member
I will answer the second question first... assuming you have proper cluster sizes and your disk hardware isn't horrible you aren't likely to see any real world problems with variable extents. If your disk hardware is slow then you should resolve that instead of worrying about variable extents since it will cause much larger issues than just growing extents.

Use proutil dbanalys to show the object usage per area for Type II areas. VST monitoring for space usage with Type II areas has a few issues that aren't easy to get around.

Progress KB - VST's to monitor space in a Type II Storage Area?
 

BigSlick

Member
Hi TheMadDBA.

Thanks for the response. I have imported the dbanalys and used that previously. But to monitor the extents I saw a post that suggested to use VST's hence why used this path. Although the block details are the same from the VST's as they are in the dbanalys.

It does seem another day, another dawn; i've cracked it! It's me looking too hard into it and after spending all day on it yesterday i must have burnt out. Within ten minutes I've realised where i'm going wrong.

243,712,000 / 4096 [blocksize] * 1024 * 1024 * 1024 = 3.5GB

The extent size is in bytes not blocks. The correct formula (to get GB) would be:

243,712,000 / 1024 / 1024 = 232gb.

241gb - 232gb = 9gb.

*This matches the data in the dbanalys.
 

TheMadDBA

Active Member
Sorry... I kind of glazed over when looking at the code :)

You can use the VSTs for basic things like total space but when you get into details like the RM chain and determining if the RPB settings are correct you will have to go back to dbanalys to get proper values.

You might want to consider downloading ProTop to get a jump start.. Tom has spent a lot of time putting quite a few features in there and the code is available to review and/or steal if needed.
 
Top