prvt_compression.get_allindex_compression_ratio(
scratchtbsname IN VARCHAR2,
ownname IN VARCHAR2,
tabname IN VARCHAR2,
comptype IN NUMBER,
index_cr OUT dbms_compression.compreclist,
comptype_str OUT VARCHAR2,
subset_numrows IN NUMBER);
prvt_compression.get_comression_ratio(
scratchtbsname IN VARCHAR2,
tabowner IN VARCHAR2,
tabname IN VARCHAR2,
tabpart IN VARCHAR2,
comptype IN NUMBER,
blkcnt_cmp OUT BINARY_INTEGER,
blkcnt_uncmp OUT BINARY_INTEGER,
row_cmp OUT BINARY_INTEGER,
row_uncmp OUT BINARY_INTEGER,
cmp_ratio OUT NUMBER,
comptype_str OUT VARCHAR2);
prvt_compression.get_index_compression_ratio(
scratchtbsname IN VARCHAR2,
indexowner IN VARCHAR2,
indexname IN VARCHAR2,
partname IN VARCHAR2,
comptype IN NUMBER,
blkcnt_cmp OUT BINARY_INTEGER,
blkcnt_uncmp OUT BINARY_INTEGER,
row_cmp OUT BINARY_INTEGER,
row_uncmp OUT BINARY_INTEGER,
cmp_ratio OUT NUMBER,
comptype_str OUT VARCHAR2,
subset_numrows IN NUMBER);
prvt_compression.get_lob_compression_ratio(
scratchtbsname IN VARCHAR2,
tabowner IN VARCHAR2,
tabname IN VARCHAR2,
lobname IN VARCHAR2,
partname IN VARCHAR2,
comptype IN NUMBER,
blkcnt_cmp OUT BINARY_INTEGER,
blkcnt_uncmp OUT BINARY_INTEGER,
lobcnt OUT BINARY_INTEGER,
cmp_ratio OUT NUMBER,
comptype_str OUT VARCHAR2,
subset_numrows IN NUMBER);
SELECT dtc.table_name, dt.num_rows
FROM dba_tab_cols dtc, dba_tables dt, dba_lobs dl
WHERE dtc.data_type LIKE '%LOB%'
AND dtc.owner = 'SYS'
AND dtc.owner = dt.owner
AND dtc.owner = dl.owner
AND dtc.table_name = dt.table_name
AND dtc.table_name = dl.table_name
AND dtc.column_name = dl.column_name
AND dl.securefile = 'NO'
AND dt.num_rows > 0
AND dtc.num_nulls < dt.num_rows
ORDER BY 2;
desc wri$_optstat_histhead_history
DECLARE
v1 BINARY_INTEGER;
v2 BINARY_INTEGER;
v3 BINARY_INTEGER;
v4 NUMBER;
v5 VARCHAR2(60);
BEGIN
FOR i IN 1.. 2 LOOP
prvt_compression.get_lob_compression_ratio(
'SYSTEM',
'SYS',
'WRH$_SQL_PLAN',
'OTHER_XML',
NULL,
i,
v1, v2, v3, v4, v5,
1000);
dbms_output.put_line('Comptype = ' || TO_CHAR(i));
dbms_output.put_line(TO_CHAR(v1));
dbms_output.put_line(TO_CHAR(v2));
dbms_output.put_line(TO_CHAR(v3));
dbms_output.put_line(TO_CHAR(v4));
dbms_output.put_line(v5);
END LOOP;
END;
/
*
ORA-20000: Compression Advisor scratch tablespace must be space management auto
SELECT tablespace_name, segment_space_management
FROM dba_tablespaces
ORDER BY 2,1;
DECLARE
v1 BINARY_INTEGER;
v2 BINARY_INTEGER;
v3 BINARY_INTEGER;
v4 NUMBER;
v5 VARCHAR2(60);
BEGIN
FOR i IN 1.. 2 LOOP
prvt_compression.get_lob_compression_ratio(
'SYSAUX',
'SYS',
'WRH$_SQL_PLAN',
'OTHER_XML',
NULL,
i,
v1, v2, v3, v4, v5,
1000);
dbms_output.put_line('Comptype = ' || TO_CHAR(i));
dbms_output.put_line(TO_CHAR(v1));
dbms_output.put_line(TO_CHAR(v2));
dbms_output.put_line(TO_CHAR(v3));
dbms_output.put_line(TO_CHAR(v4));
dbms_output.put_line(v5);
END LOOP;
END;
/ Sampling percent: 92.42
*
ORA-20000: Compression option is not supported for securefile lobs
DECLARE
v1 BINARY_INTEGER;
v2 BINARY_INTEGER;
v3 BINARY_INTEGER;
v4 NUMBER;
v5 VARCHAR2(60);
BEGIN
FOR i IN 1.. 2 LOOP
prvt_compression.get_lob_compression_ratio(
'SYSAUX',
'SYS',
'TABPART$',
'BHIBOUNDVAL',
NULL,
i,
v1, v2, v3, v4, v5,
100); -- note that this table contains only 363 rows so I've dropped the sample size
dbms_output.put_line('Comptype = ' || TO_CHAR(i));
dbms_output.put_line(TO_CHAR(v1));
dbms_output.put_line(TO_CHAR(v2));
dbms_output.put_line(TO_CHAR(v3));
dbms_output.put_line(TO_CHAR(v4));
dbms_output.put_line(v5);
END LOOP;
END;
/
*
ORA-20000: Compression Advisor sample size must be at least 1000. Use COMP_RATIO_ALLROWS if the table has fewer rows.
/* from this demo you can safely surmise that you must choose a tablespace with space management auto,
a table that does not use securefile to store the LOB and a table with 1000 rows or greater. */
I now have feedback from Oracle support and have tried using the COMP_RATIO_ALLROWS constant ... and there is yet another bug.
DECLARE
v1 BINARY_INTEGER;
v2 BINARY_INTEGER;
v3 BINARY_INTEGER;
v4 NUMBER;
v5 VARCHAR2(60);
BEGIN
FOR i IN 1.. 2 LOOP
prvt_compression.get_lob_compression_ratio(
'SYSAUX',
'SYS',
'TABPART$',
'BHIBOUNDVAL',
NULL,
i,
v1, v2, v3, v4, v5,
prvt_compression.comp_ratio_allrows);
dbms_output.put_line('Comptype = ' || TO_CHAR(i));
dbms_output.put_line(TO_CHAR(v1));
dbms_output.put_line(TO_CHAR(v2));
dbms_output.put_line(TO_CHAR(v3));
dbms_output.put_line(TO_CHAR(v4));
dbms_output.put_line(v5);
END LOOP;
END;
/ Sampling percent: 100
*
ORA-20000: Compression option is not supported for securefile lobs
-- the error message is incorrect as can be seen here:
SELECT securefile
FROM dba_lobs
WHERE table_name = 'TABPART$'
AND column_name = 'BHIBOUNDVAL';
prvt_compression.get_lob_new_compr_ration(
scratchtbsname IN VARCHAR2,
tabowner IN VARCHAR2,
tabname IN VARCHAR2,
lobname IN VARCHAR2,
partname IN VARCHAR2,
comptype IN NUMBER,
byte_comp_ratio OUT NUMBER,
subset_numrows IN NUMBER,
cmp_ratio OUT NUMBER,
total_time OUT NUMBER,
blkcnt_uncmp OUT BINARY_INTEGER,
blkcnt_cmp OUT BINARY_INTEGER,
lobcnt OUT BINARY_INTEGER);
prvt_compression.get_table_compression_ratio(
scratchtbsname IN VARCHAR2,
tabowner IN VARCHAR2,
tabname IN VARCHAR2,
partname IN VARCHAR2,
comptype IN NUMBER,
blkcnt_cmp OUT BINARY_INTEGER,
blkcnt_uncmp OUT BINARY_INTEGER,
row_cmp OUT BINARY_INTEGER,
row_uncmp OUT BINARY_INTEGER,
cmp_ratio OUT NUMBER,
comptype_str OUT VARCHAR2,
subset_numrows IN NUMBER);
CREATE TABLE c##abc.comptest
COMPRESS FOR ALL OPERATIONS AS
SELECT * FROM obj$;
DECLARE
v1 BINARY_INTEGER;
v2 BINARY_INTEGER;
v3 BINARY_INTEGER;
v4 BINARY_INTEGER;
v5 NUMBER;
v6 VARCHAR2(60);
BEGIN
FOR i IN 1.. 2 LOOP
prvt_compression.get_table_compression_ratio(
'SYSTEM',
'C##ABC',
'COMPTEST',
NULL,
i,
v1, v2, v3, v4, v5, v6,
1000);
Returns TRUE if a table is compressible, otherwise FALSE
prvt_compression.oltp_compressible(
p_owner IN VARCHAR2,
p_table_name IN VARCHAR2)
RETURN BOOLEAN;
set serveroutput on
BEGIN
IF prvt_compression.oltp_compressible('UWCLASS', 'SERVERS') THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/
