计算表在压缩的比率
解决方案:
1)创建一个有相册数据和存储参数的非压缩表与压缩表
2)确定非压缩表的大小 (DBA_EXTENTS.BYTES)
3)确定压缩表的大小 (DBA_EXTENTS.BYTES)
4) 计算压缩比率
以上测试,表的extent sizes尽量保持小些,以便extents中非使用空间到最小化,以便得到的精准不被扭曲的结果
数据库版本
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
本测试用全程用SYS用户执行
1.创建一个表空间
SQL>create tablespace test
datafile '/u01/app/oracle/oradata/HXZGSJQY/test01.dbf'
size 2000m extent management local uniform size 128k;
Tablespace created.
2.创建一个压缩的表
SQL> create table compressed compress tablespace test as select * from dba_source;
Table created.
3.创建一个非压缩的表
SQL>create table non_compressed nocompress tablespace test as select * from dba_source;
Table created.
4计算压缩表COMPRESSED大小
SQL> select sum(bytes) from dba_extents where segment_name = 'COMPRESSED' and owner = 'SYS';
SUM(BYTES)
----------
58720256
5.计算非压缩表大小
SQL> select sum(bytes) from dba_extents where segment_name = 'NON_COMPRESSED' and owner = 'SYS';
SUM(BYTES)
----------
88342528
6.计算压缩比率
100 - (( 58720256 / 88342528 ) * 100) = 33.53%
11g 计算表在压缩的比率
最新推荐文章于 2024-05-13 15:50:32 发布