Category Archives: 11gR2

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.


V$SQL_MONITOR

V$SQL_PLAN_MONITOR

Oracle11gR2 Linux install

Preinstall requirements:

Minimum RAM requirement: 1.5 GB for GRID Infrastructure and 2.5GB for GI plus RAC

you can verify easily by:

# grep memTotal /proc/meminfo

For Swap space checking
# grep SwapTotal /proc/meminfo
For disk temp space
# df -h /tmp