datawarehouse config

[Edgar_8]

We have an oracle 9i datawarehouse running on a HP-UX 11i K580 server (4x240 Mhz CPU;4Gb memory) and
are having performance problems with loading the data into fact tables. Does anyone have the following info
we could peruse/compare in their datawarehouse environment:
- block size of database filesystems & oracle db_block_size parameter
- RAID sertup for datawarehouse vg's/lvol's
- kernel parameters & oracle datawarehouse parameters ( ie.java pool;sga;sort_area_size)

Please could the feedback be from a runing oracle datawarehouse environment.Your assistance is most
appreciated.

Thanks in advance!

[Jean-Luc Oudart]

A few questions :
- what is the size of the database ?
- what kind of storage do you use ?
- oracle : partitioning option ?
- oracle data : load method ?

Regards,
Jean-Luc

[Sanjay Kumar Suri]

Some numbers:

File system block size=8K

db_block_size=8192
DB_BLOCK_BUFFERS=50000
JAVA_POOL_SIZE=33554432
SORT_AREA_SIZE=16777216
SGA_MAX_SIZE=4920144624

Data is kept on RAID5 where as redo logs are kept in RAID0/1.

sks

[Edgar_8]

Hi,

db size = approx. 800 Gb
storage = XP 256; 1 vg divided into diff lvols
partitioning = daily partitions on a date column
load method = sqlldr via conventional method due to unique constraint on table

We have read all the documentation that is available, so any advice is most appreciated!

[Jean-Luc Oudart]

Once I had to compare sqlldr methods.
Of course direct path is much faster whenever you can use it. But as your requiremnt is conventional path, how much tuning did you do in BINDSIZE and ROWS parameters ?

Jean-Luc

[Edgar_8]

Hi Jean,

The parameters are as follows:

ROWS=300
BINDSIZE=500000

However the actual number of rows commited at a time are 64.Any ideas why?

Thanks!

[Jean-Luc Oudart]

Edgar,

according to documentation :
"if the maximum bind array size is too small to accommodate the initial number of rows, SQLloader uses a smaller number of rows"

64 : this is the default value for ROWS

The question would be what is the maximum row length for your table upload ?

Jean-Luc
PS : I would think that 500000 is far too small.

[Brian Crabtree]

Edgar,

I ran across a note on an Oracle bug with sqlldr for 9i that was resolved by setting the pga_aggregate_target and workarea_size_policy settings. I can try and find the note again if you like. Have you tried setting this parameter yet?

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=223730.1

Thanks,

Brian