Database Reference
In-Depth Information
•
The Plan Control tab shows whether there are objects, like SQL profiles and SQL plan
baselines, associated to the SQL statement. Such objects are discussed in Chapter 11.
•
The Tuning History tab shows information generated by the SQL Tuning advisor. The SQL
Tuning advisor is also briefly covered in Chapter 11.
•
The SQL Monitoring tab, available from version 11.1 onward, shows information related to
real-time monitoring. If this information isn't available, the tab can't be selected.
If the version of Oracle Database you're using is 11.2 or newer, and you have the license of the Tuning Pack
option, from the SQL Detail page you can also generate a SQL Details Active Report. Because it doesn't provide
additional information compared to the SQL Detail page, I only use it when I want to save the information I'm seeing
in an HTML file. That way, I can consult it later on or send it to somebody else. If required, you can also generate the
same report without using Enterprise Manager. To do that, use the
report_sql_detail
function of the
dbms_sqltune
package. The function accepts several input parameters and returns a CLOB containing the report. Whereas several
input parameters can be used to change the data that is displayed in the report, with the
sql_id
parameter you specify
which SQL statement the information is displayed for. The following query, an excerpt from the
report_sql_detail.sql
script, shows an example of how to generate such a report:
SELECT dbms_sqltune.report_sql_detail(sql_id => 'c13sma6rkr27c')
FROM dual
To display the execution statistics (Figure
4-24
), as well as the general information about a SQL statement without
Enterprise Manager, you can use one of the following scripts:
sqlarea.sql
,
sql.sql
, and
sqlstats.sql
. As their
names imply, they extract data provided through
v$sqlarea
,
v$sql
, and
v$sqlstats
, respectively. Refer to the
“SQL Statement Information” part of the “Analysis Without Diagnostics Pack” section for additional information.
■
The
sqlarea.sql
,
sql.sql
, and
sqlstats.sql
scripts provide a feature that isn't available in Enterprise
Manager. They can not only show the cumulated statistics since the cursor was loaded as Enterprise Manager does,
but also the statistics about the last
n
seconds. This is useful to know what the statistics about the current executions are.
In fact, for cursors that stay in the library cache for a long period of time, the picture provided by the cumulated statistics
might be misleading.
Tip
To display the activity data about a single SQL statement without Enterprise Manager, you can use the
ash_activity.sql
script by specifying
all
as first parameter (that means no restriction at the session level) and the
ID of the SQL statement as second parameter. The following example shows data similar to Figure
4-25
:
SQL> @ash_activity.sql all c13sma6rkr27c
TIME AvgActSes CPU% UsrIO% SysIO% Conc% Appl% Commit% Config% Admin% Net% Queue% Other%
----- --------- ---- ------ ------ ----- ----- ------- ------- ------ ---- ------ ------
19:10 2.6 3.2 96.8 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
19:11 2.4 5.5 94.5 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
19:12 2.9 2.3 97.7 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
19:13 2.4 5.6 94.4 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
19:14 2.4 4.2 95.8 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
19:15 2.1 6.3 93.7 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
19:16 1.8 4.7 95.3 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
19:17 2.1 1.6 98.4 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
