Oracle 长数据类型的表未释放

ASKTOM 2021-01-18

411

问题描述

嗨，汤姆，

我们正在处理一个系统，该系统具有一些带有长列的旧表。其中一个表在长列中包含关键数据 (电子邮件正文和附件，聊天记录等)。不幸的是，我们无法以任何方式删除甚至更改此表，系统严重依赖它。2年前，我们介绍了以下解决方案:

-具有相同列的新表，但是CLOB而不是LOB
-Minutely job，它将新插入的记录从原始表复制到新表中，并将LONG转换为CLOB
-从旧表中删除已经复制的记录的每日作业

引入此解决方案时，我们对原始表进行了手动清理:

-停止使用表格的系统
-创建具有完全相同定义的新表 (长列)
-复制所有必须保存的记录
-将原始表重命名为 “_old” 后缀，将新表重命名为原始表名
-放下旧表

但是，2年后，这个数据库所在的客户想要删除旧的分区 (注意: 有问题的表没有分区)，并且在检查磁盘空间时，注意到原始表仍然增长到非常大 (这也是2年前的原始问题)。我们对上述解决方案的期望是，尽管Oracle不会因为长列而在删除后释放磁盘空间，但它仍将重用现有块，因此在某个点之后该表不会增长。然而，这不是正在发生的事情，表每天都在增长。目前它有大约13 000条记录，但超过1900万个块。白天添加了大约18000条记录，晚上删除了每条记录。

我试图使用下面的SQL重现这个问题，但我不确定它是完全正确的，它产生了一个不同的结果，这是我们所期望的: 在我们删除并再次插入后，使用的块计数没有增加。

-- Helper proc
CREATE OR REPLACE PROCEDURE show_space (
    PI_TABLE_NAME IN VARCHAR2
) AS
    l_unformatted_blocks   NUMBER;
    l_unformatted_bytes    NUMBER;
    l_fs1_blocks           NUMBER;
    l_fs1_bytes            NUMBER;
    l_fs2_blocks           NUMBER;
    l_fs2_bytes            NUMBER;
    l_fs3_blocks           NUMBER;
    l_fs3_bytes            NUMBER;
    l_fs4_blocks           NUMBER;
    l_fs4_bytes            NUMBER;
    l_full_blocks          NUMBER;
    l_full_bytes           NUMBER;

    PROCEDURE p (
        p_label   IN        VARCHAR2,
        p_num     IN        NUMBER
    ) IS
    BEGIN
        dbms_output.put_line(rpad(p_label, 40, '.')
                             || p_num);
    END;

BEGIN
    dbms_space.space_usage(
        'CCQ',
        PI_TABLE_NAME,
        'TABLE',
        l_unformatted_blocks,
        l_unformatted_bytes,
        l_fs1_blocks,
        l_fs1_bytes,
        l_fs2_blocks,
        l_fs2_bytes,
        l_fs3_blocks,
        l_fs3_bytes,
        l_fs4_blocks,
        l_fs4_bytes,
        l_full_blocks,
        l_full_bytes);

    p('Unformatted Blocks ', l_unformatted_blocks);
    p('FS1 Blocks (0-25) ', l_fs1_blocks);
    p('FS2 Blocks (25-50) ', l_fs2_blocks);
    p('FS3 Blocks (50-75) ', l_fs3_blocks);
    p('FS4 Blocks (75-100)', l_fs4_blocks);
    p('Full Blocks ', l_full_blocks);
END;
/

-- Dummy table, exact same definition as original table
CREATE TABLE test_long (
    qwkey       NUMBER(38, 0) NOT NULL,
    tablename   VARCHAR2(90 BYTE),
    fieldname   VARCHAR2(90 BYTE),
    textkey     NUMBER(38, 0),
    text        LONG
);

ALTER TABLE test_long ADD CONSTRAINT pk_test_long PRIMARY KEY (qwkey) USING INDEX;

-- Original stats
BEGIN
    dbms_stats.gather_table_stats(ownname => '"CCQ"', tabname => '"TEST_LONG"', estimate_percent => 1);
END;

BEGIN
    show_space('TEST_LONG');
END;

/*

Output:

Unformatted Blocks .....................0
FS1 Blocks (0-25) ......................0
FS2 Blocks (25-50) .....................0
FS3 Blocks (50-75) .....................0
FS4 Blocks (75-100).....................0
Full Blocks ............................0
*/

-- Insert 1000 dummy records, each has 10 000 characters in TEXT column to simulate email body size
DECLARE
    text VARCHAR2(10000);
BEGIN
    FOR i IN 1 .. 1000
    LOOP
        text := '';
        
        FOR j IN 1 .. 1000
        LOOP
            text := text || '1234567890';
        END LOOP;
        
        INSERT INTO test_long (qwkey, tablename, fieldname, textkey, text)
            VALUES (i, TO_CHAR(i), 'FIELD', i, text);
    END LOOP;
    
    COMMIT;
END;
/

-- Updated stats
BEGIN
    dbms_stats.gather_table_stats(ownname => '"CCQ"', tabname => '"TEST_LONG"', estimate_percent => 1);
END;

BEGIN
    show_space('TEST_LONG');
END;

/*

Output:

Unformatted Blocks .....................0
FS1 Blocks (0-25) ......................0
FS2 Blocks (25-50) .....................0
FS3 Blocks (50-75) .....................1
FS4 Blocks (75-100).....................8
Full Blocks ............................1999

Total count is 2008.
*/

-- Deleting 500 records, half table
DELETE FROM test_long WHERE MOD(TO_NUMBER(tablename), 2) = 0;
COMMIT;

-- Updated stats
BEGIN
    dbms_stats.gather_table_stats(ownname => '"CCQ"', tabname => '"TEST_LONG"', estimate_percent => 1);
END;

BEGIN
    show_space('TEST_LONG');
END;

/*

Output:

Unformatted Blocks .....................0
FS1 Blocks (0-25) ......................0
FS2 Blocks (25-50) .....................0
FS3 Blocks (50-75) .....................0
FS4 Blocks (75-100).....................1008
Full Blocks ............................1000

Total count is 2008, same as before, just in different distribution. So far as expected, blocks are not freed with DELETE.
*/

-- Adding 500 records again
DECLARE
    text VARCHAR2(10000);
BEGIN
    FOR i IN 1001 .. 1500
    LOOP
        text := '';
        
        FOR j IN 1 .. 1000
        LOOP
            text := text || '1234567890';
        END LOOP;
        
        INSERT INTO test_long (qwkey, tablename, fieldname, textkey, text)
            VALUES (i, TO_CHAR(i), 'FIELD', i, text);
    END LOOP;
    
    COMMIT;
END;
/

-- Updated stats
BEGIN
    dbms_stats.gather_table_stats(ownname => '"CCQ"', tabname => '"TEST_LONG"', estimate_percent => 1);
END;

BEGIN
    show_space('TEST_LONG');
END;

/*

Output:

Unformatted Blocks .....................0
FS1 Blocks (0-25) ......................0
FS2 Blocks (25-50) .....................0
FS3 Blocks (50-75) .....................1
FS4 Blocks (75-100).....................8
Full Blocks ............................1999

Total blocks is 2008 again, 1000 records again, so it seems blocks were reused.
*/

-- Delete 750 records
DELETE FROM test_long WHERE MOD(TO_NUMBER(tablename), 2) = 1;
COMMIT;

-- Updated stats
BEGIN
    dbms_stats.gather_table_stats(ownname => '"CCQ"', tabname => '"TEST_LONG"', estimate_percent => 1);
END;

BEGIN
    show_space('TEST_LONG');
END;

/*

Output:

Unformatted Blocks .....................0
FS1 Blocks (0-25) ......................0
FS2 Blocks (25-50) .....................0
FS3 Blocks (50-75) .....................1
FS4 Blocks (75-100).....................1508
Full Blocks ............................499

Total blocks is still 2008, so again, nothing was freed.
*/

-- Insert 500 records again.
DECLARE
    text VARCHAR2(10000);
BEGIN
    FOR i IN 1501 .. 2000
    LOOP
        text := '';
        
        FOR j IN 1 .. 1000
        LOOP
            text := text || '1234567890';
        END LOOP;
        
        INSERT INTO test_long (qwkey, tablename, fieldname, textkey, text)
            VALUES (i, TO_CHAR(i), 'FIELD', i, text);
    END LOOP;
    
    COMMIT;
END;
/

-- Updated stats
BEGIN
    dbms_stats.gather_table_stats(ownname => '"CCQ"', tabname => '"TEST_LONG"', estimate_percent => 1);
END;

BEGIN
    show_space('TEST_LONG');
END;

/*

Output:

Unformatted Blocks .....................0
FS1 Blocks (0-25) ......................0
FS2 Blocks (25-50) .....................0
FS3 Blocks (50-75) .....................2
FS4 Blocks (75-100).....................508
Full Blocks ............................1498

Still 2008 blocks, so again, blocks were reused.
*/

专家解答

我的猜测是，您的客户停止/忘记/错过了清理工作的错误，并且该错误尚未运行。

在自由空间管理方面，LONG不会得到特殊待遇，但是正如您已经指出的那样，如果表已增长到X字节，那么它将保持在X字节。

这是一个简单的基准，您可能更接近于模仿客户应用程序

Session 1
========
--
-- setup plus regular long population
--
create sequence seq;

create table t_long ( 
  x int default seq.nextval primary key,
  ts date default sysdate,
  txt long );

create table t_clob ( 
  x int primary key,
  ts date,
  txt clob );
  
create table msg ( d timestamp default systimestamp, m varchar2(100));

declare
  l_hi int;
begin
  loop
    for i in 1 .. 10 
    loop
      insert into t_long (txt) values (rpad('x',30000,'x'))
        returning x into l_hi;
      commit;
      dbms_session.sleep(dbms_random.value(0.2,0.6));
    end loop;
    insert into msg (m) values ('10 records insert, HWM='||l_hi);
    commit;    
  end loop;
end;
/

-- You might need to replace dbms_session with dbms_lock on 12c

Session 2
=======
--
-- transfer long to clob at regular intervals
--
declare
  cnt int;
begin
loop
  cnt := 0;
  for i in ( select * from t_long where x not in ( select x from t_clob ))
  loop
    insert into t_clob values (i.x, i.ts, i.txt);
    cnt := cnt + 1;
  end loop;
  insert into msg (m) values ('Transferred '||cnt||' records');
  commit;
  dbms_session.sleep(10);
end loop;
end;
/

Session 3
=======
--
-- on demand cleanup
--
declare
  cnt int;
begin
  select count(*) into cnt from t_long;
  insert into msg (m) values ('Active rows in T_LONG: '||cnt);
  commit;
  delete from t_long
  where x in ( select x from t_clob);
  cnt := sql%rowcount;
  insert into msg (m) values ('Cleaned '||cnt||' records');
  select bytes 
  into   cnt
  from   user_segments
  where  segment_name = 'T_LONG';
  insert into msg (m) values ('Segment size: '||cnt);
  commit;
end;
/
select * from msg order by d;

我在我的数据库上运行了一个小时，总段大小完全取决于我运行清理的频率，即，如果我每隔几分钟运行一次，段增长很小，因为未清理行的数量的高水印很低。如果我将其放置一个小时，那么清理仍然可以正常工作，但是段大小映射到1小时的行。但是在那之后，该细分市场永远不会增长，因为该清理空间被重复使用。

过去，ASSM存在一些早期错误，即位图块无法正确反映可用空间，但这些错误更多是8i，9i时间范围而不是12c。即使这样，也值得与支持联系，以确保您没有遇到一些深奥的错误。

oracle asktom

文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle 长数据类型的表未释放

问题描述

专家解答

评论