q_compress_table

admin · 发表于 2022-9-28 10:18:45

1.当前使用压缩得表和分区

select owner,sum(zs) from
(select owner,count(*) zs
from dba_tables
where compression='ENABLED'
group by owner
union all
SELECT table_owner ,count(*) zs
from (select table_owner,table_name from dba_tab_partitions where compression='ENABLED' group by table_owner,table_name)
group by table_owner
union all
SELECT table_owner ,count(*) zs
from (select table_owner,table_name from dba_tab_subpartitions where compression='ENABLED' group by table_owner,table_name)
group by table_owner
)
group by owner;

复制代码

2.待压缩得表和分区

with st as
(select owner,segment_name,'_NonPart' partition_name,ROUND(sum(bytes)/1048576/1024,2) sizegb
from dba_segments
where segment_type='TABLE'
and owner not in ('SYS','SYSAUX')
group by owner,segment_name
having sum(bytes)/1048576/1024>0.01),
pt30g as
(select owner,segment_name,partition_name,sum(bytes)/1048576/1024 sizegb
from dba_segments
where segment_type in ('TABLE PARTITION','TABLE SUBPARTITION')
and owner not in ('SYS','SYSAUX') and owner='LXJ'
and (owner,segment_name,partition_name) in
(select table_owner,TABLE_NAME,PARTITION_NAME
from dba_tab_partitions
where COMPRESSION<>'ENABLED'
group by table_owner,TABLE_NAME,PARTITION_NAME
union
select table_owner,TABLE_NAME,subPARTITION_NAME
from dba_tab_subpartitions
where COMPRESSION<>'ENABLED'
group by table_owner,TABLE_NAME,subPARTITION_NAME )
group by owner,segment_name,partition_name
having sum(bytes)/1048576/1024>0.01),
pt50g as
(
select owner,segment_name
from dba_segments
where segment_type in ('TABLE PARTITION','TABLE SUBPARTITION')
and owner not in ('SYS','SYSAUX') and owner='LXJ'
group by owner,segment_name
having sum(bytes)/1048576/1024>0.02),
pnc as
(select table_owner,TABLE_NAME,PARTITION_NAME
from dba_tab_partitions
where COMPRESSION<>'ENABLED'
group by table_owner,TABLE_NAME,PARTITION_NAME
union
select table_owner,TABLE_NAME,subPARTITION_NAME PARTITION_NAME
from dba_tab_subpartitions
where COMPRESSION<>'ENABLED'
group by table_owner,TABLE_NAME,subPARTITION_NAME),
t_lob as
(
select OWNER,TABLE_NAME
from dba_tab_columns
where owner not in ('SYS','SYSTEM')
and (data_type like '%LOB%' or data_type like '%LONG%')
group by owner,table_name),
stc as
(select owner,table_name,COMPRESSION,COMPRESS_FOR from dba_tables where IOT_NAME is null),
ptc as
(select table_owner,table_name,partition_name,COMPRESSION,COMPRESS_FOR
from dba_tab_partitions where table_owner not in ('SYS','SYSTEM')
union
select table_owner,table_name,subpartition_name partition_name,COMPRESSION,COMPRESS_FOR
from dba_tab_subpartitions where table_owner not in ('SYS','SYSTEM')),
dc as
(select o.owner,o.object_name,o.SUBOBJECT_NAME,
sum(dhss.db_block_changes_total) bct,
sum(dhss.physical_writes_total) pwt,
sum(dhss.physical_writes_direct_total) pwdt
from dba_hist_seg_stat dhss, DBA_HIST_SEG_STAT_OBJ o
where dhss.obj#=o.obj#
and dhss.DATAOBJ#=o.DATAOBJ#
and o.owner not in ('SYS','SYSTEM')
and o.object_type like 'TABLE%'
group by o.owner,o.object_name,o.SUBOBJECT_NAME)
select st.owner,st.segment_name,st.partition_name,st.sizegb,stc.COMPRESSION ,stc.COMPRESS_FOR ,dc.bct,dc.pwt,dc.pwdt
from st,stc,dc
where (st.owner,st.segment_name) not in (select * from t_lob)
and st.owner=stc.owner
and st.segment_name=stc.table_name
and (st.owner,st.segment_name) not in (select owner,table_name from dba_tables
where COMPRESSION='ENABLED'
and owner not in('SYS','SYSTEM'))
and st.owner=dc.owner(+)
and st.segment_name=dc.object_name(+)
union
select pt30g.owner,pt30g.segment_name,pt30g.partition_name,pt30g.sizegb,
ptc.COMPRESSION ,ptc.COMPRESS_FOR,dc.bct,dc.pwt,dc.pwdt
from pt30g,ptc,dc
where (pt30g.owner,pt30g.segment_name) not in (select owner,table_name from t_lob)
and pt30g.owner=ptc.table_owner
and pt30g.segment_name=ptc.table_name
and pt30g.partition_name=ptc.partition_name
and pt30g.owner=dc.owner(+)
and pt30g.segment_name=dc.object_name(+)
and pt30g.PARTITION_NAME=dc.SUBOBJECT_NAME(+)
union
select ds.owner,ds.segment_name,ds.partition_name,round(bytes/1048576/1024,2) sizegb,
ptc.COMPRESSION ,ptc.COMPRESS_FOR,dc.bct,dc.pwt,dc.pwdt
from dba_segments ds,pnc,ptc,dc
where ds.owner not in ('SYS','SYSTEM')
and ds.segment_type in ('TABLE PARTITION','TABLE SUBPARTITION')
and ds.owner=pnc.table_owner
and ds.segment_name=pnc.table_name
and ds.partition_name=pnc.partition_name
and (ds.owner,ds.SEGMENT_NAME) in (select owner,segment_name from pt50g)
and ds.owner=ptc.table_owner
and ds.segment_name=ptc.table_name
and ds.partition_name=ptc.partition_name
and ds.owner=dc.owner(+)
and ds.segment_name=dc.object_name(+)
and ds.PARTITION_NAME=dc.SUBOBJECT_NAME(+);

复制代码

3.表压缩比评估

set serveroutput on
declare
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);
begin
DBMS_COMPRESSION.GET_COMPRESSION_RATIO (
scratchtbsname=>'TEST', --评估中间表空间
ownname=>'LXJ', --表所属的用户
tabname=>'TT', --评估的目标表名字
partname=>NULL, --分区名字
comptype=>2, --压缩类型，参考6.1.6 说明
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));
end;
/

复制代码

		自动登录	找回密码
密码			立即注册

[日常管理] q_compress_table

浏览过的版块