Oracle Scratchpad

Direct SGA Access – pt 1

Outline, assumptions, and (current) limitations

The question I’m going to address is a fairly open one: “How can you see the SQL statement each session is currently running using direct SGA access from a C program?”

Without a clearer statement of the question it would be easy to start chasing after a problem that’s much harder than it needs to be. A suitable counter-question would be: “Where are you starting from?”

If you want to write a program that can be installed once and “just work” against whichever version of Oracle you’re running without needing a privileged user to feed it with some starting data it will be much harder to write the program, and the problem of chasing pointers around the SGA will be much more complex.

On the other hand, if you want to write a program (which you compile yourself) that you can run against a database you are responsible for and to which you already have a high level of privileged access then you could extract the driving information that the program needs with a few simple calls to the database. The extract could then be supplied to the program either as an “include” file at compile time or as a file to be read at startup, reducing the complexity of the program to little more than attaching to Oracle’s shared memory and jumping directly around a few “known” memory addresses. (Of course you still have to arrange for the program to have the necessary privilege to attach to and read the shared memory segments.)

You might ask, of course, why you want a C program to pick its way through a directly attached SGA when you already have the privilege to query the instance. But there is an argument that even if you can find the information using standard SQL methods, the overheads are (relatively speaking) significant and the direct attach strategy will allow for more frequent sampling with a lower impact on resources.

In these notes I have absolutely no intention of going into a completely generalised “just run it” design, but I will talk around some of the things you would need to do to get there; and I can’t resist the temptation of poking (or PEEKing) around a little more than is necessary to create the simplest possible solution that could work in the hands of a careful DBA.

Using the database

Rather than diving straight into problem solving, let’s start addressing the requirement by asking how we would approach the problem if we were connected to the database in the SYS schema.

We would write a query that joined v$session to v$sql (or maybe v$sqlarea, v$sqlstats, etc.) on the sql_id – and if we really wanted nothing more that the SQL we would restrict the result to just one child (which we could do by including the child number in the join). For example:

select
        se.sid, se.username, sq.sql_fulltext
from
        v$session       se,
        v$sql           sq
where
        sq.sql_id = se.sql_id
and     sq.child_number = se.sql_child_number
/

If we don’t want to connect to the database how do we translate this query into a series of steps that we could write into a C program.

How the instance works

As an Oracle instance starts up it creates several shared memory segments for the SGA, the redo buffers, etc. The oracle executable itself contains instructions on how to setup a number of structures of the size dictated by the instance startup parameters and to construct a “blueprint” describing the shape and size of all the memory structures that it has set up.

A key feature of this blueprint is that for any very specific version of Oracle on any specific platform the blueprint itself is always the same shape and size. This means that if your development system was on the same type of hardware and O/S, running the same version of Oracle you could dump the blueprint in development and use it to learn how to walk the SGA on the production system. Actual values stored in the blueprint will be different, of course, and the sizes of structures that the blueprint points to (and the values of those pointers) will vary according to the startup parameters, but the set of “pigeonholes” will always be the same.

If we can access the database we can see this blueprint either by querying the internal structure x$ksmfsv, or by using oradebug (in one of two different ways) to dump a trace file holding the data.

From the blueprint (which I’ll now refer to as x$ksmfsv) we can find an entry point to the structure that Oracle exposes as v$session. In fact this dynamic performance view is defined as a join of three such structures x$ksuse, x$kslwt (v$session_wait) and x$ksled (v$event_name), but the one we want is x$ksuse and with a little effort we can find it and step through it.

x$ksuse is a segmented array of fixed size rows, so the next thing we have to do is find a way of interpreting the bytes in each row so that (a) we can identify which rows correspond to the active sessions we can see in v$session and (b) identify the bytes in each row that will point us to (the internal equivalent of) the v$sql entry that we’re interested in.

Fortunately there is a way to see most of the column definitions of any of the x$ structures if we can query a structure called x$kqfco; and that’s another chunk of data that we might want to query from an instance of Oracle to prime our program.

The method we use to get to v$sql (or x$kglob as it is ultimately identifiable) is also going to take a little thought. We can’t emulate the join on sql_id, because internally Oracle uses that method to identify a library cache hash bucket and then walk a library cache chain. For our purposes we will need to learn how to use (the equivalent of) the sql_address to get to a single row in x$kglob and find the piece that represents the sql_fulltext.

So, assuming we can find and attach to the shared memory segments, we need to design into our program something that make some of the contents of both x$ksmfsv and x$kqfco available to the program, either as an “include” file, or as a text file that can be read as the program starts.

If we have to do that you might ask why we don’t also take the easy option for setting up the x$ksuse array in the program by running a query like: “select indx, addr from x$ksuse order by indx” and making the result available to the progam. There’s an important difference, though: the first two x$ structures are fixed (for the version of Oracle you’re running), the results of querying x$ksuse could change every time you restart the instance – especially if you’ve changed some (apparently unrelated) parameters.

In part 2 of this series I’ll give more detail about the shared memory segments that Oracle creates, and how we can access information about them and attach to them.

Leave a Comment »

No comments yet.

RSS feed for comments on this post.

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

%d bloggers like this: