11g SQL Monitoring , A Must Monitoring Tool for Exadata

Exadata is not for the faint-heart databases. When there are some dense, complicated, parallel and long executions of SQLs upon huge volumes of data are required, Exadata is the way to go. It's compute nodes, cell nodes, IB switches, storage server software, database server software create an astounding synergy to provide a unique fastest experience.

Most of the queries which run on the Exadata are long and parallel in nature. Oracle 11g provides a sumptuous feature of SQL Monitoring to monitor any statements which are parallel or consume more than 5 secs in IO or at the CPU. Given the workload of Exadata, SQL Monitoring is so natural a tool to monitor your application in real time.

SQL Monitoring can be savored through OEM or through the SQL*Plus. For example to generate SQL Monitoring report in SQL Plus do the following:

set long 10000000
set longchunksize 10000000
set linesize 200
select dbms_sqltune.report_sql_monitor from dual;

Query Example Ref: Oracle Docs

You may also generate a cool graphical ACTIVE HTML report of your query:

set long 10000000
set longchunksize 10000000
set linesize 200

spool /tmp/sql_ACTIVE.html
select dbms_sqltune.report_sql_monitor(report_level=>'+histogram', type=>'ACTIVE', sql_id=>'') monitor_report from dual;
spool off

You can put the above script in scheduler or cron job and monitor the query by

scheduling the job at regular intervals. This way you can get a nice, cute, and

genuine life history of any SQL, as SQL monitoring shows how many parallel slaves

were used, and how much time was spent at which steps in the execution plan, and

it also shows the workload of the parallel slaves.