EHCC Analysis

-- http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_compress.htm#BEICEJED
-- Script analyse the EHCC benefit 

set serverout on verify off feed off
SET PAGESIZE 50000
ACCEPT myScrTbs CHAR DEFAULT 'EHCCSCRATCHPAD' PROMPT 'ScratchPad Tablespace [EHCCSCRATCHPAD] : ';
ACCEPT myCntTbls NUMBER DEFAULT 10 PROMPT 'Number of Top Tables to be Analysed  [10] : ';
create directory ehcc_estimate_dir as '/tmp';
grant read, write on directory ehcc_estimate_dir to sys

declare 
dyn_stmt varchar2(500); 
oradata varchar2(10); 
tbs_num number; 
v_blkcnt_cmp     pls_integer;
v_blkcnt_uncmp   pls_integer;
v_row_cmp        pls_integer;
v_row_uncmp      pls_integer;
v_cmp_ratio      number;
v_comptype_str   varchar2(60);
fileHandler UTL_FILE.FILE_TYPE;

begin
fileHandler := UTL_FILE.FOPEN('EHCC_ESTIMATE_DIR', 'EHCC_ESTIMATE_DATA.txt', 'W');
UTL_FILE.PUTF(fileHandler, 'Copy this Data to an Xls file for better analysis');
UTL_FILE.FCLOSE(fileHandler);

if '&myScrTbs' is not null and &myCntTbls is not null
then
dbms_output.put_line('');
dbms_output.put_line('#################');
dbms_output.put_line('Tablespace is  : &myScrTbs');
dbms_output.put_line('Count of tables  is : &myCntTbls' );
dbms_output.put_line('#################');
select count(1) into tbs_num from dba_tablespaces where tablespace_name='&myScrTbs'; 
IF  tbs_num > 0  then 
dbms_output.put_line('Tablespace  &myScrTbs already exisits'); 
 else 
select value into oradata from v$parameter2 where name ='db_create_file_dest';
dbms_output.put_line('Creating Tablespace &myScrTbs with files in '|| oradata);
EXECUTE IMMEDIATE 'create tablespace &myScrTbs';  
EXECUTE IMMEDIATE 'alter tablespace &myScrTbs add datafile'; 
end if; 
-- Find Object Names 
FOR tbls IN (SELECT OWNER, SEGMENT_NAME , SIZE_GB
FROM (SELECT OWNER, SEGMENT_NAME, BYTES/1024/1024/1024 SIZE_GB 
     FROM DBA_SEGMENTS 
     WHERE SEGMENT_TYPE = 'TABLE'
     ORDER BY     BYTES/1024/1024/1024  DESC )
WHERE ROWNUM <= &myCntTbls
order by SIZE_GB DESC)
LOOP
  DBMS_OUTPUT.PUT_LINE ('Owner = ' || tbls.OWNER || ', Table = ' || tbls.SEGMENT_NAME || ', Size is  = ' ||tbls.SIZE_GB );
  dyn_stmt := 'alter user '||tbls.OWNER||'  QUOTA UNLIMITED ON  &myScrTbs '; 
  EXECUTE IMMEDIATE dyn_stmt ; 
  
  dbms_compression.get_compression_ratio(
scratchtbsname   => upper('&myScrTbs'),      
ownname          => tbls.OWNER,           
tabname          => upper(tbls.SEGMENT_NAME),   
partname         => NULL,           
comptype         => dbms_compression.comp_for_query_high,    
blkcnt_cmp       => v_blkcnt_cmp,    
blkcnt_uncmp     => v_blkcnt_uncmp,  
row_cmp          => v_row_cmp,    
row_uncmp        => v_row_uncmp,  
cmp_ratio        => v_cmp_ratio,  
comptype_str     => v_comptype_str);
-- dbms_output.put_line('Estimated Compression Ratio: '||to_char(v_cmp_ratio));
-- dbms_output.put_line('Blocks used by compressed sample: '||to_char(v_blkcnt_cmp));
-- dbms_output.put_line('Blocks used by uncompressed sample: '||to_char(v_blkcnt_uncmp));
dbms_output.put_line( tbls.OWNER||','|| tbls.SEGMENT_NAME||','|| to_char(v_cmp_ratio)||','||to_char(v_blkcnt_uncmp)||','||to_char(v_blkcnt_cmp)) ;
fileHandler := UTL_FILE.FOPEN('EHCC_ESTIMATE_DIR', 'EHCC_ESTIMATE_DATA.txt', 'A');
UTL_FILE.PUTF(fileHandler, tbls.OWNER||','|| tbls.SEGMENT_NAME||','|| to_char(v_cmp_ratio)||','||to_char(v_blkcnt_uncmp)||','||to_char(v_blkcnt_cmp));
UTL_FILE.FCLOSE(fileHandler);
END LOOP;
dbms_output.put_line('Dropping Tablespace &myScrTbs');
EXECUTE IMMEDIATE 'drop  tablespace &myScrTbs'; 
EXECUTE IMMEDIATE 'drop DIRECTORY ehcc_estimate_dir';
else
dbms_output.put_line('#################');
dbms_output.put_line('#################');
dbms_output.put_line('ERR : at least one of the variables is null ! Cannot proceed ');
dbms_output.put_line('#################');
dbms_output.put_line('#################');
end if;
dbms_output.put_line('Check file /tmp/EHCC_ESTIMATE_DATA.txt for data');
end;
/
exit;