Databases Reference
In-Depth Information
Listing 14-2.
lst14-02-sqlmon-list-text.sql
SQL> set pagesize 0 echo on timing off
SQL> set linesize 1000 trimspool on trim on
SQL> set long 2000000 longchunksize 2000000
SQL> select dbms_sqltune.report_sql_monitor_list(
type=>'TEXT',
report_level=>'ALL') as report
from dual;
How It Works
Starting with 11g, Oracle introduced a new tool to add to the performance optimizer's toolkit called “Real-Time
SQL Monitoring.” Real-Time SQL Monitoring, or simply SQL Monitoring, enables you to view and analyze detailed
performance statistics for SQL statements.
SQL Monitoring is enabled any time a statement runs in parallel or when it consumes more than five seconds
of CPU or I/O time. For large databases on Exadata, these conditions are common. The SQL Monitoring browser
interface, text output, and HTML output is externalized from two data dictionary views,
v$SQL_MONITOR
and
v$SQL_PLAN_MONITOR
. Oracle populates
v$SQL_MONITOR
and
v$SQL_PLAN_MONITOR
views with performance
characteristics of running SQL statements and stores these for as long as the
SQL_ID
remains in the library cache.
The
v$SQL_MONITOR
view contains key performance statistics for each qualifying SQL statement (a subset of
what's in
v$SQL
) and
v$SQL_PLAN_MONITOR
provides statistics for each execution path step for the statement.
This allows tools such as Enterprise Manager Grid Control to provide a graphical, intuitive representation of the time
taken at each step in the path, as well as I/O consumption, CPU consumption, and so on.
Contrary to
v$SQL
,
v$SQL_MONITOR
tracks performance statistics for each execution of a SQL statement, not a
summary of accumulated statistics.
As presented in the solution of this recipe, you can view SQL Monitoring data from Enterprise Manager or by
using
DBMS_SQLTUNE.REPORT_SQL_MONITOR
. Whether you use an Enterprise Manager browser interface or use
DBMS_SQLTUNE
directly, there are a number of nice features with Real-Time SQL Monitoring:
•
SQL Monitoring provides intuitive visual feedback that shows you exactly how much time
each operation in an execution plan is taking. This is very helpful for an Oracle performance
engineer, DBA, or Exadata DMA because it shows you exactly which step or steps to focus your
optimization efforts on.
•
SQL Monitoring shows not only estimated rows, but also actual rows, for qualifying
operations. This helps in determining if the optimizer is not estimating cardinality optimally.
•
SQL Monitoring provides the best and most information for parallel query operations, period.
All SQL statements with a degree of parallelism greater than 1 are captured in
v$SQL_MONITOR
,
and with SQL Monitoring you see a lot of detail about parallel execution.
•
In the Enterprise Manager user interface, the SQL Monitoring details allow for intuitive
drill-downs into the query execution plan, query statistics, and so forth.
DBMS_SQLTUNE.REPORT_SQL_MONITOR
report output displays activity detail for each
operation. This activity detail shows statistics including wait event information, CPU
information, and so forth, so you can determine which events or statistics contributed time to
each operation.
•
The
•
SQL Monitoring reports on Exadata-specific features such as smart scan and offload
efficiency. This provides the Exadata DMA with valuable insight to the benefit of Exadata's
performance features for a currently running SQL workload.
Search WWH ::
Custom Search