1. Administering Oracle Database Classic Cloud Service
  2. Using Oracle DBaaS Monitor
  3. Viewing Real Time SQL Monitor

Viewing Real Time SQL Monitor

This page shows, in real time, the SQL statements that are being monitored in the database.

To review the SQL statements being monitored:

  • On the DBaaS Monitor home page, in the Database drop-down menu, click Real Time SQL Monitor.

    The Real Time SQL Monitor page displays.

This tool helps identify run-time issues for SQL statements and monitor their behavior, by providing two major functions:

  • General view of monitored statements

  • View of SQL execution details

General View of Monitored Statements

The page contains a table of SQL statements currently running. This table shows the following information:

  • Status — Current state of the SQL statement execution. For example, a SQL statement that has already finished its execution will show a status of "DONE".

  • Duration — This is the amount of time a SQL statement is taking, or has taken, to execute.

  • SQL ID — SQL identifier of the statement being monitored.

  • Session ID — Session identifier that is executing, or has executed, the SQL statement.

  • Session Serial Number — Used to uniquely identify a session's objects. 

  • Instance Degree of Parallelism — This Degree of Parallelism (DOP) column shows how many instances and parallel execution servers are allocated. It is shown in the form of “number of instances” | “number of parallel servers”

  • CPU Time — This is the CPU time consumed by the execution of the query.

  • I/O Time — This is the I/O time consumed by the execution of the query.

  • Start Time — This is the time in which the execution of the SQL statement started.

  • SQL Statement — This is the SQL statement being monitored.

For more information, see "Monitoring the Database" in Oracle Database Administrator's Guide for Release 18, 12.2, 12.1 or 11.2.

View of SQL Execution Details

When a SQL statement is drilled down from the main monitor table, a detailed view is shown. The SQL ID, Start Time and the SQL Execution ID represent the execution key that uniquely identify this SQL statement. A detail view consists of the general characteristics that integrate the execution of a SQL statement.

General information about the query execution is provided:

  • Execution Plan — Degree of Parallelism of the SQL statement

  • Execution Started — Time that the SQL statement execution started

  • Last Refresh Time: — Last update time of the SQL monitor registry for the SQL statement

  • Execution ID — Execution identifier

  • User — User in the format USER@CONTAINER

  • Fetch Calls — Number of fetch calls done by the SQL statement

General statistics of the SQL statement are provided: total duration of execution, the number of buffered gets, number of Input/Output requests and bytes.

A duration breakdown shows the percentage of the total duration of the execution of the SQL statement, divided into two types of times:

  • Database Time — Any time of execution related to the database needs; for example, CPU Time

  • Wait Time — The waiting time that the statement goes through to complete an execution

Each duration time can be drilled down by clicking on the "Show Detail" element, where a gauge graph shows the percentage of time that integrates each type of duration time.

PLSQL and Java Time are provided. These time measurements are outside of the duration of the SQL statement.

Detailed information of the statement — This space holds the information corresponding to the explain plan, parallel behaviour and CPU activity involved in the execution of the statement:

  • SQL Text — SQL statement that was or is being executed.

  • Plan Statistics — Explain plan of the execution of the SQL statement in the form of a table. Each row is a different operation involved in the execution of the SQL statement and it shows hierarchy dependency by adding a space at the beginning of the text in the Operation column.

    • Operation, Name, Estimated Rows, Cost, Actual Rows, Memory, Temp(Max), IO Requests, IO Bytes

  • Graphic view of the Plan Statistics — The plan statistics table in a graphic representation; the hierarchy is presented as a collapsible tree map in which each node represent an operation.

  • Parallelism Details for the SQL statement — Each execution consists of a parallel coordinator and one or more parallel sets. Each set can have one or more processes. When a row has dependents, each of its columns will be the sum of the values of its dependants. When this happens, a sigma symbol will appear to show that a value consists of the sum of others. The columns shown are the following:

    • Process Name, Buffer Gets, CPU Time, Elapsed Time, Other Wait Time, Server Set, Read Requests, Read Bytes

  • Activity Line Chart for the CPU Usage — Line chart showing the number of different types of CPU activities registered in the execution of the SQL statement. The Y axis represents the number of CPU activities and the X axis represents the time registered for that activity. Each activity is represented by a custom set of colors depending on the activity.