Table size higher after Online LOB conversion (BrSpace)

[Former Member]

Hi all,

After several archiving processes run by the application team was the time to reorg the involved table to reclaim the free space. One of the tables (RFBLG) had a lot of WASTED space based on the statistics data:

	BLOCKS	NET_MB	GROSS_MB	WASTED_MB
Before Reorganization	30.295.051	10.195	217.497	207.302
After Reorganization	37.124.387	247.397	266.527	19.131

As you can see the growth in gross space was about 30% (266.527 - 217.497) and in net space was 23 times more (247.397 - 10.195).

Even having a Wasted space of 207.302MB before the reorg, the gross and net size of the table were increased.

As the table had a Long Raw field we run an Online Lob conversion with BRSpace to reorg the table online and convert to LOB.

Do you know any logical reason to have seach high increase after a reorg.

Thanks in advanced.

Cheers

[stefan_koehler]

Hi Jose,

well does your space statistic (After Reorganization) include the LOB segment and index as well or just the "base table"? Is the LOB defined as "ENABLE STORAGE IN ROW" and what is the CHUNK size?

It will always take up space in multiples of the CHUNK parameter, if the data is stored "outline" in the LOB segment, even if you just insert a few bytes.

So at first we need to know what is included in your space statistic data and how the table and LOB is defined exactly.

Regards

Stefan

[Former Member]

Hi Stefan,

The statistics after reorganization only include the "Base table" because the LOB segment generated barely has 125 Kb:

------------------------------------------------------------------------------------------------------------------------------------------

|Segment                                   | Type                 | Header File       | Header Block  |Size(MB) |

|---------------------------------------------------------------------------------------------------------------------------------------- |

|SYS_LOB0000312879C00008$$ | LOBSEGMENT |        835            |  1.044.618      |  0,125    |

-----------------------------------------------------------------------------------------------------------------------------------------

So in this case the proble is not the LOB segment but the table.

Cheers.

Jose

[stefan_koehler]

Hi Jose,

thanks for the clarification of your statistics. However the other important information (like exact object definition) is missing.

Please generate the DDL with help of ABAP report "RSORADDL" and attach the output as text file here. Please also post statistics like NUM_ROWS, AVG_SPACE and AVG_ROW_LEN for RFBLG.

Regards

Stefan

[Former Member]

Here you the full statistics:

---------------------------------------------------------------------

|TABLE_NAME | NUM_ROWS| ROWLEN| BLOCKS| NET_MB|GROSS_MB| WASTED_MB|

|RFBLG | 216.918.000 | 46 | 30.295.051 | 10.195 |217.497 | 207.302 |

- After Reorganization:

|TABLE_NAME | NUM_ROWS| ROWLEN| BLOCKS| NET_MB|GROSS_MB| WASTED_MB|

RFBLG | 239.725.333 | 1.031 | 37.124.387 | 247.397 | 266.527 | 19.131

- The growth in number of rows within the reorg time was around 10%

(239.725.333 - 216.918.000) but the growth in gross space was about 30%

(266.527 - 217.497) and in net space was 23 times more (247.397 -

10.195).

Attached also the DDL.

Thanks.

Cheers.

Jose

RFBLG_DDL.TXT.zip

[stefan_koehler]

Hi Jose,

thanks for providing the detailed information.

1. I am not quite sure how you determined the values, but the column names (like ROWLEN, etc.) look like table fragmentation script by SAP. Please be aware of that statistics created with DBMS_STATS did not take LONG, LONG RAW, and LOB columns into account correctly. (just cross-check your posted row length of 46 bytes for the table with LONG RAW column / Oracle bug #5842686 and event 38087)
2. The (other) real data increase can be explained by the data growth itself and the overhead that is introduced with LOBs. The LOB locator (with LOB ID) is 30 bytes for SecureFile LOBs (as far as i can remember - would need to test it to get the exact values). even if the data is stored in line.

If you can provide a few sample data sets of yours - we could examine point 2 closely.

Was the reorg done in parallel? If yes - then you got ASSM side effects as well.

Regards

Stefan

[Former Member]

Hi Stefan,

I got the statistics directly from Oracle with the sentence:

SELECT * FROM

(SELECT

    SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,

    NUM_ROWS,

    AVG_ROW_LEN ROWLEN,

    BLOCKS,

    ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0) NET_MB,

    ROUND(BLOCKS * (8000 - 23 * INI_TRANS) *

      (1 - PCT_FREE / 100) / 1000000, 0) GROSS_MB,

    ROUND((BLOCKS * (8000 - 23 * INI_TRANS) * (1 - PCT_FREE / 100) -

      (AVG_ROW_LEN + 1) * NUM_ROWS) / 1000000) "WASTED_MB"

  FROM DBA_TABLES

  WHERE

    NUM_ROWS IS NOT NULL AND

    OWNER LIKE 'SAP%' AND

    PARTITIONED = 'NO' AND

    (IOT_TYPE != 'IOT' OR IOT_TYPE IS NULL)

AND TABLE_NAME =' RFBLG'

  ORDER BY 7 DESC)

WHERE ROWNUM <=20;

The layout is SAP style because Ì have run the query through the SQL Command Editor of DB02.

Regarding Parallel processin, yes, the reorg was run in parallel. Could you please let me know what is the ASSM side effects?

I'm working to get some data sets. I'll attach them as soon I get it.

Thanks

[stefan_koehler]

Hi Jose,

i don't mean the SAP style itself - i was just curious about the column naming convention.

However you used the query, that i was suspecting (look closely at the NET_MB calculation and so on). The calculation is based on AVG_ROW_LEN - which is obviously not true at all (46 bytes - Oracle bug #5842686 and event 38087). So forget all of its output

> Could you please let me know what is the ASSM side effects?

Well this would be about "nasty low level details", but basically said ASSM is designed to waste space to increase concurrency (OLTP workload). Increasing the parallelism will cause more "wasted space" at the start. I already took part into a discussion about ASSM on OTN (https://forums.oracle.com/thread/2564075) .. some internals information like presentations and so on is mentioned there.

Regards

Stefan

[Former Member]

Hi Stefan,

regarding the bug 5842686, our Oracle Database version is 11.2.0.3. I had seen that this bug was reported for Oracle 9.2.x and 10.2.x and fixed in the merged patch 6340979 (SAP note #948197). The event 38087 is also set in the database.

Should I trust in the output?

Regards

[stefan_koehler]

Hi Jose,

> Should I trust in the output?

Yes for the current (after reorganization) statistics, but as already mentioned they seem to have a valid value (avg. row length 1.031 bytes) in any case. No for the statistics (before reorganization), it depends on the last analyzed date (statistic gathering) and if the patch and the event was already set at this time, but i would not assume that this was the case based on the provided value (46 bytes).

So you can not trust the initial values (before reorganization) and make a statement based on these.

Regards

Stefan

[Former Member]

Hi Stefan,

Bt there is a week between both statisctics and neither patches nor parameter changes were applied in the meantime. So the database was 11.2.0.3 before and after the reorganization.

In addition I have just run the same query in another 11.2.0.3 database with the event where we have not still reorg the table and the ACG_ROW_LEN 46..

Here you can find a couple of data sets of the table with LONG RAW and BLOB (Field VARDATA):

LONG RAW:

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|MANDT|BUKRS|BELNR     |GJAHR|PAGENO|TIMESTMP      |PAGELG|VARDATA                                                                                                                             |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|300  |2020 |1100000000|2007 |     0|HKZ3jY(VQA52(D|   562|320200010001000100420053004500470020002000200020002000200C14000600010143180000121F9D02AFFD32D8691886C1B0C785CBCE68C73D423706881B200A|
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------------------------

BLOB:

----------------------------------------------------------------------|
|MANDT|BUKRS|BELNR     |GJAHR|PAGENO|TIMESTMP      |PAGELG|VARDATA  
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|010  |2020 |0100000000|2008 |     0|IEDUE3(ZwA5(5R|   570|3A0200010001000100420053004500470020002000200020002000200C14000600010143180000121F9D026DBF5C7A120682383E7AF0C4D970EC472815A3DC84888F78134DBC12630C8972D1831FDFD9|
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|

[stefan_koehler]

Hi Jose,

sorry i maybe was not specific enough.

Can you please exp(dp) a few sample data sets of the table with the LOB and attach the dump file here (with something like this in the WHERE clause "rownum < 500").

> In addition I have just run the same query in another 11.2.0.3 database with the event where we have not still reorg the table and the ACG_ROW_LEN 46..

Yes, but when were the statistics gathered (this is important)? You can crosscheck the "AVG_ROW_LEN" easily by using the PL/SQL procedure UTL_RAW.LENGTH on field VARDATA and calculate the average length of it. The other columns should not be the main space "causer" here.

Regards

Stefan

[sunny_pahuja2]

Hi,

Since you have also done Long RAW to LOB conversion then sometimes it may lead to increase in space. As per SAP note 83552, it says:

• For UNICODE systems, all CLOB data is saved with 2 bytes per character. For this reason, more disk space may be required when you migrate from LONG to CLOB. This restriction affects only the migration from LONG to CLOB, but not the migration from LONG RAW to BLOB. The memory space may also increase for BLOB data since certain characters in the BLOB fields must be converted to Unicode.

Thanks

Sunny

[Former Member]

Dear Sunny,

Thanks for the quick reply.

I have had a look to the note 83552 but it doesn't look like a Technical Note. Most probably was a Typo .

In our case the conversion was from Long Raw to BLOB.

Regards

[sunny_pahuja2]

Sorry, It was a typo. SAP note 835552.

Thanks

Sunny

[Former Member]

Hi,

Thanks sunny.

But in my view the increase is too high for an Unicode Conversion. We are talking about 

237GB more (New Net Size 247 GB - Old Net Size 10 GB).

Any other idea?

Cheeers

Jose