Oracle sql monitor

The dynamic performance view V$SQL_MONITOR, which is used to display SQL statement information monitored by Oracle. The information in V$SQL_MONITOR and v$sql_plan_monitor will be refreshed in real time with a frequency of once per second. After the execution of the SQL statement, the monitoring information will not be deleted immediately, and Oracle will ensure that the relevant records will be saved for one minute (controlled by the parameter _ sqlmon_recycle_time, default is 60s), and eventually these records will be deleted and reused. Enabled only for CONTROL_MANAGEMENT_PACK_ACCESS-DIAGNOSTIC+TUNING and STATISTICS_LEVEL-ALL or TYPICAL.

The v$sql_monitor view contains the currently running SQL statements, as well as the most recently run SQL statements.

The following conditions must be met when using the SQL statements monitored in the v$sql_monitor view:

1) automatically monitor any parallel statements

2) if a SQL statement consumes more than 5 seconds of CPU or I time, it will be automatically monitored.

3) Monitor any SQL statements (monitor) that enable monitoring at the statement level

Tip: combining v$sql_monitor view with v$sql_plan_monitor view can further query the execution plan of SQL and other information. More information about SQL can be obtained by combining some other views, such as v$active_session_history, v$session, v$session_longops, v$sql, v$sql_plan, and so on.

Select dbms_sqltune.report_sql_monitor (sql_id= >'', type= > 'ACTIVE') from dual

Here will come out a clob field information, save and generate a html file in IE to open and browse.

This report will get the displayed framework and JS script through OTN. If you cannot connect to the public network, you can build the corresponding file locally:

Mkdir-p / sqlmon

Cd / sqlmon

Wget-mirror-no-host-directories-cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/flashver.js

Wget-mirror-no-host-directories-cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/loadswf.js

Wget-mirror-no-host-directories-cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/document.js

Wget-mirror-no-host-directories-cut-dirs=1 http://download.oracle.com/otn_software/emviewers/sqlmonitor/11/sqlmonitor.swf

-DBMS_SQLTUNE.report_sql_monitor_list