JL Computer Consultancy

New V$ items in Oracle 8 (and symmetric difference)

Pre- Aug 1999

One of the first things I do with a new version of Oracle (even a minor release) is to check if there are any changes in the v$ performance monitoring view. This note describes how I do it, and highlights a few of the interesting differences between and

To list the definitions of the V$ views, I take the simple minded approach that the views themselves will all have names starting with 'V_$' - if you check catalog.sql you will find sections of code like the following - so it seems a good bet to look at the dictionary view USER_TAB_COLUMNS where table_name like 'V_$%' to get the information I need.

        create or replace view v_$session as selcet * from v$session;
        drop public synonym v$session;
        create public synonym v$session for v_$session;

The approach I take to identifying the changes is to get both instances up and running at once, then create database links between the two so that I can check the symmetric difference. For those not in the know, the symmetric difference is simply the list of the bits where two table do not overlap - Oracle does not support this feature, although it supports the rest of the set-based operations - UNION, INTEREST, and MINUS.

To get at the symmetric difference in Oracle, we have two options:

        (A union B) minus (A intersect B)
or      (A minus B) union (B minus A)

The second option is the most appropriate for getting the difference whilst including a tag to show which table the data comes from. The code below is the script I used to find the differences between the two sets of V$ tables currently on my system.

Back to Main Index of Topics

rem     Script:        get_v2.sql
rem     Author:        Jonathan Lewis
rem     Dated:         14-Aug-98
rem     Purpose:       Report changes in V$ views across versions
rem     Notes:
rem     ------
rem     Has to be run by a DBA-type.
rem     Needs a couple of database links defined before you use it.
rem     The version of SQL has to support the ESCAPE option for wildcards
rem     Alternatively:
rem            Use substr(table_name,2,1) = '_'
rem     or      Replace '_' with '#' (say)
rem     The version column (7 or 8) tags which instance the data came from
set pagesize 0
set trimspool on
column table_name format a29
column column_name format a27
column version format 99 heading Vn
break on table_name skip 1
spool v_list
select * from 
               select table_name,column_name,8 version
               from user_tab_columns@D804 
               where table_name like  'V\_$%' escape '\'
               select table_name,column_name,8 version
               from user_tab_columns@D733
               where table_name like  'V\_$%' escape '\'
union all      -- there will be no duplicates, so union all is safe
               select table_name,column_name,7 version
               from user_tab_columns@D733 
               where table_name like  'V\_$%' escape '\'
               select table_name,column_name,7 version
               from user_tab_columns@D804
               where table_name like  'V\_$%' escape '\'
order by 1,3,2
spool off

Back to Main Index of Topics

Sample of Output

V_$INSTANCE                   KEY                           7
                              VALUE                         7
                              ARCHIVER                      8
                              HOST_NAME                     8
                              INSTANCE_NAME                 8
                              INSTANCE_NUMBER               8
                              LOGINS                        8
                              LOG_SWITCH_WAIT               8
                              PARALLEL                      8
                              SHUTDOWN_PENDING              8
                              STARTUP_TIME                  8
                              STATUS                        8
                              THREAD#                       8
                              VERSION                       8
660 rows selected.

Back to Main Index of Topics

You will notice that there are rather a lot of differences between the two - a total of 660 column changes. It is a matter of opinion which ones are most interesting or useful, but from my viewpoint the ones I particularly like are:



Now includes a maximum read time and a maximum write time - helps eliminate timing anomalies



Much friendlier and more information - a convenience compared to the V7 version.



How close to maximum configured sessions (et. al.) have you been since instance startup



Now includes a maximum wait time for the event - helps eliminate timing anomalies



Ever wanted to know how much longer an SQL statement will run ? this is supposed to tell you



Every DBA eventually wants to identify who is using how much sort space - now it's easy to find out.

There are also many more views for:


Backup and recovery status



DLM stats for Oracle Parallel Server



Advanced Queues



Multi-threaded Server



Locking and latching



Memory usage


Back to Main Index of Topics