Gateway to the Autonomous Database with the SQL Developer

As the world’s #1 tool for Managing your Oracle database, the SQL Developer should be every ones best friend.

There are in fact 3 interfaces for SQL Developer:

  • Desktop
  • Web (ie SQL Developer Web)
  • Command line (ie SQLcl)

The desktop version of SQL Developer is still arguably the best interface.

Download it from here:

Let’s checkout what desktop SQL Developer has to offer for the Autonomous Database.

Prerequisites

Before attempting to connect to an autonomous database, the wallet file for the database needs to be downloaded to your workstation.

From the details page of the autonomous database, click on the ‘DB Connection’ button.

For our purposes the defaults should be suitable, so now choose ‘Download Wallet’.

Now choose a password for the wallet.

The password must be at least 8 characters long and must include at least 1 letter and either 1 numeric character or 1 special character. This password protects the downloaded Client Credentials wallet.

Now a package of files (including the wallet files) will download to your workstation.

The zipped package will download as a single file.

Note — do not unzip the file, as it will be used by the SQL Developer as is.

Important — the wallet has an expiry date. To determine the expiry date of the wallet, inspect the README file of the zip file. To avoid any service interruptions due to an expired SSL certificate, you must re-download the wallet before this date.

Configure the desktop SQL Developer

Now in SQL Developer, click on the add connection button.

Then choose ‘Cloud Wallet’ for the connection type.

Now you will get the opportunity to enter the connection details for the autonomous database.

For the username, you can use the admin account, or a schema that you create inside the database.

The password for the admin account will be available from the autonomous database detail page.

The configuration file is the zip file that was just downloaded.

For the service selection, there are a number of options.

The zip file contains a TNS names file with the connect descriptors of the services available:

  • db202012121225_high
  • db202012121225_low
  • db202012121225_medium
  • db202012121225_tp
  • db202012121225_tpurgent

For administration purposes you can just choose the db202012121225_high service.

Now you’re ready to connect via SQL Developer.

Browsing the autonomous database

If all is well with your connection you’ll be able to browse the autonomous database.

From the menu, you can select the DBA view, which will allow you to inspect the autonomous database further.

You’ll also be able to inspect the instance of the autonomous database.

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 accumulated wait summary can be used as a defacto health check for an application that is transactional.

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.

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.

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 Autonomous Database is the world’s leading relational database, and it gets even better when combined with Oracle 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, Quest, 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.

Paul Guerin is an international consultant that specialises in Oracle database performance. Paul is based from a global delivery center in South East Asia.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store