Slow CREATE BITMAP INDEX and high consistent gets

From: Jeff Thomas <thomasjd_at_insightbb.com>
Date: Mon, 21 Aug 2006 20:37:47 -0400
Message-ID: <44EA51DB.20606@insightbb.com>

Any ideas as to why create bitmap index performance on a particular table would
essentially degrade over the weekend?

Example below. The first trace is on the original table. This create bitmap
used to take a few seconds on Friday, but started taking 10-14 minutes today.

I did a CTAS on this table to create my own copy and the same create bitmap only
takes a few seconds. I'm puzzled as to the high consistent gets on the WC_RCI_ORG_DX
table.

I'm considering rebuilding the table to see if that fixes the issue, but according to
the developers they did not make any changes to this table over the weekend.

In terms of extents, blocks, and bytes, the table sizes are almost identical.

create bitmap index WC_RCI_ORG_DX_B21 on SIEBEL.WC_RCI_ORG_DX(WORLMARK_ID) pctfree 1 nologging PARALLEL 8 TABLESPACE SIEBEL_DWBT_IDX

call     count       cpu    elapsed       disk      query    
current        rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.01       0.01          0          4          
3           0
Execute      1      0.13     771.18      28530    4762347       
1491           0
Fetch        0      0.00       0.00          0          0          
0           0

------- ------ -------- ---------- ---------- ---------- ----------

total        2      0.14     771.20      28530    4762351       
1494           0

create bitmap index siebel.jeff_B21 on SIEBEL.jeff(WORLMARK_ID) pctfree 1 nologging PARALLEL 8 TABLESPACE SIEBEL_DWBT_IDX

call     count       cpu    elapsed       disk      query    
current        rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.03       0.01          0          0          
0           0
Execute      1      0.14       9.70      27233      27979       
2577           0
Fetch        0      0.00       0.00          0          0          
0           0

------- ------ -------- ---------- ---------- ---------- ----------

total        2      0.17       9.71      27233      27979       
2577           0

Thanks,
Jeff
--

http://www.freelists.org/webpage/oracle-l Received on Mon Aug 21 2006 - 19:37:47 CDT