I am wracking my brains over this. I am trying to use a Korn Shell script to execute an Oracle PL/SQL procedure, using the Oracle command line interface (sqlplus). The script starts sqlplus in a coprocess, and the two processes communicate using a two-way pipe. The bgnice option is off, so both processes run at the same priority. The operating system is AIX 5.1.
Here's the problem. If I run the PL/SQL procedure using the script, it takes (quite literally) about ten times as long to run as it would take were I to start sqlplus myself, and run the procedure manually.
There is one more piece to this. Since I cannot predict how many lines will be output from the coprocess, I use a "suicidal read" technique to stop the script waiting indefinitely on the pipe.
Can anyone help?
Here is a code fragment:
while true ; do
(sleep 1 ; kill $$ 2>/dev/null) & # Set up alarm process to
bp=$! # kill read after 1 second
OLDIFS="$IFS" # Read data from SQL Plus
IFS='' # without splitting into
rc=0 # individual fields
result=""
read -rp result 2>/dev/null
rc=$?
IFS="$OLDIFS" # Restore field delimiters
if (( rc != 0 )) ; then # If a read error occurred
kill $bp 2>/dev/null # kill the alarm process
exitnow=1 # exit from func and script
break
fi
if kill $bp 2>/dev/null ; then # If we killed the alarm
set -- $result # Split data into fields
if [[ "$1" = "SQL>" ]] ; then # Remove leading SQL
while [[ "$1" = "SQL>" ]] ; do # prompts from output
shift
result=$(print "$result" | cut -c6-)
done
fi
else
sleep $pollInterval # Wait if alarm timed out
fi
done
How many rows does your script process? sqlplus running as a coprocessor is still interactive from sqlplus' perspective. Setting TERMOUT OFF obviously can't be done and your bottleneck is probably the KSH read loop (if you have thousands of records that you expect back). I had to reengineer a process for this very reason to open a REFCURSOR and spool the output to a file using sqlplus PRINT (from my coprocessor). If you are required to loop through the records, you can do this after-the-fact. The performance gain was dramatic in my case.
The coprocessor I/O pipe is apparently chocked by thousands of records or is simply an inefficient channel for passing many large character stings (mine where 250 bytes in one case and 3000 bytes in another).
On another note, you mentioned one thing that intrigues me:
I would like to test this suicidal read myself. However, It isn't necessary if you post known termination messages from your script. That is to say, output something like "SQL-COMPLETE" after the SELECT, DBMS_OUTPUT, or REFCURSOR messages that you are expecting back. When you read messages back from the coprocessor, break when you find your termination message. My coprocessors never "freeze" since my resuls are allways predictable.
Thanks for responding. It looks like a little experimentation is in order here. I'll try your suggestion of using PRINT in SQLPlus and let you know what happens.
Use HOST ECHO "somevalue".
What I don't get is why you don't just run PL/SQL in the current process
sqlplus uid/pwd -s<<EOF
SET TERMOUT OFF
SPOOL T.LIS
SET SERVEROUT ON SIZE 1000000
DECLARE
blah blah
BEGIN
DBMS_OUTPUT.enable(1000000);
blah blah
DBMSOUTPUT.put_line('bla blah');
END;
/
EOF
-- now play with t.lis
Jim
That sounds far too simple and sensible.
Actually, the reason why I don't do this is that I want to run a series of SQLPlus commands in a single session, and exit when the first error occurs. for example, if I execute this command:
isql connect_string <<-EOT
blah1
blah2
blah3
EOT
isql will not go on to execute blah2 if blah1 fails.
Otherwise, yes, I could use a here document with SQLPlus.
Mark
I can't speak for Mark but I can let you know several reasons why I use a coprocessor:
- sqlplus persistence. I keep one session and don't have to keep spinning up sqlplus with costly session connections.
- PL/SQL session state persistence. I can take advantage of package session variables.
- Multiple database sessions using several coprocessors simultaneously. Many of our databases can't have database links so multiple sessions simplifies access to more than one database. This is accomplished by redirecting stdin and stdout to different file descriptors.
- Finally, the method lends itself (sort of forces you) to wrapping it all up into function libraries. This results in more reuse. Any time I want a new coprocessor, I create a new coprocessor instance and reference it through a handle. I simply send new SQL to one instance or the next. This also provides a convenient mechanism for tracing the SQL and result set
There are a few more. Most people I work with use the same technique that you do but are slowly realizing the advantages to using a coprocessor. A coprocessor may be overkill for some applications but my large data loading and data extraction systems work well with this technique.
Actually, you did a pretty good job of speaking for me :-). Some of the benefits of using a coprocess apply in my world too. I too support an application (actually two of them) that uses multiple SQL Loader, COBOL and PL/SQL procedures to perform updates. The PL/SQL procedures can be run independently of each other, in theory at least, but by using a persistent session, and monitoring for error messages, I can prevent needless damage to my data by aborting a session at the point of failure.
Unfortunately the application I support is for an insurance product that is in runoff. No new policies have been sold for 4 years, and no new development on the system is being sanctioned. The programmer who wrote much of this had an aversion to including any error trapping in his code (he was fired for incompetence in 2002), so I have to look for unhandled exceptions and abort processing when they happen. Much as I would like to make the code more robust and efficient, I will never be allowed to do so.
The PL/SQL procedures run in unattended mode at night, and nobody gets to see the results until the next day. The use of coprocesses allows the loads to be initiated by shell scripts that have the intelligence to monitor the ouput from SQLPlus, and shut down gracefully in the event of an error.
Mark
Just to update you on this problem. I did implement the suggestion of listening for a specific string that indicates the completion of a SQL command. It did improve the runtime of the job, but only by about 5%. However, I do like the concept of having the script work this way. I also set the SQLPlus prompt to the null string, again to simplify parsing of the output. I changed the "suicidal read" poll interval to 60 seconds (actually it is specifiable as a parameter now, rather than hard-coded). Performance improvement was marginal.
I'm open to other suggestions at this point.....
I asked in my first response how many rows does your loop process. If there are thousands, you may want to spool the output to a file and set TERMOUT to off. This will limit the I/O bottleneck to sqlplus and not your loop. Following your co-processor read loop, implement another loop if necessary to process your results. Thousands of records through the co-processor read was just too slow for me. I used a REFCURSOR from the PL/SQL stored procedure and sqlplus PRINT.
Here is an example:
# Prepare PL/SQL Block and create a SQL script
print "
SET VERIFY OFF FEEDBACK OFF HEADING OFF
SET LINESIZE 3000 PAGESIZE 0
SET TRIMSPOOL ON SPACE 0
VARIABLE v_record_cnt VARCHAR2(50)
VARIABLE v_header_results REFCURSOR
VARIABLE v_detail_results REFCURSOR
VARIABLE v_trailer_results REFCURSOR
BEGIN
stored_procedure(
-- in parameters
...
-- out parameters
,:v_record_cnt
,:v_header_results
,:v_detail_results
,:v_trailer_results
);
:v_record_cnt := 'Record_Count:' || :v_record_cnt;
END;
/
PRINT v_header_results
PRINT v_detail_results
PRINT v_trailer_results
" > ${TEMP_SQL_SCRIPT}
# Launch the SQL file
# Print the stored procedure output variables
# Reset the SQL parameters
# Print marker message to keep the shell script from freezing up
print -p "
SET TERMOUT OFF
SPOOL ${TEMP_EXTRACT_FILE}
@${TEMP_SQL_SCRIPT}
SPOOL OFF
PRINT v_record_cnt
SET TERMOUT ON
SET VERIFY ON FEEDBACK ON HEADING ON TRIMSPOOL OFF
PROMPT SQL-COMPLETE
"
... now you can process your results file ${TEMP_EXTRACT_FILE} ...
That's pretty swift, I never though of setting it to NULL. However, why is it necessary if you "SET FEEDBACK OFF VERIFY OFF ECHO OFF"?
What happens if your SQL takes 65 seconds to return data? I believe the suicidal read will terminate your job prematurely.