TST03 LOB Segment grows a lot after billing process

[Former Member]

Hello,

I have a question about LOB Segment corresponding to TST03 (field DCONTENT). TST03 is a table from TEMSE.

We are using ECC 6.0 / NW 7.01.

After a billing process job which creates arround 5 spool requests with arround 19.100 pages without graphics nor logos, I noticed that DB size growed arround 16 Gb the first time.

I used DB02 to detect the grow cause and it was due to the LOB Segment corresponding to field TST03-DCONTENT.

Using SP12 (menu TEMSE -> MEMORY ALLOCATION) I noticed that the TEMSE grow was due to these spool requests we generated.

I read SAP note [500340 - FAQ: LOBS|https://websmp230.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/sapnotes/index2.htm?numm=745639] and detected that this LOB Segment had RETENTION attribute activated.

We deleted the spool requests with SP01 transaction (or report RSPO1041) and run again the billing process. Then de LOB Segment growed again arround 16 Gb (now with a total size of arround 32 Gb). I understood again the meaning of RETENTION property explained in that SAP note.

Then I decided to activate property PCTVERSION instead of RETENTION. I set PCTVERSION to 1 (1% of allocated space for this LOB Segment is now reserved for storing before images). In this case (32 Gb size) arround 320 Mb is reserved.

We deleted again the last spool requests and run once more time the billing process. I supposed that this LOB, with 32 Gb reserved would not grow now again, as it had 320 Mb reserved for undo management and the rest to 32 Gb with free blocks.

But we detected that this LOB Segment growed again to 43.530 Mb.

We deleted again the spool requests and run again the billing process (always is the same billing process). Now the LOB segment growed 320 Mb, to a total size of 43.850 Mb. This is normal if we suppose that the billing process spool requests need these 43.530 Mb more or less. As the LOB Segment had 435 Mb reserved for undo (1% of 43.530 Mb), it needed to increase its size that quantity more or less.

I have 2 questions:

- Is this LOB Segment behavour correct according to its PCTVERSION=1 property activated (instead of RETENTION)?

- Is it normal that about 19.100 billing pages (without images nor logos) in 5 spool requests used 43.850 Mb in LOB Segment corresponding to table TST03? This is an average of arround 2,2 Mb/page.

I think I do not understand something about LOB Segments.

Any help will be appreciated.

Thanks in advance and best regards.

[Former Member]

Hello,

We have been experiencing the same issue but one step further. We have been getting HW contention issues on the LOB index on DCONTENT on our Production instance causing many deadlock issues. I have an OSS note opened to try and rectify the issue. But so far no luck. We are deleting our spool files every hour to avoid locking issues.

Did you ever get anything more on this issue? I would appreciate any help.

[former_member257378]

I'm also interested in this post since we have the similar situation.

TST03 LOB now 300GB.

Any update please?

[Former Member]

Hey Sergio

I put this one on my watch list because i am interested in the possible answers too. As nobody answered so far, i take the occasion to bump this thread.

I did not really do a lot of research though, so i could be easily wrong. In my opinion the problem you have is that you deal with an object that is far too large. It is completely irrelevant if you use RETENTION or PCTVERSION, because the database has to store the before image along with the new version (even for a single second). You cannot put 19'000 pages in 190 pages (1%) worth undo space. So you will end up with at least 38'000 pages in TST03.

So your problem is that the spool is simply too large, you cannot print 19'000 pages, you cannot read them online, i cannot imagine a use for all this data. So what you have to do is make the billing people change their job to only write useful output.

Kind regards, Michael

[Former Member]

Hello Michael,

Now, this LOB segment for table TST03 has 44.426 Mb size. We run a new billing process yesterday and this is the second time this LOB segment does not grow. Therefore, I think the PCTVERSION activation works.

I think it works because the 19.000 pages are not inserted all together, but the are inserted as records in TST03 table (and field DCONTENT in the corresponding LOB segment, scope of this thread).

Even if you are right with respect to that always the before image of a LOB record is stored during a short time, this only would occur for several few records and not for the total records corresponding to those 19.000 pages.

Therefore, I don't thik this LOB Segment is duplicating its size every billing process run.

What I cannot explain is why the first time this billing process run, the LOB segment grew to 16.000 Mb for 19.000 pages, an average of arround 0,84 Mb/page. The page only had several billing positions, and in this run it contained 2 images for 2 vertical texts. These images were 80 kb size.

Supposing this size is correct, the second time it grew another 16.000 Mb (til 32000 Mb). This is explained because at that moment parameter RETENTION was activated for the LOB Segment. RETENTION time was 12 hours (43200 seconds, standar in Oracle for UNDO_RETENTION), and we run this second billing process just after deleting the previous spool requests, I mean, before those 12 hours. Then LOB grew another 16.000 Mb, because the before images of the last run still were not be able to be deleted.

After that I activated PCTVERSION, but now this LOB segment should not have grown more, but its actual size is 44.426 Mb.

This is the strange thing regarding this LOB Segment:

- Initial size of 16.000 Mb for 19.000 Pages.

- Continuous growing even with PTCVERSION=1 activated (only 1% reserved for before images).

Thanks and best regards.

Sergio Medina.