Performance Tuning Functions

V$SQL_MONITOR displays SQL statements whose execution have been (or are being) monitored by Oracle. An entry is created in V$SQL_MONITOR every time the execution of a SQL statement is being monitored. SQL monitoring is automatically started when a SQL statement runs parallel or when it has consumed at least 5 seconds of CPU or I/O time.

V$SQL_MONITOR is a replacement for v$session_longops
Statistics collection is now automatic in 11g with v$sql_monitor.

With V$SQL_MONITOR, Oracle 11g will automatically collect the execution plans for these long-running statements. These statements are visible within V$SQL_PLAN_MONITOR. The V$SQL_MONITOR view will also display execution statistics.

Each time the execution of a SQL statement is monitored, an entry is created in V$SQL_MONITOR.

The refresh rate for the statistics in v$sql_monitor is once per second, which is very close to real time.

The retention for v$sql_monitor is designed such that the SQL monitoring information is not deleted immediately after the execution is completed. In fact, v$sql_monitor retains the information for at least one minute; however, v$sql_monitor continues to monitor new statements, so entries will be deleted in order to reclaim the space.

The important execution statistics inside v$sql_monitor include:

•v$sql_monitor. elapsed_time

•v$sql_monitor. cpu_time

•v$sql_monitor. fetches

•v$sql_monitor. buffer_gets

•v$sql_monitor. disk_reads

•v$sql_monitor. direct_writes

•v$sql_monitor. application_wait_time

•v$sql_monitor. concurrency_wait_time

•v$sql_monitor. cluster_wait_time

•v$sql_monitor. user_io_wait_time

•v$sql_monitor. plsql_exec_time

•v$sql_monitor. java_exec_time

Of course, like many of Oracle’s most appealing features, v$sql_monitor is available via a license for the Oracle Diagnostic & Tuning Pack.



Leave a Reply

Your email address will not be published.