Category Archives: Compression

check_ac.sql adv compression


 

set head off
set feedback off
set serveroutput on
whenever sqlerror exit;
set termout on
set scan off
set linesize 128

declare

v_hostname sys.v$instance.host_name%TYPE;
v_dbname sys.v$database.db_unique_name%TYPE;
v_db_role sys.v$database.database_role%TYPE;
ex_custom exception;
rowcnt number := 0;
v_message varchar2(50);
v_installed number := 0;
v_used number := 0;
v_ac_cnt number := 0;

sql_stmt1 varchar2(1000) := 'WITH TABLE_COMPRESSION as (' ||
'        SELECT OWNER' ||
'        FROM DBA_TABLES' ||
'        WHERE COMPRESS_FOR IN (''FOR ALL OPERATIONS'', ''OLTP'', ''ADVANCED'')' ||
'        UNION ALL' ||
'        SELECT TABLE_OWNER AS OWNER' ||
'        FROM DBA_TAB_PARTITIONS' ||
'        WHERE COMPRESS_FOR IN (''FOR ALL OPERATIONS'', ''OLTP'', ''ADVANCED'')' ||
'        UNION ALL' ||
'       SELECT TABLE_OWNER AS OWNER' ||
'       FROM DBA_TAB_SUBPARTITIONS' ||
'       WHERE COMPRESS_FOR IN (''FOR ALL OPERATIONS'', ''OLTP'', ''ADVANCED'')' ||
'       )' ||
'       SELECT' ||
'       (SELECT COUNT(*) FROM V$OPTION WHERE PARAMETER = ''Advanced Compression'' and VALUE = ''TRUE'') as Installed,' ||
'       COALESCE((SELECT COUNT(*) FROM TABLE_COMPRESSION WHERE OWNER NOT IN (''SYSMAN'')),0) as Used' ||
'     FROM DUAL';

-- AdvancedCompression_ByLogArchiveCompression
sql_stmt2 varchar2(1000) := 'WITH LOGARCHIVE_COMPRESSION as (' ||
'    SELECT NAME' ||
'    FROM V$PARAMETER' ||
'    WHERE UPPER(name) LIKE ''%LOG_ARCHIVE_DEST%'' AND UPPER(value) LIKE ''%COMPRESSION=ENABLE%''' ||
'    )' ||
'    SELECT' ||
'    (SELECT COUNT(*) FROM V$OPTION WHERE PARAMETER = ''Advanced Compression'' and VALUE = ''TRUE'') as Installed,' ||
'    COALESCE((SELECT COUNT(*) FROM LOGARCHIVE_COMPRESSION),0) as Used' ||
'    FROM DUAL';

-- advancedCompression_ByFeatureName11gR2
sql_stmt3 varchar2(1000) := 'WITH OPTION_DATA as (' ||
'    SELECT ' ||
'        DECODE(CURRENTLY_USED,''TRUE'',1,0) as USED' ||
'    FROM DBA_FEATURE_USAGE_STATISTICS' ||
'    WHERE NAME IN (''HeapCompression'', ''Backup ZLIB Compression'',' ||
'    ''Backup HIGH Compression'', ''Backup LOW Compression'', ''Backup MEDIUM Compression'',' ||
'    ''SecureFile Compression (user)'', ''SecureFile Deduplication (user)'')' ||
'    )' ||
'    SELECT ' ||
'        (SELECT COUNT(*) FROM V$OPTION WHERE PARAMETER = ''Advanced Compression'' and VALUE = ''TRUE'') as Installed,' ||
'        COALESCE((SELECT SUM(USED) FROM OPTION_DATA),0) as Used' ||
'    FROM DUAL';

--AdvancedCompression_ByFeatureDataPumpExport 11.2
sql_stmt4 varchar2(1000) := 'WITH OPTION_DATA as ( ' ||
'    SELECT  ' ||
'        1 as USED' ||
'    FROM' ||
'        DBA_FEATURE_USAGE_STATISTICS' ||
'    WHERE name = ''Oracle Utility Datapump (Export)''' ||
'        AND FEATURE_INFO IS NOT NULL' ||
'        AND REGEXP_LIKE(lower(to_char(FEATURE_INFO)), ''compression used: [1-9]\d* times'')' ||
'    )' ||
'    SELECT ' ||
'        (SELECT COUNT(*) FROM V$OPTION WHERE PARAMETER = ''Advanced Compression'' and VALUE = ''TRUE'') as Installed,' ||
'        COALESCE((SELECT SUM(USED) FROM OPTION_DATA),0) as Used' ||
'    FROM DUAL';

BEGIN
select host_name into v_hostname from v$instance;
select db_unique_name into v_dbname from v$database;
select decode(database_role, 'PRIMARY', 'P', 'S') into v_db_role from v$database                                       ;

if v_db_role <> 'P' then
raise ex_custom;
end if;

&nbsp;

EXECUTE IMMEDIATE sql_stmt1 INTO v_installed,v_used;

if v_used > 0 then
v_message := v_message || 'TableCompression';
v_ac_cnt := v_ac_cnt+1;
end if;

-- AdvancedCompression_ByLogArchiveCompression
EXECUTE IMMEDIATE sql_stmt2 INTO v_installed,v_used;

if v_used > 0 then
v_message := v_message || 'LogArchiveCompression';
v_ac_cnt := v_ac_cnt+1;
end if;

EXECUTE IMMEDIATE sql_stmt3 INTO v_installed,v_used;

if v_used > 0 then
v_message := v_message || 'BackupCompression';
v_ac_cnt := v_ac_cnt+1;
end if;

EXECUTE IMMEDIATE sql_stmt4 INTO v_installed,v_used;

if v_used > 0 then
v_message := v_message || 'DataPumpCompression';
v_ac_cnt := v_ac_cnt+1;
end if;

if v_ac_cnt > 0 then
DBMS_OUTPUT.put_line(v_hostname ||
':' || v_dbname ||
':' || v_db_role ||
':' || v_message ||
':' || v_used);
else
DBMS_OUTPUT.put_line(v_hostname ||
':' || v_dbname ||
':' || v_db_role ||
':' || 'AdvancedCompression_Not_Used' ||
':' || v_used);
end if;

&nbsp;

exception
when ex_custom then
DBMS_OUTPUT.put_line(v_hostname ||
':' || v_dbname ||
':' || v_db_role ||
':' || 'Standby_Database' ||
':' || 'Standby_Database');
when NO_DATA_FOUND then
DBMS_OUTPUT.put_line(v_hostname ||
':' || v_dbname ||
':' || v_db_role ||
':' || 'No_AC' ||
':' || 'No_AC');

end;
/

Compression Check Script

Compression Check Script

#!/bin/ksh
############################################################################
# Script Name..: comp_chk.ksh
# Description..: Estimate the compression for a table
# Author.......: Michael Culp
# Date.........:
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
#
############################################################################
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
set serveroutput on
-- spool <some file name>
declare
   lv_cmp_ratio number;
   lv_comptype_str varchar2(300);
   lv_BLKCNT_CMP number;
   lv_BLKCNT_UNCMP number;
   lv_ROW_CMP number;
   lv_ROW_UNCMP number;
begin
   dbms_compression.GET_COMPRESSION_RATIO(SCRATCHTBSNAME=>'USERS',
                                                 OWNNAME=>'CFGADM',
                                                 TABNAME=>'EQUIPMENT',
                                                PARTNAME=>null,
                                                COMPTYPE=>2, ---2 means OLTP
                                             BLKCNT_CMP =>lv_BLKCNT_CMP,
                                           BLKCNT_UNCMP =>lv_BLKCNT_UNCMP,
                                                ROW_CMP =>lv_ROW_CMP,
                                              ROW_UNCMP =>lv_ROW_UNCMP,
                                               CMP_RATIO=>lv_cmp_ratio,
                                            COMPTYPE_STR=>lv_COMPTYPE_STR);
dbms_output.put_line('====================================================');
dbms_output.put_line('1. Compression Ratio.: '||lv_cmp_ratio);
dbms_output.put_line('2. Block Count.......: '||lv_blkcnt_cmp);
dbms_output.put_line('3. Compression Type.......: '||lv_comptype_str);
dbms_output.put_line('4. Blk Count Compressed...: '||lv_BLKCNT_CMP);
dbms_output.put_line('5. Blk Count Un-compressed: '||lv_BLKCNT_UNCMP);
dbms_output.put_line('6. Row Count Compressed : '||lv_row_cmp);
dbms_output.put_line('4. Row Count Un-Compressed: '||lv_row_uncmp);
dbms_output.put_line('====================================================');
end;
/
-- spool off
EOF

Compression Example