Oracle 高低水位线的学习

背景

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

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

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

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

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

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

查询情况

使用的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

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

部分表信息

复制表之后

修改一下表大小获取的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