Host and Database Performance Monitoring - Oracle Exadata Recipes: A Problem-Solution Approach - page 414

Figure 14-10. Monitored SQL executions
Similar to in the previous section, you can not only see a number of SQL execution details from this main page,
but you can also select a SQL ID to drill down into the Monitored SQL Execution Details page. The Overview
sections are similar to the examples above, but in Figure 14-11 , we are displaying an excerpt showing the SQL
execution plan. As you can see, each operation is color-coded and weighted based on time, I/O requests, CPU
activity, and so on.
Figure 14-11. SQL plan details from SQL Monitor
Generating SQL Monitoring Reports with dbms_sqltune
If you do not have Enterprise Manager or want an alternative to using the Enterprise Manager, you can use
DBMS_SQLTUNE.REPORT_SQL_MONITOR to produce a text- or HTML-based SQL Monitoring report. Use Listing 14-1
to display a SQL Monitor report for a specific SQL ID.
Listing 14-1. lst14-01-sqlmon-sqlid-html.sql
SQL> set pagesize 0 echo on timing off
SQL>set linesize 1000 trimspool on trim on
SQL>set long 2000000 longchunksize 2000000
SQL>set echo on
SQL>spool output.html
SQL>select DBMS_SQLTUNE.REPORT_SQL_MONITOR( sql_id=>'&&p_sqlid',
event_detail=>'YES',
report_level=>'ALL',
type=>'HTML')
from dual;
spool off
The type parameter is used to specify the report output, which in this case is HTML. If you wish to generate a text
file report, you can omit the type attribute or specify the type=>'TEXT' argument.
To report on the current SQL Monitoring data without a specific SQL ID, execute the script in Listing 14-2.
This is analogous to navigating to the SQL Monitoring window in Enterprise Manager but generates a point-in-time
SQL Monitoring report for active SQL statements.