Since we finished with our migration to the Oracle 10g database, I have been meaning to post some useful notes about developing in this new environment. This last two weeks, I have found myself using Oracle’s V$ views a fair amount so I thought it might be good to share (or at least archive this information for myself).

What are V$ views?
V$ views are defined by Oracle as dynamic performance related views because they are continually updated to reflect the database state as it is running, and most of the information is pertinent to performance tuning and useful for performance monitoring. You can find more information about V$ views quite easily, both through the Oracle Technology Network or the great AskTom website.

Why should I bother to use V$ views?
Many GUI interfaces are widely available to help administer and monitor Oracle databases and sometimes you may never have a need to use these views. However sometimes those tools are beyond the reach of your normal developer and interfacing with Oracle at this lower level can be the simplest solution for what you need. A database configuration where people who can execute any arbitrary SQL statement should also generally grant them with the access to read from these very informative views.

What are some useful examples for making the most of these views?
Easily monitor through the number of connections with:

SELECT SID, SERIAL#, PADDR, USERNAME, STATUS, MACHINE
FROM V$SESSION

Monitoring the total number of connections as your application is running may give you a good example of how well your application is properly managing its connections. Those connections that sit there in an INACTIVE state are probably ideal candidates for further investigation. If you have the DBA role, you can then kill sessions (with care) with:

alter system kill session 'sid,serial#';

Uncover the SQL statements that are currently running with:

SELECT text.SQL_TEXT
FROM V$SESSION session, V$SQLAREA text
WHERE session.sql_id = text.sql_id

Another view exposes settings for National Language Support (NLS) parameters. We once had issues with database instance managements that resulted in us creating a set of environmental acceptance tests as a cheap way of detecting differences in database configurations. These were useful for ensuring settings such as the NLS_DATE_FORMAT were configured correctly for standard JDBC.

SELECT *
FROM V$NLS_PARAMETERS