Oracle Scratchpad

March 18, 2022

v$ v_$ v$

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 5:33 pm GMT Mar 18,2022

One of the “curiosity” questions that comes up from time to time on the various forums is the one about the cascade of names from synonym to view to object that Oracle uses to provide access to the dynamic performance views. Typically we see the pattern:

  • v$XXX is a public synonym for a stored view v_$XXX
  • v_$XXX is defined as ‘select {list of columns} from v$XXX

So, after two steps, we seem to be back where we started. Why has Oracle done this? I came up with the hypothesis a long time ago that there were two parts to the answer; the first is a privilege requirement, the second is for convenience.

SYS is a very special user (as many people have found when running SQL in the sys schema and finding that it doesn’t behave in exactly the same way it does in every other schema) and access to the dynamic performance views is one of the most exotic features of the SYS account. Underneath the respectable veneer of the dynamic performance (v$) views lies the murky horror that is the x$ layer where an “table” may turn out to be a function call, some form of memory array, a linked list, or a hideously complex structure that needs to be filtered, fragmented, reassembled and sorted to yield something that looks like a row containing useful information – an “object” may even be a function that reads an array of pointers to linked lists of hideously complex structures.

So the dynamic performance views hide nasty things and SYS is the only schema allowed to execute the Oracle kernel code to interpret and display those things. But the developers of tools like EM may find it highly desirable to get access the contents of the dynamic performance views – so Oracle creates “real” (i.e. stored) views in the SYS schema to expose the contents of the dynamic performance views and may grant select privileges on those stored views to ordinary users. Of course, since the views are created by SYS the stored queries defining the view contents operate with the privileges of SYS , which means the views can execute the kernel code and return the correct results.

Everyone happy so far?

So now a developer writes a really terrific query that they test very carefully against the dynamic performance view to make sure it’s producing the right results. And when it’s working they have to write a new version of the query using the names of the stored view rather than the names of the dynamic performance views because they also want to include their query in an Enterprise Manager / Grid Control monitor screen.

But why have two versions of a query when, by creating a suitable set of synonyms, a single version of the query will work. Choose your synonym names carefull and when the super query is run by SYS the parser will interpret names as direct references to the dynamic performance views and when it’s run by any other user (who’s had select granted on the stored views) it will translate names of synonyms to names of stored views and onwards to the names of dynamic perfermance views.

So: the stored views make it possible for non-SYS users to run SQL with the privileges of SYS; the cunning use of synonyms means the same SQL text can be run by SYS and non-SYS users and mean the same thing.

Lagniappe

There’s one dynamic performance view that breaks the general pattern, the one about sequences. Try running the following as SYS – the first triplet demonstrates the general pattern – the second doesn’t:

select 'dynamic' object_type, kqfvinam     object_name from x$kqfvi       where kqfvinam     like 'V%SQLAREA'
union all
select 'real view',           view_name                from dba_views     where view_name    like 'V%SQLAREA'
union all
select 'synonym',             synonym_name             from dba_synonyms  where synonym_name like 'V%SQLAREA';

select 'dynamic' object_type, kqfvinam     object_name from x$kqfvi       where kqfvinam     like 'V%SEQUENCES'
union all
select 'real view',           view_name                from dba_views     where view_name    like 'V%SEQUENCES'
union all
select 'synonym',             synonym_name             from dba_synonyms  where synonym_name like 'V%SEQUENCES';

The dynamic performance view v$_sequences doesn’t follow the pattern. There is no associated stored view, hence no possibility of a working synonym. (The dynamic performance view is also unusual in having an underscore after the v$, maybe that’s why some automatic code to generate the view and synonym didn’t work for it ;)

You could argue that v$_lock and v$_lock1 also break the pattern, but they’re just Oracle flailing around frantically but deliberately invisibly under v$lock, which does follow the pattern.

You might also point out that the one pair of dynamic performance views that start with GO$/O$ also breaks the pattern as the stored view and synonym change the ‘O’s to ‘V’s.

1 Comment »

  1. […] V$ cascade (Mar 2022): Why does is v$ a synonym for v_$ which is a view on v$ […]

    Pingback by Infrastructure Catalogue | Oracle Scratchpad — March 18, 2022 @ 6:09 pm GMT Mar 18,2022 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

%d bloggers like this: