How to Estimate HCC Ratio in Oracle Database

1) Start SQL*Plus.

2) Create a temporary tablespace:

CREATE TEMPORARY TABLESPACE SAMPLETBS;

3) Run the following script:

SET SERVEROUTPUT ON;

DECLARE
  blkcnt_cmp PLS_INTEGER;
  blkcnt_uncmp PLS_INTEGER;
  row_cmp PLS_INTEGER;
  row_uncmp PLS_INTEGER;
  cmp_ratio NUMBER;
  comptype_str VARCHAR2(32767);

BEGIN
  DBMS_COMPRESSION.GET_COMPRESSION_RATIO('SAMPLETBS', 'USER', 'SAMPLETB', NULL, DBMS_COMPRESSION.COMP_FOR_QUERY_HIGH, blkcnt_cmp, blkcnt_uncmp, row_cmp, row_uncmp, cmp_ratio, comptype_str);

  DBMS_OUTPUT.PUT_LINE('Estimated HCC ratio = ' || cmp_ratio);

END;
/

4) Exit SQL*Plus.

Refer to section 36 of the Oracle Database PL/SQL Packages and Types Reference manual for more information.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s