Home -> Mailing Lists -> Oracle-L -> WG: AW: 4 TB LOB Segment (ANYDATA) - Need suggestions

WG: AW: 4 TB LOB Segment (ANYDATA) - Need suggestions

From: <ahmed.fikri_at_t-online.de>
Date: Wed, 5 Aug 2020 18:50:04 +0200 (CEST)
Message-ID: <1596646204020.79947.4435f584176466522ebcc0df295412a49f9c4ac4_at_spica.telekom.de> 



 
 
 
 


Hi,
 


to see whether you have space wasting, you have to compare the length of 
the content with the chunk size from the lob definition.
 


For example if the chunk size = 32k and the size of the value stored in the 
CLB equal to 5k (less then 4k are inlined) then the difference (27k) is 
allocated but not used.
 


Regards


Ahmed Fikri
 
 
 


-----Original-Nachricht-----


Betreff: 4 TB LOB Segment (ANYDATA) - Need suggestions
Datum: 2020-08-05T17:57:11+0200


Von: "Chris Taylor" <christopherdtaylor1994_at_gmail.com>
An: "ORACLE-L" <oracle-l_at_freelists.org>
 
 
 


Env:  12.1.0.2 Linux x86-64
 


So I have the following LOB segment that has reached 4 TB and I'm not sure 
what to do about it if anything.  (I mainly doubt it really needs 4TB of 
space and that we're wasting space here)
 


The COLUMN on the table is an XMLTYPE datatype called SNAPSHOT
 


So I need thoughts/suggestions on how to analyze this guy and determine if 
its wasting space and if so, what are my options?



I've not really worked with LOBs in depth so I'm a bit out of my depth 
here.
 


Any thoughts/comments/suggestions are welcome.



OWNER 	SEGMENT_NAME 	SEGMENT_TYPE 	SEGMENT_SUBTYPE 	TABLESPACE_NAME 	

SIZE_GB 	  	  	  	  	  	  	  	  	  	  	 

METADATA 	SYS_LOB0040419200C00016$$ 	LOBSEGMENT 	SECUREFILE 	METADATA 	

4679.496277 	  	  	  	  	  	  	  	  	  	  	 
                                                                                                                                                                 

TABLE_NAME 	COLUMN_NAME 	INDEX_NAME 	PCTVERSION 	RETENTION 	FREEPOOLS 	
CACHE 	LOGGING 	ENCRYPT 	COMPRESSION 	DEDUPLICATION 	IN_ROW 	FORMAT 	
PARTITIONED 	SECUREFILE 	RETENTION_TYPE 	RETENTION_VALUE
VALUE_LIST 	SYS_NC00016$ 	SYS_IL0040419200C00016$$ 	  	  	  	YES 	YES 	NO 	
NO 	NO 	YES 	NOT APPLICABLE 	NO 	YES 	DEFAULT 	 


I pasted the values above in table format, but if you'd like to see it in 
some other format, let me know.



The results above are generated via this query:



select ds.owner, ds.segment_name, ds.segment_type, ds.segment_subtype, 

ds.tablespace_name, ds.bytes/1024/1024/1024 as size_gb, ls.table_name, 
ls.column_name, ls.index_name, ls.pctversion, ls.retention, ls.freepools, 
ls.cache, ls.logging,
ls.encrypt, ls.compression, ls.deduplication, ls.in_row, ls.format, 
ls.partitioned, ls.securefile, ls.retention_type, ls.retention_value


from dba_segments ds, dba_lobs ls


where ds.owner = 'METADATA'

and ds.segment_type = 'LOBSEGMENT'
and ds.segment_name = ls.SEGMENT_NAME
and ds.owner = ls.OWNER
and ls.table_name = 'VALUE_LIST'




order by ds.bytes desc


/



Thanks,


Chris
 




--
http://www.freelists.org/webpage/oracle-l


Received on Wed Aug 05 2020 - 18:50:04 CEST












Original text of this message