Top Objects finds PK Index in Oracle

By Staff Contributor 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
 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;
 ———-  —————————-     —————-         —————–
 17               58                      428483                  28-OCT-08 PM
 18               58                      428484                  28-OCT-08 PM
 19               58                      428485                 28-OCT-08 PM
 20               58                      428486                 28-OCT-08 PM
 21               58                      428487                 28-OCT-08 PM
 22               58                      428488                 28-OCT-08 PM
 23               58                      428489                  28-OCT-08 PM
 24               58                      428490                  28-OCT-08 PM
 25               58                      428491                  28-OCT-08 PM
 26               58                       428492                 28-OCT-08 PM
 27               58                      428493                  28-OCT-08 PM
 ———-    ——————————     ———-              ————————
 28               58                            428494               28-OCT-08 PM
 29               58                            428495               28-OCT-08 PM
 30               58                            428496               28-OCT-08 PM
 31               58                            428497               28-OCT-08 PM
 32               58                            428498               28-OCT-08 PM
 33               58                           428499                28-OCT-08 PM

Most of the contention is on the PK index – sys_c0011109.

Related Posts

Leave a Reply