SV: Semi DBA-type question

[Jon Kvisli]

Hi.
 
>I'm looking at trying to optimize a 'C' program (ick!).  Part of its
>processing is the insertion of approximately 69 000 records into one table.
>This program typically runs for about 10 hours, unless the inserts have been
>done, in which case it runs in < 1 hour.

Under "normal" conditions, inserting 69' records into one Oracle table should not take anywhere near this amount of time (unless the recordsize of the table is huge). I suggest you look for any sideeffects caused by the inserts in the database (high number of indexes on the table? any Oracle Text indexes? any complex triggers? any foreign key constaints lacking indexing? any other "heavy" constraint checking?). Are you sure that the 9 hours difference is entirely caused by the inserts, and not by any C-code that is only executed when inserting? How do you handle transaction controll (COMMITS) while inserting. If COMMIT is done ony after inserting the final record, redo logs may be large and slow down execution. Try COMMITing after each insert (or a smaller number of inserts). 

>I realize that inserting a bunch of records will take more time, but one of
>my co-workers thinks that someone mentioned once that the overhead required
>just to allocate table space for that volume of inserts would be significant.

Oracle allocates physical disc-space in chuncks called "extents". Unless the INITIAL, NEXT and PCTINCREASE parameters of the CREATE TABLE statement, are set to extreme (low) values, allocating disc space should not be very time consuming.

>So, if any of you folks with DBA-type experience has words of wisdom, I
>welcome them.  And, as a Powerhouse "guy", I'd really rather be writing this
>in Qtp instead of "C"!

So would I, but if the 9 hrs are spent bye the Oracle DBMS, this will not solve ypur problem. QTP will also insert the same 69' records using one INSERT statement for each record. Database execution time should be expected to be in the same order as when inserting from C (unless the C program is using some time consuming middleware like ODBC or JDBC).
 
Regards
Jon Kvisli
Pricipal consultant