Oracle Database 11g: SQL Details Active Report

Interactive Enterprise Manager screens display details about a SQL statement. This includes the SQL text, Top Activity by various dimensions, CPU and Wait Activity over time, key SQL statistics, and execution plans. SQL Profiles and SQL Plan Baselines will be displayed if they exist, and a monitored execution is displayed, if available (see Real Time SQL Monitoring).

This report uses data from views that are licensed with the Diagnostics and Tuning Pack.

SQL Details Active report is a new type of interactive report powered by Enterprise Manager UI technology hosted on OTN. Other active reports are SQL Monitor and SQL Performance Analyzer.


Active Report Examples

High Load SQL
SQL with multiple plans
SQL with monitored executions
High Load SQL from both disk and memory

Frequently Asked Questions


How do I generate a SQL Details Active Report?

From SQL*Plus, invoke the dbms_sqltune.report_sql_detail function and spool the output

set trimspool on trim on
set pages 0 linesize 1000
set long 1000000 longchunksize 1000000
spool sqlmon_details.html
select dbms_sqltune.report_sql_detail('&sql_id') from dual;
spool off

The resulting sql_details.html should be edited to remove the first line (call to dbms_sqltune.report_sql_detail) and the last line (the spool off), and then viewed in any browser, with connectivity to OTN.


What if I want to specify a time frame?

The parameters in dbms_sqltune.report_sql_detail allow you to specify a start_time and the duration for the report. By defualt, the report displays the activity for the SQL statement over the past hour.


select dbms_sqltune.report_sql_detail('&sql_id',start_time=>to_date('&time',
'yyyy-mm-dd hh24:mi:ss'), duration=>&duration_in_seconds) from dual;

If you specify duration without a start_time, it will display data for the last n seconds, where n is the number specified for duration.

See Oracle Database PL/SQL Packages Types and Reference 11g Release 2 (11.2) for parameters to dbms_sqltune.report_sql_detail.

Currently, the time frame is only used for determining activity of the SQL statement, and does not influence the Statistics Tab in the 'Details' section.


What does Top Activity mean?

The heading, in the format "Top Activity - n samples: x% of Total Activity" shows this SQL statements contribution to the database's Total DB time or Total Activity. As the Oracle Performance Tuning Methodology is based on DB time, this helps determine the relevance of this SQL statement to overall system performance.

The dimensions for Top Activity are the various dimensions in Active Session History (ASH). The ASH sample is included if the SQL ID is sampled as either the top_level_sql_id or the sql_id (current). See Oracle Database Reference 11g Release 2 (11.2) for the definition of the columns in ASH.


What does 'Other' in 'Top Activity' mean?

Top Activity displays the top n members of the various dimensions in Active Session History (ASH). If there are more than n members, the remaining members are grouped as 'Other'. This allows one to see whether or not a particular member in a dimension is responsible for most of the activity. If 'Other' is the biggest dimension, this is an indication that the activity is spread across several members of the specific dimension.



Why do I have multiple Top Level SQL IDs or multiple SQL IDs in 'Top Activity'?

Top Activity includes ASH samples where the SQL ID is either the top_level_sql_id or the sql_id (current).

If the SQL statement is called from different PL/SQL procedures, you could have multiple Top Level SQL IDs in the Top Activity section, one for each of the different PL/SQL procedure calls that are using the same SQL statement.

Similarly, if the SQL statement invokes other SQL statements, perhaps through the use of a PL/SQL function, the SQL ID may be sampled as a Top Level SQL ID, with a different current SQL ID being executed.

Example with multiple Top Level SQL IDs.


Why are there multiple plan hash values in Top Activity?

Multiple plan hash values usually indicates that there are different plans for the SQL statement. This also shows the plan responsible for most of the activity.

Example with multiple plans


Why are there multiple plan hash values in Top Activity, but only a single plan in the 'Details' section?

Since Top Activity includes samples where the SQL ID is either the top_level_sql_id or the current sql_id, check the sql_id that the plan_hash_value corresponds to.

The Details only includes the execution plans for the SQL ID specified for the report.

Example with multiple Top Level SQL IDs.


Why are there 'Real Time' and 'Historical' radio buttons?

Data may be retrieved from in-memory and on-disk ASH (Active Session History), depending on the time frame specified. As the sampling interval is different between the two sources of ASH, the radio-button makes this distinction when displaying the Activity for the SQL statement.

Example with in-memory and on-disk samples


What does the Statistics tab represent?

The key SQL statistics in the 'Statistics' tab currently represents data in-memory only, i.e. from gv$ views.


How do I display the SQL Monitoring data for a monitored execution?

Under the SQL Monitoring tab, a list of monitored executions is displayed (if the SQL has been monitored). Only a single monitored execution, the latest execution, has monitor data available in the report. To view the SQL monitoring report for this monitored execution, click on the icon in the Status column.

Example with a list of monitored executions


Why does the Activity Tab have a shorter time frame than what was specified during report generation?

The Activity tab will have the time frame of when the SQL ID was sampled. If it was not active during the entire time specified during report generation, the Activity tab will show a shorter timeline.

In this example, the report was generated from 3:41:35 to 4:41:35 (as seen in the top right of the screen), but the timeline in Activity only shows ~8-10 minutes when the SQL statement was sampled in ASH.


Oracle Live SQL

OTN Cloud Promo RHS