Hi,
We're currently using DS6 in a UNIX (SunOS) environment on a 8 CPU box with 16G or RAM. We run Oracle 9i on the same box as the DS Engine and we use the Net8 Client (doesn't support 9i 64 bit client yet).
We ran tests to load a flat file (about 80 columns) in an Oracle table (OCI Stage). We don't have any weird transformations, we put default values to 2-3 fields, that's it. So it's almost a 1-to-1 mapping from the flat file and the OCI. Settings in the OCI are Array Size 250 and Commit is 0 (one commit). We get about 500-600 rows/sec.[V] I tried different performance setting (row by row, in process and inter process) and nothing really changes. I tried with various Array Size (from 1 to 1000) and didn't get better... sometimes worst.
We ran the same test by changing the target to be a flat file (so from flat file to flat file) and it goes up to 2000-2500 rows/sec.[:D]
Is it normal to have the OCI to run that slow in very simple jobs? I tried with NOLOGGING tables on Oracle and got the same numbers. Should we look closer into Oracle or is there any limitations in the OCI stage???
Thanks,
Steve
Loading performance in Oracle
Moderators: chulett, rschirm, roy
Possible solutions:
1. If you have indexes in table that might slow the insert operation.
2. You can split your input file and load by two OCI jobs in parallel.
3. Use sql*loader for straight forward move.
4. Ask you DBA or Sys Admin to monitor disk operations. Might be you run into bottle neck on some disc. It's probable especially when you use local Unix disk rather then some datastore server.
Regards
1. If you have indexes in table that might slow the insert operation.
2. You can split your input file and load by two OCI jobs in parallel.
3. Use sql*loader for straight forward move.
4. Ask you DBA or Sys Admin to monitor disk operations. Might be you run into bottle neck on some disc. It's probable especially when you use local Unix disk rather then some datastore server.
Regards
To provide feedback to your solutions dtsipe:
1. The table has no indexes. It's a staging table at the beginning of the load.
2. Splitting the input file is not a valid solution for us. Although, we could probably use partitioners/collectors Stage in DS.
3. SQL*Loader is our next test using the ORA OCI BULK but we still have a problem to load it in Automatic mode (abnormal termination in Stage detected)
4. From UNIX sys admin, we ran some tests and there's no contention on the server (CPU and DISK wise). Need to look from an Oracle stand point.
Nevertheless, I surprised that the throughtput is low for small jobs. I worked with other ETL tool in the past and I'm not impress so far by DataStage execution performance. I'm expecting very bad throughtput when we'll do more complex jobs. In those cases, we'll probably have less solutions to look at.
Steve
1. The table has no indexes. It's a staging table at the beginning of the load.
2. Splitting the input file is not a valid solution for us. Although, we could probably use partitioners/collectors Stage in DS.
3. SQL*Loader is our next test using the ORA OCI BULK but we still have a problem to load it in Automatic mode (abnormal termination in Stage detected)
4. From UNIX sys admin, we ran some tests and there's no contention on the server (CPU and DISK wise). Need to look from an Oracle stand point.
Nevertheless, I surprised that the throughtput is low for small jobs. I worked with other ETL tool in the past and I'm not impress so far by DataStage execution performance. I'm expecting very bad throughtput when we'll do more complex jobs. In those cases, we'll probably have less solutions to look at.
Steve
Hi Steve,
Did you check oracle table constraints e.g. : primary key, checks, not null. All of them usually slow insert/update operation.
Nevertheless your load speed is very low for OCI stage.
I loaded using this stage 4000-5000 records per second on less powerful server. Try to ask DBA to check following definition:
1. oracle roolback segment allocation, roolback contention may cause to slow insert speed.
2. If it's very big table than try to allocate whole storage using one or small numbers of segments in create table statement.
This is very general guidelines, but I sence that it is some Oracle/Unix configuration problem in your environment.
I also ran into same problem with ORA OCI BULK in version 6.
After all we simply used ORACLEBULK stage instead.
From performance point of view the best mean for massive load into oracle is Sql*Loader. You may use direct mode to bypass oracle SGA and redo logs files and load directly to oracle datafiles.
Did you check oracle table constraints e.g. : primary key, checks, not null. All of them usually slow insert/update operation.
Nevertheless your load speed is very low for OCI stage.
I loaded using this stage 4000-5000 records per second on less powerful server. Try to ask DBA to check following definition:
1. oracle roolback segment allocation, roolback contention may cause to slow insert speed.
2. If it's very big table than try to allocate whole storage using one or small numbers of segments in create table statement.
This is very general guidelines, but I sence that it is some Oracle/Unix configuration problem in your environment.
I also ran into same problem with ORA OCI BULK in version 6.
After all we simply used ORACLEBULK stage instead.
From performance point of view the best mean for massive load into oracle is Sql*Loader. You may use direct mode to bypass oracle SGA and redo logs files and load directly to oracle datafiles.
-
vmcburney
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
In version 6 the fastest way to insert into an Oracle table is to write the rows out through a sequential file stage and bulk load it into oracle. Use the bulk load stage to create a control file but do not use it to create a dat file as it is slower than a sequential file stage.
Vincent McBurney
Data Integration Services
www.intramatix.com
Vincent McBurney
Data Integration Services
www.intramatix.com
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
chanthou_2000
- Participant
- Posts: 11
- Joined: Mon Jun 16, 2003 2:21 am
Hi all,
I discovered the same problem about the Oracle OCI.
Indeed, i tried to load data into a table with 70 columns,
the source is a User Sqlquery. The result is : 600 rows/s
To fix this, i used the sqlplus command : copy.
It's a great command because you can insert data by using
a sql query and without overloading the rollback segment
thanks to the parameter "arraysize" and "copycommit" (it's very
similar to datastage OCI
). the only thing to do is to
run a script inside DS to launch this command (Becareful, the
sqlplus command copy is limited to 2500 caracters ... but you can
create a view and select the view). With this method, the insertion
is only limited by oracle and oracle is faster than DS.
What do you think about this method ?
Regards
I discovered the same problem about the Oracle OCI.
Indeed, i tried to load data into a table with 70 columns,
the source is a User Sqlquery. The result is : 600 rows/s
To fix this, i used the sqlplus command : copy.
It's a great command because you can insert data by using
a sql query and without overloading the rollback segment
thanks to the parameter "arraysize" and "copycommit" (it's very
similar to datastage OCI
). the only thing to do is to run a script inside DS to launch this command (Becareful, the
sqlplus command copy is limited to 2500 caracters ... but you can
create a view and select the view). With this method, the insertion
is only limited by oracle and oracle is faster than DS.
What do you think about this method ?
Regards