Yurena Mata Posted September 8, 2020 Posted September 8, 2020 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 Posted October 5, 2020 Posted October 5, 2020 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 Posted October 6, 2020 Author Posted October 6, 2020 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
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now