DB02 - understand record size values

[#1]

Hi,

I know this topic was alrady mentioned, but I couldn't find good answer to my doubts.

What I want to achieve is to get DB size of table record (in order to estimate space usage in production). Right now I have following values from test system:

Can anyone explain what is the difference between:

Avg. length

Avg. lenght + header

Avg. initial length

?

And which one would be the best for capacity estimation?

There was some other thread (link) which suggests Avg. lenght + header, but it seems to me that this value is based on space allocated, not the space actually used by records, so it's useless for capacity planning. I my example difference is huge.

I will apprecieate your help!

Jan

[Former Member]

Hi Jan,

>> Avg. length

Average row size in the table without header info

>> Avg. lenght + header

Average row size in the table with header info

>> Avg. initial length

Empty row size

You should evaluate all the values at the same level, but "Avg. lenght + header" may give more a hint from the capacity planning point of view.

Best regards,

Orkun Gedik

[Former Member]

Thank Orkun,

but what is the header info in this case?

Looking at my example - values in the screenshot are from test system with ~3500 records loaded.

In production I estimate amount of records for ~2,6 mln.

If I take size of the record as 86b, it will give me size of 220Mb,

if I take 99b -> 253Mb, but

if I take 2964b, estimated size will be 7,5Gb

so it makes huge difference!

When I calculate 2964*3460 I'm getting 10Mb, so exactly this size of the table which is reported in the upper part of the DB02 screenshot. This is what makes me think that this value is based on allocated space (instead of real disk usage).

[Former Member]

Hello Jan,

I would not trust these statistics in DB02, they are not precise. (How can it be that an empty row uses up more space than a non-empty row?) Better follow 's advice from the other thread and have a look at SAP note 1295200. Use DBMS_SPACE for calculating the gross and net space usage of a table if you want reliable results.
Regards,

Mark

[Former Member]

It would be great, but I'm not from BASIS team and so I have no access to Oracle. I'm limited to work with SAP transactions.

[Former Member]

>> but what is the header info in this case?

It stores row size, locked or not and the other info, show the difference regarding to the table type.

2964*3460 is correct value. Please note that this is the statistical and calculated value which has been collected by the database. If the threshold value does not exceed, statistical value may not be updated.

Avg. lenght + header value has been calculated by tablespace block size, number of rows, average rows and blocks.

To be sure, go to DB20 and perform an analyze. Or execute the stats at the Oracle level by the DBMS scripts.

Best regards,

Orkun Gedik