ORA-03123 Operation would block

Hello:

I have an executable written in Oracle Pro-C/C++ which does array fetches of the data generated by a sql query. Many copies of this executable is run in a concurrent fashion in an attempt to speed up the processing.

Occasionally I get the ORA-03123 error with the ora text "Operation would block". Oracle suggests that the query should be resubmitted. I would rather have a definite fix than resubmitting the query again thereby saving time and resources.

By further analysis here is my theory about the cause of the error: The queries involved are complex and join multiple tables to generate data. An explain plan shows hash joins thereby implying that the server has assembled the data completely and successfully before responding to the query ( as opposed to the processing that takes place with FIRST_ROWS hint). There is a silent period when query results are being assembled and a turbulent period when the data is sent over the network to the executable.

Since multiple executables are running at the same time, the queries can complete selection at about the same time. The next step is to do the array fetches by each executable. The number of rows resulting from query is in order of 10,000 at a time. Array fetches of 1500 rows is being done at a time. Once the fetch is completed, the query is repeated for another set of bind variables. Since the array-fetch is network intensive operation there is a strong possibility for a contention for the network resource by the competing copies of the executable. This results in the stated error.

The executable is run on solaris machine with 7.3.3 oracle server.

Has anyone experienced this problem before? Does anyone agree/disagree with my analysis? Anyone got solution for this?

Any response is greatly appreciated.

Thanks
nan.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Dec 29 1998 - 23:26:09 CST