So, we have now this new company live since three months and we are checking how much oracle space has been used.

We found good result for all tables except this one: TFGLD498.
The problem is that its row size is quite large and oracle uses one 8kb blocks for ETCH record.

I believe that it should store more than one record in a single block, since otherwise its datafiles would grow too much.

Currently this is what oracle writes about this table

1 select table_name, tablespace_name, num_rows, blocks, empty_blocks, avg_space, AVG_ROW_LEN, CHAIN_CNT
2 from all_tables
3* where table_name = 'TTFGLD498500'

TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT
------------- ------------------ ---------- ---------- ------------ ---------- ----------- ----------
TTFGLD498500 DATA_500 3425033 3461341 3875 4246 3838 0

Its tablespace uses 8kb blocks, so it seems that oracle uses 8192 bytes for a 3838 bytes record.

Oracle uses automatically allocation for this tablespace, so I cannot use any STORAGE parameter in ora_storage.

I think one solution could be to create a tablespace dedicated to this table, using a 32kb block. In this case I think oracle would store at least 6-7 records per page, wasting only about 12-18% instead of 50%.

Any other ideas?

Thanks,
Giuseppe

Depends on your disk setup. You could make a 4k tablespace and put all of the zero sized tables + this table into it, that'd save you more space. But if your OS is doing 8k blocks, then it won't matter.

It's kind of a micromanaged point though, if this is your biggest worry, then you're doing pretty well...

Dave

Hi Dave,
thanks for your reply. I agree with you: setting up a tablespace with 4kb pages for empty/small tables is quite micromanagement.

My question is only about table TFGLD498 since we reached 14Gb of real data (28Gb of oracle segment size plus a small amount for indexes) and we are only at 3 month since live (with 3.5 million records on this table).

I am afraid in two years we will need 100Gb of real data, i.e., 200Gb of segment size. This really need to change our backup strategy since the total company size (data and indexes, segment size) is about 47Gb as of today.

Anyway, I think you agree that my proposed solution (moving to 32kb or 64kb block size for a tablespace for this table) is the right one.

Thanks.
Giuseppe

Yes that's probably the best option.

I'd be worried about generating that much data though, what table is that?
I don't have a 5c/LN env to look at. Can it be archived?

Dave

I am afraid in two years we will need 100Gb of real data, i.e., 200Gb of segment size. This really need to change our backup strategy since the total company size (data and indexes, segment size) is about 47Gb as of today.
Refer to the link - Oracle Compression (http://www.baanboard.com/baanboard/showthread.php?t=41021)
I found the document has published more information for this table as a good candidate for compression.

Disk space reduction
There is a large reduction in disk space for a number of tables in the tfgld range. The actual percentage will differ per customer based on data stored in the tables.
Table Gain in diskspace
Table compression Table + Index compression
% %
tfgld481 88 88
tfgld482 29 68
tfgld495 43 72
tfgld498 96 96

Recommendation
Infor recommends enabling table and indexing compression only for the largest tables in the tfgld range:
Table Data access Comments
Tfgld481 Only inserts Being replaced by tfgld465
Tfgld465 Only inserts Only available in new ERP LN versions
Tfgld482 Inserts and updates
Tfgld495 Inserts and updates
Tfgld498 Only inserts When ‘log all elements’ is disabled, this table is no longer being used
The general recommendation is to enable table and index compression for large transaction logging and history tables which have none, or limited, updates and deletes.
Note that compression is not always very effective. In our test to compress tdsls451 there was 0% disk space improvement for table compression and only 9% for index compression.
1-4 | Summary
Infor does not recommend enabling compression on other ERP LN tables.

Table TFGLD498 Logged elements, 1279994 records
Initial
Table
compress
Table+Index
compress
Segment Bytes Bytes Bytes
TTFGLD498100 3507486720 90177536 90177536
TTFGLD498100$IDX1 55574528 55574528 40894464
Total size (MB) 3398 139 125
Gain % 0 96 96

Time to compress and rebuild indexes
To indicate the expected downtime, the table below shows the duration of the table compressions and index rebuild in our environment. The server is an IBM AIX p550 server with 4 * 1.65 GHz CPU.
Table or index Rows Duration of table
compress
Duration of index
rebuild + compress
Tfgld498 127999
4
71 sec -
Tfgld498101$IDX1 - 5 sec

Hi NPRao,
thanks for the link. I read the whole guide and I think I could try oracle compression. I have to check what oracle licence we have, since EE is required.

Bye,
Giuseppe

Giuseppe,

My 2 cents:
- please also read this thread (http://www.baanboard.com/baanboard/showthread.php?t=41021).
- the document you mentionend has been updated.

Best regards,
Dick

Hi,
TFGLD498 is only used until you have set up prober reconciliation, please look for this in manual.

best regards
alf

I solved the problem in a different way. Still using 8kb blocks it is possible to change PCT_USED and PCT_FREE in order to get two records per block.

Bye,
Giuseppe