Re: Slow CREATE BITMAP INDEX and high consistent gets

Jeff,

You did rebuild the table (when you did the CTAS) and it made a huge difference, so that's a clue right there.

High consistent gets... is there a transaction open on this table while you're creating the index? The "raw" trace file should show wait-events on "db file sequential read" against the UNDO tablespace, interspersed amongst the wait-events on "db file scattered read" on the table's tablespace. Query DBA_OBJECTS to get the OBJECT_ID for the table, then query V$LOCK where TYPE = 'TM' and where ID1 = <object-ID>, and see if any sessions show up? Although that might be a rat-hole, because Oracle should kick back ORA-00054 ("resource busy and acquire with NOWAIT specified") if there's an uncommitted transaction open. Still, can't hurt to check anyway...

Could delayed block cleanout from a massive UPDATE or DELETE be causing high LIO? Perhaps someone else can confirm, but I think the count for "current" (a.k.a. "db block changes") should be pretty high if that were happening. However, it also sounds like you've done the CREATE BITMAP INDEX several times, and delayed block cleanout probably wouldn't repeat like that.

When you did the CTAS, did you recreate the table copy in the same tablespace as the original? That's grasping at straws, because the problem appears to be too many LIOs, and apparently not the speed per PIO. But one never knows until one asks...

Just some thoughts...

-Tim

Jeff Thomas wrote:
> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l