Go Beyond the Data Dictionary - Utilize the V$ Tables A Look Behind the Scenes

by Joseph C. Trezzo

Have you ever figured there has to be a way to see how Oracle is working and what is really going on behind the scenes, beyond a simple SELECT statement. Many people have a concept of what is theoretically going on behind the scenes, but what is really going on ? And how can I go beyond the SQLDBA monitor to see the actual statements executing, the contents of the SGA and various components? If these are some of the things that are of interest, then read on.

There are a set of tables that supplement the Oracle data dictionary that contain real-time information about the current state of the database and various facets of the database and the database environment. This information can be very helpful in performance tuning, recovery, resource contention, and identification.

The information is stored in the SGA and updated similar to the method in which the data dictionary is updated, when an action takes place, it typically updates one of these tables. These tables are usually not by Oracle or, for that matter, in the Oracle documentation to any great detail. It is up to the Oracle user community to delve into these tables and determine their significance.

These tables are known as the virtual tables, v$ tables, dynamic performance tables, and a half a dozen other names. They are tables maintained in memory real-time and store up to date information on the current activity of the database at the current point in time or since the last database startup.

This article focuses specifically on the v$ tables and on the education of these tables and the powerfulness of their applicability. Once an understanding of these tables is reached, the accessible information is limitless.

Some of this information would have been very helpful in earlier versions of Oracle, but it�s available now. (Note: The following information was obtained when working with Oracle7 version 7.0.16, and updated with version 7.1.4 differences. These differences are highlighted in the article.)

Throughout this article, the v$ entities are referenced as tables. In actuality the v$ entities are views and each v$ view is a join of two or more x$ tables. The x$ tables are very similar in construct to the underlying data dictionary tables in that they are difficult to read and very complex. Therefore, this article will refer to the v$ entities as tables and concentrate at this level.

v$ Table Creation and Access
The v$ tables are created by the catalog.sql script for the version 7 database and there are more than 70 tables created, two of which are formed by the catldr.sql script used for SQL*Loader direct load statistical information) all with the prefix of "v_$." In the same script, there is a view created for each "v_$" table to allow users to access the view rather than the actual tables. The view name changes the prefix of each table to "v$". And lastly, there is a public synonym created on each view since the tables are owned by the SYS user. An example of a v$ table creation in the catalog.sql is shown below:

create or replace view v_$datafile as 
select * from v$datafile;
drop public synonym v$datafile;
create public synonym v$datafile for 
v_$datafile;

The v$ tables are virtual ones, created at database startup and maintained in memory. They are, in every sense, treated as tables with some limitations. These tables cannot be modified in any manner and no indexes can be created on these tables. The only operation that can be performed on these tables is a SELECT.

The dynamic performance tables are the underlying ones used for the SQLDBA monitor. All information contained in the monitor can be obtained by directly querying the v$ tables. In order to provide access to the v$ tables, Oracle has provided the script utlmontr.sql that grants SELECT privilege to the underlying SQLDBA v$ tables to a role called monitorer and then grants that role to the DBA role. This script can be modified to grant access to a specific user, a different role, or to only grant specific tables to a user, thus allowing the limitation of access to the information.

Basically, the SQLDBA monitor is a simplified facility to allow DBAs to monitor this real-time information; however, querying directly from the v$ tables allows DBAs the ability to view the information they need and customize the queries to their needs. Since the v$ tables are accessible to a user that belongs to the DBA role, a script called gntmontr.sql has been created to grant privilege to a specified user on all the v$ tables. This provides the ability to allow an Oracle developer or DBA to have access to the v$ tables and not the DBA type privileges. A revmontr.sql script has been developed to revoke all the v$ table access from a specified user. Keep in mind, three roles are created when the database is created for compatibility with version 6, namely, connect, resource and dba. These roles are created by the sql.bsq file and this section of code is displayed below.

create role connect;
grant create session, alter session, create synonym, create view,
create database link, create table, create cluster, create sequence to connect;

create role resource;
grant create table, create cluster, create sequence, create trigger,
create procedure to resource;

create role dba;
grant all privileges to dba with admin option;

Please note that Oracle Corporation explicitly states in its manuals that the v$ tables may change in the future and as evidenced from the modifications from version 6 to version 7, this is definitely true. Not only have there been an increase in the number of tables, but some of the tables have changed columns as well. Additionally, modifications to the v$ tables between incremental version upgrades as evidenced between version 7.0 and 7.1 have occurred.

SQLDBA Monitor: A Closer Look
As previously mentioned, the 16 sixteen SQLDBA monitor options will retrieve certain information from the v$ tables. In actuality, it will execute a SELECT against the table(s) to retrieve this information as evidenced by looking at the kbcus.ora file. This file is used by SQLDBA, typically located in the rdbms/sqldba directory under oracle_home on UNIX platforms, and contains all the information about SQLDBA including the SELECT statements to retrieve the information for each monitor option. Therefore, a user can be granted access to certain SQLDBA monitor options by granting access only to the v$ tables that are selected from for that option. Below is a list of the v$ tables referenced for each SQLDBA monitor option.

Monitor Option...........................v$ Table(s)

Multi-Threaded: Shared Server............v$shared_server
Multi-Threaded: Dispatcher...............v$dispatcher
Multi-Threaded: Circuit..................v$circuit, v$dispatcher,
v$shared_server, v$session
Multi-Threaded: Queue....................v$queue
Process..................................v$process
Session..................................v$session, v$process
Table....................................v$access
SQL Area.................................v$sqlarea
Library Cache............................v$librarycache
Latch....................................v$latch, v$latchname,
v$latchholder
Lock.....................................v$lock, v$session
File I/O.................................v$dbfile, v$filestat
System I/O...............................v$process, v$session,
v$sess_io
Rollback.................................v$rollstat, undo$
Statistic: Session.......................v$sysstat, v$sesstat,
v$session
Statistic: System........................v$sysstat

In version 7.1.4, the rollback option replaced the undo$ table with the v$rollname table and the statistic: session option replaced the v$sysstat table with the v$statname table.

Since the SELECT statements used to retrieve the information for the SQLDBA monitor are in the kbcus.ora file, these SELECTs were copied out of this file and have created a SQL*Plus script for each SQLDBA monitor option using the SELECT from the kbcus.ora as the base. This allows the ability to query the same information as monitor without going into the monitor, and also provides the ability to modify the information being displayed by modifying these scripts. A portion of the SQLDBA Table monitor option is shown below as a SQL*Plus script and mimics the display and input parameters.

define �Minimum_Session_ID� = �&&Minimum_Session_ID�
define �Maximum_Session_ID� = �&&Maximum_Session_ID�
define �Schema_Filter� = �&&Schema_Filter�
define �Table_Filter� = �&&Table_Filter�

SELECT	 SID,OWNER,OBJECT
FROM	 V$ACCESS
WHERE	 SID >= nvl(�&&Minimum_Session_ID�, 0)
AND	 SID <= nvl(�&&Maximum_Session_ID�,999999)
AND	 OWNER LIKE UPPER(�%� || �&&Schema_Filter� || �%�)
AND	 OBJECT LIKE UPPER(�%� || �&&Table_Filter� || �%�)
ORDER BY	 SID,OWNER;



71 v$ Tables in SGA


50 tables 


21 tables 
71 v$ Tables in SGA

Monitor 
16 Options

The other 15 SQLDBA monitor options have been created as well and are available upon request. As evidenced, the SQLDBA monitor only provides a view of a subset of the valuable information contained within the v$ tables; therefore, querying the remainder of the v$ tables is left for the Oracle developers and DBAs and is discussed in more detail in the following sections.

Querying the v$ tables
The v$ tables can be queried the same as any other Oracle table, but keep in mind, the information is changing very rapidly in these tables. The information can be periodically inserted into a pre-created table to allow for the compilation of data over a time period. The data will be analyzed later, to build statistical reporting and alerting based on different conditions in your database. The v$ table information is utilized by the DBA monitoring tools on the market today. The key to querying the data outside of any tools is the ability to know what are all the tables, what information is contained in each one and what can this information provide if queried properly. A list and description of every table and column is available in the "Oracle7 Server: Application Developer�s Guide, Appendix D". Provided below is a logical grouping of the v$ tables into categories, along with a brief description of each category.

>v$ Table Categories
The v$ tables are categorized according to their primary function and are often times needed to join to another category to retrieve certain information. Below is a description of each category followed by the actual v$ tables that belong to each group.

Category Descriptions
Backup/Recovery:	Information related to database backups and recovery, including last backup, archive logs, state of files for backup, and recovery.

Caches:	Information related to the various caches, including objects, library, cursors, and the dictionary.
Cursors/SQL Statements:	Information related to cursors and SQL statements, including the open cursors, statistics, and actual SQL text.

Direct Loader:	Information related to the SQL*Loader direct load option.

General:	General Information related to various system information, including background processes, database, data files, licensing, instances, SGA, and versions.

I/O:	Information related to I/O, including files, and statistics.

Latches:	Information related to latches.

Locks:	Information related to locks.

Multi-Threaded/Parallel Server:	Information related to multi-threaded and parallel servers, including connections, queues, dispatchers, and shared servers.

Overall System:	Information related to the overall system performance.

Parallel Query:	Information related to the Parallel Query Option (New in v7.1.4).

Parameters:	Information related to various Oracle parameters, including initialization and nls per session.

Redo Logs:	Information related to redo logs, including statistics and history.

Rollback Segments:	Information on rollback segments, including statistics and transactions.

Sessions:	Information related to a session, including object access, cursors, processes, and statistics.


Tables per Category

Backup/Recovery
v$archive   v$backup   v$recovery_log   v$recover_file	

Caches
v$db_object_cache   v$library_cache   v$rowcache

Cursors/SQL Statements
v$open_cursor   v$sqlarea   v$sqltext(

Direct Loader
v$loadcstat	v$loadtstat

General
v$bgprocess    v$compatibility*   v$compatseg*    v$controlfile
v$database     v$datafile         v$dbfile        v$dblink
v$enabledprivs v$fixed_table      v$instance**    v$license
v$option(      v$reqdist          v$resource      v$sga	
v$sgastat      v$timer            v$type_size     v$version	

I/O
v$filestat   v$waitstat

Latches
v$latch   v$latchholder   v$latchname

Locks
v$lock   v$_lock

Multi-Threaded/Parallel Server
v$circuit         v$dispatcher   v$mts   v$queue	
v$shared_server   v$thread	

Overall System
v$sysstat   v$system_cursor_cache   v$system_event	

Parallel Query
v$pq_sesstat*   v$pq_slave*   v$pq_sysstat*

Parameters
v$nls_parameters   v$parameter   v$mls_parameter   v$nls_valid_values*

Redo Logs
v$log   v$logfile   v$loghist   v$log_history

Rollback Segments
v$rollname   v$rollstat   v$transaction

Sessions
v$access          v$process	      v$session   v$session_cursor_cache	
v$session_event   v$session_wait   v$sesstat    v$sess_io	
v$statname

Note: The "*"signifies tables that are new in version 7.1.4. The "**" signifies tables only accessible by the SYS user.

Also, some of the v$ timing fields are dependent on the timed_statistics init.ora parameter being set to true, otherwise there will be no timing in these fields.

Sample Queries
This section is centered around the "educate by example" method of education and will display several SQL*Plus queries with a brief explanation of the information that is retrieved from a query of this type. This should provide a very good knowledge base on the major v$ tables and the information that is readily available in these tables.

Caches
Determine library cache hit ratio. If the hit ratio or reloads is high, increase the shared_pool_size init.ora parameter.

select namespace, gethitratio, pinhitratio, reloads
from   v$librarycache;

Determine dictionary cache hits per parameter.

select parameter, gets, getmisses from v$rowcache;

Determine dictionary cache hit ratio. If the ratio of misses is greater than 15 percent, increase the shared_pool_size init.ora parameter. The dc SGA storage is now a part of the shared pool along with the library cache, there are no more "dc_" parameters in the init.ora. This is a key area since the dictionary is accessed so frequently especially by the internals of Oracle. I would never recommend lowering the shared_pool_size since the library cache is also a part of this shared pool.

select sum(gets), sum(getmisses),
       sum(getmisses)/sum(gets) "Ratio of Misses"
from   v$rowcache;

Cursors/SQL Statements
Display the current cursor being executed by a session.

select a.sid, a.username, b.sql_text
from   v$session a, v$open_cursor b
where  a.saddr = b.saddr;

Display the entire SQL statement being executed by a session.

select a.sid, a.username, b.sql_text
from   v$session a, v$sqltext b
where  a.sql_address = b.address
and    a.sql_hash_value = b.hash_value
order  by a.sid, a.username, b.piece;

Display each cursor of the shared cursor cache, including the times executed and loaded into the cache.

select sql_text, version_count, executions, loads
from   v$sqlarea;

General & I/O
Determine the I/O that is taking place on each database file. If the reads and writes are not distributed evenly between files, the tablespaces may need to be restructured for better performance.

select a.file#, a.name, a.status, a.bytes, b.phyrds, b.phywrts
from   v$datafile a, v$filestat b
where  a.file# = b.file#;

Display database information, including the name, creation date, and archive mode (on or off).

select * from v$database;

Display the actual memory in bytes that each init.ora parameter is using. The v$sga table summarizes the bytes by the type of parameter.

select * from v$sgastat;

Displays licensing information, including the maximum number of licensed concurrent or named users, the current number of active concurrent sessions, and the highest number of users that were concurrently logged on at anyone time since the last database startup.

select sessions_max, sessions_current,
       sessions_highwater, users_max
from   v$license;

Multi-Threaded/Parallel Server
Determine the activity of each dispatcher. If the percentage is low, that would indicate that the dispatchers are busy; therefore, more dispatchers should be added.

select name, (idle/(busy+idle))*100
from v$dispatcher;

Determine dispatcher queue average wait time.

select paddr, type, queued, wait, totalq,
       wait/totalq avg_wait_time
from   v$queue;

Determine shared server activity, if idle often, remove a shared server(time converted to minutes).

select name, status, idle/6000, busy/6000, requests
from   v$shared_server;

Overall System
Determine if the data block buffers are being utilized optimally. If the read hit ratio is less than 95 percent, increasing the db_block_buffers may help performance. However, beware that if the ratio is near 100 percent and the number of gets is in the millions, there is a very good chance that the statement being executed is not optimized. Keep in mind, the hit ratio could be near 100% and a query could take very long to complete execution. Likewise, the hit ratio could be much less than 100% and a query could take a very short time to complete execution. The key is to also consider the gets or logical reads, if the number is extremely high, then examine the application code.

select 1-(sum(decode(name, �physical reads�, value,0))/
       (sum(decode(name, �db block gets�, value,0)) +
       (sum(decode(name, �consistent gets�, value,0)))))
       "Read Hit Ratio"
from   v$sysstat;

Parameters
Display the natural language parameters in effect for a session. A user can alter the different parameters for a session. The default date format is "dd-mon-yy". If a user wants to change this for the current session, they could enter the command "alter session set nls_date_format=�mm/dd/yy�" and all dates returned in that session would display in that format.

select * from v$nls_parameters;

Display all the init.ora parameters and the current values, along with signifying if the value is the default value.

select name, value, isdefault
from   v$parameter
order  by name;

Redo Logs
Display details on the redo log files, including the filename, size, and archive status.

select a.member, b.*
from   v$logfile a, v$log b
where  a.group# = b.group#;  

Display a history of the archive logs created along with the file names.

select * from v$log_history;

Rollback Segments
Display rollback information and determine if more segments are needed. If the waits to gets goes over one, then add more rollback segments.

select a.name, b.extents, b.rssize, b.xacts, b.waits,
       b.gets, optsize, status
from v$rollname a, v$rollstat b
where a.usn = b.usn;

Display each rollback segment that has current transactions, along with the session id, serial#, username, and actual SQL statement being executed in the rollback segment. A session can be killed (terminated) by using the Oracle system kill command (alter system kill session �sid,serial#�; where sid and serial# are obtained from the session table). This allows a DBA the ability to determine which rollback segment is processing each transaction.

select a.name, b.xacts, c.sid, c.serial#, c.username, d.sql_text
from   v$rollname a, v$rollstat b, v$session c, v$sqltext d,
       v$transaction e
where  a.usn = b.usn
and    b.usn = e.xidusn
and    c.taddr = e.addr
and    c.sql_address = d.address
and    c.sql_hash_value = d.hash_value
order by a.name, c.sid, d.piece;

Sessions
Determine the number of sessions for each user.

select username, count(*) from v$session
group by username;

Display detail information about a session, including the operating system username and process, and terminal.

select sid, username, program, osuser, process,
	machine, terminal, type
from v$session;

Display detailed statistics per session to determine a session�s resource usage.

select a.sid, a.username, b.name, c.value
from v$session a, v$statname b, v$sesstat c
where a.sid = c.sid
and b.statistic# = c.statistic#
and a.username = upper(�&username�)
order by a.sid, a.username, b.name;

Determine the objects that are being accessed by each session.

select a.sid, a.username, b.owner, b.object, b.ob_typ
from   v$session a, v$access b
where  a.sid = b.sid;

Display the processes for each session. If the process is a background process, then the operating system and identifier are retrieved from the process table.

select a.sid, decode(b.background,1,b.program,a.username)
       "user", b.pid,
decode (b.background,1,b.spid,a.audsid) "os id"
from   v$session a, v$process b
where  a.paddr = b.addr;

Determine the memory usage per session.


select a.sid, a.username, b.value
from   v$session a, v$sesstat b, v$statname c
where  a.sid = b.sid
and    b.statistic# = c.statistic#
and    c.name = �session memory�;

This by no means is all encompassing, and there is much more that can be obtained from the v$ tables, but the intent is to provide a solid starting point. Additionally, any query tool can be utilized to view the v$ tables as long as the necessary privilege has been granted.

Future Version Impact
As noted several times in this article, the v$ tables are continually being enhanced as more and more features are added to Oracle. This trend will continue with the release of Oracle version 7.2 and 7.3. Based on a presentation at the DEVOUG user group meeting in early June of this year, the key features of version 7.2 and 7.3 indicate that the direction is continuing toward parallel operations, data warehousing, database administration, backup and recovery, memory management, query execution, and overall performance improvements. Since the v$ tables are currently used to support these features, each incremental version will provide increased power and usefulness of the v$ tables.

Summary
The intent of this article was to provide an education on the v$ tables, how they are created, what they are used for, and how they can be utilized by Oracle developers and DBAs to take advantage of this valuable information that Oracle provides. They are truly an extension to the standard data dictionary tables and should be treated as such. If you have any questions about this article, feel free to contact me at 708-960-2909.

Editor�s Note:
If you find any problems in this document, please report them to us in writing. Neither TUSC or the author warrant that this document is error-free.

References
Oracle7 Server: SQL Language Reference Manual, Oracle Corporation
Oracle7 Server: Application Developer�s Guide, Oracle Corporation
Walter Lindsay, EcoSystems Software,1993 IOUG Proceedings,Volume 1,Paper 38
Gita Kulandaiswamy, Oracle Corporation,1993 IOUG Proceedings,Volume 2,Paper 82
Sue Jang, Oracle Corporation, June, 1994 RMOUG, Tuning an Oracle Database
G. Dale Taylor, Oracle Corporation, June, 1995 DEVOUG, Oracle Database Directions

About the Author
Joseph Trezzo is Executive Vice President of The Ultimate Software Consultants (TUSC). Joseph is a regular author in Oracle publications and presenter at Oracle User Groups. TUSC offers consulting services for the complete project life cycle with in depth experience in CASE, GUI development, system migration, database administration and performance tuning. TUSC also provides software integration solutions with a recommended portfolio of product development tools and offers Oracle related training courses.

TUSC
377 East Butterfield Road
Suite 100
Lombard, Illinois 60148
Phone: 708-960-2909
Fax: 708-960-2938
Internet: [email protected]

---

This is a copy of an article published @ http://www.ioug.org/