Database: View Top Activity without Enterprise Manager

When there is a development related to databases, there is almost always a need to see what the database is doing at a given moment in time.
If a developer comes up to the DB administrator and asks to see why his query is “slowing down”, then a seasoned DBA will get his own handwritten script wrappers over hidden x $ views and instantly say what is causing the developer’s frustration.

But if the DBA is not so harsh? Is he a beginner DBA or not a DBA at all, but just keeps an eye on the base?
In this he will help the tools described under the cut.

Enterprise manager

The most convenient way to see what the database is doing is to use Oracle Enterprise Manager. Rather, if it comes to one database, Database Manager.
This is a web-based interface that allows you to view the status of the database, perform administrative actions and configure the database.
I think many people know the Top Activity screen:

which shows what is currently happening in the database. It is convenient, beautiful, and it can be shown to the authorities :)
But what if the OEM installation is impractical or the database is configured without Enterprise Manager? This will help us

ASH Viewer

Free, small, graphical utility written in Java.
You can download Sourceforge: sourceforge.net/projects/ashv
Before the first launch, you need to edit run.sh (or run.bat) and replace JAVA_HOME with the current one. It is also necessary to put the driver for Oracle DB (ojdbc14.jar) in lib / so that the program can connect to the database. In order to connect to the database, it is enough to know SID / IP / port and user / password. After launch, we have a beautiful picture:


The interface practically repeats Top Sessions from OEM and is therefore easy to learn.
When you hover over the SQL ID, a tooltip appears with SQL text. You can also see SQL text in the SQL Text


tab: The query plan is also available, in the corresponding tab:


Statistics about the cache are also available and what events were at the selected time:


If you need to look at the charts for each class of metrics separately, there is a Detail tab:


All statistics are stored locally and in the future you can view statistics for the past days when ASH Viewer was launched:


Authors promise support Oracle DB since 8i.
ASH Viewer is convenient if you do not have access to OEM and you can configure a firewall (or ssh tunnel) to access the database. However, these conditions are also not always satisfied. And you may need to look at the database load when there is no ASHV installed nearby or when only SSH is at hand.
Exit - monitoring on the server itself, for example through sqlplus. All you need is to use

MOATS

As the developers note, this abbreviation stands for The (M) other (O) f (A) ll (T) uning (S) cripts. The script provides the ability to monitor Top sessions and Top waits directly (and only) from the sqlplus console!
After a simple installation procedure for a specific user, he will be able to observe a similar picture directly from the console:

+ INSTANCE SUMMARY ----------------------------------------------- ------------------------------ +
| Instance: ora112 | Execs / s: 2.0 | sParse / s: 0.0 | LIOs / s: 219637.3 | Read MB / s: 0.0 |
| Cur Time: 13-Aug 19: 25: 14 | Calls / s: 0.0 | hParse / s: 0.0 | PhyRD / s: 0.5 | Write MB / s: 0.0 |
| History: 0h 0m 26s | Commits / s: 0.0 | ccHits / s: 1.5 | PhyWR / s: 2.9 | Redo MB / s: 0.0 |
+ ------------------------------------------------- ---------------------------------------------- +
+ TOP SQL_ID (child #) ----- + TOP SESSIONS - + + TOP WAITS ------------------------- + WAIT CLASS - +
| 50% | bwx4var9q4y9f (0) | 71 | | 100% | latch: cache buffers chains | Concurrency |
| 50% | bq2qr0bhjyv1c (0) | 133 | | 50% | SQL * Net message to client | Network |    
| 50% | 799uuu8tpf6rk (0) | 6 | | | |    
+ ------------------------------------------ + + ----- --------------------------------------------- +
+ TOP SQL_ID - + PLAN_HASH_VALUE + SQL TEXT ----------------------------------------- ------------ +
| bwx4var9q4y9 | 2119813036 | select / * + full (a) full (b) use_nl (ab) * / count (*) from |
| | | ys.obj $ b where a.name = b.name and rownum <= 1000002 |
+ ------------------------------------------------- -------------------------------------------- +
| bq2qr0bhjyv1 | 644658511 | select moats_ash_ot (systimestamp, saddr, sid, serial #, |
| | | audsid, paddr, er #, username, command, ownerid, taddr |
+ ------------------------------------------------- -------------------------------------------- +
| 799uuu8tpf6r | 2119813036 | select / * + full (a) full (b) use_nl (ab) * / count (*) from |
| | | ys.obj $ b where a.name = b.name and rownum <= 1000001 |
+ ------------------------------------------------- -------------------------------------------- +

I cut the width example a bit so that it fit into the article. An example of use can be seen in the author's video:


And you can download the script from the site: www.oracle-developer.net/utilities.php True, Oracle needs at least 10gR2 to run it.

UPD : Clarification regarding licensing of some features. ASHV by default uses the Active Session History (ASH) available from 10g. ASH is part of the Oracle Diagnostic Pack and must have an ODP license to use it.
But since ASHV also supports Oracle 9i (8i) on which ASH functionality is emulated, when connecting on 10g, you can choose the type of Standard base and then ASH will also be emulated on 10g (though some Diagnostic Pack functionality, such as AWR or ASH reports, will be unavailable )