Index creation

[JPDB_75]

Oracle 9.2
Solaris 2.8
block size 8K

I have a table partitioned with the partitions filled as follow:

part39 => 0 rows 0 blocks
part40 => 0 rows 0 blocks
part41 => 0 rows 0 blocks
part42 => 0 rows 0 blocks
part43 => 0 rows 0 blocks
part44 => 593690 rows 22647 blocks
part45 => 719160 rows 25741 blocks
part46 => 2692460 rows 96062 blocks
part47 => 5679485 rows 223782 blocks
part48 => 3537830 rows 134173 blocks
part49 => 1529230 rows 55311 blocks
part50 => 3937270 rows 148661 blocks
part51 => 5492000 rows 202906 blocks
part52 => 10563360 rows 419427 blocks
part53 => 13351520 rows 538029 blocks
part54 => 7495000 rows 298408 blocks
part55 => 8732335 rows 358214 blocks
part56 => 6293385 rows 263279 blocks
part57 => 5107925 rows 19287 blocks
part58 => 4938015 rows 210527 blocks
part59 => 5193988 rows 219691 blocks

I create an index on this table :

SQL> CREATE BITMAP INDEX contrat1.IDX_UD_BNUMBER
2 ON contrat1.FA_USAGE_DETAIL(CHAR3)
3 COMPUTE STATISTICS
4 LOCAL
5 TABLESPACE IND_XXL;

Index created.

Elapsed: 00:33:47.48

Then I create another user in which I create the same table as above with only the following partitions with the same data as above:

part49, part50, part51, part52, part53, part54.

Then I create the same index as above :

SQL> CREATE BITMAP INDEX PERF_STAT.IDX_UD_BNUMBER
2 ON PERF_STAT.FA_USAGE_DETAIL(CHAR3)
3 COMPUTE STATISTICS
4 LOCAL
5 TABLESPACE IND_XXL;

Index created.

Elapsed: 00:50:07.93

As you can see , it takes almost 50% more time when there is less
data.

I ran the creations querying v$session_wait for both sessions.
I took sample every second

In the first one, I have mostly
"db file scattered read" (normal ) ,
"direct path write" and "direct path read" (sort area too small )


In the second one I have like above
"db file scattered read" (normal ) ,
"direct path write" and "direct path read" (sort area too small )
but in addition :

"db file sequential read" Why ?
"free buffer waits" Why ?

I would like to add that I was the only one working on the DB.

Can someone explain ?

[mike9]

Was one of the table analyzed before you began to create you index.

Which is the degree of parallelisme of the 2 tables.

do you know how much IO get generated against the TEMP tablespace during the creation of the 2 indexes (V$TEMPSTAT,V$SQL_WORKAREA_ACTIVE) ?

[JPDB_75]

Was one of the table analyzed before you began to create you index.

Which is the degree of parallelisme of the 2 tables.

do you know how much IO get generated against the TEMP tablespace during the creation of the 2 indexes (V$TEMPSTAT,V$SQL_WORKAREA_ACTIVE) ?

The first table was analyzed ( the one it took 33 mn to create the index.) The other was not.

The parallelisme of the two tables is default (1).

No, I do not the exact IO against the TEMP tablespace. But I know there was because the sort_area_size is too small.

[mike9]

Are you sure that you are using the SORT_AREA_SIZE parameter?
Is the parameter WORKAREA_SIZE_POLICY set to MANUAL ? If it is set to AUTO all *_AREA_SIZE parameters are ignored. Oracle will then take PGA_AGGREAGTE_TARGET to define the size of the different workareas.

So I guess that in your case WORKAREA_SIZE_POLICY = AUTO. Because your 2nd table was not analyzed the PGA Memory Management made some other estimation for the PGA which resulted in much more IO to temp.

[mike9]

So I recommand you to recreate your 2nd table, analyze your partitions (1% is enough) and only then begin with the creation of the Bitmap Indexes.

[tamilselvan]

I would like to add that I was the only one working on the DB.

Can someone explain ?

I think it was mainly due to delayed block clean out.

Tamil

[JPDB_75]

So I recommand you to recreate your 2nd table, analyze your partitions (1% is enough) and only then begin with the creation of the Bitmap Indexes.

Well I am in WORKAREA_SIZE_POLICY = MANUAL.

But your solution works :

SQL> execute dbms_stats.gather_table_stats ('PERF_STAT','fa_usage_detail',estimate_percent => 1);

PL/SQL procedure successfully completed.

Elapsed: 00:07:45.19

SQL> drop index PERF_STAT.IDX_UD_BNUMBER;

Index dropped.

Elapsed: 00:00:00.51

SQL> CREATE BITMAP INDEX PERF_STAT.IDX_UD_BNUMBER
2 ON PERF_STAT.FA_USAGE_DETAIL(CHAR3)
3 COMPUTE STATISTICS
4 LOCAL
5 TABLESPACE IND_XXL;


Index created.

Elapsed: 00:30:46.86


Thanks for the tip.
#############################################
If it is not too much too ask, could you please the following questions:

1. Do you know what kind of assumptions it is making and why is it making "db file sequential read" ?

2. The reason I was making this test is the following:

About a month ago, we had to partition the table.

On a pre-production platform, we did the following :

1. Move the table to table_old
2. create the partition table as select * from table_old.
3. Drop table_old.
4. create the bitmap indexes.

On the pre-production, it took 15 mns to create the table and we had 5 indexes. They took about 10 mns each except for this particular one wich took 30 mns.

When we ran the same procedure on the production platform, the table creation took 7 mns but the indexes took 30 mns each except the particular index which took 1h15 mns.

Could it be the same problem ( However on both the pre-prduction and the production platform, the tables were not analyzed)

Thanks for the answer

[tamilselvan]

On the pre-production, it took 15 mns to create the table and we had 5 indexes. They took about 10 mns each except for this particular one wich took 30 mns.

Certainly pre-prod server and prod server are not the same, even if both have same number of CPUs and same amount of memory and disks etc.

If you do sql trace with 10046 events, you will see the difference between them in pro-prod and prod.

Tamil

[JPDB_75]

Certainly pre-prod server and prod server are not the same, even if both have same number of CPUs and same amount of memory and disks etc.

If you do sql trace with 10046 events, you will see the difference between them in pro-prod and prod.

Tamil

I ran sql trace with 10046 events. The only difference is that at somme point, in the session where the creation of the index is longer, there's a free buffer waits. Since at the time of the test, I made sure I was the only one doing work on the database.

Can someone explain ?

Here's a sample of both trace files:

prod (4 processors) where it takes more time :


WAIT #1: nam='db file scattered read' ela= 815 p1=105 p2=249178 p3=8
WAIT #1: nam='db file scattered read' ela= 272 p1=105 p2=249186 p3=8
WAIT #1: nam='db file scattered read' ela= 255 p1=105 p2=249194 p3=8
WAIT #1: nam='db file scattered read' ela= 249 p1=105 p2=249202 p3=8
WAIT #1: nam='db file scattered read' ela= 249 p1=105 p2=249210 p3=8
WAIT #1: nam='db file scattered read' ela= 321 p1=105 p2=249218 p3=8
WAIT #1: nam='db file scattered read' ela= 709 p1=105 p2=249226 p3=8
WAIT #1: nam='db file scattered read' ela= 296 p1=105 p2=249234 p3=8
WAIT #1: nam='db file scattered read' ela= 207 p1=105 p2=249242 p3=6
WAIT #1: nam='free buffer waits' ela= 333162 p1=105 p2=249248 p3=3
WAIT #1: nam='db file scattered read' ela= 320439 p1=105 p2=249248 p3=8
WAIT #1: nam='db file scattered read' ela= 265 p1=105 p2=249256 p3=8
WAIT #1: nam='db file scattered read' ela= 311 p1=105 p2=249264 p3=8
WAIT #1: nam='db file scattered read' ela= 250 p1=105 p2=249272 p3=8
WAIT #1: nam='db file scattered read' ela= 308 p1=105 p2=249280 p3=8
WAIT #1: nam='db file scattered read' ela= 894 p1=105 p2=249288 p3=8
WAIT #1: nam='db file scattered read' ela= 361 p1=105 p2=249296 p3=8
WAIT #1: nam='db file scattered read' ela= 262 p1=105 p2=249304 p3=8
WAIT #1: nam='db file scattered read' ela= 1042 p1=105 p2=249312 p3=8

###################################################

Pre Prod (2 processors)

WAIT #1: nam='db file scattered read' ela= 196 p1=105 p2=249194 p3=8
WAIT #1: nam='db file scattered read' ela= 181 p1=105 p2=249202 p3=8
WAIT #1: nam='db file scattered read' ela= 179 p1=105 p2=249210 p3=8
WAIT #1: nam='db file scattered read' ela= 179 p1=105 p2=249218 p3=8
WAIT #1: nam='db file scattered read' ela= 444 p1=105 p2=249226 p3=8
WAIT #1: nam='db file scattered read' ela= 182 p1=105 p2=249234 p3=8
WAIT #1: nam='db file scattered read' ela= 611 p1=105 p2=249242 p3=8
WAIT #1: nam='db file scattered read' ela= 198 p1=105 p2=249250 p3=8
WAIT #1: nam='db file scattered read' ela= 483 p1=105 p2=249258 p3=8
WAIT #1: nam='db file scattered read' ela= 193 p1=105 p2=249266 p3=8
WAIT #1: nam='db file scattered read' ela= 208 p1=105 p2=249274 p3=8
WAIT #1: nam='db file scattered read' ela= 205 p1=105 p2=249282 p3=8
WAIT #1: nam='db file scattered read' ela= 489 p1=105 p2=249290 p3=8
WAIT #1: nam='db file scattered read' ela= 210 p1=105 p2=249298 p3=8
WAIT #1: nam='db file scattered read' ela= 732 p1=105 p2=249306 p3=8
WAIT #1: nam='db file scattered read' ela= 227 p1=105 p2=249314 p3=8
WAIT #1: nam='db file scattered read' ela= 220 p1=105 p2=249322 p3=8
WAIT #1: nam='db file scattered read' ela= 200 p1=105 p2=249330 p3=8
WAIT #1: nam='db file scattered read' ela= 198 p1=105 p2=249338 p3=8
WAIT #1: nam='db file scattered read' ela= 185 p1=105 p2=249346 p3=8
WAIT #1: nam='db file scattered read' ela= 457 p1=105 p2=249354 p3=8
WAIT #1: nam='db file scattered read' ela= 180 p1=105 p2=249362 p3=8

[tamilselvan]

WAIT #1: nam='free buffer waits' ela= 333162 p1=105 p2=249248 p3=3

It shows the bottleneck is on the DBWR.
Did you configure single DBWR or multiple DBWRs?

Also, you need to set the highest value for DB_FILE_MULTIBLOCK_READ_COUNT during index creation.
Its current value 8 is not sufficient for scattered read.

Tamil