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

一秒鐘內顯示出來，比先前一分鐘都出不來，快了相當多！到此問題解決