Software

Ask something
Discussion Locked

Oracle: End runaway processes on Windows

0 helpful
4 replies

by maryweilage

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

This week's Oracle e-newsletter describes how to kill runaway Oracle processes on Windows with OraKill. Did this tip teach you something you didn't know about star and snowflake schemas? Are there any other differences between the two schemas that this tip didn't cover?

If you aren't subscribed to the free Oracle e-newsletter, you can automatically subscribe to it by pasting the following URL into your browser:
http://nl.com.com/MiniFormHandler?brand=builder &subs_channel=bldr_front_door&list_id=e050&tag=fb

* Please delete any extra spaces that appear when you paste this URL into your browser.

4 replies

by careus

I'd tried this. But if the process still working, take some time until the work end or the rollback process complete.
For example, I have a procedure with cursors and take some time. If the process has no end yet, and I kill the session. The process still working for another time, until end.

by swstephe

Killing a process stops the process from continuing, but the database needs to maintain its integrity, so it will still need to release resources and free rollback segments. This can be a long process if your system is short on resources. I find that extra memory helps. I always recommend at least 512MB of physical memory on a Windows machine running a database to avoid the rollback process from thrashing memory too much. You should check Task Manager, when that is happening to see if "PF Usage" is really high. I find that processes end pretty quickly then. On UNIX systems, you can do something similar by increasing the shared memory area reserved for the SGA and making sure there is enough physical memory, (at least twice your SGA), available.

by earl.coombs

Hi Scott,
I am trying to "set up" an Orakill process/document for reference for the next time somebody hogs the system and I want to Orakill the offending thread rather than bounce the database. I am mor interested in the condition where I can't get into SQL*Plus to ALTER SYSTEM KILL SESSION and I have to use Qslice. I am having a hard time describing which TID to kill (from Qslice) other than the one using the most memory. Qslice doesn't identify which thread is which!
Any suggestions?
Earl

by swstephe

I mentioned pstat and qslice because they are free and from Microsoft's Resource Kit. There are hundreds of similar utilities out there. Check out this one from www.sysinternals.com : http://www.sysinternals.com/ntw2k/freeware/procexp.shtml called "Process Explorer". It gives you every possible piece of information about processes and threads on your system. But it still doesn't tell you which thread is which. The problem is that the only place to put that information is the thread's name, and Oracle doesn't set the thread name.

So, it's still up to best guess, depending on knowing your own situation. Often, the last person to log in gets a higher thread id, although numbers will eventually get recycled.

You can turn on more information in the task manager by turning on more columns, (such as PID, which is turned off by default).

Sometimes, SQL*Plus isn't completely dead, but just really, really, slow. If it is just hanging, give it a few minutes and it may be able to connect, (through local connection, TCP/IP or other network connections can time out). The virtual tables are stored in memory, so you should be able to scan for the SPID if you are patient. So try that before bouncing the database, but only if you can connect. You could also try bouncing the tnslistner, that will stop all clients, but not the database. Any client threads that are spinning will probably be left running. In that case, there will be only one thread, instead of a two-task thread pair. But there is also one background process which has only one thread.

This discussion is closed to new replies.