Home » RDBMS Server » Performance Tuning » v$session output (oracle 10203 /AIX 5.3)
v$session output [message #396431] Mon, 06 April 2009 11:43 Go to next message
loveoracle
Messages: 41
Registered: February 2006
Location: Mumbai
Member

Dear All,

When I fired select for update from one session then I am getting differnt ROW_WAIT_OBJ# & ROW_WAIT_FILE#.
Can some one explain me why I am getting different objectid in v$session?


Below are the details for same:

From session one fired for update on table TESTCASE, whose object_id=32997.
If checked in v4locked_object it's showing me correct object_id.
When i looked into v$session to generate rowid it's showing me different objectid.

Session 1:  select * from TESTCASE where FOD_ORDR_RFRNC='200902241100000150' for update; 

Session 2: 

select owner,object_name,object_id,object_type from dba_objects where object_name='TESTCASE';

OWNER                          OBJECT_NAME                     OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ---------- -------------------
SYS                            TESTCASE		                   32997 TABLE

SQL>  select * from v$locked_object;

    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME                OS_USER_NAME                   PROCESS      LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------ -----------
        10          5      63030      32997       2190 SYS                            ora10g                         6582324                3

SQL>  select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# from v$session where sid=2565;

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
           37              1           61766             0


SQL> select object_name from dba_objects where object_id=37;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
I_OBJ2

SQL> select table_name,owner from dba_indexes where index_name='I_OBJ2';

TABLE_NAME                     OWNER
------------------------------ ------------------------------
OBJ$                           SYS



Thanks in advance.

Re: v$session output [message #396432 is a reply to message #396431] Mon, 06 April 2009 11:52 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
ROW_WAIT_OBJ# is NOT the same as OBJECT_ID!

You are comparing apple to brick.
Re: v$session output [message #396433 is a reply to message #396432] Mon, 06 April 2009 11:57 Go to previous messageGo to next message
loveoracle
Messages: 41
Registered: February 2006
Location: Mumbai
Member



Hi,

For generating roid we will take row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#.

Here I am getting different values. Whatever you are saying, please explain.
Re: v$session output [message #396434 is a reply to message #396431] Mon, 06 April 2009 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ROW_WAIT% columns are only valid if your session is waiting, check STATE column.

Regards
Michel
Re: v$session output [message #396435 is a reply to message #396434] Mon, 06 April 2009 12:12 Go to previous messageGo to next message
loveoracle
Messages: 41
Registered: February 2006
Location: Mumbai
Member

Yes, it's showing me WAITED SHORT TIME. 
Van you please explain meaning of "WAITED SHORT TIME".

       SID    SERIAL# STATUS   STATE
---------- ---------- -------- -------------------
      2293        185 ACTIVE   WAITED SHORT TIME

Re: v$session output [message #396440 is a reply to message #396435] Mon, 06 April 2009 12:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
WAITED SHORT TIME means it no more waits but it has waited.
When it is waiting it is WAITING.

Regards
Michel


Re: v$session output [message #396531 is a reply to message #396440] Tue, 07 April 2009 02:27 Go to previous messageGo to next message
loveoracle
Messages: 41
Registered: February 2006
Location: Mumbai
Member



From one session i am getting following output.

SQL> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,state from v$session where sid=2565;

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# STATE
------------- -------------- --------------- ------------- -------------------
           -1              0               0             0 WAITING


Here state is waiting, but showing ROW_WAIT_OBJ# is -1.


Re: v$session output [message #396538 is a reply to message #396531] Tue, 07 April 2009 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case, the information is not available.
Having WAITING does not mean ROW_WAIT% are filled. It is clearly stated in the documentation that row_wait_obj# can be -1 and in this case the other columns are meaningless.

Regards
Michel
Re: v$session output [message #396571 is a reply to message #396538] Tue, 07 April 2009 04:24 Go to previous messageGo to next message
loveoracle
Messages: 41
Registered: February 2006
Location: Mumbai
Member



Hi Michel,

Thanks for information. Can you please provide me the link of documentation.

Re: v$session output [message #396611 is a reply to message #396571] Tue, 07 April 2009 05:55 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Reference, V$SESSION

Regards
Michel

Previous Topic: Temp Tablespace usage
Next Topic: ORA-00604: error occurred at recursive SQL level x
Goto Forum:
  


Current Time: Thu Dec 08 14:12:21 CST 2016

Total time taken to generate the page: 0.28845 seconds