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:
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: