LOBSEGMENT Growing Fast

[former_member433211]

Hi,

We have a LOBSEGMENT which is growing quite fast is related to the Java schema of our XI system. I have some doubts:

What transaction can I used to see which tables are associated to this lobsegment?

The lobsegment is 320GB big but the biggest java table is only 35GB is this behaviour normal?

Thanks in advance.

[stefan_koehler · Accepted solution]

Hello Ruben,

> What transaction can I used to see which tables are associated to this lobsegment?

It can only be one table.. and you can use the following SELECT statement

shell> sqlplus / as sysdba
SQL> SELECT OWNER, TABLE_NAME, COLUMN_NAME FROM ALL_LOBS WHERE SEGMENT_NAME = '<LOB_NAME>';

> The lobsegment is 320GB big but the biggest java table is only 35GB is this behaviour normal?

Which table? What is stored in the LOB segment? It can be .. but it don't have to.

Maybe it is the LOB retention, etc... if you don't have any idea about LOBS you can read the following white paper by oracle:

http://www.oracle.com/technology/products/database/application_development/pdf/lob_performance_guide...

Regards

Stefan

P.S.: What is your oracle version?

[former_member433211 · Accepted solution]

Hi,

The table associated to lobsegment is only 13GB, the payload of XI messages are stored in the LOB.

Still looks to me that something is not correct 320GB for a 13GB table?

Is the LOB field stored physically separated from the table itself?

Thanks.

Edited by: Ruben Hopkins on Apr 14, 2009 11:37 AM

[stefan_koehler · Accepted solution]

Hello Ruben,

> Is the LOB field stored physically separated from the table itself?

Just check the documentation that i mentioned above.

LOB storage is said to be in-line when the LOB data is stored with the other column data in the row. A LOB can only be stored in-line if its size is less than ~4000 bytes. 
...
LOB storage is said to be out-line when the LOB data is stored, in CHUNK sized blocks in the lob segment, seperate from the other column's data.

> Still looks to me that something is not correct 320GB for a 13GB table?

As i already told .. it can be.. that is the use for LOBs (to swap out the amount of data).

- Is data deleted from this LOB continuous? If yes what is the retention or pctversion?

- What is the chunk size (how big is the inserted data)? Is storage in row enabled or not (then the size of the data matters), etc..

All these stuff is explained in the white paper that i mentioned above. Check this and get an idea about the LOB in your particular case.

Regards

Stefan

[former_member433211 · Accepted solution]

Hi, I received the following information:

SQL> SELECT * FROM dba_lobs where segment_name like 'SYS_LOB0000085157C00020%';

OWNER TABLE_NAME

-

---

-

---

COLUMN_NAME

-

---

SEGMENT_NAME TABLESPACE_NAME

-

---

-

---

INDEX_NAME CHUNK PCTVERSION RETENTION FREEPOOLS

-

---

-

---

-

---

-

---

-

---

CACHE LOGGING IN_ FORMAT PAR

-

---

-

---

--- -

---

---

SAPSR3DB XI_AF_MSG

MSG_BYTES

SYS_LOB0000085157C00020$$ PSAPSR3DB

OWNER TABLE_NAME

-

---

-

---

COLUMN_NAME

-

---

SEGMENT_NAME TABLESPACE_NAME

-

---

-

---

INDEX_NAME CHUNK PCTVERSION RETENTION FREEPOOLS

-

---

-

---

-

---

-

---

-

---

CACHE LOGGING IN_ FORMAT PAR

-

---

-

---

--- -

---

---

SYS_IL0000085157C00020$$ 8192 10

NO YES YES NOT APPLICABLE NO

The PCTVERSION is 10, and it seems that the CHUNK Size is 8Kb, is it possible that the lobsegment is fragmented? if so is it a way to de-fragment it?

Thanks in advance.

[stefan_koehler · Accepted solution]

Hello Ruben,

at first i have to say that i have no XI system here, so i can not take a look at that specific table.

> The PCTVERSION is 10, and it seems that the CHUNK Size is 8Kb, is it possible that the lobsegment is fragmented? if so is it a way to de-fragment it?

The default value for PCTVERSION is 10.. it means that older versions of the LOB data are not overwritten until they consume 10% of the overall LOB storage space. In your particular case round about 32 GB. But this value "can only" be reached in the case of LOB manipulations.

As you can see the column MSG_BYTES is stored in the LOB. So it depends on your message sizes if the 8kb of each chunk is completely used. An example of space wastage is given in the oracle white paper that i mentioned above.

I searched in service marketplace for the table XI_AF_MSG and found a sapnote about "Preventing Basis tables from increasing considerably". Please check sapnote #706478 and the detailed one #872388 for "Troubleshooting Archiving and Deletion in XI 3.0 / PI 7.0".

I just think that you don't archive or delete (or have problem with both) your messages .. so in this case the LOB segment size is "normal".

Regards

Stefan

[former_member433211 · Accepted solution]

Hi,

Actually the deletion job for the Adapter Engine runs daily and deletes messages older than 4 days, this job hits the specific table for the LOBSEGMENT.

Thanks a lot for your help.