Notice that the documentation shows that the value from the ROW_WAIT_OBJ# column in V$SESSION is used to drive into DBA_OBJECTS using the DATA_OBJECT_ID column to determine the OBJECT_NAME and SUBOBJECT_NAME of the object involved in the wait event. At this point you might be thinking that it is also the DATA_OBJECT_ID that appears in the WAIT lines found in a 10046 trace file, and that would be a logical conclusion. Let’s see if we are able to find a little clarification from the documentation about the OBJECT_ID and DATA_OBJECT_ID columns:

“OBJECT_ID: Dictionary object number of the objectDATA_OBJECT_ID: Dictionary object number of the segment that contains the object

Note: OBJECT_ID and DATA_OBJECT_ID display data dictionary metadata. Do not confuse these numbers with the unique 16-byte object identifier (object ID) that Oracle Database assigns to row objects in object tables in the system.”

Well, that almost helps a little, DATA_OBJECT_ID relates to the segments that compose the object. More information may be found here.

A quick check of Metalink (MOS) finds the helpful Metalink Doc ID 39817.1 “Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE Output” that describes the contents of a 10046 trace file, but the document completely avoids describing the meaning of the obj# parameter in the WAIT lines of the trace file. Another Metalink document found by searching on the keyword DATA_OBJECT_ID also seemed to be a bit unclear about V$SESSION and DATA_OBJECT_ID/OBJECT_ID (to put it nicely).

So, do the waits events in V$SESSION and in 10046 trace files show the OBJECT_ID or the DATA_OBJECT_ID? What we need is a test case to see for ourselves. We will create a simple table with a primary key index, add a secondary index, insert 100,000 rows into the table, and then collect table and index statistics:

At this point the OBJECT_ID and DATA_OBJECT_ID columns contain identical values. So, let’s flush the buffer cache to force physical reads, enable a 10046 trace at level 12, and then select from the test table:

One of Jonthan Lewis’ blog articles shows that rebuilding an index will cause its OBJECT_ID and DATA_OBJECT_ID columns to deviate from each other. So, let’s rebuild the table’s indexes and see what happens to the OBJECT_ID and DATA_OBJECT_ID columns:

In the above, we still see the OBJECT_ID value, not the DATA_OBJECT_ID value. So, the quote from the Oracle Performance Tuning Guide does not agree with the trace file tests when we use the primary key index. Let’s try again using the secondary index:

The above trace file section again shows the OBJECT_ID value, not the DATA_OBJECT_ID value. Maybe the single block reads just do not follow the pattern shown in the documentation – the documentation example showed a “db file scattered read wait” event. Let’s change the DATA_OBJECT_ID value for the table by truncating the table and re-insert the 100,000 rows so that we will be able to test a multi-block read wait event:

The above trace file section again shows the OBJECT_ID value, not the DATA_OBJECT_ID value – even for the “db file scattered read” wait event. The documentation reference must not apply to 10046 trace files.

Now let’s test V$SESSION – the view mentioned in the Oracle Performance Tuning Guide. We will need 2 sessions for this test. First, let’s insert 1,000,000 rows into the test table so that it takes a little longer to select the rows from the table (allowing us to check on the session’s wait events using a second session), and then determine the SID for the first session:

Notice in the above that V$SESSION shows the OBJECT_ID for table T10, not the DATA_OBJECT_ID as described in the 11.2.0.1 Performance Tuning Guide. Maybe it is just the “db file sequential read” and “direct path read” wait events (obviously, this test was performed in 11g or above due to the “direct path read” wait events, specifically 11.1.0.7) that show the OBJECT_ID – the example from the Performance Tuning Guide showed a lookup that involved a “db file scattered read” wait event. Let’s see if that wait event is handled differently in V$SESSION than the “db file sequential read” and “direct path read” wait events. We will need to set event 10949 in the session to disable the direct path reads for the full table scan:

We are still seeing the OBJECT_ID of table T10, and not the DATA_OBJECT_ID in the query of V$SESSION. Just to make certain that nothing strange happened where the DATA_OBJECT_ID reverted back to the OBJECT_ID value:

Is this the third documentation bug that was mentioned in articles of this blog (bug number 2 was identified by Chris Antognini just a couple of days ago)? Maybe things change with partitioned objects? What better way to determine what happens than to set up a test case?

CREATE TABLE T11
PARTITION BY RANGE(C1)
(PARTITION P1 VALUES LESS THAN (5),
PARTITION P2 VALUES LESS THAN (10),
PARTITION P3 VALUES LESS THAN (20),
PARTITION P4 VALUES LESS THAN (40),
PARTITION P5 VALUES LESS THAN (80),
PARTITION P6 VALUES LESS THAN (160),
PARTITION P7 VALUES LESS THAN (320),
PARTITION P8 VALUES LESS THAN (640),
PARTITION P9 VALUES LESS THAN (1280),
PARTITION P10 VALUES LESS THAN (2560),
PARTITION P11 VALUES LESS THAN (5120),
PARTITION P12 VALUES LESS THAN (10240),
PARTITION P20 VALUES LESS THAN (MAXVALUE))
AS
SELECT
ROWNUM C1,
ROWNUM C2,
LPAD('A',100,'A') C3
FROM
DUAL
CONNECT BY
LEVEL<=1000000;
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T11',CASCADE=>TRUE)

Let’s take a look at the OBJECT_IDs and DATA_OBJECT_IDs for this table (note that I have switched from Oracle Database 11.1.0.7 to 11.2.0.1 running on a different server):

The 10046 trace files shows the OBJECT_ID column of DBA_OBJECTS, not the DATA_OBJECT_ID column as seen in the tests that used both unpartitioned and partitioned tables. This documentation bug has been bugging me for at least four and a half years, but probably longer than that – I just thought it was worth briefly mentioning the problem.

Your comment pretty well matches what I found when I started working with 10.2 in late 2005/early 2006. However, it seems that I have forgotten this fact once or twice, so I thought it best to document it in my Oracle Notes.

It is typically a lot faster (and less resource intensive) to use the OBJ# directly from the trace file to determine the object name, than it is to use the (named in 10g) P1, P2, and P3 parameters to find the object name. The 10g R2 Performance Tuning Guide, however, caused me a bit of confusion initially when I was trying to work with the OBJ# parameters found in 10046 trace files. I kept wondering what caused some lookups using the DATA_OBJECT_ID rather than the OBJECT_ID to work, while others failed – when I read Jonathan Lewis’ blog article (mentioned above) the cause was suddenly quite obvious.

But rather than relying on that is is a good idea to test all queries with a moved table, in order to have different ID, (and move it into a transported tablespace in order to avoid another confusion between relative and absolute file numbers…)

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: