About 5 years ago I wrote about the risks that connecting to Oracle processes via debuggers may cause and what are (in my opinion) the safer and less safer options for taking stack samples from running Oracle processes.
In the end of that article I listed different options for getting a stack traces and whether they were safe or not.
For example, ORADEBUG-based process stack traces (DUMP ERRORSTACK, SHORT_STACK and event the process/system state dumps (at level 256 or higher) are not 100% safe – because they alter the execution path of the process they attached to. Your process may crash or get some error if you hit a bug (of course once you patch/fix the bug, you’ll be fine again – until you may hit the next bug).
An example bug is this:
Bug 15677306 : SUNBT6994922 ORACLE LOGWRITER HARD HANG WHEN SIGUSR INTERRUPTS LIBAIO
LGWR may hang when SIGUSR interrupts libaio (that’s what LGWR uses for asynchronous IO). And if LGWR is hung – your whole instance will be hung pretty soon. But what does the SIGUSR signal have to do with Oracle? Well, that’s exactly the signal what ORADEBUG sends to the process it has attached to, to notify it about some debug/dump work it needs to do. And apparently receiving or handling such a signal (jumping into some stack dump function), when being in libaio codepath causes an issue.
So, while I would happily use ORADEBUG on a shadow process which is running some report which is stuck already (and about to be killed anyway), I never use ORADEBUG on critical background processes like LGWR, DBWR in production! Just in case. Well, the exception again is that if the instance is about to be killed anyway because of some serious issue, then why not dump some diagnostics info via oradebug first – if the instance crashes as a side effect, well, that’s what I was going to do next anyway.
As I explained in my old post, I consider the OS-level debuggers safer than ORADEBUG, as the OS-level debuggers do not alter the execution path of the measured process, they usually just suspend the target process very briefly and read whatever they need from its memory. However, if the tool uses the ptrace() system call against the target process (Linux) and not just read the /proc filesystem to access the other process private memory (Solaris), this may cause more issues if the target process is within a system call when the ptrace() is issued – it happens rarely, but it does happen. For example, I recently ran pstack on an Exadata cellsrv process during an experiment and it reported an error 11 – syscall failure – try again error (EAGAIN) in the alert log:
Sat Apr 27 12:20:13 2013 LinuxBlockIO: io_getevents err = -4, errno 11
Luckily the cellsrv was able to handle it and (probably) just tried again – but if such an error happens in your LGWR or CKPT process (when trying to write into a controlfile), you may end up with an instance crash.
So, both ORADEBUG and ptrace() based stack samplers (like Linux GDB and the pstack wrapper scripts) are a little dangerous.
Note that there’s a nice Oracle support tool called ProcWatcher, which can periodically sample process stacks too, but you should be careful with it as with certain configuration it may crash your process (or instance) for the abovementioned reasons. Here’s an excerpt from the ProcWatcher note:
Procwatcher: Script to Monitor and Examine Oracle DB and Clusterware Processes [ID 459694.1]
Disclaimer, especially if you are monitoring clusterware with EXAMINE_CLUSTER=true (default is false) or if FALL_BACK_TO_OSDEBUGGER=true (default is false): Most OS debuggers will temporarily suspend a process when attaching and dumping a stack trace. Procwatcher minimizes the amount of time that takes as much as possible. Some debuggers can also be CPU intensive. The THROTTLE,; IDLECPU, and INTERVAL parameters (see below) may need to be adjusted to suit your needs depending on how loaded the machine is and how fast it is. Note that some debuggers are faster and can get in and out of a process quicker than others. ; For example, pstack and oradebug short_stack are fast, ladebug is slower.
If you are on HP Itanium or HP-UX: Apply the fix for bug: 10158006 (or bug: 10287978 on 126.96.36.199) before monitoring the database with Procwatcher to fix a known short stack issue on HP. See Note: 1271173.1 for more information.
If you are on Solaris 10: Apply the fix for Solaris bt 6994922 ( see bug: 15677306 ) before monitoring the database with Procwatcher.
So what are the safe methods for sampling stack traces? The list that I mentioned in my original Debugger Dangers article, still applies.
But it’s worth mentioning OProfile (RHEL5 / 2.6.18 kernels) and the new Linux perf utility (Perf Events) which went mainstream in RHEL6 with 2.6.32 kernel. These tools don’t rely on ptrace() or /proc filesystem as they are using their own kernel modules & infrastructure and do not have to suspend or alter execution path of the examined process. So as long as you run the standard versions in your RHEL/OEL/CentOS linux distribution (and don’t compile your own kernel version etc), you should be safe. However, both of these tools only sample stacks of processes/threads that are on CPU, so if you’re trying to troubleshoot why is your LGWR hung, you’d still need to use other (and potentially less safe) means.
Anyway, the moral of this story is – don’t just run debugger or ptrace() based commands against critical background processes in production!
Update 1: Frits Hoogland has written a couple of articles about using Perf for measuring Oracle CPU usage.
Update 2: Brendan Gregg has written an article about performance overhead of ptrace() based tools like strace.
NB! If you want to move to the "New World" - offload your data and workloads to Hadoop, without having to re-write your existing applications - check out Gluent. We are making history! ;-)