Check the distribution of session on all the nodes

Check the distribution of session on all the nodes

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN DUMMY NOPRINT
COMPUTE SUM OF sessions ON DUMMY
COMPUTE SUM OF sessions ON report
BREAK ON DUMMY SKIP 1 on report
select nvl(to_char(inst_id),'TOTAL') DUMMY,inst_id ,status, count(status) sessions from gv$session  group by inst_id,status order by inst_id,status;

Output will be as below :-

   INST_ID STATUS     SESSIONS

---------- -------- ----------

         1 ACTIVE          123

         1 INACTIVE       2076

                    ----------

                          2199

         2 ACTIVE           67

         2 INACTIVE          8

                    ----------

                            75

                    ----------

                          2274

 

 --check the distribution for username all the nodes
  CLEAR COLUMNS
 CLEAR BREAKS
 CLEAR COMPUTES
 break on inst_id skip page
compute sum label 'sum of status' of USERNAME on status
compute sum of total on status
 compute sum of total on report
 select inst_id,status,USERNAME,count(status) total from gv$session
group by rollup(inst_id,USERNAME,status)
 order by inst_id,USERNAME,status
 /