jump to navigation

Looking inside v$ performance views August 5, 2009

Posted by mwidlake in internals, performance.
Tags: ,
trackback

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.

desc v$LOCKED_OBJECT
Name                                      Null?    Type
 ------------------------------------------------------
 XIDUSN                                             NUMBER
 XIDSLOT                                            NUMBER
 XIDSQN                                             NUMBER
 OBJECT_ID                                          NUMBER
 SESSION_ID                                         NUMBER
 ORACLE_USERNAME                                    VARCHAR2(30)
 OS_USER_NAME                                       VARCHAR2(30)
 PROCESS                                            VARCHAR2(12)
 LOCKED_MODE                                        NUMBER

OK, what is v$locked_object, is it a table, a view, a synonym…?

select owner,object_name,object_type
from dba_objects
where object_name ='V$LOCKED_OBJECT'

OWNER      OBJECT_NAME     OBJECT_TYPE
---------- --------------- ---------------
PUBLIC     V$LOCKED_OBJECT SYNONYM

It’s a synonym. Not a view, even though people refer to the v$ views or performance views.

What is it a synonym for?

select synonym_name,table_owner,table_name
from dba_synonyms
where synonym_name='V$LOCKED_OBJECT'

SYNONYM_NAME    TABLE_OWNER     TABLE_NAME
--------------- --------------- --------------------
V$LOCKED_OBJECT SYS             V_$LOCKED_OBJECT

OK, it’s a synonym on sys.v_$locked_object. What is that?

select owner,object_name,object_type
from dba_objects
where object_name ='V_$LOCKED_OBJECT'

OWNER      OBJECT_NAME      OBJECT_TYPE
---------- ---------------- ---------------
SYS        V_$LOCKED_OBJECT VIEW

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…

select owner,view_name,text
from dba_views
where view_name='V_$LOCKED_OBJECT'

OWNER           VIEW_NAME
--------------- --------------------
TEXT
--------------------------------------------------
SYS V_$LOCKED_OBJECT
select "XIDUSN","XIDSLOT","XIDSQN","OBJECT_ID","SE
SSION_ID","ORACLE_USERNAME","OS_USER_NAME","PROCES
S","LOCKED_MODE" from v$locked_object

Bingo! Its a simple view on top of v$locked_object.
Sorry? v$locked_object? That’s where we started.
v$locked_object is a synonym for
v_$locked_object that is a view on top of
v$locked_object.

This is the circular black hole I mentioned. Grrrr.

The solution is the afore mentioned v$fixed_view_definition.

desc v$fixed_view_definition
 Name                    Null?    Type
 ----------------------- -------- ----------------
 VIEW_NAME                        VARCHAR2(30)
 VIEW_DEFINITION                  VARCHAR2(4000)

select * from v$fixed_view_definition
where view_name = 'V$LOCKED_OBJECT'

VIEW_NAME
------------------------------
VIEW_DEFINITION
--------------------------------------------
V$LOCKED_OBJECT
select xidusn,xidslot,xidsqn,object_id,session_id,
oracle_username,os_user_name,process,locked_mode 
from gv$locked_object
where inst_id = USERENV('Instance')

It is a view on gv$locked_object. OK, let’s go look at that {anyone getting a bad feeling about this becoming another circular reference?}

select * from v$fixed_view_definition
where view_name = 'GV$LOCKED_OBJECT'

VIEW_NAME
------------------------------
VIEW_DEFINITION
----------------------------------------------
GV$LOCKED_OBJECT
select x.inst_id,x.kxidusn, x.kxidslt, x.kxidsqn, l.ktadmtab,
s.indx, s.ksuudlna, s.ksuseunm, s.ksusepid, l.ksqlkmod
from x$ktcxb x, x$ktadm l, x$ksuse s
where x.ktcxbxba = l.kssobown and x.ktcxbses = s.addr

Now that’s better. It is showing what that view is really looking at.

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.

About these ads

Comments»

1. Gary - August 6, 2009

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.

2. coskan - August 6, 2009

thank god !!
Finally someone wrote about it
I was thinking I am the only DBA in the village who entered what circular loop :)

3. B. Polarski - August 6, 2009

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$.

4. mwidlake - August 6, 2009

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}.

I , love it when I learn stuff from blogging :-)

5. Gary - August 7, 2009

“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.

mwidlake - August 7, 2009

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.


Leave a Reply

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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 159 other followers

%d bloggers like this: