a collection of technical fixes and other randon stuff

Spodworld

  • Join Us on Facebook!
  • Follow Us on Twitter!
  • LinkedIn
  • Subcribe to Our RSS Feed

How to detect and stop long running processes in Oracle

 

How to detect and kill long running processes in Oracle

 

 

 

To get a list of long running processes/sessions:

 

 

 

SELECT sid,opname,sofar,totalwork,units,elapsed_seconds,time_remaining

FROM v$session_longops

 --WHERE sofar != totalwork;

 orderby ELAPSED_SECONDS DESC

 

To view all sessions:

 

 

 

select *
from
  v$session s
order
bysid;

 

To kill a session:

 

ALTER SYSTEM KILL SESSION 'sid,serial#';

 (where sid and serial no are taken from the data shown in the script above.

 

 

 

Running a published command line executable as a Scheduled task in Windows 7

If you experience any of the following errors while trying to run a scheduled task:

  • Task Scheduler failed to start instance "{xxxxxxxx-8xxxa43-xxx-xxxx-xxxxxxxxxx}" of "myprogram"  task for user "mydomain\myuser" . Additional Data: Error Value: 2147942593.

It may be that you are using publishing in visual studio to deploy this.

How to fix it:

First of all , once published you need to run the myapp.application file for it to be installed.

Then you need to consider that this installation puts the application elsewhere. If you dont need auto updating, then teh best bet is to point your scheduled task to the installed location.

Typically this would be in...

C:\Users\myuser\AppData\Local\Apps\2.0\xxxxxxxxxxxxxxxxxxxxxxxxxx\xxxxxxxxxxxxxxxxxx

Where the user is the same as that you installed it/will be runnning it and teh xxxxxx is some random crap.

Best way to find this is to search for a file in your app from  C:\Users\myuser\AppData\Local\Apps\2.0 and then right click on the file and select 'open location'

This solution worked for me, however our solution did not require auto updating which some of you might need.

Any advice on getting the main .application file call would be great. I suspect it will permission based problems.

 

How to use SQL profiler (quick guide)

SQL profiler usage

 

SQL profiler is a great tool for finxing problems with your database. It can highlight where errors are occurring, and also to some extent assist with improving performance.
I say to some extent because, once you get running really fast, you need to take into account that profiler does slow down the responses coming from your database. My policy on this is to always test and improve on a slow running machine or virtual machine and then deply the changes to your fast server. Simply throwing more CPU and memory requires you to test with larger volumnes and makes you work harder rather than your tools.

 

·                     First you'll need SQL management studio, either the full version or a developer version.(SQL express wont have this)

·                     Connect to the database using SQL management studio. (You could try remoting to the server and using the management studio thats installed there if it's not on your machine)

·                     SQL profiler can be found in [Start]->[Programs]->[Microsoft SQL 2005]->[Performance Tools] or through the [Tools] menu option in SQL management studio it's self.

·                     On initial startup you are presented with a blank screen. You need to create a new trace.(process whereby data is collected)
Click [File]->[New trace]
Select your database server to connect to (usually the one your working on) Note: you will probably need Admin permission or the correct settings to do this.

·                     Use the standard template if this is your first attemt, but click on the [Events Selection] Tab to have a look what's available.
Here you can add events to be monitored by checking the check boxes. More options are available if you choose "Show all events" and/or "Show all columns".
You can also add filters to narrow down what your looking for by right clicking over a column header and selecting "Edit column filter". I mainly use this to select long running events eg: over 60ms initiallly to filter out neglegable actions.

·                     When you select okay, the trace starts and the events are displayed onscreen.

·                     Pause/Stop/COntinue buttons are available at the top to allow you to brows the captures data once it's come in.

·                     You can use this to gather information on what actions are taking the longest to execute, or... you can save the trace for the Database tuning Advisor to process and make suggestions on inproving performance.
To do this clisk [File]->[Save as]->[trace file].
Once saved go to [Tools]->[Dataabse tuning advisor] and use this to analyse your trace file.