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