When object size shrinks, how do we identify that it happened due to deletion reorganization

Contributor

Hi ,

 

When we see a table size has shrunk, is there a way to identify it happend due to table reorganize that got triggered as 25 % threshold has crossed? Also in the memory object size is the 25% deleted records are taken into consideration?

 

Thanks,

Sreekanth

1 ACCEPTED SOLUTION

Xpert

Hi Sreekanth,

there is a deletedMarkedMargin parameter, but as with all parameter modifications you should talk to exa-support first - also: it would effect you globally, so you can´t manage it on a "size-based-level" or per table.

As for the 2nd question: I can´t draw from experience since we never make the effort to trigger a REORGANIZE TABLE...FORCE manually...as it states in the documentation you might incur a lot of I/O , plus the index rebuilds might be a pain...also, it will lock your table for a while...keeping that in mind you could test how it´ll effect your systems.

Cheers,
Malte

View solution in original post

3 REPLIES 3

Contributor

thank you for the reply.  extending the question. For a table that is around 2 TB, 25% space for deleted rows seems  a lot. 1) Can we configure this % value 2) we can reorganize big tables manually to avoid this space consumption but  are there any disadvantages to this manual process?

 

Xpert

Hi Sreekanth,

there is a deletedMarkedMargin parameter, but as with all parameter modifications you should talk to exa-support first - also: it would effect you globally, so you can´t manage it on a "size-based-level" or per table.

As for the 2nd question: I can´t draw from experience since we never make the effort to trigger a REORGANIZE TABLE...FORCE manually...as it states in the documentation you might incur a lot of I/O , plus the index rebuilds might be a pain...also, it will lock your table for a while...keeping that in mind you could test how it´ll effect your systems.

Cheers,
Malte

View solution in original post

Xpert

Hi Sreekanth,

as I remember the mem_object_size is the actual object size at the given time, so when the reorg hasn´t happend yet the size of the deleted rows is still in there.

You could check in the exa_dba_tables for the DELETE_PERCENTAGE column (if you keep snapshots/backups of that table even "post mortem" ) which would tell you if at a given time the DELETE_PERCENTAGE decreased.

Cheers,
Malte