Re: 'create bitmap index' on a large partitioned table very slow after upgrade to 9.2.0.4

Have you switched to using

    workarea_size_policy = auto
and

    pga_aggregate_target = XXX

The speed with which you can create
bitmap indexes is affected by the
sort_area_size and create_bitmap_area_size, both of which are 'taken over' by the
pga_aggregate target.

It is possible that the effective memory allocation you are getting for the bitmap creation is much smaller than it used to be. There is a limit of 5% of the pga_aggregate_target that applies to each session (and 30% across all processes involved in parallel execution). Compare this with the 8MB default for create_bitmap_area_size.

I don't know what the figures mean after the bitmap() call - but it may be related to a future development to ensure that the Hakan factor is catered for properly. In my case, the first number is 8168 - which happens to be a 'magic number' that Oracle uses elsewhere to represent 'unknown number of rows'. Is your system built on a 16K block - this might explain why I see a different number from you.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated Sept 19th





"Shehnaz N." <Shehnaz_Nurmohamed_at_cpr.ca> wrote in message 
news:1adc56d1.0411261033.39529375_at_posting.google.com...


> An index that used to take a half hour to build on HPUX 11 with

> 8.1.7.0 is now taking over two hours on AIX 5.2 with 9.2.0.4. This is

> my index build statement:

>

> create bitmap index EQPMV.FACT_TRAFFIC_SEGMENT_IX23

> on EQPMV.FACT_TRAFFIC_SEGMENT

> (SHIPMENT_ID)

> LOCAL

> tablespace FACT_PART_10M_INDX01

> NOLOGGING PARALLEL 4

>

> The table is partitioned and has over 170 million rows. When I look at

> what is executing, I notice the following difference:

>

> AIX Server with 9.2.0.4:  /*+ NO_EXPAND FULL(A1) BITMAP(16360 127) */

> HP UX Server with 8.1.7.0 /*+ NO_EXPAND FULL(A1) BITMAP */

>

> 1. Does anyone know what these numbers mean (16360 127)?  The numbers

> stay consistent regardless of how many times I try to rebuild this or

> any other bitmap index on this large table.

> 2. Any idea why it takes so long to build the index?

>

> Thanks.