topic Re: Oracle Input Buffer Limitation in Operating System - HP-UX

Oracle Input Buffer Limitation

David Cogley_1 — Tue, 02 Apr 2002 14:28:46 GMT

A Perl script which uses DBI to copy information from production servers to a data warehouse functions without problem on Sybase but freezes on Oracle when the UPDATE statement gets to be too big.

For a particular day and server, I successfully copy over 200 records and the script freezes at the attached update statement. Upon inspection, it seems that this statement is just a little longer than the successful statements. When I paste this statement into SQL*Plus and add a semicolon, Oracle complains that a quoted string is not ended properly. If I just insert a carriage return in the middle of the statement, it executes successfully.

Is there a way to change the input buffer so it will successfully process a longer statement?

The data warehouse is implemented with Oracle 8.1.7 on WIN2K. The WIN2K box has only 128 MB RAM. This might be a contributing factor.

Re: Oracle Input Buffer Limitation

Andreas D. Skjervold — Wed, 03 Apr 2002 12:26:06 GMT

Hi

Have seen some references to multicolumn insterts spinning with more than 256 columns.

As I could see yours has 140, so you should be in the safeside here.

128 MB RAM is low but still your able to run the other updates... Beats me!

Andreas

Re: Oracle Input Buffer Limitation

Bill Hassell — Wed, 03 Apr 2002 12:32:46 GMT

Although this is an HP-UX (Unix) forum, experience with large scale databases such as Oracle indicate that memory should be measured in gigabytes for reasonable performance. Also, WinXX platforms have a long history of poor memory management, that is, when most of memory is used, swapping is not always effective and the lock-up scenario you describe is common to all programs when RAM is almost gone. I would increase RAM to a minimum of 512 megs but 1000 megs would be better.