Friday, April 22, 2011

What is the difference between V$ and GV$, also V$ and V_$ ?

These “$” views are called dynamic performance views. They are continuously updated while a database is open and in use, and their contents relate primarily to performance. The actual dynamic performance views are identified by the prefix V_$ .  Public synonyms for these views have the prefix V$ . We should access only the V$ objects, not the V_$ objects . Let's  have look on the object type  .
SQL>select owner, object_name, object_type  from dba_objects where object_name like '%SESSION'   and  object_name like 'V%' ;
Output :
OWNER        OBJECT_NAME               OBJECT_TYPE    
-----------     ---------------------           ------------------------
SYS              V_$SESSION                           VIEW
SYS              V_$HS_SESSION                     VIEW
SYS              V_$PX_SESSION                     VIEW
SYS              V_$DETACHED_SESSION         VIEW
SYS              V_$LOGMNR_SESSION             VIEW
SYS              V_$DATAPUMP_SESSION         VIEW
SYS              V$XS_SESSION                       VIEW
PUBLIC        V$SESSION                           SYNONYM
PUBLIC        V$HS_SESSION                     SYNONYM
PUBLIC        V$PX_SESSION                     SYNONYM
PUBLIC        V$XS_SESSION                     SYNONYM
Hence "v$xx" objects are synonym and "v$_xx"  are the views.

Let's have a look on the difference between V$ view and GV$ views . 
GV$ views are called 'Global Dymanic Performance views' and retrieve information about all started instances accessing one RAC database. In contrast, standard dynamic performance views (V$ views) retrieve information about the local instance only. For each of the V$ views available, there is a corresponding GV$ view except for a few exceptions. In addition to the V$ information, each GV$ view possesses an additional column name INST_ID. The INST_ID column displays the instance number from which the associated V$ view information is obtained. 
GV$  views use a special form of parallel execution. The parallel execution coordinator runs on the instance that the client connects to and one slave is allocated in each instance to query the underlying V$ view for that instance.

Enjoy     :-)