JL Computer Consultancy

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 7.3.3.5 and 8.0.4.2.

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.

rem

rem     Script:        get_v2.sql

rem     Author:        Jonathan Lewis

rem     Dated:         14-Aug-98

rem     Purpose:       Report changes in V$ views across versions

rem

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     

rem     Alternatively:

rem            Use substr(table_name,2,1) = '_'

rem     or      Replace '_' with '#' (say)

rem

rem     The version column (7 or 8) tags which instance the data came from

rem

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 '\'

        MINUS

               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 '\'

        MINUS

               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

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.

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:

There are also many more views for: