Executing SQL queries as background processes in Unix

[sahabcse]

Problem
For application maintenance and support, we often have to run queries which return huge dataset or consume long time. When such queries are executed in GUI based software like TOAD or PL/SQL Developer, it consumes a lot of time and also there is a chance of alerts being generated by database monitoring tools.
Solution
When a query is drafted one may use explain plan or otherwise to check the performance of the query. If the explain plan statistics reveal something of the form “TABLE ACCESS FULL” and the tables involved in the query contain huge data then it indicates that the query will take substantial amount of time to run in TOAD or PL/SQL Developer. In such a case, the following alternative may be used to run the query.
The pre-requisite is to have the Oracle database resident on a Unix machine.

1. Build a Unix script (say) testScript.sh in the database server in a suitable directory. It contains the following lines of code.
sqlplus -s <userName>/<passWord>@<HostName> <<EOF > testData.txt
set pagesize 0
select sysdate from dual;
EOF
2. Assign proper privilege to the script.
3. Execute the script as a background process in Unix having least priority. The syntax for the command is:
nice -n +19 testScript.sh &

Using nice -n +19 least priority is assigned to the script testScript.sh. Then it is run as a background process in Unix. Since the script runs as the least priority job, it will not hold onto resources when other queries (from front-end application or from other users) are submitted simultaneously to the database server.
4. On issuing the command mentioned in step 3, a process id <pid> will be returned and the command prompt will be made available to the users to continue with their work.

5. We may issue the command ps –ef | grep <pid> to check whether the process is running. A tabulated output will be generated where the columns will be in the following sequence:
UID PID PPID C STIME TTY TIME CMD
where
UID : User Id
PID : Process Id
PPID : Parent process Id
C : CPU utilization factor for scheduling
STIME : System Time
TTY : Controlling terminal device name
TIME : Current CPU time used
CMD : The issued command

6. The output of the command issued in Step 5 will comprise of three rows as mentioned below:
§ One row will have CMD = sh ./ testScript.sh and its PID will be <pid>.
§ Another row will have CMD = sqlplus – s <userName>/<passWord>@<HostName> and its PPID will be <pid>.
§ The other row will be displayed due to the issuance of ps –ef | grep <pid> command. (Not important in this context)
7. Once the script execution is complete, the following message is displayed in the command prompt:
Done nice -n +19 testScript.sh &
The result set is available in the text file testData.txt which is mentioned in the script testScript.sh.
8. However, if the process is continuing for a long period of time and the user wishes to terminate the script, two processes need to be killed. The following commands are issued to kill the two processes:
§ kill –9 <pid of the process having CMD = sh ./ testScript.sh>
§ kill –9 <pid of the process having CMD = sqlplus – s <userName>/<passWord>@<HostName> >

 

[berry120]

Nice - the only thing I'd do differently would be to use nohup with this line:

nice -n +19 testScript.sh &

That'll ensure that even if you log out it carries on running.

I'd also potentially redirect stdout / stderr to files so you can see what's going on without losing the output on the terminal.
So:

./nohup nice -n +19 testScript.sh >stdout.out 2>stderr.out

Then you can just do the usual "tail -f" on stdout.out and stderr.out to see what the contents are.

It depends on how long it takes to execute though - if we're talking about queries that take a few minutes and you'll always stay logged in, there might not be a huge amount of point. If we're talking about queries that span multiple servers and are so big they could take hours or days, I'd definitely recommend it.

 

[JCB1]

this could be quite helpful for me. I do mysql dumps via the command line and the CPU always max's out. I did try to look up how to do this but couldnt work it out. Thanks for the guide

 

[Lowndsey]

If this guide was posted for the competition is it in the best interests of competition fairness to be helping him improve it??

 

[berry120]

If this guide was posted for the competition is it in the best interests of competition fairness to be helping him improve it??

Fair point, but I meant that as a comment rather than it be included in the original article (it'd be pretty easy to see if he had included it anyway.)