Use SQL Developer to Monitor Database Performance

Oracle SQL Developer allows you to monitor your Oracle Database Exadata Express Cloud Service database performance in real time through different options like Real Time SQL Monitor, Active Session History (ASH) Report Writer, and Database Status.

Topics

• Use Real Time SQL Monitor in Oracle SQL Developer

• Use ASH Report Writer in Oracle SQL Developer

• View Database Status in Oracle SQL Developer

Use Real Time SQL Monitor in Oracle SQL Developer

To use the Real Time SQL Monitor, you must perform the following steps:

1. Install Oracle SQL Developer locally, and create a cloud connection from Oracle SQL Developer to your service. see Connect SQL Developer.

2. From Oracle SQL Developer, click View, and select DBA.

   The DBA Connections are displayed in a tree view.

   
   

   
   Description of the illustration dba_connections.png

   
   
3. Connect to your Exadata Express service, under the DBA connections.

4. Expand Tuning in the DBA connections tree, under your Exadata Express service connection.

   If you are accessing this feature under this connection, for the first time, you may see a warning message to make sure you have a valid license for the Tuning Pack with your database. Click Yes to proceed.

   Note:

   You must have the Tuning Pack licensed, in order to use this feature. This is because the normal licensing procured as part of subscribing your service, applies for your non-Oracle cloud databases only.
5. Click Real Time SQL Monitor.

   The Real Time SQL Monitor tab displays details and performance metrics for all the SQL queries that are running in real time. You can browse through the pages, if there are multiple queries in the real time sql monitor.

6. Click any entry in the list displayed in the Real Time SQL Monitor.

   You can see additional details for the selected entry underneath the list. It displays additional performance monitoring information for the selected entry under two tabs namely, Plan Statistics and Metrics. Plan Statistics provides you clear information like drilled down operation by line id, estimated rows, cost, executions, timeline, memory consumption, temporary memory consumption , I/O requests etc;

7. Click the Metrics tab.

   This tab shows the real time resource consumption for different resources like CPUs, memory, I/O.

   Note:

   • This is refreshed in real-time, and all the information is accurate as of that moment. However, you can choose the refresh interval from the drop-down provided at the top right corner of the tab, named Auto Refresh.

   • If you wish to view a download a snapshot of the information displayed in this tab, you can click the Save icon provided on the top left of the tab.

View Database Status in Oracle SQL Developer

The Database Status node in the DBA Connections tree provides Instance Viewer which provides an overview of your instance. It is a very powerful interface which provides a detailed snapshot of your instance, along with a Top SQL report.

To use the Database Status feature in Oracle SQL Developer, you must perform the following steps:

1. Install Oracle SQL Developer locally, and create a cloud connection from Oracle SQL Developer to your service. see Connect SQL Developer.

2. From Oracle SQL Developer, click View, and select DBA.

   The DBA Connections are displayed in a tree view.

   
   

   
   Description of the illustration dba_connections.png

   
   
3. Connect to your Exadata Express service, under the DBA connections.

4. Expand Database Status in the DBA connections tree, under your Exadata Express service connection.
   You can see two options as:

   • Instance Viewer

   • Status.

5. Click Instance Viewer.
   The Instance Viewer provides you with an overview of your instance in terms of database sessions, waits, clients, processes etc; One major aspect of instance viewer is the Top SQL report that it provides along with Memory and Storage metrics.

   Note:

   • The Instance Viewer is refreshed in real time, and therefore you do not have a refresh option.

   • The Top SQL report shown in this page can be sorted on the various attributes like, CPU Secs, Disk Reads and so on.

   • You can also drill down into a top SQL item, to look into the complete query text, execution plan, SQL Tuning Advisor’s advice, and so on.

Use ASH Report Writer in Oracle SQL Developer

To view the ASH (Active Session History) Report Writer, you must perform the following steps:

1. Install Oracle SQL Developer locally, and create a cloud connection from Oracle SQL Developer to your service. see Connect SQL Developer.

2. From Oracle SQL Developer, click View, and select DBA.

   The DBA Connections are displayed in a tree view.

   
   

   
   Description of the illustration dba_connections.png

   
   
3. Connect to your Exadata Express service, under the DBA connections.

4. Expand Performance in the DBA connections tree, under your Exadata Express service connection.

5. Click ASH Report Viewer.

   The ASH Report Viewer page displays with links to various types of ASH reports such as: Top Events, Load Profile, Top SQL, Top PL/SQL, Top Java, Top Call Types, Top Sessions, Top Objects/Files/Latches and so on.

   
   

   
   Description of the illustration ash_report.jpg