Exponential growth of the SPOTFIRE tablespace

[Yurena Mata]

Hello,

Do you know if Table Fragmentation with LOB, in this case LIB_DATA, occurs frequently with this product

Do you have a procedure / job created to run every x time

 

We attach the actions that we have taken in our environment to detect and solve it.

"The Incidence of the exponential growth of the SPOTFIRE tablespace resolved, the LOB of the LIB_DATA table was being fragmented, after compacting the space we have gone from 56G to 12G.

How did we come to that conclusion

-We search at the database level which object is the one that occupies the most in the tablespace:

SEGMENT_NAME SEGMENT_TYPE SUM(BYTES)/1024/1024/1024

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

SYS_LOB0000114960C00005$$ LOBSEGMENT 57.7373047

SYS_LOB0000114954C00004$$ LOBSEGMENT .359375

SYS_IL0000114960C00005$$ LOBINDEX .328125

LIB_DATA TABLE .0390625

 

 

And who does this LOBSEGMENT belong to

 

TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME

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

LIB_DATA DATA SYS_LOB0000114960C00005$$ SPOTFIRE SYS_IL0000114960C00005$$

 

- We found Dashboards that occupied 8G, and from there we started to pull the thread when it came to space:

 

-Definition / relationship between the 2 tables

"Bookmarks are permanently stored in the TIBCO Spotfire application database in the LIB_ITEMStable, which includes the unique bookmark ID and a CONTENT_SIZE column. The LIB_DATA tables contain the actual data for each bookmark (where LIB_ITEMS.ITEM_ID = LIB_DATA.ITEM_ID)."

-According to the note above, if the tables are related to each other, the sum of the CONTENT_SIZE field of the LIB_ITEMS table would have to be equal to the sum of the DATA field of the LIB_DATA table

 

 

SQL> SELECT SUM(CONTEnt_SIZE)/1024/1024/1024 FROM SPOTFIRE_OWN.LIB_items;

 

SUM(CONTENT_SIZE)/1024/1024/1024

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

11.0693683

SQL> SELECT SUM(DBMS_LOB.GETLENGTH(DATA))/1024/1024/1024 FROM SPOTFIRE_OWN.LIB_DATA;

 

SUM(DBMS_LOB.GETLENGTH(DATA))/1024/1024/1024

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

11.0693683

 

 

- So why does it occupy the LOBSEGMENT 56G and not 11G .... We find the following note that explains it:

 

How to Find Fragmentation for Tables and LOBs (Doc ID 2132004.1)

The size of the LOB segment can be found by querying dba_segments, as follows:

select bytes from dba_segments where segment_name ='' and owner ='';

To get the details of the table to which this LOB segment belong to:

SELECT TABLE_NAME, COLUMN_NAME FROM DBA_LOBS WHERE OWNER = '' AND SEGMENT_NAME= '' ;

Check the space that is actually allocated to the LOB data :

select sum(dbms_lob.getlength ()) from ;

The difference between these two is free space and/or undo space. It is not possible to assess the actual empty space using the queries above alone, because of the UNDO segment size, which is virtually impossible to assess.

 

 

We compare it with our data and we find the BUG!!!

SQL> select bytes/1024/1024 from dba_segments where segment_name ='SYS_LOB0000114960C00005$$' and owner='SPOTFIRE_OWN';

BYTES/1024/1024

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

59123

 

SQL> SELECT SUM(DBMS_LOB.GETLENGTH(DATA))/1024/1024/1024 FROM SPOTFIRE_OWN.LIB_DATA;

SUM(DBMS_LOB.GETLENGTH(DATA))/1024/1024/1024

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

11.0693683

 

 

So we perform the LOB shrink:

10:07:19 SQL> ALTER TABLE SPOTFIRE_OWN.LIB_DATA MODIFY LOB(DATA) (SHRINK SPACE) parallel 8;

Table altered.

"

Regards

[Donald Johnson]

Your image was cut and pasted, creating an external link to the image in your gmail.  When. you deleted the email, the image here disappeared too.

[Yurena Mata]

Hello Don Johnson,

 

How are you Sorry, I don't understand your awswer...Can't you see the images included in this post If the image that you can't see is related with the comment "We found Dashboards that occupied 8G, and from there we started to pull the thread when it came to space:" ,  you can ignore it