topic Re: datawarehouse config in Operating System - HP-UX

datawarehouse config

Edgar_8 — Fri, 20 Feb 2004 06:42:32 GMT

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!

Re: datawarehouse config

Jean-Luc Oudart — Fri, 20 Feb 2004 06:51:01 GMT

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

Re: datawarehouse config

Sanjay Kumar Suri — Fri, 20 Feb 2004 07:02:28 GMT

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

Re: datawarehouse config

Edgar_8 — Fri, 20 Feb 2004 07:03:48 GMT

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!

Re: datawarehouse config

Jean-Luc Oudart — Fri, 20 Feb 2004 07:45:29 GMT

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

Re: datawarehouse config

Edgar_8 — Fri, 20 Feb 2004 08:56:45 GMT

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!

Re: datawarehouse config

Jean-Luc Oudart — Fri, 20 Feb 2004 09:38:48 GMT

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.

Re: datawarehouse config

Brian Crabtree — Fri, 20 Feb 2004 16:48:27 GMT

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