How to estimate UNCOMPRESSED size of tablespace segments for COMPRESSED objects

[SantaMufasa]

Our data warehouse is 5TB compressed. What is a good algorithm to use to calculate the UNCOMPRESSED size?

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[LKBrwnDBA]

Hi Dave,

If you are referring to RMAN backup, perhaps you could query RC_BACKUP_DATAFILE_DETAILS view?
Otherwise the compression utility may have an option and provide the answer.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Ooops, missed the "size of tablespace segments" part from the title.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Thanks LK. Are you aware of any data dictionary view that gives the compression ratio on a segment basis?

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[LKBrwnDBA]

Unfortunately not, because the database stores compressed rows where duplicate values are replaced with a short reference to a symbol table stored at the beginning of the block.
Therefore the compression ratio will depend on the duplicity of data in the rows.

Nevertheless, to get a rough estimate of the uncompressed size for one table I would create an uncompressed table with a sample of the data:

CREATE TABLE My_Table_Uncompresses
AS
    SELECT *
      FROM My_Table SAMPLE BLOCK ( 10 );

And compare sizes using this formula:

uncompressed size = ( compressed #rows ) * uncompressed ( #blocks / #sample.rows )

HTH



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[fredericofonseca]

as far as I am aware there is no real way to get that info when tables are already compressed.

One way of getting an approximation is to for each table get a sample set of records (to occupy at least 1000 blocks) copied to an uncompressed version of the table and then either using dbms_compression package to determine possible compression, or determining space used before compression, issuing alter table move compress, get new size and compare to uncompress size to determine ratio.

hard working and slowish depending on number of tables.

And do see this as it highlights a "correct" way of doing it.

http://decipherinfosys.wordpress.com/2007/01/24/estimating-projecting-the-size-of-a-table-in-oracle/

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886