(This guest blog entry contributed by Dallas D., a long time Ignite user and expert DBA)
I actually had occasion to use the "top objects" view today, and it was very handy! I had a insert that was experiencing massive buffer busy waits (same insert running across 32 threads, doh!). I drilled into bbw, and there were of course tons of blocks listed. I had looked at the table and index structures, and determined that there was certainly contention on the primary key, but it looked like another index with different columns might have contention on it as well. I ran a couple of the "block number between block_id and block_id + blocks -1" queries and confirmed the one index (the pk index on the ID column, sys_c0011109), then remembered the top objects view. It confirmed the pk index was getting the bulk of the bbw by far, and also confirmed that the enq: TX - index contention was also on the pk index and not on the second index. Very cool and great time-saver!
My original (confirmed) thought was that there was definitely pk contention from 32 processes inserting where the ID number increased sequentially. After looking a some of the data, I thought there may be contention on index1 as well - but Ignite didn't indicate that so I can ignore it for the time being.
some table/index background and screen shots.
SQL> desc fcsiprd.work_item
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
WORK_INSTANCE_ID NOT NULL NUMBER
SEQUENCE NOT NULL NUMBER
UUID VARCHAR2(36)
RAW_DATA VARCHAR2(4000)
XML_DATA NOT NULL FCSIPRD.XMLTYPE
CREATE_TS TIMESTAMP(6)
UPDATE_TS TIMESTAMP(6)
select index_name,column_name,column_position
from dba_ind_columns
where table_name = 'WORK_ITEM';
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ------------------------------
INDEX1 WORK_INSTANCE_ID 1
INDEX1 SEQUENCE 2
INDEX1 CREATE_TS 3
SYS_C0011109 ID 1
SQL> select id,WORK_INSTANCE_ID,sequence,create_ts
2 from fcsiprd.work_item
3 where rownum < 25;
ID WORK_INSTANCE_ID SEQUENCE CREATE_TS
---------- ---------------------------- ---------------- -----------------
17 58 428483 28-OCT-08 06.55.24.020000 PM
18 58 428484 28-OCT-08 06.55.24.020000 PM
19 58 428485 28-OCT-08 06.55.24.020000 PM
20 58 428486 28-OCT-08 06.55.24.020000 PM
21 58 428487 28-OCT-08 06.55.24.020000 PM
22 58 428488 28-OCT-08 06.55.24.020000 PM
23 58 428489 28-OCT-08 06.55.24.020000 PM
24 58 428490 28-OCT-08 06.55.24.020000 PM
25 58 428491 28-OCT-08 06.55.24.020000 PM
26 58 428492 28-OCT-08 06.55.24.020000 PM
27 58 428493 28-OCT-08 06.55.24.020000 PM
ID WORK_INSTANCE_ID SEQUENCE CREATE_TS
---------- ------------------------------ ---------- ------------------------
28 58 428494 28-OCT-08 06.55.24.020000 PM
29 58 428495 28-OCT-08 06.55.24.020000 PM
30 58 428496 28-OCT-08 06.55.24.020000 PM
31 58 428497 28-OCT-08 06.55.24.020000 PM
32 58 428498 28-OCT-08 06.55.24.020000 PM
33 58 428499 28-OCT-08 06.55.24.020000 PM
Most of the contention is on the PK index - sys_c0011109.
