用Oracle实现ASH的数据透视图

11g里面有个很有用的新特性，对数据透视图的支持。

简单而言，它可以实现宽表和窄表之间的转化。举一个例子，有一张表记录了全校所有班级所有学生的成绩(A,B,C,D,E)，现在想统计每个班级里 每个分数级别对应的学生人数。当然，一个SQL就可以实现：

SELECT
 class,
 score,
 count(
*
)
 FROM
 score_table
GROUP
 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
 total
FROM

   v$active_session_history ash
WHERE

          sample_time > sysdate-
1
/
24

GROUP
 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
 total
FROM

   v$active_session_history ash
WHERE

          sample_time >  sysdate-
1
/
24

GROUP
 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 scan
TOP2  ASM file metadata operation
TOP3  control file sequential read
TOP4  ON CPU
TOP5  enq: XL - fault extent map
TOP6  DFS lock handle
TOP7  cell single block physical read
TOP8  reliable message
TOP9  read by other session
TOP10 latch: shared pool

TIME      TOP1  TOP2  TOP3  TOP4  TOP5  TOP6  TOP7  TOP8  TOP9 TOP10
-------- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
00:30:50                       1
00:31:30
00:31:40
00:31:50
00:32:10
00:32:30
00:32:50                       1
00:34:10
00:34:30                       1
00:34:40
00:35:10                       1
00:35:40                       1
00:37:50    16    34     5     9     2           1    14     3     9
00:38:00   296    95     7     9     3           6     1     6
00:38:10   478   133    15     4    17           7           1
00:38:20   543    71    21     6     8           7           5
00:38:30   531    81    13     2    14           9     1     2
00:38:40   600    30    17     4     2     1     2
00:38:50   592    36    16     4     5    10     1
00:39:00   609    20    12     6          10           4
00:39:10   620    14    13     4     2    10
00:39:20   628     5    10     4          10     1
00:39:30   248     7     4                 4           4