Thanks for the question, Duda.

Asked: November 03, 2016 - 10:43 pm UTC

Last updated: July 18, 2020 - 4:02 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hello Tom,
I'm trying to use DBMS_COMPRESSION.get_compression_ratio on my AIX box to estimate HCC storage savings if I move to an Exadata platform. MOS notes & many public links says it is possible to use the advisor on NON EXA platforms like my case. For example: Can DBMS_COMPRESSION.COMP_FOR_QUERY_HIGH be Run from a Non-Exadata Database? (Doc ID 1572835.1).

My problem is I'm getting the following error:

ORA-12801 error signaled in parallel query server
ORA-64307: hybrid columnar compression is not supported for tablespaces on this storage type
ORA-06512: at "SYS.PRVT_COMPRESSION", line 1269
ORA-06512: at "SYS.PRVT_COMPRESSION", line 237
ORA-06512: at "SYS.DBMS_COMPRESSION", line 218
ORA-06512: at line 9

Any ideas, what am I missing? I took in count: The table has more than 1 million rows, compatible parameter 12.1.0.2.

My environment:
Oracle DB: 12.1.0.2.0
Server/OS: Power AIX−Based_Systems_(64−bit)
No ASM, datafiles are on FS

The script I'm using is:

SET SERVEROUTPUT ON
DECLARE
l_blkcnt_cmp PLS_INTEGER;
l_blkcnt_uncmp PLS_INTEGER;
l_row_cmp PLS_INTEGER;
l_row_uncmp PLS_INTEGER;
l_cmp_ratio NUMBER;
l_comptype_str VARCHAR2(32767);
BEGIN
DBMS_COMPRESSION.get_compression_ratio (
scratchtbsname => upper('&TBS'),
ownname => upper('&own'),
objname => upper('&obj'),
subobjname => NULL,
comptype => DBMS_COMPRESSION.COMP_QUERY_HIGH,
blkcnt_cmp => l_blkcnt_cmp,
blkcnt_uncmp => l_blkcnt_uncmp,
row_cmp => l_row_cmp,
row_uncmp => l_row_uncmp,
cmp_ratio => l_cmp_ratio,
comptype_str => l_comptype_str,
subset_numrows => DBMS_COMPRESSION.comp_ratio_allrows,
objtype => DBMS_COMPRESSION.objtype_table
);
DBMS_OUTPUT.put_line('Number of blocks used (compressed) : ' || l_blkcnt_cmp);
DBMS_OUTPUT.put_line('Number of blocks used (uncompressed) : ' || l_blkcnt_uncmp);
DBMS_OUTPUT.put_line('Number of rows in a block (compressed) : ' || l_row_cmp);
DBMS_OUTPUT.put_line('Number of rows in a block (uncompressed) : ' || l_row_uncmp);
DBMS_OUTPUT.put_line('Compression ratio : ' || l_cmp_ratio);
DBMS_OUTPUT.put_line('Compression type : ' || l_comptype_str);
END;
/

and Connor said...

I'm thinking that might be a bug, because if I run it *without* parallel, then its ok


SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t as select d.* from dba_Objects d,
  2    ( select 1 from dual connect by level <= 20 )
  3  /

Table created.

SQL>
SQL> select count(*) from t;

  COUNT(*)
----------
   1983480

1 row selected.


SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2  l_blkcnt_cmp PLS_INTEGER;
  3  l_blkcnt_uncmp PLS_INTEGER;
  4  l_row_cmp PLS_INTEGER;
  5  l_row_uncmp PLS_INTEGER;
  6  l_cmp_ratio NUMBER;
  7  l_comptype_str VARCHAR2(32767);
  8  BEGIN
  9
 10  DBMS_COMPRESSION.get_compression_ratio (
 11  scratchtbsname => 'LARGE_TS',
 12  ownname => upper('MCDONAC'),
 13  objname => upper('T'),
 14  subobjname => NULL,
 15  comptype => DBMS_COMPRESSION.COMP_QUERY_HIGH,
 16  blkcnt_cmp => l_blkcnt_cmp,
 17  blkcnt_uncmp => l_blkcnt_uncmp,
 18  row_cmp => l_row_cmp,
 19  row_uncmp => l_row_uncmp,
 20  cmp_ratio => l_cmp_ratio,
 21  comptype_str => l_comptype_str,
 22  subset_numrows => DBMS_COMPRESSION.comp_ratio_allrows,
 23  objtype => DBMS_COMPRESSION.objtype_table
 24  );
 25
 26  DBMS_OUTPUT.put_line('Number of blocks used (compressed) : ' || l_blkcnt_cmp);
 27  DBMS_OUTPUT.put_line('Number of blocks used (uncompressed) : ' || l_blkcnt_uncmp);
 28  DBMS_OUTPUT.put_line('Number of rows in a block (compressed) : ' || l_row_cmp);
 29  DBMS_OUTPUT.put_line('Number of rows in a block (uncompressed) : ' || l_row_uncmp);
 30  DBMS_OUTPUT.put_line('Compression ratio : ' || l_cmp_ratio);
 31  DBMS_OUTPUT.put_line('Compression type : ' || l_comptype_str);
 32  END;
 33  /
DECLARE
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P003
ORA-64307:  Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type
ORA-06512: at "SYS.PRVT_COMPRESSION", line 1269
ORA-06512: at "SYS.PRVT_COMPRESSION", line 237
ORA-06512: at "SYS.DBMS_COMPRESSION", line 218
ORA-06512: at line 10


SQL>
SQL> alter system set parallel_max_servers = 0;

System altered.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2  l_blkcnt_cmp PLS_INTEGER;
  3  l_blkcnt_uncmp PLS_INTEGER;
  4  l_row_cmp PLS_INTEGER;
  5  l_row_uncmp PLS_INTEGER;
  6  l_cmp_ratio NUMBER;
  7  l_comptype_str VARCHAR2(32767);
  8  BEGIN
  9
 10  DBMS_COMPRESSION.get_compression_ratio (
 11  scratchtbsname => 'LARGE_TS',
 12  ownname => upper('MCDONAC'),
 13  objname => upper('T'),
 14  subobjname => NULL,
 15  comptype => DBMS_COMPRESSION.COMP_QUERY_HIGH,
 16  blkcnt_cmp => l_blkcnt_cmp,
 17  blkcnt_uncmp => l_blkcnt_uncmp,
 18  row_cmp => l_row_cmp,
 19  row_uncmp => l_row_uncmp,
 20  cmp_ratio => l_cmp_ratio,
 21  comptype_str => l_comptype_str,
 22  subset_numrows => DBMS_COMPRESSION.comp_ratio_allrows,
 23  objtype => DBMS_COMPRESSION.objtype_table
 24  );
 25
 26  DBMS_OUTPUT.put_line('Number of blocks used (compressed) : ' || l_blkcnt_cmp);
 27  DBMS_OUTPUT.put_line('Number of blocks used (uncompressed) : ' || l_blkcnt_uncmp);
 28  DBMS_OUTPUT.put_line('Number of rows in a block (compressed) : ' || l_row_cmp);
 29  DBMS_OUTPUT.put_line('Number of rows in a block (uncompressed) : ' || l_row_uncmp);
 30  DBMS_OUTPUT.put_line('Compression ratio : ' || l_cmp_ratio);
 31  DBMS_OUTPUT.put_line('Compression type : ' || l_comptype_str);
 32  END;
 33  /
Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1983480
rows
Number of blocks used (compressed) : 1713
Number of blocks used (uncompressed) : 33260
Number of rows in a block (compressed) : 1158
Number of rows in a block (uncompressed) : 59
Compression ratio : 19.4
Compression type : "Compress Query High"

PL/SQL procedure successfully completed.

SQL>
SQL>

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

It worked!

Duda kata, November 07, 2016 - 2:37 pm UTC

Yes, you were right, it is a bug.
After I applied the change it worked just fine. Thank you so much.

November 09, 2016 - 1:49 am UTC

Glad we could help

HCC on non Exadata platform.

Rajeshwaran, Jeyabal, October 10, 2019 - 1:03 pm UTC

Team,

we are running a test case from non-Exadata platform to evaluate the estimated compression ratio for HCC - but the below scripts were failing. could you please advice what privilege are needed for this?

demo@PDB1> create or replace procedure get_comp_ratios(
  2     p_scratchtbsname in varchar2,
  3     p_ownname in varchar2,
  4     p_objname in varchar2,
  5     p_subobjname in varchar2 default null,
  6     p_comptype in varchar2 )
  7  as
  8     l_blkcnt_cmp number;
  9     l_blkcnt_uncmp number;
 10     l_row_cmp number;
 11     l_row_uncmp number;
 12     l_cmp_ratio number;
 13     l_comptype_str varchar2(80);
 14     l_comptype number;
 15  begin
 16     l_comptype := case when p_comptype = 'QUERY_HIGH' then dbms_compression.COMP_QUERY_HIGH
 17                                     when p_comptype = 'QUERY_LOW'  then dbms_compression.COMP_QUERY_
 18                                     when p_comptype = 'ARCHIVE_HIGH'  then dbms_compression.COMP_ARC
 19                                     when p_comptype = 'ARCHIVE_LOW'  then dbms_compression.COMP_ARCH
 20
 21             dbms_compression.get_compression_ratio(
 22                     scratchtbsname => p_scratchtbsname,
 23                     ownname => p_ownname,
 24                     objname => p_objname,
 25                     subobjname => p_subobjname,
 26                     comptype => l_comptype,
 27                     blkcnt_cmp => l_blkcnt_cmp,
 28                     blkcnt_uncmp  => l_blkcnt_uncmp,
 29                     row_cmp => l_row_cmp,
 30                     row_uncmp => l_row_uncmp,
 31                     cmp_ratio => l_cmp_ratio,
 32                     comptype_str => l_comptype_str );
 33             dbms_output.put_line( 'Estimated compression ratio using '|| l_comptype_str ||' : ' ||
 34                     round( l_cmp_ratio, 3) );
 35  end;
 36  /

Procedure created.

demo@PDB1> select count(*) from big_table;

  COUNT(*)
----------
  11000000

demo@PDB1> begin
  2     get_comp_ratios(
  3             p_scratchtbsname=> 'TS_DATA',
  4             p_ownname=> user,
  5             p_objname =>'BIG_TABLE',
  6             p_comptype => 'QUERY_LOW' );
  7  end;
  8  /
begin
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.PRVT_COMPRESSION", line 1309
ORA-06512: at "SYS.PRVT_COMPRESSION", line 1303
ORA-06512: at "SYS.PRVT_COMPRESSION", line 972
ORA-06512: at "SYS.PRVT_COMPRESSION", line 252
ORA-06512: at "SYS.DBMS_COMPRESSION", line 238
ORA-06512: at "DEMO.GET_COMP_RATIOS", line 21
ORA-06512: at line 2

October 21, 2019 - 11:51 am UTC

Don't have privs via a role. You need them direct.

SQL> begin
  2     get_comp_ratios(
  3             p_scratchtbsname=> 'USERS',
  4             p_ownname=> user,
  5             p_objname =>'BIGT',
  6             p_comptype => 'QUERY_LOW' );
  7  end;
  8  /
begin
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.PRVT_COMPRESSION", line 1310
ORA-06512: at "SYS.PRVT_COMPRESSION", line 1304
ORA-06512: at "SYS.PRVT_COMPRESSION", line 973
ORA-06512: at "SYS.PRVT_COMPRESSION", line 252
ORA-06512: at "SYS.DBMS_COMPRESSION", line 250
ORA-06512: at "SCOTT.GET_COMP_RATIOS", line 18
ORA-06512: at line 2


SQL> grant create table to scott;

Grant succeeded.

SQL> conn scott/tiger@db18_pdb1
Connected.

SQL>
SQL> begin
  2     get_comp_ratios(
  3             p_scratchtbsname=> 'USERS',
  4             p_ownname=> user,
  5             p_objname =>'BIGT',
  6             p_comptype => 'QUERY_LOW' );
  7  end;
  8  /

PL/SQL procedure successfully completed.

Is there a way to get the "compressed" ratio?

Allen Stephen, July 16, 2020 - 1:25 pm UTC

Is there a way to get the compressed ratio? The tables were HCC Compressed for Query High and the exported dumps were also compressed with medium compression and the dump size was 310G. The import from these dumps were very slow and the completed in 29 hours with parallel 4. We tried to export without any data pump compression and the size was about 4TB. I retried the export and import with parallel 16 and the import completed in 10.5 hours. Is there a way to identify the schemas where we have maximum compression ratio so that we can accordingly increase the parallels?

July 17, 2020 - 6:35 am UTC

num_row and avg_row_len as still as per uncompressed table, so using that you can get an idea of what the table size would be uncompressed, eg

SQL> create table t pctfree 0
  2  as select * from dba_objects
  3  order by 1,2,3;

Table created.

SQL>
SQL> create table t1 compress
  2  as select * from dba_objects
  3  order by 1,2,3;

Table created.

SQL>
SQL> select blocks, num_rows, avg_row_len, num_rows*avg_row_len/8000 blk_guess
  2  from user_tables
  3  where table_name in ('T','T1');

    BLOCKS   NUM_ROWS AVG_ROW_LEN  BLK_GUESS
---------- ---------- ----------- ----------
      1295      72428         132   1195.062
       482      72429         132  1195.0785

Comparing BLOCKS to BLK_GUESS gives a estimate of the compression

Thank you

Allen Stephen, July 17, 2020 - 3:37 pm UTC

Thank you Connor, it helped.

July 18, 2020 - 4:02 am UTC

glad we could help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

It worked!

HCC on non Exadata platform.

Is there a way to get the "compressed" ratio?

Thank you

More to Explore

PL/SQL demos

PL/SQL docs

DBMS_COMPRESSION.get_compression_ratio ERROR on NON EXA platform

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

It worked!

HCC on non Exadata platform.

Is there a way to get the "compressed" ratio?

Thank you

More to Explore

PL/SQL demos

PL/SQL docs