Looking inside v$ performance views August 5, 2009

I keep forgetting how to look inside the V$ performance objects, I’ve got a mental block on it. I had to ask a colleague again this week how to do it. So I’m blogging it. This should fix it in my mind.

You use the V$FIXED_VIEW_DEFINITION dynamic view to seem them. You have to use this as the damned things hide in a circular data dictionary black hole.

Here is an example. I want to know what the v$locked_object is actually looking at.

It’s a view. Good, so what is commonly called a view IS a view, hidden by a synonym. What is also good is that we have been here before, I posted about looking inside data dictionary viewsabout a month ago. You can just extract the SQL of the view…

I don’t know why the v$xxxx synonym to v_$xxxx view on v$xxxx synonym circular reference exists but I suspect it is something to do with allowing Oracle’s internal code to realise it has to look elsewhere to resolve the v$ objects, ie as being built on top of the x$ objects – which are themselves a way of exposing C objects {I think they are C objects} on memory structures… I’ll stop now as I am in deeper than I can swim. Maybe someone more adept with oracle internals has worked this out and blogged about it but I have not found it yet {maybe Jonathan Lewis has, he started mentioning the x$ and v$ views back in Oracle 7! Or René Nyffenegger, who’s pages on the v$ and x$ stuff I find very useful}

Sadly, you can’t look at the x$ objects at all unless you are logged on as SYS. If you have access to the SYS user on your company systems you should know enough to not go poking around on such things on production systems. Install Oracle on your PC and poke around on there. It can be addictive though.

I think I’ll leave it there for tonight.

Like this:

LikeLoading...

Related

Not technically a circular reference. The dependency from V_$LOCKED_OBJECT is on the SYS.V$LOCKED_OBJECT view not the PUBLIC.V$LOCKED_OBJECT synonym. Its just that there isn’t an object for SYS.V$LOCKED_OBJECT in the dba_objects view.
In dba_dependencies, it actually looks at sys.disk_and_fixed_objects which has separate entries for the PUBLIC and SYS V$LOCKED_OBJECT.

Hang on to your sockets with a firm grip for in 11g the views contains context variables that may hold (or not) additional parts of the view code. This mechanism enable different RAC instances to run different data dictionary versions. On the readability side, life will just become a bit more harder for those who like digging behind V$.

Hi Gary
Hey, you are right! I never twigged that the synoym would be a public one and thus technically not in the SYS schema. Thanks for the post {and sorry for the delay, I have to approve the first comment any person ever makes}.
But then, am I still right? Oracle will look at the reference v$locked_object, look for an object in the SYS name space, not find one so look for a private synonym, not find one and then look for a public synonym…and find one.
The looking at DBA_DEPENDENCIES is nice, I’ll have to go and check that {need to start the day job now}.

Coskan – glad to open this can of worms for you, I too have wondered why it not mentioned much. People just say “The v$ views are built on the x$ views” usually and leave it at that. Then you go and look yourself and get stuck.

Bernard – Thanks for the tip, I will get hold of some sockets to grip and dive into my Oracle 11 test box {only got 10 on-site at present}.

“Oracle will look at the reference v$locked_object, look for an object in the SYS name space, not find one ” Not quite. There is a sys owned object in sys.disk_and_fixed_objects. It just isn’t visible in DBA_OBJECTS or the underlying sys.obj$. In fact, sys.disk_and_fixed_objects is a union of sys.obj$ and three x$ views. so it can be the missing link where the conventional database objects meet the x$ objects.

Thanks Gary,
I need to go and look at DBA_DEPENDENCIES, sys.disk_and_fixed_objects and what DBA_OBJECTS is really looking at don’t I? I did not get time to follow up your input yesterday, but tonight or this weekend I will.

You’ve briefly mentioned where the x$ fixed tables get their data – I think you would be interested in chapter 78 of “Reverse Engineering for Beginners” (http://beginners.re/), the chapter digs into what happens behind the scenes for the x$ fixed tables.

Cheers

(I’ve just come across your post via your comment today on Martin Bach’s blog – Excuse the comment on such an old post!)