-- 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;
-- 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;
