A couple of things you could consider
- parallelizing the load across several sessions
- Increasing the cache size for the sequence.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Giovanni Azua" <bravegag_at_hotmail.com> wrote in message
news:b3v8ci$1qhlda$1_at_ID-114658.news.dfncis.de...
> Hello all,
>
> I have a batch process which builds time-series aggregations
> over million of records each loading time (daily), in order to
> cluster those new generated aggregation time-series records I
> needed to provide a primary key and I was really surprised by
> the result, several times slower, the process without pk took 4
> minutes, and with pk generation using sequence and trigger 35
> minutes!!!!.
>
> That means the calling of the trigger is killing performance (I also
> tried creating an adhoc function getting exactly the same result). I
> suspect that the slowdown is due to the fact that sequences
> implement synchronization among sessions since here will always
> be one session calling this batch loading process I could get rid of
> this contention problem? or using something like:
>
> insert into my_table
> select nvl(max(id), 1) as id
> ... // remaining columns...
>
> should be faster and more appropriate in this case than sequence and
> trigger, right?
>
> TIA,
> Best Regards,
> Giovanni
>
>
Received on Mon Mar 03 2003 - 05:24:33 CST
