oracle升级后出查询问题,oracle11.2.0.3升级到11.2.0.4出现查询性能问题，分析处理...

在上次我们的博客中提到帮客户升级oda一体机，将数据库从oracle 11.2.0.3升级到oracle 11.2.0.4，顺利升级后，却出现了一些性能问题，比如说查询表空间的情况时，性能比以前下降了很多，执行时间较长，经过检查分析后发现是统计信息缺失所致，现在把整个过程记录下来与大家分享。

1、升级完成后，客户在执行这条语句时，相当慢

SELECT a.tablespace_name,

a.bytes total,

b.bytes used,

c.bytes free,

(b.bytes * 100) / a.bytes "% USED ",

(c.bytes * 100) / a.bytes "% FREE "

FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c

WHERE a.tablespace_name = b.tablespace_name

AND a.tablespace_name = c.tablespace_name

order by a.tablespace_name;

大概使用了好几分钟，根据我们以前的经验判断，怀疑可能是回收站可能有大量删除的对象所致

所以直接对回站进行处理

SQL> select count(*) from dba_recyclebin;

COUNT(*)

----------

6455

SQL> purge recyclebin;

Recyclebin purged.

处理好后，我们又重新执行那语句，但仍然非常慢，这个时候感觉奇怪了，于是我们对执行计划进行分析

2、分析执行计划后，找到疑点

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|  40 |           TABLE ACCESS FULL           | TS$              |    12 |   312 |     5   (0)|

|  41 |           FIXED TABLE FIXED INDEX     | X$KTFBFE (ind:1) |     7 |   273 |     0   (0)|

|  42 |          INDEX UNIQUE SCAN            | I_FILE2          |     1 |     6 |     1   (0)|

|  43 |         NESTED LOOPS                  |                  |     1 |   136 |    16  (57)|

|  44 |          NESTED LOOPS                 |                  |     1 |   130 |    15  (60)|

|  45 |           MERGE JOIN                  |                  |     1 |    65 |     7  (15)|

|  46 |            TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$      |     1 |    39 |     1   (0)|

|  47 |             INDEX FULL SCAN           | RECYCLEBIN$_TS   |     1 |       |     1   (0)|

|  48 |            SORT JOIN                  |                  |    12 |   312 |     6  (17)|

|  49 |             TABLE ACCESS FULL         | TS$              |    12 |   312 |     5   (0)|

|  50 |           FIXED TABLE FULL            | X$KTFBUE         |100000 |  6500K|     8 (100)|

在这里，我们看到X$KTFBUE使用全表扫描。通过官网，我们可以看到

x$ktfbue:kernel transaction, file bitmap free extents,Free extent bitmap in file header for LMT (equivalent to fet$ in DMT);

这个表是记录空闲的区块的，对这表进行的是全表扫描，问题应该就出在这里

3、解决和处理问题

SQL> select num_rows, last_analyzed from dba_tables where table_name = 'X$KTFBUE';

no rows selected

没有统计信息

于是，我们对该表进行统计分析

SQL> exec DBMS_STATS.GATHER_TABLE_STATS ('SYS', 'X$KTFBUE');

PL/SQL procedure successfully completed.

分析结束后，很快完成

09:16:50 SQL> SELECT a.tablespace_name,

a.bytes total,

16:08:53   2  16:08:53   3  b.bytes used,

16:08:53   4  c.bytes free,

16:08:53   5  (b.bytes * 100) / a.bytes "% USED ",

16:08:53   6  (c.bytes * 100) / a.bytes "% FREE "

16:08:53   7  FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c

16:08:53   8  WHERE a.tablespace_name = b.tablespace_name

16:08:53   9  AND a.tablespace_name = c.tablespace_name

16:08:53  10  order by a.tablespace_name;

。。。。。。。。。。。

11 rows selected.

Elapsed: 00:00:00.85

一秒钟内显示出来，比先前一分钟都出不来，快了相当多！到此问题解决