Jagjeet's Oracle Blog!

May 13, 2012

Sqlplus Dashboard For RAC

I have been passionate to work with CLI due to flexibility, lightweight and easy access. Long back, I had an idea to write a sqlplus script to provide a dashboard kind of utility but could never figure out the auto refresh functionality until unless Tanel & Adrian provided moats.

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 )

It took almost 3 weeks for me to write this utility from scratch, It tells you info about Exadata smart scan/storage index saving/Flash cache/ CPU usage/TOP sqls/waits/Cluster interconnect traffic/Top segments by GC* waits and a lot more info. which can be useful to monitor system performance. Based on current formatting, It can support up to 8 node cluster information easily.
I tried my best to make this utility lightweight by reading data from gv$ views once and store in plsql/collections for subsequent requests, Could not find session in EM top activity page running this utility. As of today, it will only work with 11g RAC instances or single instance with gv$* views [catclust.sql].

here is video link… video quality isn’t that great. Must be seen in HD mode.

I have tried to explain about metrics below.


############################################
-- Below section shows metrics instance wise
############################################


+Inst-------+CPUIDL%--IO%-USR%--SYS%+--Tprse/s--+Hprse/s+--PhyWIO/s-+-PhyWMB/s-+--PhyRIO/s--+-PhyRMB/s-+-SessLIO/s--+---Exec/s-+RedoMB/s+Commit/s+-ExSSMB/s-+-ExSIMB/s+-ExFCRh/s+
|AIA1       | 67.8     0  31.2    .7|       2714|      0|       2623|        21|        1183|         9|      370001|      8471|      11|    2001|         0|        0|      980|
|AIA2       | 68.2     0  31.1    .6|       2709|      0|       2491|        22|         928|         7|      340040|      8609|      11|    2004|         0|        0|      703|
+-----------+-----------------------+-----------+-------+-----------+----------+------------+----------+------------+----------+--------+--------+----------+---------+---------+


+Inst-------+ <-- Instance name     +CPUIDL%--IO%-USR%--SYS%+ 
|AIA1       |                       | 67.8     0  31.2    .7| -- This sections shows CPU usage IDLE% IO%, USER% SYS% 
|AIA2       |                       | 68.2     0  31.1    .6| -- For Exadata, we may not see IO Usage as Grid disks exposed to ASM only 
+-----------+                       +-----------------------+




+--Tprse/s--+Hprse/s+--PhyWIO/s-+-PhyWMB/s-+--PhyRIO/s--+-PhyRMB/s-+-SessLIO/s--+
|       2714|      0|       2623|        21|        1183|         9|      370001|
|       2709|      0|       2491|        22|         928|         7|      340040|
+-----------+-------+-----------+----------+------------+----------+------------+

--  Tprse/s   : Total parse per second
--  Hprse/s   : Hard parse per second
--  PhyWIO/s  : Physical Write IO per second
--  PhyWMB/s  : Physical Write MB per second                                                         
--  PhyRIO/s  : Physical Read IO per second								
--  PhyRMB/s  : Physical Read MB per second								
--  SessLIO/s : Session Logical IO per second								
		


+---Exec/s-+RedoMB/s+Commit/s+
|      8471|      11|    2001|
|      8609|      11|    2004|
+----------+--------+--------+

-- Exec/s   : Total Executions for the Instance per second
-- RedoMB/s : Redo Generation MB per second 
-- Commit/s : User commit per second






-- Exadata Related Stats
+-ExSSMB/s-+-ExSIMB/s+-ExFCRh/s+
|         0|        0|      980|
|         0|        0|      703|
+----------+---------+---------+

-- ExSSMB/s  : cell physical IO interconnect bytes returned by smart scan MB per second 
-- ExSIMB/s  : cell physical IO bytes saved by storage index MB per second
-- ExFCRh/s  : cell flash cache read hits per second 

--     Note : Above snapshot was taken for OLTP kind of workload, hence no 
--            smartscan/storage index was in picture, only Flash Cache

 
#########################################
-- Below section shows Cluster Level info
#########################################

+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

  
 * * Cluster Level info * *
+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






   * * Cluster Level info * *

           -------+                                 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:42:22                     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.

-- Note : if value falls in between 2 range buckets then it will be shown in front of lower value, for example - in above screenshot 
--        current active sessions were 288 and it was displayed in front of 268, same applies to MAX CPU info as well
 

   * * Instance level info * *

    |  Global  |  Global  | Estd.     | 
    |  Cache   |  Cache   | Intercnt  | 
Inst|  Blocks  |  Blocks  | Traffic   | 
  ID|  Sent/s  |  Rcvd/s  | MB/s      | 
+---+----------+----------+-----------+ 
  1 |     10831|     10188|      173.3| 
  2 |     10650|     10586|        175| 
                                        

-- Above sections shows Global Cache info instance wise

-- InstID : Instance ID no.
-- GC Blocks Sents/s :  Global cache blocks sent by instance per second
-- GC Blocks Rcvd/s  :  Global cache blocks received by instance per second
-- Estd. Interconnect Traffic MB :  Estimated traffic MB for interconnect by instance





 * * Cluster Level info * *
                             
 | TOP Segments by GC*     |    
 | Waits                   |    
 | IMPACT% [Type:Segment]  |   
 +-------------------------+    
 | 40.8% IND:REFERENCE_INSTANCE 
 | 23.3% IND:MEDIATOR_INSTANCE  
 | 17.4% IND:REFERENCE_INSTANCE 
 |  5.8% IND:S_DOCK_TXN_LOG_P1  
    4.2% IND:COMPOSITE_INSTANCE                                                                         

-- TOP Segments based on all Global Cache* events, Impact is calculated based on GC* waits on individual
-- Segment / Total GC* waits * 100





  * * Cluster level info * * 

+--SqlID--------+--SqlText----------------------------------------------------------------------------+-LongstDur-+-InstCnt-+-Cnt-+--CPU%--+--CONC%--+--CLUS%--+-IO%-+---ReadMb-+
| 0r5xv5d42p3p6 | INSERT INTO REFERENCE_INSTANCE (ID, BINDING_TYPE, REFERENCE_NAME, UPDATED_TIME, PRO |           |    2    |  12 |      0 |      0  |      0  |    0|         0|
+---------------+-------------------------------------------------------------------------------------+-----------+---------+-----+--------+---------+---------+-----+----------+
| bk2mk4dh179y7 | select VALUE from XREF_DATA  where XREF_COLUMN_NAME = :1  and XREF_TABLE_NAME = :2  | 00:00:09  |    2    |  46 |    59% |     3%  |      0  |   4%|       227|
+---------------+-------------------------------------------------------------------------------------+-----------+---------+-----+--------+---------+---------+-----+----------+
| 7jjp97nb9h2up | INSERT INTO COMPOSITE_INSTANCE (ID, COMPOSITE_DN, CONVERSATION_ID, UPDATED_BY, PARE |           |    2    |  14 |      0 |      0  |      0  |    0|         0|
+---------------+-------------------------------------------------------------------------------------|-----------+---------+-----+--------+---------+---------+-----+----------+
| apgu0yan9pb6m | insert into  XREF_DATA  ( XREF_TABLE_NAME, XREF_COLUMN_NAME, ROW_NUMBER, VALUE, IS  |           |    2    |  14 |      0 |      0  |      0  |    0|         0|
+---------------+-------------------------------------------------------------------------------------|-----------+---------+-----+--------+---------+---------+-----+----------+

-- Above sections shows cummulative info for the TOP Sqls 

-- LongstDur : if multiple sessions are running same sql, this column would tell timing for the first query, longest duration.
-- InstCnt   : Total no. of Instance running same sql at time of sample [ including parallel and non-parallel query ]
-- Cnt       : Total sessions running same query across all the instances [ including parallel and non-parallel ] 

-- CPU%  : %time spent on CPU
-- CONC% : %time spent on Concurrency
-- CLU%  : %time spent on Cluster waits
-- IO%   : %time spent on IO

--ReadMB : Total physical read mb by the query, cummulative for all running queries across the cluster

-- Note: Most of the columns are fetched from gV$sql_Monitor, will only be populated if query running more than 6 seconds on CPU/IO


Code can be downloaded here.

Please do not forget to share your suggestions/feedback. Thanks –

Note: This utility uses ASH and v$sql_monitor which requires diagnostics and tuning pack !

About these ads

47 Comments »

  1. [...] you think MOATS was cool?! Check this out by Jagjeet Singh :) Filed Under: Exadata, [...]

    Pingback by MOATS-like sqlplus “top” utility for RAC | Tanel Poder's blog: IT & Mobile for Geeks and Pros — May 13, 2012 @ 5:12 pm

  2. Great stuff! Note that you might want to mention that you’re using ASH and v$sql_monitor views, which require diagnostics (ASH) and tuning pack (ASN and v$sql_monitor) in the blog and/or readme…

    Comment by Tanel Poder — May 13, 2012 @ 5:25 pm

  3. Hi Tanel-

    Thank you very much for visiting blog, I have mentioned in the post and will update readme as well. Thanks again !!

    Comment by Jagjeet Singh — May 13, 2012 @ 6:44 pm

  4. What a great work!
    Now I have to decide if this will replace my MOATS rollout or just extend it.
    Thank you for sharing it!

    Comment by Martin Berger (@martinberx) — May 14, 2012 @ 12:42 am

  5. Great work. Thanks for sharing.

    Comment by vishaldesai — May 14, 2012 @ 5:23 am

  6. Awesome tool. Thank you very much for developing it and, more so, for sharing it.

    Comment by arupnanda — May 14, 2012 @ 5:30 am

  7. Great Tool. Thank a lot for developing and sharing it.

    Comment by baskardba — May 14, 2012 @ 7:48 am

  8. Cool stuff!!!.. Thanks for sharing.

    Comment by Anand — May 14, 2012 @ 9:40 am

  9. Awesome tool. Thank you for sharing this tool.

    Comment by Ruben — May 14, 2012 @ 3:25 pm

  10. Great tool Sir!!Very usefull.Thanks.

    Regards,

    Atul Purswani

    Comment by Atul Purswani — May 14, 2012 @ 5:26 pm

  11. hi there, after downloading the zip and trying to open it, I was prompted that this zip file “not appear to be a valid archive”. where can I download except this blog? thanks

    Comment by tangcf2004 — May 14, 2012 @ 7:29 pm

  12. Hi Tangcf-

    I tried again, i have no problem in downloading and unzipping the zip file. Could you please try again and let me know?

    Thanks

    Comment by Jagjeet Singh — May 14, 2012 @ 7:32 pm

  13. @Tangcf2004 sometimes browsers may cause zip file corruption. You can use a different browser and see it it helps.

    Comment by arupnanda — May 14, 2012 @ 7:39 pm

  14. Thank you very much for sharing…!!!

    Comment by JAP (@JAPiDBA) — May 15, 2012 @ 12:42 pm

  15. Frankly speaking, I wasn’t much impressed, primarily because we already got used to the lavishness of other gui tools including grid control. But it could come handy if you don’t have such tools.

    Comment by Saroj Mohapatra — May 16, 2012 @ 7:16 pm

  16. Or when you do have such tools, but they don’t work! ;-)

    Comment by Tanel Poder — May 16, 2012 @ 7:50 pm

  17. great work Jagjeet Sir

    Prakher SHukla

    Comment by Prakher Shukla — May 16, 2012 @ 8:50 pm

  18. [...] Sqlplus Dashboard For RAC [...]

    Pingback by RAC interconnect traffic | IT World — May 16, 2012 @ 9:06 pm

  19. @jap, nothing new, right. but there are times, when i have shell only acess to a database, so this is quite handy.

    anyway: i got an ora-4030 while runnning this. there is a bug when v$segement_statistics is queried to often, should be fixed, but isn’t obviously.

    Comment by SLB — May 21, 2012 @ 1:04 am

  20. Thanks for visiting, you are right, it is handy when you have shell access. Idea was to show performance related metrics on one page right from CPU/Exadata/..etc

    I have tested it on 11.2.0.3 continuously for 14 hours and had no issue.

    Comment by Jagjeet Singh — May 21, 2012 @ 2:46 am

  21. I tested it on fullrack exadata .I got the following error:
    ORA-20001: GTOP:ORA-22814: attribute or element value is larger than specified in type
    ORA-06512: at “J.JSS”, line 1037
    ORA-6512: at line 1
    Could you fix it?

    Comment by johnwuwuwu — June 5, 2012 @ 12:03 pm

  22. Thanks for the update, would update you once I fixed this.

    Sent from my iPhone

    Comment by Jagjeet Singh — June 5, 2012 @ 12:38 pm

  23. Good Stuff, Jaggi

    Comment by Sourabh Sharma — June 30, 2012 @ 8:42 pm

  24. this is really cool tools and amazing , we would like to recommend this tools to Chinese DBAs.

    Comment by liumaclean — August 2, 2012 @ 8:33 pm

  25. Thanks Liumaclean, I am already working on second version ( added some more stuff ), would share once i am done.
    thanks

    Comment by Jagjeet Singhh — August 2, 2012 @ 8:36 pm

  26. Looks GREAT on video :-)
    AND thanks for sharing all this good work.
    But ….
    Downloaded it today and installed: Compilation error on package body. Tried several downloads to eliminate browser issues with zip files.

    I see no other comments on this in here, so maybe you have just made new version available??

    Error is: ORA-00923: FROM keyword not found where expected (line 371), which is:

    select substr(sidstring,1,66) into fv_sid_string from (
    select listagg(inst_id||':’||sid,’, ‘) within group ( order by sql_id||'(‘||sql_child||’)’ ) sidstring
    from (select distinct sql_id,sql_child,inst_id,sid from table(fv_only_gash) where sql_id||'(‘||sql_child||’)’ = ii.sql_id ) );

    Is something missing here in second line??

    Kind regards,
    Trond

    Comment by Trond — August 15, 2012 @ 11:26 am

  27. Seem you are using 10g, listagg function is not available in 10g, I did not have time to make it 10g compatible.. Will update here

    Sent from my iPhone

    Comment by Jagjeet Singh — August 15, 2012 @ 10:33 pm

  28. Thanks for sharing.
    I would like to say I am on Oracle 11.1.0.7 and I am encountering the error reported by Trond above on the exact same line see below:

    08:25:08 UAT5> show err
    Errors for PACKAGE BODY JSS:

    LINE/COL ERROR
    ——– —————————————————————–
    370/12 PL/SQL: SQL Statement ignored
    371/60 PL/SQL: ORA-00923: FROM keyword not found where expected
    08:25:12 UAT5> l 371
    371* select substr(sidstring,1,66) into fv_sid_string from (
    08:26:34 UAT5>

    so did you change the code or is it becuase the database I am installing on isn’t RAC

    Comment by Anthony — August 17, 2012 @ 1:07 pm

  29. Hi Anthony-

    I haven’t changed anything, I verified again and found it’s working fine with 11gR2 version. As mentioned in post, it can work with non-RAC instance as long as you have GV$ views installed using [catclust.sql].

    but it seems listagg function is not available with 11gR1, could you please verify if you have all below gv$ views exist in 11.1.0.7, I can fix this for 11.1.0.7 ..

    gv_$sql
    gv_$sql_monitor
    gv_$active_session_history
    gv_$osstat
    gv_$instance
    gv_$statname
    gv_$sysstat
    gv_$segment_statistics
    gv_$dlm_misc

    Comment by Jagjeet Singh — August 17, 2012 @ 8:46 pm

  30. To back port to 11.1.0.7 will be great.

    Thanks,
    Charlie | DBA

    Comment by Charlie 木匠 (@mujiang) — August 18, 2012 @ 12:11 am

  31. Hello Jagleet,

    Thanks for your response I have checked and I have all the listed views. So it would be nice if you could port back to 11.1.0.7

    Thanks
    Anthony

    Comment by Anthony — August 19, 2012 @ 1:07 am

  32. Hi Anthony, Charlie

    I spent some time and created a 11gR1 compatible version which seems to be working fine. However, I did not have much time to check it. Even though views were available but some of the key columns were missing in the views which I used for calculation/gathering info .. so I had to change the logic a bit ..

    Few changes in 11gR1 version

    1- It’s not Exadata compatible now ( obviously )
    2- Phyical Read Mb will not be shown as Active Session History does not contain this info in 11gR1
    3- I had to change the logic of calculating the wait events impact

    you can download the code here 11gR1

    However, I would encourage to use 11gR2 version

    Comment by Jagjeet Singh — August 20, 2012 @ 12:09 am

  33. Hello Jagleet,

    I have installed the 11gR1 version and it is working well.

    Thanks once again for the code and your hard work.

    Comment by anthony — August 20, 2012 @ 7:24 pm

  34. I understand that this is not compatible with 10G RAC. I tried to use it on 10GRAC and I get below error

    ERROR:
    ORA-20001: GTOP: ORA-20001: GSQLM : ORA-00942: table or view does not exist
    ORA-06512: at “VBHANSALI.JSS”, line 1166
    ORA-06512: at line 1

    Comment by vinod — September 5, 2012 @ 1:52 am

  35. Jagjeet, looking at outputexplained.pdf, this tool seems to be very handy, good job. As we are still on 10G RAC, is there any way to run this on 10G RAC? Thanks.

    Comment by vinod — September 5, 2012 @ 1:59 am

  36. Hi Jagjit
    When will you have a new version that is EXADATA compatible available?

    Thank you for developing this tool

    Thanks

    Comment by gary — September 5, 2012 @ 10:39 pm

  37. Hi – current version is already EXADATA compatible

    Sent from my iPhone

    Comment by Jagjeet Singh — September 6, 2012 @ 10:09 am

  38. Hi Jagjeet
    I am getting following error when I run

    set lines 190 pages 0 arraysize 50
    select * from table(jss.gtop(50,10)) ;

    ERROR:
    ORA-20001: GTOP: ORA-20001: GSQLM : ORA-01830: date format picture ends before converting entire input string
    ORA-06512: at “JS.JSS”, line 1037
    ORA-06512: at line 1

    then it aborts

    Can you please help?

    Comment by gary — September 10, 2012 @ 10:33 pm

  39. Hi Gary-

    please clarify-

    Are you using 11g R1 compatible or 11gR2 version ?
    Have you tried with some other instance as well ?

    I understand this is related to code with to_date function but never had any issue before. Please let me know i can try fix this.

    Comment by Jagjeet Singhh — September 10, 2012 @ 10:44 pm

  40. Hi Jagjeet

    The database is 11GR2

    SQL> sho parameter compatible

    NAME TYPE VALUE
    ———————————— ———– ——————————
    compatible string 11.2.0.2.0

    Thanks

    Comment by gary — September 10, 2012 @ 10:50 pm

  41. @gary-

    can you please send an email on my personal account : jagjeet.malhi@gmail.com

    I would be looking for the result of below 2 sqls from your DB

    select to_char(max(ELAPSED_TIME)) from gv$sql_monitor
    select to_char(to_date(round(max(elapsed_time)/1000000),’sssss’),’hh24:mi:ss’) from gv$sql_monitor;

    Comment by Jagjeet Singhh — September 10, 2012 @ 10:59 pm

  42. @gary-

    I hope your issue was fixed with the latest code I provided. I have made it available. Thanks

    Comment by Jagjeet Singh — September 20, 2012 @ 1:12 am

  43. Yes the issue has been fixed. Thanks a lot.

    Comment by gary — September 20, 2012 @ 1:14 am

  44. Does this work for any RAC (non-Exadata) db also?

    Comment by OraKle — September 20, 2012 @ 11:14 am

  45. Yes, it works

    Sent from my iPhone

    Comment by Jagjeet Singh — September 20, 2012 @ 11:45 am

  46. Hi, I´m receiving the following error:

    ERROR:
    ORA-20001: GTOP: ORA-20001: GSQLM : ORA-01853: seconds in day must be between 0 and 86399
    ORA-06512: at “DBMON.JSS”, line 1037
    ORA-06512: at line 1

    I have the check queries that you sent to Gary, and got the same error:

    SQL> select to_char(max(ELAPSED_TIME)) from gv$sql_monitor;
    927242746571

    SQL> select to_char(to_date(round(max(elapsed_time)/1000000),’sssss’),’hh24:mi:ss’) from gv$sql_monitor;
    select to_char(to_date(round(max(elapsed_time)/1000000),’sssss’),’hh24:mi:ss’) from gv$sql_monitor
    *
    ERROR at line 1:
    ORA-01853: seconds in day must be between 0 and 86399

    Comment by Luis Santos — April 30, 2013 @ 9:14 pm

  47. Hi Luis-

    Please download the latest code, this error was fixed.

    http://jagjeet.wordpress.com/2012/09/20/sql-dashboard-v2/

    Comment by Jagjeet Singh — May 1, 2013 @ 2:54 pm


RSS feed for comments on this post. TrackBack URI

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

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 40 other followers

%d bloggers like this: