cancel
Showing results for 
Search instead for 
Did you mean: 

datawarehouse config

Edgar_8
Regular Advisor

datawarehouse config

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!
7 REPLIES
Jean-Luc Oudart
Honored Contributor

Re: datawarehouse config

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
fiat lux
Sanjay Kumar Suri
Honored Contributor

Re: datawarehouse config

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



A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Edgar_8
Regular Advisor

Re: datawarehouse config

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
Honored Contributor

Re: datawarehouse config

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
fiat lux
Edgar_8
Regular Advisor

Re: datawarehouse config

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
Honored Contributor

Re: datawarehouse config

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.
fiat lux
Brian Crabtree
Honored Contributor

Re: datawarehouse config

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