Oracle表的高水位信息

 昨天有反馈，说是一个表的查询特别慢。就是普通的全表扫描，没有什么特殊的语句。

于是想起来看看block的使用情况。

 

alter session set nls_date_format ='YYYY-MM-DD HH24:MI:SS';

select t.num_rows, t.blocks, t.empty_blocks, t.last_analyzed from dba_tables t where owner = 'AA' and t.table_name = 'BB';

--exec dbms_stats.unlock_table_stats('AA','BB');
--exec dbms_stats.gather_table_stats('AA','BB');

select  round((1-a.used/b.num_total)*100,0)  percent from
(SELECT COUNT (DISTINCT SUBSTR(rowid,1,15)) Used FROM AA.BB) a,
(select blocks num_total from dba_tables where owner = 'AA' and table_name='BB') b;

 

结果比较高：

   PERCENT
----------
        71

SQL>

 

和其他的表比较一下，差别还是很大的。

SQL> select round((1-a.used/b.num_total)*100,0) percent
from
(SELECT COUNT (DISTINCT SUBSTR(rowid,1,15)) Used FROM CONNECTWAPI3.WBXCUSER) a,
(select blocks num_total from dba_tables where owner = 'AA' and table_name='CC') b;
  2    3    4

   PERCENT
----------
        10

SQL>

 

于是让他们自己去处理一下。

 

下面把ROWID的信息贴出来。

An extended rowid has a four-piece format, OOOOOOFFFBBBBBBRRR:

• OOOOOO: The data object number that identifies the database segment (AAAAao in the example). Schema objects in the same segment, such as a cluster of tables, have the same data object number.

• FFF: The tablespace-relative datafile number of the datafile that contains the row (file AAT in the example).

• BBBBBB: The data block that contains the row (block AAABrX in the example). Block numbers are relative to their datafile, not tablespace. Therefore, two rows with identical block numbers could reside in two different datafiles of the same tablespace.

• RRR: The row in the block.

一开始用的查询语句，参考了下面几个URL：

http://space.itpub.net/8554499/viewspace-659914

http://blog.chinaunix.net/space.php?uid=23072872&do=blog&id=87711