The Necessary Ingredients | Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning (Osborne ORACLE Press Series)

Oracle SGA is nothing more than a large and rapidly changing piece of memory structure in the eyes of an external program. From here on we will show you how to access the X$KSUSECST structure externally. The X$ KSUSECST structure provides the information for the V$SESSION_WAIT view. The program needs to have the following information before it can successfully access the SGA contents:

The shared memory identifier ( shmid ), also known as the SGA ID
The SGA base address
The starting address of X$KSUSECST
The record size of the X$KSUSECST structure
The number of records in the X$KSUSECST structure
The X$KSUSECST view columns offsets

Find SGA ID

The SGA ID can be obtained from the trace file of an IPC dump using oradebug as follows .

 SQL> oradebug setmypid 
 Statement processed. 
 
 SQL> oradebug ipc 
 Information written to trace file. 
 
 SQL> oradebug tracefile_name 
 /oracle/admin/REPOP/udump/repop_ora_19246.trc

The following is a snippet of the IPC dump trace file. The SGA ID can be found under the heading shmid (shared memory ID).

 Area #0 `Fixed Size' containing Subareas 0-0 
 Total size 0000000000044578 Minimum Subarea size 00000000 
 Area  Subarea  Shmid  Stable Addr      Actual Addr 
 0        0  1027  0000000080000000 0000000080000000 
 Subarea size     Segment size 
 0000000000046000 000000000e406000 
 Area #1 `Variable Size' containing Subareas 1-1 
 Total size 000000000d000000 Minimum Subarea size 01000000 
 Area  Subarea    Shmid      Stable Addr      Actual Addr 
 1        1     1027 0000000080046000 0000000080046000 
 Subarea size     Segment size 
 000000000dfba000 000000000e406000 
 Area #2 `Redo Buffers' containing Subareas 2-2 
 Total size 0000000000404000 Minimum Subarea size 00000000 
 Area  Subarea    Shmid      Stable Addr      Actual Addr 
 2        2     1027 000000008e000000 000000008e000000 
 Subarea size     Segment size 
 0000000000404000 000000000e406000 
 Area #3 `skgm overhead' containing Subareas 3-3 
 Total size 0000000000002000 Minimum Subarea size 00000000 
 Area  Subarea    Shmid      Stable Addr      Actual Addr 
 3        3     1027 000000008e404000 000000008e404000 
 Subarea size     Segment size 
 0000000000002000 000000000e406000

Find SGA Base Address

The SGA base address can be discovered by querying the X$KSMMEM (kernel service memory management SGA memory) view. This view contains the physical memory address and value of every memory location in the SGA. This is essentially the map of the entire SGA. As such, don ‚ t be surprised if this view returns tens of millions of rows. For the purpose of direct SGA sampling, the most important piece of information from this view is the SGA base address, also known as the SGA starting address. Following is the structure of the X$KSMMEM view and an example of how to get the SGA base address. Based on the example, the SGA base address is 0x80000000.

 Name           Null?    Type 
 -------------- -------- ------------ 
 ADDR                    RAW(4) 
 INDX                    NUMBER 
 INST_ID                 NUMBER 
 KSMMMVAL                RAW(4) 
 
 select * from X$KSMMEM where indx = 0; 
 ADDR           INDX    INST_ID KSMMMVAL 
 -------- ---------- ---------- -------- 
 80000000          0          1 00

Find the Starting Address of X$KSUSECST

The V$SESSION_WAIT view is built on the X$KSUSECST (kernel service user session current status) view. The full definition of the V$SESSION_WAIT view can be obtained from the V$FIXED_VIEW_DEFINITION view. The V$SESSION_WAIT (or X$KSUSECST) view provides fine-grain performance data, which is very useful for performance diagnostics and hang analyses. The information in the view changes rapidly, making it a perfect candidate for high speed sampling by an external program. The starting address of the X$KSUSECST structure in memory can be discovered as follows. According to the example, the X$KSUSECST starting address is 0x861B2438.

 SQL> select min(addr) from x$ksusecst; 
 MIN(ADDR 
 -------- 
 861B2438

Find the Record Size of the X$KSUSECST Structure

The size of a record in the X$KSUSECST structure can be determined from the starting address of any two records that are next to each other. The data type of the ADDR column is RAW and the data is in hexadecimal. You must convert the data into decimal notation and perform the calculation. An example is given here:

 select addr 
 from   (select addr from x$ksusecst order by addr) a 
 where rownum < 3; 
 
 ADDR 
 -------- 
 861B2438 
 861B2D50 
 -- 861B2438 Hex = 2249925688 decimal 
 -- 861B2D50 Hex = 2249928016 decimal 
 -- The record size is 2249928016  2249925688 =  2328   bytes

Find Number of Records in the X$KSUSECST Structure

The X$ views are C structures, and the number of records in each structure is set by a kernel variable, which gets its value from an init.ora parameter. The value of the init.ora parameter may be explicitly set by the DBA or derived from other init.ora parameters. Few structures have operating system- or version-dependent record counts. For our purpose, the number of records in the X$KSUSECST structure is set by the SESSIONS initialization parameter. The default value of SESSIONS is (1.1 * PROCESSES) + 5. If the SESSIONS parameter is explicitly set and the value is higher than the default, then Oracle will use the higher value; otherwise the default value will be used. Another way to find the number of records in the X$KSUSECST structure is simply by querying the X$KSUSECST view and counting the number of rows in it.

 SQL> show parameter sessions 
 NAME_COL_PLUS_SHOW_PARAM       TYPE        VALUE_COL_PLUS_SHOW_PARAM 
 ------------------------------ ----------- ---------------------------- 
 . . . 
 sessions                       integer     300 
 . . . 
 
 SQL> show parameter processes 
 NAME_COL_PLUS_SHOW_PARAM       TYPE        VALUE_COL_PLUS_SHOW_PARAM 
 ------------------------------ ----------- ---------------------------- 
 . . . 
 processes                      integer     200 
 
 SQL> select count(*) from x$ksusecst; 
 COUNT(*) 
 ---------- 
 300

Find the X$KSUSECST View Columns Offsets

Finally, you need to find the offset of each column that is in the X$KSUSECST view beginning from the starting memory location of the view. You can get this information from the X$KQFCO (kernel query fixed tables column definitions) and X$KQFTA (kernel query fixed tables) views.

The X$KQFCO view can be considered as the data dictionary of the fixed tables. This view contains the column definitions of every X$ view, but it does not contain the fixed table names. The two important pieces of information you need from this view are the column name and the column offset, which is the starting address of the column in the memory. Without the column name and the column offset, you will not be able to access the SGA externally. The fixed table names can be obtained from the X$KQFTA view. An example of the query and its output is given next:

 SQL> desc x$kqfco 
 Name           Null?    Type 
 -------------- -------- ------------ 
 ADDR                    RAW(4) 
 INDX                    NUMBER 
 INST_ID                 NUMBER 
 KQFCOTAB                NUMBER 
 KQFCONAM                VARCHAR2(30) 
 KQFCODTY                NUMBER 
 KQFCOTYP                NUMBER 
 KQFCOMAX                NUMBER 
 KQFCOLSZ                NUMBER 
 KQFCOLOF                NUMBER 
 KQFCOSIZ                NUMBER 
 KQFCOOFF                NUMBER 
 KQFCOIDX                NUMBER 
 KQFCOIPO                NUMBER 
 
 select a.kqftanam fixed_table_name, 
 b.kqfconam column_name, 
 b.kqfcooff column_offset, 
 b.kqfcosiz column_size 
 from   x$kqfta a, x$kqfco b 
 where  a.indx     = b.kqfcotab 
 and    a.kqftanam = 'X$KSUSECST' 
 order by b.kqfcooff; 
 
 FIXED_TABLE_NAME COLUMN_NAM COLUMN_OFFSET COLUMN_SIZE 
 ---------------- ---------- ------------- ----------- 
 X$KSUSECST       ADDR                   0           4 
 X$KSUSECST       INDX                   0           4 
 X$KSUSECST       KSUSEWTM               0           4 
 X$KSUSECST       INST_ID                0           4 
 X$KSUSECST       KSSPAFLG               1           1 
 X$KSUSECST       KSUSSSEQ            1276           2 
 X$KSUSECST       KSUSSOPC            1278           2 
 X$KSUSECST       KSUSSP1             1280           4 
 X$KSUSECST       KSUSSP1R            1280           4 
 X$KSUSECST       KSUSSP2             1284           4 
 X$KSUSECST       KSUSSP2R            1284           4 
 X$KSUSECST       KSUSSP3             1288           4 
 X$KSUSECST       KSUSSP3R            1288           4 
 X$KSUSECST       KSUSSTIM            1292           4 
 X$KSUSECST       KSUSENUM            1300           2 
 X$KSUSECST       KSUSEFLG            1308           4 
 16 rows selected.

Do you wonder why some columns have an offset of 0? This shows that the column value is derived and not stored in the SGA. For example, the ADDR column of all fixed tables has an offset of 0 because it is a pointer to a memory location, and it is not stored in the SGA as a value. Similarly, the columns INST_ID, INDX, and KSUSEWTM also have an offset of 0 because their values are derived.

You may also notice that some columns share the same memory address. This means the columns share the same data, but the data may be reported in different formats or notations. For example, the KSUSSP1 and KSUSSP1R columns are associated with the P1 and P1RAW columns of the V$SESSION_WAIT view. The P1 column reports the value in the decimal notation, while the P1RAW column reports the same value in hexadecimal notation.