V$SESSION’s ROW_WAIT_OBJ# Column Shows the DATA_OBJECT_ID – Verify the Documentation Challenge

7 06 2011

June 7, 2011

While reading through the alpha copy of a performance tuning book, I noticed a set of SQL statements that were identical to those found in the Oracle Database 11.2 Performance Tuning Guide.  The set of SQL statements from the Performance Tuning Guide follow:

10.3.1.1 Causes
To determine the possible causes, first query V$SESSION to identify the value of ROW_WAIT_OBJ# when the session waits for buffer busy waits. For example:

SELECT row_wait_obj#
  FROM V$SESSION
 WHERE EVENT = 'buffer busy waits';

To identify the object and object type contended for, query DBA_OBJECTS using the value for ROW_WAIT_OBJ# that is returned from V$SESSION. For example:

SELECT owner, object_name, subobject_name, object_type
  FROM DBA_OBJECTS
 WHERE data_object_id = &row_wait_obj;

In an earlier article we found that V$SESSION’s ROW_WAIT_OBJ# column indicated the OBJECT_ID, not the DATA_OBJECT_ID of the object (as found in DBA_OBJECTS) and the same was true for the obj# entry found in a 10046 trace file.  When you first create an index the OBJECT_ID and the DATA_OBJECT_ID values will be the same for the index, as displayed in DBA_OBJECTS.  However, when you rebuild the index the DATA_OBJECT_ID value for the index will change while the OBJECT_ID value remains unchanged.  Likewise, when you first create a table the OBJECT_ID and the DATA_OBJECT_ID values will be the same, as displayed in DBA_OBJECTS.  If you TRUNCATE a table, the OBJECT_ID value will remain the same while the DATA_OBJECT_ID value for the table will change.

The challenge is to build a test case that either confirms or refutes the claim made by the Oracle Database Performance Tuning Guide.  Possible references:

  • AskTom thread
  • Metalink (MOS) ID 15476.1 “FAQ about Detecting and Resolving Locking Conflicts”
  • Metalink (MOS) Doc ID 603661.1 “ORA-01410: Invalid Rowid When Using rowid Generated from V$Session and Dba_objects”
  • Another article found on this blog (take a look at the Other Resources links)

« »


Actions

Information

10 responses

8 06 2011
Flado (05:12:43) :

Modifying my testcase from your earlier post https://hoopercharles.wordpress.com/2010/06/03/lock-watching-what-is-wrong-with-this-sql-statement/
This

select version from v$instance;
drop table t;
create table t (id)
partition by list (id)
(partition tid1 values(1), partition tjunk values (default))
as select 1 from dual;
alter table t move partition tid1;
 select id from t where id=1 for update;

pause try to lock the same row in a different session here
with lo as (select /*+ materialize */ sid, row_wait_obj# from v$session where event like 'enq%')
select lo.sid, do.object_name, do.object_type, do.object_id, do.data_object_id, lo.row_wait_obj# 
from dba_objects do, lo
where do.object_id=lo.row_wait_obj#
   or do.data_object_id=lo.row_wait_obj#;

produces this:

VERSION           
----------------- 
11.2.0.2.0        

 drop table t succeeded.
create table succeeded.
 alter table t succeeded.
ID                     
---------------------- 
1                      

SID                    OBJECT_NAME  OBJECT_TYPE         OBJECT_ID              DATA_OBJECT_ID         ROW_WAIT_OBJ#          
---------------------- ------------ ------------------- ---------------------- ---------------------- ---------------------- 
360                    T            TABLE PARTITION     61758                  61760                  61758
Reply
8 06 2011
Charles Hooper (06:22:09) :

Flado,

Thanks for the example. However, for this specific article I was curious as to whether someone could demonstrate whether or not the presence of a “buffer busy waits” wait event indicates that the ROW_WAIT_OBJ# column shows the DATA_OBJECT_ID rather than the OBJECT_ID, as indicated in the Oracle Database Performance Tuning Guide and the alpha copy of the book. Your example, did however, cause me to scroll down a bit further in the Performance Tuning Guide (and flip to part 6-6 of the alpha copy of the book). That part of the Performance Tuning Guide:
http://download.oracle.com/docs/cd/E14072_01/server.112/e10821/instance_tune.htm#PFGRF94484
Includes the following set of SQL statements:

To determine the possible causes, first query V$SESSION to identify the value of ROW_WAIT_OBJ# when the session waits for db file scattered read. For example:

SELECT row_wait_obj# 
  FROM V$SESSION 
 WHERE EVENT = 'db file scattered read';

To identify the object and object type contended for, query DBA_OBJECTS using the value for ROW_WAIT_OBJ# that is returned from V$SESSION. For example:

SELECT owner, object_name, subobject_name, object_type
  FROM DBA_OBJECTS
 WHERE data_object_id = &row_wait_obj;

The alpha copy of the book performs the following join, where DBA_OBJECTS is aliased as “b” and V$SESSION is aliased as “a” (side note: I think that it is a poor choice that the book authors made when selecting to use these meaningless aliases):

b.data_object_id = a.row_wait_obj#;

Since we are headed on a bit of a tangent, let’s continue a bit further. Start up two SQL*Plus sessions. In session 1 we will execute the following, which creates table T1, truncates the table, returns the OBJECT_ID and DATA_OBJECT_ID, and then returns the SID for session 1:

DROP TABLE T1 PURGE;
 
CREATE TABLE T1 NOLOGGING AS
SELECT
  *
FROM
  DBA_OBJECTS;
 
TRUNCATE TABLE T1;
 
SELECT
  OBJECT_ID,
  DATA_OBJECT_ID
FROM
  DBA_OBJECTS
WHERE
  OWNER=USER
  AND OBJECT_NAME='T1';
 
SELECT
  SID
FROM
  V$MYSTAT
WHERE
  ROWNUM=1;

The output of the above follows:

SQL> SELECT
  2    OBJECT_ID,
  3    DATA_OBJECT_ID
  4  FROM
  5    DBA_OBJECTS
  6  WHERE
  7    OWNER=USER
  8    AND OBJECT_NAME='T1';
 
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     90125          90126
 
SQL> SELECT
  2    SID
  3  FROM
  4    V$MYSTAT
  5  WHERE
  6    ROWNUM=1;
 
       SID
----------
        68

In session 2 we will prepare to execute the following SQL statement:

SELECT
  EVENT,
  ROW_WAIT_OBJ# 
FROM
  V$SESSION 
WHERE
  SID=68;

In session 1, let’s insert 500 copies of the data found in DBA_OBJECTS into table T1:

INSERT INTO
  T1 NOLOGGING
SELECT
  DO.*
FROM
  (SELECT * FROM DBA_OBJECTS) DO,
  (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <=500) D;

In session 2, repeatedly execute the SQL statement that returns the ROW_WAIT_OBJ# and EVENT from V$SESSION. This is the output I received (after eliminating a lot of duplicates):

SQL> SELECT
  2    EVENT,
  3    ROW_WAIT_OBJ#
  4  FROM
  5    V$SESSION
  6  WHERE
  7    SID=68;
 
EVENT                               ROW_WAIT_OBJ#
----------------------------------- -------------
SQL*Net message from client                 90125
 
SQL> COLUMN EVENT FORMAT A35
SQL> /
 
EVENT                               ROW_WAIT_OBJ#
----------------------------------- -------------
SQL*Net message from client                 90125
 
SQL> /
 
EVENT                               ROW_WAIT_OBJ#
----------------------------------- -------------
db file sequential read                     90125
 
SQL> /
 
EVENT                               ROW_WAIT_OBJ#
----------------------------------- -------------
db file sequential read                     90125
 
SQL> /
 
EVENT                               ROW_WAIT_OBJ#
----------------------------------- -------------
db file sequential read                     90125
 
SQL> /
 
EVENT                               ROW_WAIT_OBJ#
----------------------------------- -------------
db file sequential read                     90125
 
SQL> /
 
EVENT                               ROW_WAIT_OBJ#
----------------------------------- -------------
log buffer space                            90125
 
SQL> /
 
EVENT                               ROW_WAIT_OBJ#
----------------------------------- -------------
log buffer space                            90125
 
SQL> /
 
EVENT                               ROW_WAIT_OBJ#
----------------------------------- -------------
log buffer space                            90125
 
SQL> /
 
EVENT                               ROW_WAIT_OBJ#
----------------------------------- -------------
log file switch completion                  90125
 
SQL> /
 
EVENT                               ROW_WAIT_OBJ#
----------------------------------- -------------
log file switch completion                  90125
 
SQL> /
 
EVENT                               ROW_WAIT_OBJ#
----------------------------------- -------------
log file switch completion                  90125
 
SQL> /
 
EVENT                               ROW_WAIT_OBJ#
----------------------------------- -------------
log file switch completion                  90125
 
SQL> /
 
EVENT                               ROW_WAIT_OBJ#
----------------------------------- -------------
SQL*Net message from client                 90125

All of the above wait event entries are reporting 90125 for the ROW_WAIT_OBJ#, which as shown above is the OBJECT_ID. Let’s continue in session 1:

ALTER SYSTEM FLUSH BUFFER_CACHE;
SET AUTOTRACE TRACEONLY STATISTICS
SET ARRAYSIZE 1000
 
SELECT
  COUNT(*)
FROM
  T1;

Back in session 2, again checking to see the EVENT and ROW_WAIT_OBJ# for session 1 (again removing many duplicates):

SQL> /
 
EVENT                               ROW_WAIT_OBJ#
----------------------------------- -------------
direct path read                            90125
 
SQL> /
 
EVENT                               ROW_WAIT_OBJ#
----------------------------------- -------------
direct path read                            90125
 
SQL> /
 
EVENT                               ROW_WAIT_OBJ#
----------------------------------- -------------
direct path read                            90125
 
SQL> /
 
EVENT                               ROW_WAIT_OBJ#
----------------------------------- -------------
direct path read                            90125
 
SQL> /
 
EVENT                               ROW_WAIT_OBJ#
----------------------------------- -------------
direct path read                            90125
 
SQL> /
 
EVENT                               ROW_WAIT_OBJ#
----------------------------------- -------------
SQL*Net message from client                 90125

The number 90125 still appears, rather than the DATA_OBJECT_ID value 90126.

We still need to determine if the buffer busy waits wait event places the DATA_OBJECT_ID or the OBJECT_ID into the ROW_WAIT_OBJ# column of V$SESSION – is that wait event special in that it appears to be the only wait event other than db file scattered read that shows the DATA_OBJECT_ID in the ROW_WAIT_OBJ# column (oh wait, I think that I just demonstrated that the documentation is wrong about the db file scattered read wait event)?

Reply
8 06 2011
Flado (11:04:06) :

Oops – sorry, I missed the “buffer busy waits” part completely 😦
Let me completely change my misguided first comment: I didn’t know that ROW_WAIT_OBJ# was useful for anything other than enqueue waits – thanks a lot for that!
Now that I skimmed that chapter (10) of the 11.2 Performance Tuning guide, I must note that – technically – you didn’t demonstrate the documentation wrong about the db file scattered read wait event, just wrong about the “db file sequential read” when saying the segment in question may be identified by the same method as with “db file scattered read”. Even if the method works for “db file scattered read”, it obviously doesn’t for “db file sequential read” (as you demonstrated) 😉

As for a “buffer busy waits” testcase I’d just assign a biggish table to a smallish recycle pool, submit two jobs doing full scans for a while, and then do your high-frequency sampling. Of course, that would only give me “read by other session” waits. Hmm… Tricky. I finally see the point of this post 🙂

Reply
8 06 2011
Charles Hooper (12:42:45) :

Flado,

Quite correct, and if someone tried the test case that I posted using Oracle 10g R2 the “direct path read” wait event would be replaced with “db file scattered read”, and might be wondering why their results differed from mine. I should have mentioned that the test case I provided above was executed on 11.2.0.2 (or embedded a 😉 in my last sentence).

I like your last two sentences – the underlying secret for a number of my blog articles is that what seems simple on the surface is anything but simple when you start digging into the details. The alpha copy of the 11g book that I am reading seems to have missed that point about buffer busy waits (regarding the new in 10.1 wait event that you mentioned), and as of section 5-7 is only just scratching the surface of the book topics.

Sometimes it is helpful to drift off in a tangent thought as we did here – it helps me recall related facts.

Reply
8 06 2011
Jonathan Lewis (11:14:43) :

Charles,

Using my standard “kill_cpu” table:

create table kill_cpu(n, primary key(n))
organization index
as 
select rownum n
from all_objects 
where rownum <= 23 
;

alter table kill_cpu move;
execute dbms_stats.gather_table_stats(user,'kill_cpu')

Session 1
=========
SQL> l
  1  begin
  2     for i in 1..50000 loop
  3             update /*+ index(kill_cpu) */ kill_cpu set n = 1 where n = 1;
  4             commit;
  5     end loop;
  6* end;
SQL> /

Session 2
=========
SQL> l
  1  begin
  2     for i in 1..50000 loop
  3             update /*+ index(kill_cpu) */ kill_cpu set n = 2 where n = 2;
  4             commit;
  5     end loop;
  6* end;
SQL> /

From another session:


SQL> select object_id, data_object_id , object_name from dba_objects
  2  where owner = 'TEST_USER';

 OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- -------------- --------------------
     82596                KILL_CPU
     82597          82598 SYS_IOT_TOP_82596

2 rows selected.

SQL> select state, event, row_wait_obj#
  2  from v$session
  3  where username = 'TEST_USER'
  4  /

STATE                EVENT                        ROW_WAIT_OBJ#
-------------------- ---------------------------- -------------
WAITED SHORT TIME    buffer busy waits                       -1
WAITED SHORT TIME    buffer busy waits                       -1

2 rows selected.

SQL> /

STATE                EVENT                        ROW_WAIT_OBJ#
-------------------- ---------------------------- -------------
WAITING              buffer busy waits                    82597
WAITED SHORT TIME    buffer busy waits                       -1

2 rows selected.

SQL> /

STATE                EVENT                        ROW_WAIT_OBJ#
-------------------- ---------------------------- -------------
WAITED SHORT TIME    latch: cache buffers chains          82597
WAITING              buffer busy waits                    82597

This was 10.2.0.3 – and it’s the object_id.

Reply
8 06 2011
Charles Hooper (12:31:38) :

Jonathan,

Thank you for providing the test case. I was having a bit of difficulty putting together a script that would reliably generate buffer busy waits, and had not yet tried PL/SQL loops.

It appears that the output you provided demonstrates that the Performance Tuning Guide (and the alpha copy of the book that I am reading) needs to have a correction or two. Someone might also want to review the following Metalink (MOS) article and fix the broken logic: Doc ID 102925.1, “Tracing sessions: waiting on an enqueue”

Reply
8 06 2011
Paresh (15:55:13) :

This is a big surprise as in all recent sources (Oracle docs, web, books etc.) I have read that ROW_WAIT_OBJ# = DATA_OBJECT_ID no matter what (The sources didn’t explicitly say so but by not debating what you are I think they implied it). This bring us to my question – How did you suspect this to be the case? What motivated/lead you to challenge this notion and discover the ‘anomaly’? Was it just a coincidence?

Thanks,
Paresh

Reply
8 06 2011
Charles Hooper (18:51:44) :

Paresh,

Good question. Regarding the DATA_OBJECT_ID and OBJECT_ID columns, I recall seeing a comment from Jonathan Lewis a couple of years ago regarding a query that he had seen that selected the OBJ column from SYS.X$BH – that same column is exposed as OBJD in V$BH. He stated something to the effect that the X$BH.OBJ column should be joined to the DATA_OBJECT_ID, and not the OBJECT_ID column when drilling into DBA_OBJECTS. It was that comment several years ago that started to make me suspicious whenever I saw a query using either the DATA_OBJECT_ID or OBJECT_ID columns.

Several years ago I started building a performance tuning tool (or toy: https://hoopercharles.wordpress.com/2009/11/30/toy-project-for-performance-tuning/ ) to help troubleshoot problems that I encountered. I would occasionally search through Metalink looking for helpful performance related articles (see: https://hoopercharles.wordpress.com/2009/12/21/miscellaneous-metalink-performance-articles/ https://hoopercharles.wordpress.com/2009/12/24/miscellaneous-metalink-performance-articles-2/ ) looking for helpful scripts, as well as working through the Performance Tuning Guide for 8i and 10g R2. I recall getting burned by one of the scripts in Metalink – this script should be returning an object name, but it returns nothing. Getting frustrated, I changed DATA_OBJECT_ID to OBJECT_ID and the script magically worked. 🙂

I hit the same problem with the Performance Tuning Guide. In the source code for my performance tuning tool I have the following comment embedded into a SQL statement:

/* Needs to be OBJECT_ID - confirmed 3/17/2008 */
SELECT
  OWNER,
  OBJECT_NAME,
  OBJECT_TYPE
FROM
  DBA_OBJECTS
WHERE
  OBJECT_ID= :B1
/* Should be data_object_id per the performance tuning guide
  DATA_OBJECT_ID= :B1 */

The above SQL statement was intended to investigate the waits found in V$SESSION_WAIT. So, the last time I investigated that SQL statement in the program was in March of 2008 – and the Performance Tuning Guide for 11.2 is still wrong. 🙂

The article linked to at the start of this article shows that I rediscovered the problem in the Performance Tuning Guide in September of 2010:
https://hoopercharles.wordpress.com/2010/09/08/something-is-bugging-me-vsession-and-10046-trace-files-show-object_id-or-data_object_id/

The short answer is: if something does not look exactly right, investigate it – you might surprise yourself with the results.

Reply
12 06 2011
pachot (16:01:10) :

Hi,

Until now I’ve always seen ‘OBJ#’ (and ‘OBJN’) referring to the OBJECT_ID ,and the DATA_OBJECT_ID being referred by names such as DATAOBJ#, OBJD, OBJ, OBJECT_NUMBER.
I’ve seen that empiric rule being true in dictionary tables, dictionary views, trace, dumps, dbms_xxx packages … but I would be glad to know any exception to that 🙂

Event if documentation is not clear about that, I’m sure that in V$SESSIONS, ROW_WAIT_xxx# are referring to logical objects – segments – and absolute files: ROW_WAIT_OBJ# is the OBJECT_ID, and ROW_WAIT_FILE# is the absolute file number.
If you need to have the corresponding ROWID you need to do the OBJECT_ID->DATA_OBJECT_ID conversion from V$OBJECT and the FILE_ID->RELATIVE_FNO from DBA_DATA_FILES

Unfortunately, OBJECT_ID=DATA_OBJECT_ID at object creation and FILE_ID=RELATIVE_FNO at file creation. I suppose it is for compatibility reason when Oracle introduced them.
But a lot of scripts that do not take care of the distinction seems to work … until objects are moved or truncated, or tablespaces are transported.

Besides the names (‘OBJ#’ and’ ‘OBJN’ for the OBJECT_ID and DATAOBJ#, OBJD, OBJ, OBJECT_NUMBER for the DATA_OBJECT_ID), we can guess the meaning by checking if there are other columns that can help to identify uniquely the object:
Having a DATA_OBJECT_ID is not sufficient to identify the object because 2 different objects can have the same data object id. The DATA_OBJECT_ID is unique only within the same object. So when we see an object id without another way to identify the object, then we can be sure that it is the OBJECT_ID or it would be useless.
In the same way, having a relative file number without a way to identify the tablespace, is useless. So when we see a file id without an identifier for the tablespace, then it must be the absolute file id.

ROW_WAIT_OBJ# cannot be the DATA_OBJECT_ID because there is no other column in V$SESSION that helps to distinguish 2 objects that have the same DATA_OBJECT_ID. In addition, see bug 1646533 that was closed as ‘not a bug’ for this reason.

Regards,
Franck.

Reply
13 06 2011
Charles Hooper (08:03:26) :

Franck,

I appreciate the lengthy description that you provided above.

It appears that you wrote an article a while ago that *almost* answered the question about V$SESSION’s ROW_WAIT_OBJ# column (you described that the OBJECT_ID is found in several other views, but not V$SESSION). I thought that I would link to the article here, because on quick glance it appears to be helpful:
http://knol.google.com/k/oracle-table-lock-modes#

Reply

Leave a reply to Charles Hooper Cancel reply