oradebug

There are various ways in which one session can affect how another session behaves. One of the oldest and easiest is the oradebug utility, which you can take advantage of if your account has sysdba privileges. Many of the things you can do with oradebug you can also do by setting events, so some of the tricks listed in this section will reappear in different guises later on.

Suspending Processes

Some of the things I did to get a better understanding of dbwr and lgwr required those processes to stop. Oradebug allows you suspend and resume a process. The first thing to do is to attach to an Oracle process, which I tend to do by using the pid from v$process. There are many simple queries you can write to find the process id for a background process, and the following is an example that probably works across many versions of Oracle (though there are simpler options from 10g and later):

On the system I'm looking at right now this query returns the value 6, which I use as follows (warning: do not do this on a system that anyone else is using—you may find that the instance crashes, or that the process can't be made to resume):

Suspending lgwr is particularly risky. Don't forget that there may be some recursive SQL going on that will make sessions hang; for example, if you have database auditing enabled with audit connect, any ordinary end-user sessions trying to connect or disconnect will hang because their connection will try to commit after inserting or updating a row in sys.aud$.

One particular use I made of the suspend mechanism was to demonstrate that one of my long-held assumptions (that pmon detected local instance deadlocks) was wrong. I had actually made this observation in the original manuscript, and one of my reviewers pointed out that I was wrong—so I suspended pmon, set up a deadlock situation, and waited for the ORA-00060 "Deadlock detected" error; sure enough, even with pmon suspended, the deadlock was still trapped and reported within the (fairly standard) 3 seconds.

Dumps

You can use oradebug to dump memory structures to trace files. When I do this I usually connect as sys and then attach oradebug to my own process to generate the dumps. Once you're in oradebug, there is a command to show the trace file name, but it seems to work only after the first use of the trace file; however, you can modify the trace file name by setting the tracefile_identifier, and this counts as a first use:

If you want to see a list of all the available dumps, the command is oradebug dumplist. Many dump commands take two parameters—the name of the dump and the level at which to dump. The dump level then tends to follow two patterns: one is simply the higher the level the more that gets dumped; the other is a bitmap approach, where different powers of 2 result in different dumps. Table A-1 lists the dump commands I used in the course of writing this book. The effects are not consistent across versions; this table covers 10g+.

Peeking at Memory

There are two main strategies I use to examine the contents of particular memory locations: knowing the names and peeking at memory. If you know the name of an Oracle variable, you can use the dumpvar command to see its value. For example, I happen to know that there is a variable called kcbnhb (kcb is about the buffer cache, nhb is the number of hash buckets) in the SGA, so I can use oradebug as follows:

Generically, that's dumpvar {area} {variable}. So, for example, since I know that the current session value for use_stored_outlines is in a variable called ugauso_p, I can do the following:

If you know your hex and ASCII you will be able to see the text "RULE_BASED" in the last 12 bytes of the dump, starting with the length of 10 (0x000A).

If you need to find out the names of the variables in the SGA, you can query the structure x$ksmfsv (in this example I've picked a subset, but there are several thousand to choose from) and start making guesses about what the names and types mean:

Each row in x$ksmfsv holds the starting address of a variable (you will notice that the names—ksmfsnam—in this view have an underscore at the end, which you have to remove when you use them with the dumpvar option).

We could access the variables by name using dumpvar, but most of these variable are simple numbers (although one is a pointer to a number) so we have two other strategies to access them very easily. First, we can peek at the address—remembering to show that it is a hex value. For example, here's another way to check the number of hash buckets is

The peek command takes two mandatory parameters and one optional one. The first two are the address and length, the last is a flag to tell Oracle to dump the result to your trace file. In this example I've told Oracle to display 4 bytes (which is the minimum on this platform anyway) but not send the result to the trace file.

But if you've got the address, you can query memory directly. The structure x$ksmmem is indirectly a memory map of the SGA, so we can query the contents of memory 4 bytes at a time. Here's another check on the number of hash buckets:

You have to be very careful in the way you use x$ksmmem. If you use an address that isn't currently mapped, your session will crash with Oracle error ORA-03113, "end-of-file on communication channel," and the same will happen if your SQL manages to produce an execution path with any predicate that Oracle can't turn into addr = raw_value.

The option to query memory directly may seem fairly limited, but there aren't any good methods to query memory from, which is why some companies (and adventurous individuals) have written low-level programs that attach directly to Oracle's memory to read it. However, I did feel the need to check the content of the log buffer from time to time while writing this book, so when I wanted to look at it, peeking was a good enough start.

Here's a little SQL that will extract a few details about the public redo threads:

The first_buf_kcrfa and last_buf_kcrfa are the addresses of the first and last buffers (i.e., pages matching the log file block size) of the two public log buffers in my system. The pnext_buf_kcrfa is the address of the next buffer that will be used and then written to disk. With this information I could, with a little effort, and after getting some easily identifiable data into the log buffer, use oradebug to peek at the contents of the log buffer. Since each page in the log buffer is 512 bytes (which is more than Oracle will display onscreen) I would normally use oradebug peek {address} 512 1 to dump the data to the trace file one page at a time; but for the purposes of the book, here's a dump of the next few buffer pages, showing only the first 16 bytes of each page—which happen to be the header bytes for the page:

You will notice that the third word in each page (000002e9) evaluates to 745, which is the sequence# of my current redo log. The second word is the block id within the log file that this page of memory will be written to. (Unfortunately, I have no idea what the leading 00002201 represents.) I was more interested in checking the data, and cross-checking the raw dump of memory with the symbolic dump from the file—which we will be coming to shortly.