Go Back   Computer Forums > Articles | Tutorials | Tech Notes > Articles | Computer and Server Software
Click Here to Login
Join Computer forums Today


Reply
 
Thread Tools Search this Thread Display Modes
 
Old 04-26-2011, 10:52 AM   #1
In Runtime
 
Join Date: Jan 2011
Posts: 277
Default Executing SQL queries as background processes in Unix

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> >
__________________

sahabcse is offline   Reply With Quote
Old 04-26-2011, 12:39 PM   #2
Site Team
 
berry120's Avatar
 
Join Date: Jul 2009
Location: England, UK
Posts: 3,425
Default Re: Executing SQL queries as background processes in Unix

Nice - the only thing I'd do differently would be to use nohup with this line:
Code:
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:
Code:
./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.
__________________

__________________
Save the whales, feed the hungry, free the mallocs.
berry120 is offline   Reply With Quote
Old 04-26-2011, 04:53 PM   #3
JCB
Daemon Poster
 
JCB's Avatar
 
Join Date: Oct 2004
Posts: 1,302
Default Re: Executing SQL queries as background processes in Unix

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
__________________
Check Out My Computer Related YouTube Channel @ Click Here
JCB is offline   Reply With Quote
Old 04-26-2011, 09:42 PM   #4
Site Team
 
Lowndsey's Avatar
 
Join Date: Sep 2007
Posts: 3,607
Default Re: Executing SQL queries as background processes in Unix

If this guide was posted for the competition is it in the best interests of competition fairness to be helping him improve it??
__________________
JogaBonito1502: I guess Microsoft is really not to blame. Sorry!

KMATB
Lowndsey is offline   Reply With Quote
Old 04-27-2011, 08:21 AM   #5
Site Team
 
berry120's Avatar
 
Join Date: Jul 2009
Location: England, UK
Posts: 3,425
Default Re: Executing SQL queries as background processes in Unix

Quote:
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.)
__________________
Save the whales, feed the hungry, free the mallocs.
berry120 is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off



All times are GMT -5. The time now is 01:40 PM.


Powered by vBulletin® Version 3.8.8 Beta 4
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Search Engine Friendly URLs by vBSEO 3.6.0