Oracle Scratchpad

Direct SGA Access – pt 4

Navigating the pointers

In part 3 of this series I talked about all the bits of information that would be needed to emulate a join between v$session and v$sql if we write a program to do a direct attach to the SGA. I’m now going to turn words into technical details and write the program.

Part 2 demonstrated a framework for attaching to the SGA and I’m going to take that framework and add a simple loop to it. I’m going to do a significant amount of cheating in this version of the progam because I’m going to prepare a couple of #include files that hold information that I’ve queried from the database.

Include files:

I’ve precreated 2 files; one is the list of addresses for the rows in v$session (or, rather, x$ksuse), the other is a list of constants (many of which are column offsets from x$kqfco)

Here’s the list of constants – in file ora_const.h

/*
        Assumed virtual memory locations for the fixed 
        and variable portions of the SGA. Should be checked
        against x$ksmssinfo
*/
#define FIXED     0x0000000060000000
#define VARIABLE  0x0000000061000000

/*
        Various column offsets for the x$ksuse table
        extracted from x$kqfco. The naming convention
        is {table}_{column}_OFFSET, where the x$ has
        been removed from the table name
*/

#define KSUSE_KSSPAFLG_OFFSET     0
#define KSUSE_KSUSEFLG_OFFSET  2184
#define KSUSE_KSUSESQL_OFFSET  2424
#define KSUSE_KSUSESCH_OFFSET  2440
#define KSUSE_KSUSEPSQ_OFFSET  2496
#define KSUSE_KSUSEPCH_OFFSET  2512

/*
        Offset within the x$kglob structure
        for the start of the SQL statement
        when the object is a parent cursor.
*/

#define KGLOB_SQLTEXT_OFFSET  448

Here’s an extract from my list of x$ksuse addresses in file sessions.h, followed by a simple SQL script (that has to be run by SYS at the CDB Root) to generate the file:

#define SESSIONS 704

/* Unsigned long int allows for 64 bits, needed for large address  spaces */

unsigned long int sessions[]={
         0x000000008E713678
        ,0x000000008E715E60
        ,0x000000008E718648
        ,0x000000008E71AE30
...
        ,0x000000008EDE8DA0
        ,0x000000008EDEB588
        ,0x000000008EDEDD70
};

rem
rem     Script:         gen_sessions_h.sql
rem     Author:         Jonathan Lewis
rem     Dated:          April 2022
rem
rem     Last tested
rem             19.11.0.0
rem
rem     Notes
rem     Has to be run by SYS on cdb$root to generate full
rem     list of x$ksuse addresses (addr) for sessions.h
rem

set tab off
set trimspool on
set pagesize 0
set feedback off

spool sessions.h

select '#define SESSIONS ' || count(*) from x$ksuse;

prompt   
prompt  /* Unsigned long int allows for 64 bits, needed for large address  spaces */
prompt
prompt unsigned long int sessions[]={
select 
        chr(9) ||
        case when rownum = 1 
                then ' 0x'  
                else ',0x' 
        end || addr
from    
        x$ksuse
;

select '};' from dual;


spool off

You’ll see that I’ve started the file with a constant that gives the size of the following array. I’ve declared the list of addresses as an array of “unsigned long int” which means 64 bit integers with the range 0x0 to 0xFFFFFFFFFFFFFFFF, which allows me to cast them to pointers or do arithmetic with them before casting to pointer. In any of the code I publish you’re likely to see a fairly common pattern (approached in three steps below):

Add an offset to an unsigned long int
                   (sessions[i]+KSUSE_KSUSEFLG_OFFSET)

Cast the unsigned long int to a pointer (in this case a pointer to an unsigned int i.e. 4 byte value)
  (unsigned int *) (sessions[i]+KSUSE_KSUSEFLG_OFFSET)

Dereference the pointer to see the value being pointed at
* (unsigned int *) (sessions[i]+KSUSE_KSUSEFLG_OFFSET)

For reference (mine as much as anyone else’s) Here’s a little reminder of the sizes of “integer” types in C. If you’re working your way through x$kqfco you’ll need to make sure you use the right type each time:

[unsigned] char         =>  8 bit / one byte
[unsigned] short int    => 16 bit / two bytes
[unsigned] int          => 32 bit / 4 bytes
[unsigned] long int     => 64 bit / 8 bytes

The first program draft

There are a few comments inline in this code explaining what I’ve done so I won’t repeat the details. One point I will make, though, is that I haven’t included a test corresponding to the filter predicates against ksuseflg and ksspaflg at this point. The other significant point is that I only print any results if the value of the pointer to the (ksusesql) parent cursor is not zero – this tends to eliminate problems dues to my failure to test the two flag columns, but I did run into two outstanding issues which I’ll mention in the notes following the code:

/*

        File:           show_sessions.c
        Dated:          April 2022
        Inspired by:    Kyle Hailey

        cc -o show_sessions  show_sessions.c

        Usage:
                ./show_sessions {Fixed Size shmid} {Variable Size shmid}
                ./show_sessions 29294599 29327369
*/

#include <stdio.h>
#include <stdlib.h>     
#include <sys/shm.h>    
#include "ora_const.h"
#include "sessions.h"

main(int argc, char **argv) {

        int     shmid[2];
        void    *addr_fix;
        void    *addr_var;

        int     i;

        if (argc != 3) {
                fprintf(stderr, "Usage: show_sessions {fixed shmid} {Variable shmid}\n");
                exit(EXIT_FAILURE);
        }

        shmid[0] = atoi(argv[1]);
        fprintf(stderr, "Fixed shmid:    %i\n", shmid[0]);

        addr_fix = shmat(shmid[0], (void *)FIXED, SHM_RDONLY );
        if (addr_fix != (void *)-1) {
                fprintf(stderr, "Fixed address:    %p %d \n", addr_fix, addr_fix);
        }
        else {
                fprintf(stderr, "Failed to attach Fixed size \n");
                exit(EXIT_FAILURE);
        }

        shmid[1] = atoi(argv[2]);
        fprintf(stderr, "Variable shmid: %i\n", shmid[1]);

        addr_var = shmat(shmid[1], (void *)VARIABLE, SHM_RDONLY );
        if (addr_var != (void *)-1) {
                fprintf(stderr, "Variable address: %p %d \n\n", addr_var, addr_var);
        }
        else {
                fprintf(stderr, "Failed to attach Variable size \n");
                exit(EXIT_FAILURE);
        }
        
/*
        This is the bit where we walk the list of session addresses
        and print the SQL text identified by the sql_address  column

        For each x$ksuse address (addr)
                Add the offset for ksusesql, convert to a "pointer to unsigned long int"
                if the value pointed at is zero there is no SQL
                otherwise print 
                        sid, session address, sql_address, 
                        address of cursor, address of SQL text within cursor
                then print the SQL text
*/
        for (i = 0 ; i < SESSIONS ; i++){

                if  (*(unsigned long int *) (sessions[i] + KSUSE_KSUSESQL_OFFSET)) {
                        fprintf(
                                stdout,
                                "SID: %4i, saddr: %16p, sql_address: %16p, cursor address: %16p, text address: %16p\n",
                                i + 1,
                                sessions[i],
                                sessions[i] + KSUSE_KSUSESQL_OFFSET,
                                *(unsigned long int *) (sessions[i] + KSUSE_KSUSESQL_OFFSET),
                                *(unsigned long int *) (sessions[i] + KSUSE_KSUSESQL_OFFSET) + KGLOB_SQLTEXT_OFFSET
                        );
                        fprintf(
                                stdout,
                                "%s\n\n",
                                (char *)(*(unsigned long int *) (sessions[i] + KSUSE_KSUSESQL_OFFSET) + KGLOB_SQLTEXT_OFFSET)
                        );
                }
        }

        fprintf(stderr, "Detaching Fixed size:    %i\n", shmdt(addr_fix));
        fprintf(stderr, "Detaching Variable size: %i\n", shmdt(addr_var));

        exit(EXIT_SUCCESS);
}

Here’s a sample of the output – showing one of the two problems:

[oracle@linux183 trace]$ /mnt/working/direct_attach/show_sessions 29294599 29327369
Fixed shmid:    29294599
Fixed address:    0x60000000 1610612736 
Variable shmid: 29327369
Variable address: 0x61000000 1627389952 

SID:  200, saddr:       0x8e904170, sql_address:       0x8e904ae8, cursor address:       0x7b5c72d8, text address:       0x7b5c7498
BEGIN /* KSXM:FLUSH DML_MON */    dbms_stats_internal.gather_scan_rate_by_mmon;  END;

SID:  367, saddr:       0x8eaa4f68, sql_address:       0x8eaa58e0, cursor address:       0x7b9b4088, text address:       0x7b9b4248
insert into smon_scn_time (thread, time_mp, time_dp, scn, scn_wrp, scn_bas, num_mappings, tim_scn_map) values (0, :1, :2, :3, :4, :5, :6, :7)

SID:  370, saddr:       0x8eaac720, sql_address:       0x8eaad098, cursor address:       0x7c3084e0, text address:       0x7c3086a0
0^4bcfb975

Problem number 1 is the odd output for SID 370 – what is that strange thing that looks almost like a miniature hex dump instead of text? The answer might lie in a simple SQL investigation of session 370:

SQL> select sid, state, status, program, sql_id from V$session where sid = 370;
       370 WAITING	       ACTIVE	oracle@linux183.localdomain (Q001)		 01uy9sb7w8a9g

SQL> select sql_text from V$sql where sql_id = '01uy9sb7w8a9g';

no rows selected

SQL> select sid, state, status, program, sql_id from V$session where program like '%Q00%';
	23 WAITING	       ACTIVE	oracle@linux183.localdomain (Q002)
       370 WAITING	       ACTIVE	oracle@linux183.localdomain (Q001)		 01uy9sb7w8a9g

The session is a Qnnn (AQ slave) session and it has an SQL_ID recorded as “current” for an SQL_ID that doesn’t exist in the shared pool. Looking for all the other Qnnn processes I get the feeling that maybe there’s a bug in the house-keeping that takes place when the Qnnn slaves have done whatever it was they were supposed to do. This particular session had been waiting on “Streams AQ: waiting for time management” for the last three days and when I queried x$kglob for anything with the related kglhdpar (sql_address) rather than SQL_ID, there were no rows matching, and when I peeked the address the contents looked nothing like a cursor object.

The second problem is one that was predictable – my program doesn’t use latches, and I’m reading memory locations which other processes may be changing. Inevitably there will be moments when the thing I read is in-flux. Every now and again when testing the code as another session was executing dbms_stats.gather_database_stats(degree=>4) the code would “hang” for a few seconds then report:

Segmentation fault (core dumped)

Possibly this problem will go away (or be reduced) when I include a test for the ksspaflg and ksuseflg but you shouldn’t be surprised that it happens in this environment. The program was crashing when trying to report the SQL Text but had already reported the previous line with the session identifier and list of addresses, and a call to the program moments later would report the same session and its SQL with no problem at all.

This symptoms do suggest an in-flux (or “read-consistency”) problem, so perhaps I run a short loop with microsecond sleeps until the pointer gives the same values twice, doing something similar to test the first few bytes of the SQL text, and that might be enough to solve the problem. (Otherwise I have to find out how to trap and “when others” the segmentation fault!)

Comming next: adding a few more columns from x$ksuse to the output, starting with interpretations of some of the column types reported in x$kqfco.

1 Comment »

  1. Thanks for posting this Jonathan!!! I’ve been experimenting with Kyle’s code and finally decided to get back into internals. I have been struggling to get it to work on things in the variable area. Looking forward to trying out your code and comparing it to mine to see what I have been mis understanding.

    Comment by Mark Adams — May 16, 2022 @ 5:17 am BST May 16,2022 | Reply


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: