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.
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
select dbms_sqltune.report_sql_detail('&sql_id') from dual;
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.
'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.
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.
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.
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.
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.
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.