Ferhat's Blog

There will be only one database

using pivot fnc to mimic grid active session graph

Posted by fsengonul on January 2, 2012

version 2:
With Yasin’s suggestion, I have changed the script. Added the total ash_secs for the system and get the percentage for the cases when the machine is not fully utilized.
Now the first row shows the rollup of the data and the values are percents with respect to the ash seconds.
From the example below we may roughly say that machines cpu is 28 % utilized and sqlid cqkr2d84qxt6p has used 11% of the cpu for the last 60 seconds.

00:33:01 SQL> @ash2 60                                                                                                                                     
ash_counts for last 60 seconds

NUMBER_OF_NODES NUMBER_OF_THREADS SAMPLE_SECS   ASH_SECS ASH_SECS_PERCENT
--------------- ----------------- ----------- ---------- ----------------
             10                24          60      14400              144

Elapsed: 00:00:00.03

SQL_ID        ON_CPU CONC  UIO  SIO  ADM  OTH CONF SCHE CLST  APP  QUE IDLE  NTW  CMT TOTAL
------------- ------ ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -----
                  28    0    4    0    0    0    0    0    1    0    0    0    0    0    34
cqkr2d84qxt6p     11    0    0    0    0    0    0    0    0    0    0    0    0    0    11
913cu8k9858rp      6    0    0    0    0    0    0    0    0    0    0    0    0    0     7
9dmq476mc247s      7    0    0    0    0    0    0    0    0    0    0    0    0    0     7
84k1xtr2aj9fd      2    0    0    0    0    0    0    0    0    0    0    0    0    0     2
bfsy799japxd4      0    0    0    0    0    0    0    0    0    0    0    0    0    0     1
7ar015kr4jny2      0    0    0    0    0    0    0    0    0    0    0    0    0    0     1
cgsangykrg375      1    0    0    0    0    0    0    0    0    0    0    0    0    0     1
13r0r59cjc9qy      0    0    0    0    0    0    0    0    0    0    0    0    0    0     1
5tmsa82zrcbnr      0    0    0    0    0    0    0    0    0    0    0    0    0    0     1
9g4s5ycuz6x10      0    0    0    0    0    0    0    0    0    0    0    0    0    0     1
7guv13r4psz4k      0    0    0    0    0    0    0    0    0    0    0    0    0    0     1
cg1s0gh2xn49n      0    0    0    0    0    0    0    0    0    0    0    0    0    0     1
frff0g83ud57d      0    0    0    0    0    0    0    0    0    0    0    0    0    0     1
fvxc1zqs1y3ah      0    0    0    0    0    0    0    0    0    0    0    0    0    0     0
1ytrv77gunsz1      0    0    0    0    0    0    0    0    0    0    0    0    0    0     0
0g1zt3sb3y5yz      0    0    0    0    0    0    0    0    0    0    0    0    0    0     0
bakdmp8pnc8a5      0    0    0    0    0    0    0    0    0    0    0    0    0    0     0
f1y8kbhh6v9sv      0    0    0    0    0    0    0    0    0    0    0    0    0    0     0
6pw8uk8k0dv0q      0    0    0    0    0    0    0    0    0    0    0    0    0    0     0
5k2b3qsy3b30r      0    0    0    0    0    0    0    0    0    0    0    0    0    0     0
81ky0n97v4zsg      0    0    0    0    0    0    0    0    0    0    0    0    0    0     0
3jvj0zbkak9h6      0    0    0    0    0    0    0    0    0    0    0    0    0    0     0

23 rows selected.

Elapsed: 00:00:02.15
00:34:44 SQL> 
prompt ash_counts for last &1 seconds
undef ASH_SECS_PERCENT
col ASH_SECS_PERCENT new_value ASH_SECS_PERCENT

column ON_CPU format 999
column Conc format 999
column UIO format 999
column SIO format 999
column Adm format 999
column Oth format 999
column Conf format 999
column Sche format 999
column CLST format 999
column App format 999
column Que format 999
column Idle format 999
column Ntw format 999
column Cmt format 999
column TOTAL format 999

select count(*) number_of_nodes,avg(value) number_of_threads,
       &1 sample_secs, sum(value)*&1 ash_Secs,sum(value)*&1/100 AS ASH_SECS_PERCENT from gv$parameter where name='cpu_count';

WITH ASH_SECS AS
(select sql_id,
ON_CPU,CONC,UIO,SIO, ADM, OTH, CONF, SCHE, CLST, APP, QUE, IDLE, NTW, CMT,
ON_CPU+CONC+UIO+ SIO+ ADM+ OTH+ CONF+ SCHE+ CLST+ APP+ QUE+ IDLE+ NTW+ CMT total
from
(select ash.sql_id,nvl(EN.WAIT_CLASS,'ON_CPU') class from gv$active_Session_history ash, v$event_name en
where ash.sample_time > sysdate - interval '&1' second
and ash.SQL_ID is not NULL and en.event# (+)=ash.event#
)
PIVOT (count(*) FOR class IN ('ON_CPU' ON_CPU,'Concurrency' Conc,'User I/O' UIO,'System I/O' SIO,'Administrative' Adm,'Other' Oth,
'Configuration' Conf ,'Scheduler' Sche,'Cluster' "CLST",'Application' App,'Queueing' Que,'Idle' Idle,'Network' Ntw,'Commit' Cmt )))
    select sql_id,sum(ON_CPU)/&&ASH_SECS_PERCENT ON_CPU,sum(CONC)/&&ASH_SECS_PERCENT CONC,sum(UIO)/&&ASH_SECS_PERCENT UIO,sum(SIO)/&&ASH_SECS_PERCENT SIO, 
    sum(ADM)/&&ASH_SECS_PERCENT ADM, sum(OTH)/&&ASH_SECS_PERCENT OTH , sum(CONF)/&&ASH_SECS_PERCENT CONF, sum(SCHE)/&&ASH_SECS_PERCENT SCHE,
     sum(CLST)/&&ASH_SECS_PERCENT CLST , sum(APP)/&&ASH_SECS_PERCENT APP,sum(QUE)/&&ASH_SECS_PERCENT QUE, sum(IDLE)/&&ASH_SECS_PERCENT IDLE, 
     sum(NTW)/&&ASH_SECS_PERCENT NTW, sum(CMT)/&&ASH_SECS_PERCENT CMT,sum(TOTAL)/&&ASH_SECS_PERCENT TOTAL from ash_secs
    group by rollup(sql_id)
    order by TOTAL desc;

version 1:
I had been using the decode function in order to summarize the last minute activity from ash.
It seems much more tidy and “new fashioned” to use pivot instead.
I wonder if there is a way to get the TOTAL column without rescanning the data.

prompt ash_counts for last 1 minute
column ON_CPU format 99999
column Conc format 9999
column UI/O format 9999
column SI/O format 9999
column Adm format 9999
column Oth format 9999
column Conf format 9999
column Sche format 9999
column CLST format 9999
column App format 9999
column Que format 9999
column Idle format 9999
column Ntw format 9999
column Cmt format 9999
column TOTAL format 99999

select * from
(select  ash.sql_id,nvl(EN.WAIT_CLASS,'ON_CPU') class from gv$active_Session_history ash, v$event_name en
where ash.sample_time > sysdate - interval '60' second
and  ash.SQL_ID is not NULL  and en.event# (+)=ash.event#
UNION ALL
select ash.sql_id,'TOTAL' from gv$active_Session_history ash
where ash.sample_time > sysdate - interval '60' second
and ash.sql_id is not null
 )
PIVOT (count(*)   FOR class IN ('ON_CPU' ON_CPU,'Concurrency' Conc,'User I/O' "UI/O",'System I/O' "SI/O",'Administrative' Adm,'Other' Oth,
'Configuration' Conf ,'Scheduler' Sche,'Cluster' "CLST",'Application' App,'Queueing' Que,'Idle' Idle,'Network' Ntw,'Commit' Cmt ,'TOTAL' TOTAL))
order by  TOTAL desc;

16:42:20 SQL> @ash                                                                                                                                         
ash_counts for last 1 minute

SQL_ID        ON_CPU  CONC  UI/O  SI/O   ADM   OTH  CONF  SCHE  CLST   APP   QUE  IDLE   NTW   CMT  TOTAL
------------- ------ ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ------
g42mvgbnmf5ws   1585     0     4     0     0    32     0     0     0     0     0     0     0     0   1672
0vjyvrybmtt1h    746     0     2     0     0     8     0     0     0     0     0     0     0     0    787
bjutwyympyn2c    233     0     0     0     0     0     0     0    11     0     0     0     0     0    251
6pm37uuabk94w    176     0     0     0     0     2     0     0     0     0     0     0     0     0    185
5k2b3qsy3b30r     43     0     7     0     0     0     0     0     0     0     0     0     0     0     52
3tp2fqk9wp4c0     45     0     1     0     0     0     0     0     0     0     0     0     3     0     51
7t3bfrr7fpjsr     41     0     0     0     0     0     0     0     0     0     0     0     0     0     41
dw0bx0cr2aacz     14     0     2     0     0     0     0     0     0     0     0     0     1     0     17
atmwkzu6u8prw      4     0     8     0     0     2     0     0     0     0     0     0     0     0     14
c9q76xrxyyv1t      3     0     4     0     0     2     0     0     0     0     0     0     0     0      9
bnvar23frxtaa      4     0     1     0     0     0     0     0     0     0     0     0     4     0      9
f1y8kbhh6v9sv      3     0     0     0     0     0     0     0     0     0     0     0     0     0      3
84p5drb647ptj      1     0     0     0     0     0     0     0     0     0     0     0     0     0      1
fvp75qx959tu5      1     0     0     0     0     0     0     0     0     0     0     0     0     0      1
1uyp1pq4w60h7      1     0     0     0     0     0     0     0     0     0     0     0     0     0      1
aanxb0917spa9      0     0     0     1     0     0     0     0     0     0     0     0     0     0      1
08z984tgg4rqu      1     0     0     0     0     0     0     0     0     0     0     0     0     0      1
3ubt3k76mva3k      1     0     0     0     0     0     0     0     0     0     0     0     0     0      1
576kqgucy5v1q      0     0     0     0     0     1     0     0     0     0     0     0     0     0      1
4cyx7sg5hd6wn      0     0     0     0     0     0     0     0     0     0     0     0     0     0      1

About these ads

2 Responses to “using pivot fnc to mimic grid active session graph”

  1. Hi Ferhat,

    Why not just sum all the columns up?

    select sql_id,
    ON_CPU,CONC,”UI/O”, “SI/O”, ADM, OTH, CONF, SCHE, CLST, APP, QUE, IDLE, NTW, CMT,
    ON_CPU+CONC+”UI/O”+ “SI/O”+ ADM+ OTH+ CONF+ SCHE+ CLST+ APP+ QUE+ IDLE+ NTW+ CMT total
    from
    (select ash.sql_id,nvl(EN.WAIT_CLASS,’ON_CPU’) class from gv$active_Session_history ash, v$event_name en
    where ash.sample_time > sysdate – interval ’60′ second
    and ash.SQL_ID is not NULL and en.event# (+)=ash.event#
    )
    PIVOT (count(*) FOR class IN (‘ON_CPU’ ON_CPU,’Concurrency’ Conc,’User I/O’ “UI/O”,’System I/O’ “SI/O”,’Administrative’ Adm,’Other’ Oth,
    ‘Configuration’ Conf ,’Scheduler’ Sche,’Cluster’ “CLST”,’Application’ App,’Queueing’ Que,’Idle’ Idle,’Network’ Ntw,’Commit’ Cmt ))
    order by ON_CPU+CONC+”UI/O”+ “SI/O”+ ADM+ OTH+ CONF+ SCHE+ CLST+ APP+ QUE+ IDLE+ NTW+ CMT desc ;

    • fsengonul said

      I was searching for a fancy function yet the good old addition is a fine solution.
      I’ve updated the script. Calculate the total ash seconds for the machine and divide the numbers by it to show the percantice.
      Next step will be to get the top 10 or 20 sqls and sum the remaining as the rest.
      thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 157 other followers

%d bloggers like this: