Oracle 高水位线收缩示例

wendu

       一. 背景介绍

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

      二. 操作步骤

      1. 命令行以sys用户登录

        2. 创建测试表

• [Shell] 纯文本查看 复制代码

  --创建测试表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. 收集统计信息

• [Shell] 纯文本查看 复制代码

  --收集表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. 第一次全表扫描获取的数据行数与逻辑读数

• 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行）重新查询行数和逻辑读数

• 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. 收缩高水位线

• ALTER TABLE t ENABLE ROW MOVEMENT;  
• ALTER TABLE t SHRINK SPACE;  
  

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

• 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测试表

• DROP TABLE t;  
• PURGE TABLE t;  
  

上都海奶茶馆

关羽五绺长髯，风度翩翩，手提青龙偃月刀，江湖人送绰号——刀郎。

dinggela

修养的艺术,其实就是说谎的艺术。

sofh7777

所有的男人生来平等，结婚的除外。

renshanshan

微机原理闹危机，随机过程随机过，实变函数学十遍，汇编语言不会编！

q3256

人生重要的不是所站的位置，而是所朝的方向!

hwl198212

男人与女人,终究也只是欲望的动物吧!真的可以因为爱而结合吗?对不起,我也不知道。。