Top Objects finds PK Index in Oracle

By SolarWinds on December 21, 2012


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.

Related Posts

Leave a Reply