[ORACLE]oracle 如何解决高水平线问题

问题:删除表数据不会导致高水位线下降,不会使用查询效率,

模拟:

创建表,分析表

SQL> create table TEST as select * from TESTT1;

Table created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('c##sapr3',tabname =>'TEST',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>4,cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks,empty_blocks from dba_tables where table_name='TEST';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
   1236277       6947            0

SQL> set autotrace traceonly

SQL> select * from TEST;

1236277 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1236K|    41M|  2235   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |  1236K|    41M|  2235   (1)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      88809  consistent gets
       6829  physical reads
          0  redo size
   43475416  bytes sent via SQL*Net to client
     906978  bytes received via SQL*Net from client
      82420  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1236277  rows processed
SQL> set autotrace off

 删除部分数据:

SQL> DELETE from TEST where MANDT='000';

1113248 rows deleted.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('c##sapr3',tabname =>'TEST',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>4,cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks,empty_blocks from dba_tables where table_name='TEST';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
    123029       6947            0

可以看到NUM_ROWS 减少,但BLOCKS仍然和原来的一样是6947

rows processed 1236277 -->123029

consistent gets 逻辑读 88809  -->14995

SQL> set autotrace traceonly
SQL> select * from TEST;

123029 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   123K|  4325K|  2225   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |   123K|  4325K|  2225   (1)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      14995  consistent gets
          0  physical reads
          0  redo size
    3828471  bytes sent via SQL*Net to client
      90591  bytes received via SQL*Net from client
       8203  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     123029  rows processed

SQL> set autotrace off

收缩表

SQL> alter table TEST enable row movement;

Table altered.

SQL> alter table TEST shrink space cascade;

Table altered.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('c##sapr3',tabname =>'TEST',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>4,cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks,empty_blocks from dba_tables where table_name='TEST';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
    123029        704            0

可以看到表中的BLOCKS由6947 减少到704

继续压缩

SQL> ALTER TABLE TEST move compress;

Table altered.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('c##sapr3',tabname =>'TEST',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>4,cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks,empty_blocks from dba_tables where table_name='TEST';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
    123029        289            0

可以看到表中的BLOCKS由704减少到289

再次进行查询

SQL> set autotrace traceonly
SQL> select * from TEST;

123029 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   123K|  4325K|    95   (2)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |   123K|  4325K|    95   (2)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       8465  consistent gets
          0  physical reads
          0  redo size
    3841745  bytes sent via SQL*Net to client
      90591  bytes received via SQL*Net from client
       8203  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     123029  rows processed

可以看到 consistent 由 14995减少到 8465