Re: Java Stored Proc Slow....

Shelling out to the OS from a stored procedure is generally not a good idea, if you care about performance. I believe that Unix will make a copy of the whole Oracle Server process (5-8MB) and then overlay that with the shell's exacutable, so this is going to take resources and time.

Also note that Oracle Java SPs take a while to execute the first time they are called after the instance starts up. Java Native calls also tend to induce some additional level of overhead. Also, Oracle's internal JVM is not the fastest thing in town, as it has a bunch of isolation features to keep it from bringing down the instance.

You'd probably be better off having a dedicated daemon process that either listened on a TCP port, or waits for AQ events, or polls a table, etc.

If the external process is performing more DDL, you might want to consider performing distributed transactions from the original process, using Database Links.

-Peter S.

Peter wrote:
>
> My situation is pretty straightforward. I need a stored procedure to
> call a UNIX command (actually, an Infomix program, but that's besides
> the point).
>
> What I am doing is using Java Stored Procedures to call a small piece
> of Java that I loaded into an Oracle 8i database. The java simply
> invokes the Java Runtime.exec() to shell the command out the the OS. I
> chose this because the DBMS_Pipes is not practical to deploy to our
> customer base.
>
> The JSP method seems very straightforward, but it is running VERY
> slow. Here are some example times:
>
> ODBC --> JSP --> JAVA (just return back, no runtime call) = .67 secs
> ODBC --> JSP --> JAVA --> UNIX (no code executed, just return back) =
> 1.15 secs
> (NOTE: calling the JSP from SQL*Plus is insignificantly faster, so
> ODBC is not my issue!)
>
> Seems way to long considering the Java is just passing along a system
> call and the Unix command run was an empty shell script. In fact, the
> UNIX program that I intend to run does a whole lot of work, SQL
> inserts/updates/deletes and that whole process only takes 0.8 secs. So
> the JSP call more than doubles the execution time. That is
> unacceptable to me.
>
> Any suggestions on where the bottleneck may be??
Received on Thu Jan 03 2002 - 23:49:26 CET