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:
- 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:
SQL Developer Downloads
No results found Your search did not match any results. We suggest you try the following to help find what you're…
Let’s checkout what desktop SQL Developer has to offer for the Autonomous Database.
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:
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
- 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.