This post is primarily for the Oracle DBAs out there who would like a quick look at the load on a database, be it single instance or RAC. I find that this tool called "oratop" is very similar to the "top" command in UNIX or LINUX that let you look at overall health and load of a server. I would suggest using this tool every now and then during the day to monitor your DB activity, load and performance characteristics. You can download the tool from the Oracle Support Website. I am surprised that Oracle has decided to stay relatively mum about this tool. Currently the tool is only available for LINUX and for Oracle 12c and 11g versions. Once you download and install, just type oratop / as sysdba and you will see something very familiar to those who have used the "top" utility in UNIX/LINUX. The Columns of interest for me usually are ASC (Active Sessions on CPU), ASI (Active Sessions Waiting for I/O, AAS (Average active sessions), SSRT (SQL Service Response Ratio - should be low), DBC (DB CPU Wait Ratio) and DBW (DB Wait Time Ratio). In general if your AAS starts increasing, then you would see either ASC or/and ASI increasing, meaning sessions are coming actively looking to do something and are being serviced by the DB, but if AAS keeps bumping up then you know some of the SQLs are taking longer. In which case look at SSRT, if that is showing an increase then you know some SQLs are slow. In general DBC should be high (90s) while DBW should be less (10s). You can also look at section 3 of the program to see which SQL ID is running most often, Status (look for ACTIVE ones), STE (either on CPU or waiting for I/O), Wait Event and W/T (how long the SQL has waited, this should be low). There are other options to press while the tool is running like if you press "d" then it will show you real time wait events, press "f" and you will get more details for section 3, etc. I just find it useful to call it simply without any options. You can read more at the Oracle website I mentioned above.
Comments