OCI Bulk updates + Bitmap index growth

From: Serge Aleynikov <serge_at_hq.idt.net>
Date: 2000/06/09
Message-ID: <3941A9E5.FF77C561_at_hq.idt.net>#1/1


Perhaps someone has an advice in the following situation:

We have two Oracle 8.1.6.1 servers with identical installations and schemas. One (Server A) is running on a 4 CPU Dell server with 2G of RAM connected to EMC with 256G. The second one (Server B) is a more simple machine with 4CPU, 512M RAM and 100G Raid 1+0 (in both cases).

Both servers have a table WAREHOUSE partitioned by date in a tablespace with 10M init, and 10M next extents. The table has 3 B-Tree (same tablespace) and 6 BITMAP local indexes in a different table space (1M init, 1M next extent). Each partition in the table has about 3,000,000 rows.

The client application is loading data using OCI interface doing DML Array inserts / updates
(insert into WAREHOUSE values (:a, :b, ...) -- where :a, :b ... are DML array bind variables).

Problem: Server A demonstrates an accelerated growth of extents on BITMAP indexes such that it drops the speed of loading/updating drastically. Server B doesn't have such an accelerated growth of indexes.

Ideally, I would drop indexes on a partition prior to inserting/updating and then recreated them when done. Unfortunately Oracle doesn't allow index partition drops.

Questions:
1. What can be recommended to slow down the growth of Bitmap index extents?
2. How can I figure out why two servers exibit different extent growth factor?
3. Is there a way to temporary disable local partition indexes during such loading/updating operation?

Please send a reply to my e-mail: serge_at_hq.idt.net

Thank you.

Serge Received on Fri Jun 09 2000 - 00:00:00 CEST

Original text of this message