Oracle Dynamic Performance Views

Dynamic Performance v$ Views

Real-time performance metrics covering memory usage, wait events, input/output device
throughput, and instance activity are available through dynamic performance views, also known as v$ views.

Throughout its operation, the Oracle server records current database activity in a set of virtual tables called dynamic performance views. These virtual tables exist in memory only when the database is running, to reflect real-time conditions of the database operation. They point to actual sources of information in memory and the control file.

Purpose of Dynamic Performance Views

Dynamic performance views provide a snapshot of real-time data.

The dynamic performance views answer questions such as:

  • Is the object online and available?
  • Is the object open?
  • What locks are being held?
  • Is the session active?

Dynamic performance views record current database activity.

  • Views are continually updated while the database is operational
  • Information is accessed from  Memory  and Control file
  • DBA uses dynamic views to monitor and tune the database
  • Dynamic views are owned by SYS user
  • Dynamic views data is non persistent
  • Dynamic performance metrics should be considered in context of performance trends or against baseline data

Dynamic performance views should not be used to make configuration decisions until the instance hasbeen operational for a while.

Architecture of Dynamic Performance Views

Dynamic Performance views are based on very low level Oracle views X$ views. The X$views are actually not tables rather Oracle internal structures(database representation of C Structures) maintained by Oracle kernel.

On top of X$ views, Oracle has created complete set of views called V_$ views. These V_$ views have synonyms with same names starting with V$. These V$ synonyms are called dynamic performance views and V$ views as well.

The dynamic performance tables are owned by SYS.

Dynamic Performance Views Examples

v$controlfile

Displays the location and status of each controlfile in the database.

v$instance

Instance_role can be used to determine if an instance is an active instance (=primary instance) or a secondary instance (in a standby environment.

v$database

This view lets you access database information. For example, you can check (using log_mode) whether or not the database is in archivelog mode

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: