Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Korn Shell Coprocess Performance Question

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Korn Shell Coprocess Performance Question
# 1  
Old 12-08-2005
Korn Shell Coprocess Performance Question
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:

Code:
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

# 2  
Old 12-08-2005
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:
Quote:
There is one more piece to this. Since I cannot predict how many lines will be output from the coprocessor, I use a "suicidal read" technique to stop the script waiting indefinitely on the pipe.
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.
Last edited by tmarikle; 12-08-2005 at 05:05 PM..
# 3  
Old 12-09-2005
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.
# 4  
Old 12-09-2005
Use HOST ECHO "somevalue".

What I don't get is why you don't just run PL/SQL in the current process
Code:
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
# 5  
Old 12-09-2005
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
# 6  
Old 12-09-2005
I can't speak for Mark but I can let you know several reasons why I use a coprocessor:
  1. sqlplus persistence. I keep one session and don't have to keep spinning up sqlplus with costly session connections.
  2. PL/SQL session state persistence. I can take advantage of package session variables.
  3. 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.
  4. 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.
# 7  
Old 12-09-2005
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
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

SQL/Plus in a coprocess example. Also saves query results into shell variables

While assisting a forum member, I recommended running SQL/Plus in a coprocess (to make database connections and run a test script) for the duration of his script rather than starting/stopping it once for every row in a file he was processing. I recalled I made a coprocess example for folks at... (2 Replies)
Discussion started by: gary_w
2 Replies

2. Shell Programming and Scripting

korn shell remove files question

how do you show each filename in a giving directory and delete the specific file in korn script i was thinking using ls rm ? but i cant make it work (0 Replies)
Discussion started by: babuda0059
0 Replies

3. Shell Programming and Scripting

Question about a simple Korn script

Hi to everybody! I want to write a simple script in ksh that decrypts and encrypts using the DES algorithm. There is no builtin function in UNIX : i have found only a function in openssl but i don't understand how to use it. The script must accept in input the plaitext and the DESKEY in... (2 Replies)
Discussion started by: kazikamuntu
2 Replies

4. AIX

AIX 4.2 Korn shell and grep question

Ho do I find out the verion of the Kron shell on my client`s system ? There is no one to ask. They are not knowledged enough (hard to believe but yes). Also, on that AIX 4.2, I am trying to figure out how to do a grep using a search patter like below but does not seam to work. The '*' do... (11 Replies)
Discussion started by: Browser_ice
11 Replies

5. Shell Programming and Scripting

Korn shell and awk question

I am modifying a Korn shell script in using the Exceed (Solaris 10 environment). My task is to read in a .txt file with dates arranged like this (01-Sep-2006). I am to read each line and take the dates, compare them to a benchmark date and depending on if it is older than the date or the date and... (6 Replies)
Discussion started by: mastachef
6 Replies

6. Shell Programming and Scripting

korn shell question

Hi all, I am trying to tweak my ksh , i am running V: Version M-11/16/88i I have my Backspace and up/down arrows working using the following code in my ~/.profile file. set -o emacs alias __A=$(print '\020' ) alias __B=$(print '\016' ) alias __C=$(print '\006' ) alias __D=$(print... (4 Replies)
Discussion started by: mich_elle
4 Replies

7. Shell Programming and Scripting

Korn Shell Loop question

I'm needing help with assigning variables inside a while loop of ksh script. I have an input text file and ksh script below and I'm trying to create a script which will read the input file line by line, assign first and second word to variables and process the variables according to the contents. ... (4 Replies)
Discussion started by: stevefox
4 Replies

8. Shell Programming and Scripting

AWK question in the KORN shell

Hi, I have two files with the following content: gmrd.txt 235649;03;2563;598 291802;00;2563;598 314634;00;235649;598 235649;03;2563;598 393692;00;2563;598 411805;00;2563;598 411805;00;2563;598 235649;03;2563;598 414037;00;2563;598 575200;00;2563;598 70710;00;2563;598... (11 Replies)
Discussion started by: penfold
11 Replies

9. Shell Programming and Scripting

Question about Korn Shell

In Korn Shell, can you use "go to" statements? Would you then put paragraph names with a colon? For example, would you specify "goto para1" and then have the paragraph with the label para1:? I am getting an error message when Idid this. I have my paragraph name 'clsbooks:' and I get... (13 Replies)
Discussion started by: Latha Nair
13 Replies

10. Shell Programming and Scripting

Question variables Korn

I'm using the following command to test for certain characters in a script echo "${1}" | grep '\$' if (( ${?} == 0 )) then testing this script on the command line I have ksh -x script1.sh "xxxx$xxxx" this works fine but when I want to use ksh -x script1.sh "xxxx $xxx" the... (1 Reply)
Discussion started by: frank
1 Replies
Login or Register to Ask a Question