Database Reference
In-Depth Information
To display the text of the SQL statement associated to the monitored operation, you have to click on the icon
(i) next to the SQL ID. By doing so, a pop-up showing the text of the SQL statement is opened. In addition, if bind
variables are associated to the operation, the name, position, datatype, and value of the bind variables are given.
The Plan Statistics tab is the most interesting part of the report. With it, you can see not only the resource
consumption at the execution plan operation level, but also when a specific execution plan operation was performed
and how many rows it returned. For example, in Figure
4-12
, you can see that the
HASH JOIN
operation (the one
highlighted by the cursor) returned 18 million rows, was started 20 seconds after the beginning of the execution, and
lasted 16 seconds (you can display this information by hovering the pointer over the bars in the timeline). When you
use Enterprise Manager to look at reports, another important feature is that it's possible to follow the execution of
active operations in real time.
For every monitored operation, in the Activity tab there's a chart that shows the CPU utilization and the wait
events experienced during the execution. For example, Figure
4-13
shows that although at the beginning of the
execution the amount of time spent on CPU is more or less equivalent to the time spent performing direct reads, at the
end of the execution the operation was fully on CPU. Note that only operations executed in parallel can have a value
higher than 1 for the number of active sessions.
The Metrics tab shows a number of charts with metrics for the following performance figures: CPU utilization,
number of disk I/O requests, disk I/O throughput, PGA utilization, and temporary tablespace utilization. Figure
4-14
shows the CPU utilization chart. Notice that this chart points out what was already shown in the activity chart: the
CPU utilization went up as the execution advanced.
The Plan tab shows the execution plan including all information resulting from the query optimizer estimations.
For example, you can use it to see which predicates are applied by which execution plan operation. Finally, for
operations executed in parallel, the Parallel tab shows how busy every process involved in the execution was.
COMpOSIte DataBaSe OperatION
In version 11.1, real-time monitoring works only for SQl statements. For this reason, it's sometimes referred to
as real-time SQl monitoring. Since version 11.2, the feature has been extended to support Pl/SQl blocks as
well. Finally, from version 12.1 onward, with
composite database operation
, it's possible to define that several
SQl statements or Pl/SQl blocks be considered as a single operation. In other words, you can extend real-time
monitoring to user-defined operations that have a meaning from a business point of view. you could, for example,
use a composite database operation to define that all SQl statements executed by a batch job are monitored as a
single operation.
To define a composite database operation, you have to associate a name to the task you want to monitor. For that
purpose, you have the choice between three methods:
•
Generic
: use the
dbms_sql_monitor
package, specifically the
begin_operation
function and
end_operation
procedure, to delimit the beginning and the end of the operation. This method
can be used in any programming language.
•
Java
: use the
setClientInfo
method of the
java.sql.Connection
interface. This technique is
only available from JDbC 4.1 onward.
•
OCI
: use the
OCIAttrSet
function to set the
OCI_ATTR_DBOP
session attribute.
