While reading the 12.1 Documentation regarding
Dynamic Performance views and Data Dictionary views after pondering for a while, I decided to write a little bit about how exactly Oracle retrieves the information from these views.
Before we get to this, Let us understand what these views are:
Every DBA makes use of these views to pull information to know what exactly goes on in the database and know a little more detail about the database.
Dynamic Performance views provide data on internal disk structures and memory structures. We can select from these views, but we can never update or alter them.
Data dictionary views provide information regarding metadata of the database. They are available only after database is created, open and data dictionary tables become accessible.
Things to remember:
1) Dynamic performance views (or) V$ views are available since our instance startup even at NO MOUNT stage.
2) Data Dictionary views are identified by DBA_, ALL_, USER_ views
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2177328 bytes
Variable Size 373294800 bytes
Database Buffers 138412032 bytes
Redo Buffers 8052736 bytes
SQL> select count(*) from dba_users;
select count(*) from dba_users
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only (
dictionary views are inaccessible in NO MOUNT)
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
orcl
3) The V$FIXED_TABLE view contains information about all of the dynamic performance tables and views. These tables and views are
created and populated automatically during instance startup and destroyed during shutdown.
Let's define the basic structure of a dynamic performance view. Consider
V$INSTANCE.
v$instance ==> v_$instance ==> v$instance ==> gv$instance ==> x$table(s)
public synonym view fixed view fixed view
SQL> select synonym_name,table_name from dba_synonyms where synonym_name ='V$INSTANCE';
SYNONYM_NAME TABLE_NAME
------------------------------ ------------------------------
V$INSTANCE V_$INSTANCE
SQL> select view_name from dba_views where view_name ='V_$INSTANCE';
VIEW_NAME
------------------------------
V_$INSTANCE
SQL> set long 3000
SQL> select text from dba_views where view_name ='V_$INSTANCE';
TEXT
--------------------------------------------------------------------------------
select "INSTANCE_NUMBER","INSTANCE_NAME","HOST_NAME","VERSION","STARTUP_TIME","S
TATUS","PARALLEL","THREAD#","ARCHIVER","LOG_SWITCH_WAIT","LOGINS","SHUTDOWN_PEND
ING","DATABASE_STATUS","INSTANCE_ROLE","ACTIVE_STATE","BLOCKED" from v$instance
Now, to get the definition of the view.
SQL> select view_definition from v$fixed_view_definition where view_name ='V$INSTANCE';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select INSTANCE_NUMBER , INSTANCE_NAME , HOST_NAME , VERSION , STARTUP_TIME , S
TATUS , PARALLEL , THREAD# , ARCHIVER , LOG_SWITCH_WAIT , LOGINS , SHUTDOWN_PEND
ING, DATABASE_STATUS, INSTANCE_ROLE, ACTIVE_STATE, BLOCKED from GV$INSTANCE wher
e inst_id = USERENV('Instance')
Ah, We query the
GV$INSTANCE.
SQL> select view_definition from v$fixed_view_definition where view_name ='GV$INSTANCE';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select ks.inst_id,ksuxsins,ksuxssid,ksuxshst,ksuxsver,ksuxstim,decode(ksuxssts,0
,'STARTED',1,'MOUNTED',2,'OPEN',3,'OPEN MIGRATE','UNKNOWN'),decode(ksuxsshr,0,'N
O',1,'YES',2,NULL),ksuxsthr,decode(ksuxsarc,0,'STOPPED',1,'STARTED','FAILED'),de
code(ksuxslsw,0,NULL,2,'ARCHIVE LOG',3,'CLEAR LOG',4,'CHECKPOINT', 5,'REDO
GENERATION'),decode(ksuxsdba,0,'ALLOWED','RESTRICTED'),decode(ksuxsshp,0,'NO','
YES'),decode(kvitval,0,'ACTIVE',2147483647,'SUSPENDED','INSTANCE RECOVERY'),deco
de(ksuxsrol,1,'PRIMARY_INSTANCE',2,'SECONDARY_INSTANCE','UNKNOWN'), decode(qui_s
tate,0,'NORMAL',1,'QUIESCING',2,'QUIESCED','UNKNOWN'), decode(bitand(ksuxsdst, 1
), 0, 'NO', 1, 'YES', 'NO') from x$ksuxsinst ks, x$kvit kv, x$quiesce qu where k
vittag = 'kcbwst'
These views are built on the X$ tables.
These x$ tables can be viewed when our database is up (atleast at Mount). They are just memory based structures. As indicated earlier, They are destroyed at shutdown and created each time at instance startup.
x$tables are
not real tables. They represent internal structures. The information is pulled from memory.
