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 SolarWinds Database Performance Analyzer (DPA) 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.
Leave a Reply