Library Cache Lock Waits

Optimizing Performance Chpt 14

This is part of the Oracle documentation

This section deals with the SDU and why it is important to resize this to maximize performance in a situation where network performance is critical

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.


V$SQL_MONITOR

V$SQL_PLAN_MONITOR

Ready for Action?

LET'S GO!
Copyright 2024 IT Remote dot com
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram