一个需要知道的前提：全表扫描会对HWM(高水位线)以下的所有的数据块进行扫描。因此对于频繁insert、delete操作而不可避免又需要进行全表扫描的表将会引起严重的性能问题。

通过topas查看发现系统cpu使用率极高，平时都是50%--60%的空闲，现在只有30%左右，通过pid查看不到信息。

在oracle中做statspack发现top sql中的前几个都是如下的语句：

Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
205,411,225 36,592 5,613.6 7.3 1678.08 2195.76 1230179637
Module: JDBC Thin Client
select count(*) from wServMsgChg_oneboss where mod(id_no,10)=9

204,148,519 36,365 5,613.9 7.2 1654.17 2174.41 1038581438
Module: JDBC Thin Client
select count(*) from wServMsgChg_oneboss where mod(id_no,10)=7

203,987,233 36,339 5,613.5 7.2 1672.57 2184.42 1206558394
Module: JDBC Thin Client
select count(*) from wServMsgChg_oneboss where mod(id_no,10)=0

203,077,042 36,173 5,614.1 7.2 1652.98 2162.66 1097053609
Module: JDBC Thin Client
select count(*) from wServMsgChg_oneboss where mod(id_no,10)=3

202,024,783 35,994 5,612.7 7.2 1631.69 2138.13 1852790669
Module: JDBC Thin Client
select count(*) from wServMsgChg_oneboss where mod(id_no,10)=6

202,012,907 35,985 5,613.8 7.2 1629.54 2143.32 2450605557
Module: JDBC Thin Client
select count(*) from wServMsgChg_oneboss where mod(id_no,10)=5

201,989,118 35,987 5,612.8 7.2 1629.64 2137.04 2331810954
Module: JDBC Thin Client
select count(*) from wServMsgChg_oneboss where mod(id_no,10)=4

201,962,662 35,977 5,613.7 7.2 1634.81 2140.23 2144500466
Module: JDBC Thin Client
select count(*) from wServMsgChg_oneboss where mod(id_no,10)=1

201,426,586 35,885 5,613.1 7.1 1625.34 2141.15 4215973319
Module: JDBC Thin Client
select count(*) from wServMsgChg_oneboss where mod(id_no,10)=8

200,444,854 35,712 5,612.8 7.1 1633.97 2129.70 2987984319
Module: JDBC Thin Client
select count(*) from wServMsgChg_oneboss where mod(id_no,10)=2

查看已经执行时使用的执行计划

ID PID OPERATION OPTIONS OBJECT_NAME OPTIMIZER pstart pstop COST CARDINALITY
--- --- -------------------- ------------------------- -------------------- ---------- ------ ----- ---------- -----------
0 SELECT STATEMENT CHOOSE 541
1 0 SORT AGGREGATE 1
2 1 TABLE ACCESS FULL WSERVMSGCHG_ONEBOSS ANALYZED 541 6

查看表的记录数
SQL> select count(*) from oneboss.WSERVMSGCHG_ONEBOSS;

COUNT(*)
----------
0

重新查看执行计划

SQL> set autot trace
SQL> select count(*) from oneboss.wServMsgChg_oneboss where mod(id_no,10)=6
2 /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=541 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'WSERVMSGCHG_ONEBOSS' (Cost=541 Card=6 Bytes=48)

Statistics
----------------------------------------------------------
23 recursive calls
0 db block gets
5644 consistent gets
2 physical reads
104 redo size
491 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

SQL> select sum(bytes)/1024/1024 MB from dba_segments where owner='ONEBOSS' and segment_name='WSERVMSGCHG_ONEBOSS';

MB
----------
45

SQL> select partitioned from dba_tables where table_name='WSERVMSGCHG_ONEBOSS';

PAR
---
NO

监控表的记录数变化：
SQL> select count(*) from oneboss.WSERVMSGCHG_ONEBOSS;

COUNT(*)
----------
82

SQL> /

COUNT(*)
----------
69

SQL> /

COUNT(*)
----------
121

SQL> /

COUNT(*)
----------
141

SQL> /

COUNT(*)
----------
0

SQL> /

COUNT(*)
----------
0

SQL> /

COUNT(*)
----------
0

SQL> /

COUNT(*)
----------
0

SQL> /

COUNT(*)
----------
0

SQL> /

COUNT(*)
----------
0

SQL> /

COUNT(*)
----------
0

至此可以断定这是HWM所致，因为全表扫描会扫描HWM以下的所有的数据块，而这张表在不断的进行insert、delete操作导致HWM无法下降。
所以我们看到虽然表中没有几条记录，但是在执行全表扫描时却有很高的逻辑读。

SQL> analyze table oneboss.WSERVMSGCHG_ONEBOSS compute statistics;

Table analyzed.

SQL> select blocks from dba_tables where table_name='WSERVMSGCHG_ONEBOSS' and owner='ONEBOSS';

BLOCKS
----------
5604

SQL> select sum(blocks) from dba_extents where segment_name='WSERVMSGCHG_ONEBOSS' and owner='ONEBOSS';

SUM(BLOCKS)
-----------
5760

SQL> alter table oneboss.WSERVMSGCHG_ONEBOSS move;
alter table oneboss.WSERVMSGCHG_ONEBOSS move
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

SQL> /

Table altered.

SQL> select blocks from dba_tables where table_name='WSERVMSGCHG_ONEBOSS' and owner='ONEBOSS';

BLOCKS
----------

SQL> select sum(blocks) from dba_extents where segment_name='WSERVMSGCHG_ONEBOSS' and owner='ONEBOSS';

SUM(BLOCKS)
-----------
640

SQL>
SQL> c/extents/segments/
1* select sum(blocks) from dba_segments where segment_name='WSERVMSGCHG_ONEBOSS' and owner='ONEBOSS'
SQL> /

SUM(BLOCKS)
-----------
640

SQL> select sum(bytes)/1024/1024 MB from dba_segments where owner='ONEBOSS' and segment_name='WSERVMSGCHG_ONEBOSS';

MB
----------
5

再看执行计划
SQL> /

Elapsed: 00:00:03.49

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'WSERVMSGCHG_ONEBOSS'

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
54 consistent gets
0 physical reads
0 redo size
490 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

此时问题得到解决。