oracle 收缩高水位线,Oracle高水位线收缩示例

Oracle高水位线收缩示例 一。 背景介绍 我们知道这样一种情况，在oracle中，假设A表原先有1000W行数据，后来删除掉了999W行，只剩下1W行数据的时候，全表扫描A表的时间没有什 么变化(删除前后)。这就是oracle里面的高水位线引起的。通俗地讲，假设我们要装

Oracle高水位线收缩示例    一。 背景介绍

我们知道这样一种情况，在oracle中，假设A表原先有1000W行数据，后来删除掉了999W行，只剩下1W行数据的时候，全表扫描A表的时间没有什 么变化(删除前后)。这就是oracle里面的高水位线引起的。通俗地讲，假设我们要装1000L水，需要1000个桶，后来我倒掉了999个桶里面的 水，但是我没把桶回收。这时，我去找水的时候，仍然要一个一个桶地去寻找。下面这个例子就是为了描述这样一种现象，并介绍怎么解决(也就是把桶回收)

二。 操作步骤

1. 命令行以sys用户登录

2. 创建测试表

[sql]

--创建测试表T

DROP TABLE t;

CREATE TABLE t (

id NUMBER,

n1 NUMBER,

n2 NUMBER,

pad VARCHAR2(4000)

) tablespace users ;

--插入数据10000行

INSERT INTO t

SELECT rownum AS id,

1+mod(rownum,251) AS n1,

1+mod(rownum,251) AS n2,

dbms_random.string('p',255) AS pad

FROM dual

CONNECT BY level <= 10000

ORDER BY dbms_random.value;

3. 收集统计信息

[sql]

--收集表T对象统计信息

BEGIN

dbms_stats.gather_table_stats(

ownname          => user,

tabname          => 'T',

estimate_percent => 100,

method_opt       => 'for all columns size skewonly',

cascade          => TRUE

)；

END;

/

--收集 plan_executetion_statistics(执行计划 执行时候的信息)

ALTER SESSION SET statistics_level = all;

4. 第一次全表扫描获取的数据行数与逻辑读数

[sql]

SELECT /*+ full(t) */ * FROM t WHERE n2 = 19;

select *

from v$sql sqls

where sqls.SQL_TEXT like '%SELECT /*+ full(t) */ * FROM t WHERE n2 = 19%'

--参数为上一条sql语句查询出的sql_id

select last_output_rows, last_cr_buffer_gets, last_cu_buffer_gets

from v$sql_plan_statistics stat

where stat.SQL_ID = 'chk7agdpy3uqh'

我电脑上显示的是last_output_rows: 40，last_cr_buffer_gets: 436，表示返回40行数据，产生了436个逻辑读(我们这里假设一个逻辑读差不多就是一个块，也就是prefetch参数设置得比较大，使得一个块一次 逻辑读就读完了)，那么也就是差不多读了436个块。

5. 删除表中绝大部分数据(大约是9960行)重新查询行数和逻辑读数

[sql]

DELETE t WHERE n2 <> 19;

SELECT /*+ full(t) */ * FROM t WHERE n2 = 19;

--参数为sql_id

select last_output_rows, last_cr_buffer_gets, last_cu_buffer_gets

from v$sql_plan_statistics stat

where stat.SQL_ID = 'chk7agdpy3uqh'

我电脑上显示的和上一次一样，说明仍然读了436个块。但是已经有绝大部分块没有数据了，完全没必要读取这些没数据的块。

6. 收缩高水位线

[sql]

ALTER TABLE t ENABLE ROW MOVEMENT;

ALTER TABLE t SHRINK SPACE;

7. 第三次进行全表扫描，重新查询行数和逻辑读数

[sql]

SELECT /*+ full(t) */ * FROM t WHERE n2 = 19;

select last_output_rows, last_cr_buffer_gets, last_cu_buffer_gets

from v$sql_plan_statistics stat

where stat.SQL_ID = 'chk7agdpy3uqh'

我电脑显示的是 last_output_rows: 40，last_cr_buffer_gets: 4 说明这一次只进行了4次逻辑读，已经把那些删除数据的块全部释放了。

8. drop测试表

[sql]

DROP TABLE t;

PURGE TABLE t;