用Oracle實現ASH的樞紐分析圖

11g裡面有個很有用的新特性，對樞紐分析圖的支援。

簡單而言，它可以實現寬表和窄表之間的轉化。舉一個例子，有一張表記錄了全校所有班級所有學生的成績(A,B,C,D,E)，現在想統計每個班級裡每個分數層級對應的學生人數。當然，一個SQL就可以實現：

SELECT class, score, count(*) FROM score_tableGROUP BY class, score;

結果的格式如下：

班級      分數 人數一年一班     A  10一年一班     B  16一年一班     C  16一年一班     D  16一年一班     E  16一年二班     A  15一年二班     B  14一年二班     C  15一年二班     D  14一年二班     E  15

不過，很多人更想要下面的格式

班級        A    B   C   D   E一年一班   10   16  16  16  16一年二班   15   14  15  14  15

第二種格式顯然簡單明了多了。

用Oracle的新文法，可以實現如下

SELECT *FROM(SELECT class,score,count(*) cnt FROM score_table GROUP BY class,score)pivot(sum(cnt) FOR score IN ('A','B','C','D','E'));

一個更實用的場合是對ASH(active session history)資料的處理上。

一般而言，我們需要一段時間內Top 10的wait event，並掌握其在每個時間片(例如10 seconds)裡的分布。這些資訊可以通過下面的SQL進行擷取:

SELECT    to_char(to_date(trunc(to_char(sample_time,'SSSSS')/10)*10,'SSSSS'),'hh24:mi:ss') start_time  , decode(ash.session_state,'ON CPU','ON CPU',ash.event)     event  , count(1)/10 totalFROM   v$active_session_history ashWHERE          sample_time > sysdate-1/24GROUP BY  trunc(to_char(sample_time,'SSSSS')/10)       ,  decode(ash.session_state,'ON CPU','ON CPU',ash.event);

同樣的，我們更習慣將這個結果進行倒置。這同樣可以通過pivot來實現:

SELECT * FROM                   (SELECT    to_char(to_date(trunc(to_char(sample_time,'SSSSS')/10)*10,'SSSSS'),'hh24:mi:ss') start_time  , decode(ash.session_state,'ON CPU','ON CPU',ash.event)     event  , count(1)/10 totalFROM   v$active_session_history ashWHERE          sample_time >  sysdate-1/24GROUP BY  trunc(to_char(sample_time,'SSSSS')/10)       ,  decode(ash.session_state,'ON CPU','ON CPU',ash.event)) ash       pivot (sum(total) FOR event IN ('ON CPU' AS TOP1,'PX Deq: Slave Session Stats' AS TOP2))ORDER BY 1;

下面是一個ASH的例子，系統的工作狀態已經一目瞭然了！

TOP   EVENT----- ----------------------------------------------------------------TOP1  cell smart table scanTOP2  ASM file metadata operationTOP3  control file sequential readTOP4  ON CPUTOP5  enq: XL - fault extent mapTOP6  DFS lock handleTOP7  cell single block physical readTOP8  reliable messageTOP9  read by other sessionTOP10 latch: shared poolTIME      TOP1  TOP2  TOP3  TOP4  TOP5  TOP6  TOP7  TOP8  TOP9 TOP10-------- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----00:30:50                       100:31:3000:31:4000:31:5000:32:1000:32:3000:32:50                       100:34:1000:34:30                       100:34:4000:35:10                       100:35:40                       100:37:50    16    34     5     9     2           1    14     3     900:38:00   296    95     7     9     3           6     1     600:38:10   478   133    15     4    17           7           100:38:20   543    71    21     6     8           7           500:38:30   531    81    13     2    14           9     1     200:38:40   600    30    17     4     2     1     200:38:50   592    36    16     4     5    10     100:39:00   609    20    12     6          10           400:39:10   620    14    13     4     2    1000:39:20   628     5    10     4          10     100:39:30   248     7     4                 4           4