Insert and Update performance

[bensmail]

Hi,

I use a ETL for bulk insert and update from diferent sources(Flat files, Oracle ,...) to Oracle Server(Datawarehouse).
Witch is the best configuration for Oracle Server: Block size, sort area size u.s.o?
I'm using log files=100M, Block size=16K, TBS and Tables are in NOLOGGING mode, I'm disabling indexes before inserting und updating but the performance is still low?

Note: I can't not modify the generated SQL .
Any Idea?

[marcio.68]

what is size of tables ? what number of lines insert / update ?
what is size of rollback segment ?
what is commit frequence ?
for update, disabling indexes, is bad idea.
after insert, update you re create indexes ? who time this process ?

[bensmail]

Size of tables:
The tables are Fact-tables~ 1 to 2 Gb and Rows~ 4 to 10 millions rows.

commit frequence:
commit after 1000 Rows.

Time:
Insert und update time for 3 million rows~ 2 Hours

Time to create indexes:
3 to 10 Minutes


How can i take more performance?

Bensmail

[vishkr]

Can you detail more about the process please ? In terms of ETL tool used, the kind of sql generated by the tool , Also some information about Source and Target.

Cheers
Vish

[bensmail]

ETL used: Genio from Hummingbird
sqlused: Oracle sql: INSERT INTO product SELECT..... WHERE....
Source and Target: Oracle 8.1.7 on AIX

[hrishy]

Hi

Try changing the tables to nologgging..

before the load
alter table table_name nologing
after the load
alter table logging
Alter table test parallel(10);

regards
Hrishy

[marcio.68]

If you use

Insert /*+ append */
Into audit_ohb
nologging

in best performace.

Clause WHERE is in best preformace?
And Archive mode ?