Oracle 高低水位线的学习

背景

最近产品的一些脚本会大量的给一些流程表里面插入数据
因为只是一个流程相关没有时序查询的需求
所以数据量挺大, 但是按照石时间戳删除非常麻烦. 
自己执行过多次delete 但是使用自己的SQL查询表大小,发现总是失败
想起来可能跟高低水位线有关系, 所以这里学习了解一下. 

我理解水位线不进行处理, 在进行全表扫描时 ,会多扫描很多无效的区块
对性能是一个伤害, 所以应该努力降低一下相应的水位线. 

Oracle为了性能 并不会将所有的表放到一个特定的增长位置
所以就会出现, 数据库数据文件增长上去之后没有办法再缩小回来. 

SQLSERVER 没有这样的问题, 但是性能会差一下

为了解决这样的问题 exp/expdp 的备份恢复方式其实是会修改高低水位线的

再不是非常大的 并且有停机时间的环境下面执行一下备份恢复 重建表的查询统计信息对性能其实很有帮助.

查询情况

表空间名称	表空间大小(M)	表空间剩余大小(M)	表空间使用大小(M)	表空间大小(G)	表空间剩余大小(G)	表空间使用大小(G)	使用率 %
SYSTEM	920	105.625	814.375	0.8984375	0.1031494140625	0.7952880859375	88.52
xxxxORA	1925120	545808.5625	1379311.4375	1880	533.01617431640625	1346.98382568359375	71.65
UNDOTBS2	32024	31099.9375	924.0625	31.2734375	30.37103271484375	0.90240478515625	2.89
SYSAUX	7525	423.3125	7101.6875	7.3486328125	0.41339111328125	6.93524169921875	94.37
USERS	5	4	1	0.0048828125	0.00390625	0.0009765625	20

使用的SQL

SELECT
	a.tablespace_name "表空间名称",
	total / ( 1024 * 1024 ) "表空间大小(M)",
	free / ( 1024 * 1024 ) "表空间剩余大小(M)",
	( total - free ) / ( 1024 * 1024 ) "表空间使用大小(M)",
	total / ( 1024 * 1024 * 1024 ) "表空间大小(G)",
	free / ( 1024 * 1024 * 1024 ) "表空间剩余大小(G)",
	( total - free ) / ( 1024 * 1024 * 1024 ) "表空间使用大小(G)",
	round( ( total - free ) / total, 4 ) * 100 "使用率 %" 
FROM
	( SELECT tablespace_name, SUM( bytes ) free FROM dba_free_space GROUP BY tablespace_name ) a,
	( SELECT tablespace_name, SUM( bytes ) total FROM dba_data_files GROUP BY tablespace_name ) b 
WHERE
	a.tablespace_name = b.tablespace_name

修改表的水位线-尝试1

alter table table1_name enable row movement; 
alter table table1_name shrink space ;

发现这个SQL根本无效

修改表的水位线-尝试2

truncate 表可以直接降低水位线

所以想法是 先按照时间戳字段删除大部分数据
然后create tableback as select * from tablesource 建立备份表

然后truncate table tablesource 的方式处理表
然后 insert into tablesource select * from tableback

发现这样处理之后 数据库 查询出来的表大小就会发生变化了.

部分表信息

TABLE_NAMEINFO	TABLE_ROWNUM	TABLE_COLNUM	TABLE_SIZE
table1_name	2097322	569	76672.875
table2_name	62184	216	27706.5

复制表之后

TABLE_NAMEINFO	TABLE_ROWNUM	TABLE_COLNUM	TABLE_SIZE
table1_name_BACK	744443	68	6696.6875

修改一下表大小获取的SQL

SELECT
    x.table_name AS table_nameinfo,
    x.表行数 AS table_rownum,
    x.表列数 AS table_colnum,
    y.表大小 AS table_size ,
    x.table_tablespace as table_tablespace
FROM
    (
    SELECT
        b.table_name,
        a.num_rows AS 表行数,
        b.count1 AS 表列数 ,
                                a.TABLESPACE_name as table_tablespace
    FROM
        user_tables a
        INNER JOIN ( SELECT table_name, count( column_name ) AS count1 FROM user_tab_columns GROUP BY table_name ) b ON a.table_name = b.table_name
    ORDER BY
        b.table_name
    ) x LEFT outer
    JOIN (
    SELECT
        sum( tablesize ) AS 表大小,
        tablename
    FROM
        (
        SELECT
            sum( C.bytes ) / 1024 / 1024 AS tablesize,
            C.table_name AS tablename
        FROM
            ( SELECT A.table_name, B.bytes FROM USER_lobs A, USER_extents B WHERE A.segment_name = B.segment_name ) C
        GROUP BY
            C.table_name UNION ALL
        SELECT
            sum( bytes ) / 1024 / 1024 AS tablesize,
            segment_name AS tablename
        FROM
            user_extents
        WHERE
            segment_type = 'TABLE'
        GROUP BY
            segment_name
        )
    GROUP BY
        tablename
    ORDER BY
        1 DESC
    ) y ON x.table_name = y.tablename
ORDER BY
    nvl(y.表大小,0) desc FETCH NEXT 100 ROWS ONLY