Sqlplus Dashboard For RAC

From Jagjeet's blog: I got the Idea and solution of auto refresh functionality in sqlplus. Big thanks to great Tanel & Adrian. Majority of my time I spent doing bench-marking on Exadata, so I decided to write a sqlplus dashboard kind of utility for RAC, idea was to get some critical metrics for performance benchmarks on one screen and have a automatic refresh capability ( similar EM "TOP Activity" page):

        -------+                     ACTIVE SESSIONS GRAPH                              +----
  Active   375 |                                                                        | 375
Sessions   349 |                                                                        | 349
           322 |                #                    #                                  | 322
           295 |                #   # ##       #   # #                                  | 295
           268 |                #   # ##       #   # #                  #          #_288| 268
           242 |                #   # ###  #   #   # #                 ### #       #    | 242
           215 |                #   # ###  #   #   # #                 #####       #    | 215
           188 |                #   # ###  #   #   # #                 #####  #    #    | 188
           161 |               ##################  #########     ##    #####  ######    | 161
           134 |               #############################     #### ##############    | 134
           109 |               ##############################    ###################    | 109
            81 |               ##############################  #####################    | 81
            24 |-MAX CPUs [24]-#####################################################    | 24
             1 |               #####################################################    | 1
             0 +------------------------------------------------------------------------+ 0
                01:49:02 ^                             01:55:42 ^

Above sections shows total active sessions across the cluster, time window calculated based on sample time [6 second default] total 80 samples are printed in graph, one line represents one sample time. Active Session range will change automatically based on active sessions and graph will adjust accordingly. Idea was to have some trend about the active session history. MAX CPU will also be shown to have some idea about Active sessions Vs CPUs available.

+IMPACT%-+ TOP SQLS -(child)-+-TOP SESSIONS-------INST:SID----------------------------------------+
|  26.5% | bk2mk4dh179y7(2)  | 1:1043, 1:1174, 1:1239, 1:1304, ......                             |
|  17.6% | bk2mk4dh179y7(0)  | 1:1045, 1:1109, 1:1110, 1:1174, .......                            |
|  11.8% | 7jjp97nb9h2up(1)  | 2:1048, 2:1109, 2:1112, 2:1174,                                    |
|   7.5% | 0r5xv5d42p3p6(2)  | 1:653, 1:979, 2:1047, 2:1501, 2:588, 2:68  .....                   |
|   7.3% | apgu0yan9pb6m(5)  | 1:653, 1:979, 2:1047, 2:1501, 2:588, 2:68  ......                  |
+--------+-------------------+--------------------------------------------------------------------+

Above sections shows TOP 5 SQLs for the last sample [default 6 second]

IMPACT% : Impact was calculated based on AWR formula, Time spent on individual sql / Total Wait Time * 100
TOP SQL - (child) : Sql id along with child info
INST:SID : Instance ID and SID for the session running sql, due to space limitation, only few sessions will shown

+IMPACT%-+--TOP WAIT EVENTS-----------------------+-WAIT CLASS--------+ 
|  51.2% | transaction                            | Other             | 
|  28.2% | enq: TX - index contention             | Concurrency       | 
|   8.7% | gc buffer busy acquire                 | Cluster           | 
|     5% | cell single block physical read        | User I/O          | 
|   2.2% | gc current grant busy                  | Cluster           | 
+--------+----------------------------------------+-------------------+ 

Above sections shows TOP 5 wait events for the last sample [ default 6 second ]

IMPACT% : Impact was calulcated based on AWR formula, Time spent on individual wait event / Total Wait Time * 100
WAIT EVENT : Name of the Wait Event
WAIT CLASS : Name of the Wait Class

From: http://jagjeet.wordpress.com/2012/05/13/sqlplus-dashboard-for-rac/