dba unix

How to reuse connection in shell

It is Friday, I wanted to give my readers some stuff for the week-end 😉

Imagine that piece of code :

countlines() {
c=$(sqlplus -s /nolog <

I can run this

time ./script1
there are 14 lines in EMP
there are 4 lines in DEPT

real 0m0.46s
user 0m0.06s
sys 0m0.09s

Sounds like a regular shell script. How could we optimize the countlines function? Well, we could create the connection only once and use coprocess pipe (with |& that is pipe ampersand)

sqlplus -s /nolog |&

print -p "connect scott/tiger"

read -p line
if [ $line != Connected. ]
exit 1

print -p "set feed off head off"

countlines() {
print -p "select count(*) from $1;"
read -p c
echo "there is $c lines in $1"

countlines EMP
countlines DEPT

print -p disconnect

A two-ways pipe is opened with sqlplus. There is only one connect, and one disconnect.

Let's check the performance :

$ time ./script2
there is 14 lines in EMP
there is 4 lines in DEPT

real 0m0.23s
user 0m0.00s
sys 0m0.01s

About twice as fast! Note the "Connected" output may not exist in recent version of sqlplus in silent mode. If you have a script that generates hundreds of connections, or which create a connection every 5 seconds or so, think about it 🙂

Enjoy your week-end

By Laurent Schneider

Oracle Certified Master

7 replies on “How to reuse connection in shell”

This is why I’ll always prefer Korn shell to Bash, even though most *nix distributions have only the ancient ksh 88 rather than ksh 93.

zsh seems to support coprocesses as well but I haven’t experimented with it.

Hey, that’s a great tip! I’ll keep this idea in mind (and not just for the weekend..)

[…] Link to the original site Tags: Amp, C Lines, Emp, Exit 1, Fi, Grep, Lt, Nbsp, ORACLE BLOGS, Performance Time, Select Count, Shell Script, Silent Mode, Sqlplus S, Tiger, Two Ways […]

Leave a Reply

Your email address will not be published.