Vagrant and the SQL Developer dashboards — like 2 peas in a pod!

Everyone knows that SQL Developer is a great tool for creating world-class applications.

Not so well known, is that SQL Developer produces some amazing dashboards of database operation. To view the dashboards, the user account needs to be granted the SELECT_CATALOG_ROLE role.

Even less well known is that a Vagrant file can be preconfigured to allow SQL Developer to connect via port forwarding.

Fortunately, the Vagrant file from Oracle will preconfigure port forwarding for the NAT network.

..
oracle-19c-vagrant: Adapter 1: nat
==> oracle-19c-vagrant: Forwarding ports...
    oracle-19c-vagrant: 1521 (guest) => 1521 (host) (adapter 1)
    oracle-19c-vagrant: 5500 (guest) => 5500 (host) (adapter 1)
..

So on a vagrant up, the log of the startup of the virtual machine will show a forwarding of port 1521.

That’s it! There’s nothing more to configure.

Now in SQL Developer, we just need to login to the ORCLCDB database with a valid user account to port 1521 of the localhost. In SQL Developer, the hostname of the virtual machine is referenced as 0.0.0.0.

To display the database dashboards, choose the Instance Viewer from Database Status from the DBA menu.

Let’s examine the various dashboards available from the Instance Viewer.

The basic health dashboard

The indicator in the top left of the screen just gives some simple details such as:

• Oracle version
• uptime
• operating system
• CPU count

Then below the basic indicator is a session summary view.

The session view in SQL Developer counts the total number of sessions and the session total for:

• blocked sessions
• inactive sessions
• active sessions

If a transaction locks an object (or part of an object), then another transaction in another session can be locked out. Consequently, the transaction that is locked will indicate that the session is blocked.

This indicator is most relevant for applications that are transactional in nature.

The instance performance dashboard

The indicators in the bottom left of the screen, including the accumulated wait summary of all sessions.

The accumulated wait summary can be used as a defacto health check for an application that is transactional.

Then below the accumulated wait summary, there is the DB CPU time ratio indicator.

According to the official documentation, the DB CPU time ratio shows the database operations as a percentage of CPU activity, where DB time is defined as:

1. Time spent in the database by foreground sessions

2. Includes CPU time, IO time and wait time

3. Excludes idle wait time.

As for the accumulated wait summary, the DB CPU time ratio could also be used as a defacto health check for an application that is transactional.

The cursor performance dashboard

For overall cursor performance, the cursor indicator shows the following metrics:

• Cursor execution rate
• Cursor parse rate
• Open cursors
• Cursor (or transaction) commit rate

In a transactional application, cursors should be reused by different sessions when possible.

There are also accumulated metrics for the top cursors (or SQL statements) still cached. The metrics are:

• Total CPU time
• Total disk I/O
• Total buffer gets
• Total executions
• Total elapsed time

From those metrics, you could calculate a performance metric for each SQL statement.

So if an SQL statement needs say, 10k I/O over 10 executions for an average of 1k I/O per execution, then the next day the average is 100k then that would be cause for further investigation because the efficiency is less.

Cursor performance is most helpful for applications that are transactional in nature.

The capacity usage dashboards

There are two capacity usage indicators: one for memory, and another for storage.
The memory capacity indicators trend the following:

• DB block rate
• Logical reads
• Redo generated

Consequently, the memory usage indicators, show the workload of the instance.

The 2nd indicator is for the storage capacity, and there are workload graphs of:

• Log file fill time
• Total Read time, and write time

There are also utilisation indicators for the storage capacity of the 3 types of database files:

• Data files
• Temporary files
• Undo files

The utilisation metrics compare the used space to the allocated file size.

Therefore in the above screenshot, those utilisation percentages are comparing the utilisation of the allocated file, and don’t take into account files that can autoextend.

This means a file that can autoextend and is growing in size, will often have a utilisation close to 100%.

There you are! Oracle Database 19c is the world’s leading relational database, and it gets even better when combined with Oracle SQL Developer.

For dashboards, you don’t need to look much farther than SQL Developer.

Paul Guerin is an international consultant that specialises in Oracle database. Paul is based from a global delivery center in South East Asia, but has clients from Australia, Europe, Asia, and North America. Moreover, he has presented at some of the world’s leading Oracle conferences, including Oracle Open World 2013. Since 2015, his work has been featured in the IOUG Best Practices Tip Booklet, and in publications from AUSOUG, Oracle Technology Network, and Oracle Developers (Medium). In 2019, he was awarded as a most valued contributor for the My Oracle Support Community. He is a DBA OCP, and continues to be a participant of the Oracle ACE program.