Use Oracle to implement the ASH data perspective

The 11g contains a very useful new feature that supports data perspective.

In short, it can convert between a wide table and a narrow table. For example, A table records the scores (A, B, C, D, E) of all the students in the school ), now we want to count the number of students corresponding to each score level in each class. Of course, one SQL statement can be implemented:

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

The result format is as follows:

Class scores per year class A 10 A year class B 16 A year class C 16 A year class D 16 A year class E 16 A year class A 15 A year class B 14 A year class 2 class C 15 one year, two shifts, D 14, one year, two shifts, E 15

However, many prefer the following format:

Class a B C D E One Year One Class 10 16 16 16 16 one year two class 15 14 15 14 15

The second format is much simpler and clearer.

The new Oracle syntax can be used as follows:

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'));

A more practical scenario is to process ASH (active session history) data.

In general, we need to Top 10 wait events in a period of time and master their distribution in each time slice (such as 10 seconds. This information can be obtained through the following 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);

Similarly, we are more accustomed to putting this result upside down. This can also be achieved through compaction:

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;

The following is an example of ASH. The system's working status is clear!

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