X$MESSAGES hidden columns

Sometimes X$ tables might have seemingly unused gaps in a row. Here is an example with X$MESSAGES:

SQL> select kqftarsz
  2    from x$kqfta
  3   where kqftanam = 'X$MESSAGES'
  4  /

  KQFTARSZ
----------
        32

SQL> select c.kqfconam column_name,
  2         c.kqfcodty datatype,
  3         c.kqfcosiz size_byte,
  4         c.kqfcooff offset
  5    from x$kqfta t,
  6         x$kqfco c
  7   where t.kqftanam = 'X$MESSAGES'
  8     and c.kqfcotab = t.indx
  9   order by c.indx
 10  /

COLUMN_NAME   DATATYPE  SIZE_BYTE     OFFSET
----------- ---------- ---------- ----------
ADDR                23          8          0
INDX                 2          4          0
INST_ID              2          4          0
CON_ID               2          2          0
DESCRIPTION          1         64          8
DEST                 1         64         16

6 rows selected.

SQL> desc x$messages
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 CON_ID                                             NUMBER
 DESCRIPTION                                        VARCHAR2(64)
 DEST                                               VARCHAR2(64)

Having looked carefully at the output above, several discrepancies can be noticed:

• How come the row size is 32 bytes whereas there are only two non-generated columns of 8 bytes each (DESCRIPTION and DEST, which are pointers)?
• If the row size is 32 bytes, and DESCRIPTION and DEST starts at offsets 8 and 16 correspondingly, what data is in bytes 0-7 and 24-31?

Based on the fact that X$MESSAGES takes its data from ksbsdt (see X$ tables: starting address), the structure itself can be inspected in case it can help connect the dots.

[oracle@rac1 bin]$ readelf -s oracle | grep -w ksbsdt
203009: 00000000152e5760 16992 OBJECT  GLOBAL DEFAULT   17 ksbsdt
218757: 00000000152e5760 16992 OBJECT  GLOBAL DEFAULT   17 ksbsdt
[oracle@rac1 bin]$ objdump -s --start-address=0x00000000152e5760 --stop-address=$((0x00000000152e5760+16992)) oracle

oracle:     file format elf64-x86-64

Contents of section .rodata:
 152e5760 203eee00 00000000 48f12e15 00000000   >......H.......
 152e5770 f818be13 00000000 11000000 00000000  ................
 152e5780 d04aee00 00000000 60f12e15 00000000  .J......`.......
 152e5790 f818be13 00000000 01000000 00000000  ................
 152e57a0 f0b11307 00000000 78f12e15 00000000  ........x.......
 152e57b0 f818be13 00000000 00000000 00000000  ................
 152e57c0 80043401 00000000 90f12e15 00000000  ..4.............
 152e57d0 f818be13 00000000 05000000 00000000  ................
 152e57e0 f0162707 00000000 acf12e15 00000000  ..'.............
 152e57f0 c0b28f15 00000000 00000000 00000000  ................
...

It can be seen that there is a symbol at byte 0 and some number at byte 24; byte 8 and byte 16 has expected pointers:

[oracle@rac1 bin]$ readelf -s oracle | grep 00ee3e20
 30104: 0000000000ee3e20  1344 FUNC    GLOBAL DEFAULT   13 ksl_pdb_event_stats_exten
322705: 0000000000ee3e20  1344 FUNC    GLOBAL DEFAULT   13 ksl_pdb_event_stats_exten
[oracle@rac1 bin]$ objdump -s --start-address=0x152ef148 --stop-address=$((0x152ef148+32)) oracle

oracle:     file format elf64-x86-64

Contents of section .rodata:
 152ef148 70646220 6576656e 74207374 61747320  pdb event stats
 152ef158 61637469 6f6e0000 57616974 204f7574  action..Wait Out
[oracle@rac1 bin]$ objdump -s --start-address=0x13be18f8 --stop-address=$((0x13be18f8+32)) oracle

oracle:     file format elf64-x86-64

Contents of section .rodata:
 13be18f8 47454e30 00000000 4c4d4643 00000000  GEN0....LMFC....
 13be1908 44425730 00000000 4c434b31 00000000  DBW0....LCK1....
...

Thus, the missing columns can be obtained using another tool that I wrote for such cases - bide (binary data extractor):

[oracle@rac1 src]$ bide dump-table ksbsdt --format func:symbol description:string dest:string L
+----------------------------------------+-----------------------------------------------------------------+------------------------------------+-----------+
| func                                   | description                                                     | dest                               | _unnamed3 |
+----------------------------------------+-----------------------------------------------------------------+------------------------------------+-----------+
| ksl_pdb_event_stats_extend             | pdb event stats action                                          | GEN0                               |        17 |
| kslwo_compute_sys_thresholds_bg_action | Wait Outlier thresholds                                         | GEN0                               |         1 |
| kslwo_process_sys_wait_bg_action       | Process wait outlier                                            | GEN0                               |         0 |
| ksuguid_mac_update_act                 | KSU GUID MAC Address update                                     | GEN0                               |         5 |
| ksu_inc_sos_param                      | session param update action                                     | GEN1                               |         0 |
| ksu_inc_sos                            | session update action                                           | GEN2                               |         1 |
| kstmUpdateTranslationCbk               | Update KGSTM Translation                                        | MMON                               |         1 |
| ksustaen_init_act                      | event nfy timeout action                                        | GEN0                               |         8 |
| ksbmsg                                 | KSB action for X-instance calls                                 | *                                  |         2 |
| ksbckbast                              | KSB action for bast checking                                    | *                                  |         1 |
| ksbsht                                 | generic shutdown background                                     | *                                  |         0 |
...

Comparing it with the data seen in the database, it can be concluded that not only do we have a description of each "message" and a process presumably handling it, but also we have an underlying function:

SQL> select * from x$messages;

ADDR                   INDX    INST_ID     CON_ID DESCRIPTION                                                      DEST
---------------- ---------- ---------- ---------- ---------------------------------------------------------------- -----
00000000152E5760          0          1          0 pdb event stats action                                           GEN0
00000000152E5780          1          1          0 Wait Outlier thresholds                                          GEN0
00000000152E57A0          2          1          0 Process wait outlier                                             GEN0
00000000152E57C0          3          1          0 KSU GUID MAC Address update                                      GEN0
00000000152E57E0          4          1          0 session param update action                                      GEN1
00000000152E5800          5          1          0 session update action                                            GEN2
00000000152E5820          6          1          0 Update KGSTM Translation                                         MMON
00000000152E5840          7          1          0 event nfy timeout action                                         GEN0
00000000152E5860          8          1          0 KSB action for X-instance calls                                  *
00000000152E5880          9          1          0 KSB action for bast checking                                     *
00000000152E58A0         10          1          0 generic shutdown background                                      *
...

This allows one to execute those actions by calling the respective function. It also explains what is stored in bytes 0-7 and 24-31 of X$MESSAGES rows.

X$ tables: starting address

The starting address of X$ tables makes sense for tables residing in SGA or defined within the Oracle binary. It is usually found by querying an X$ table. This blog post describes an alternative method of obtaining the starting address by calling an Oracle C function.

The Oracle function that can be used here is qerfxArrayMaxSize. Here is a short GDB script demonstrating its usage:

set pagination off
set trace-commands on

set $ctx = (int *)malloc(4)

def x_desc
  printf "result = 0x%x\n", (int)qerfxArrayMaxSize($arg0, (int)strlen($arg0), (int *)$ctx)
  printf "start_addr = 0x%x\n", *(long *)($ctx+18)
  info symbol *(int *)($ctx+18)
  p/a (long[10])(**(long *)($ctx+18))
  printf "row_count = %d\n", *(int *)($ctx+20)
end

x_desc "X$KSUPR"
x_desc "X$KSUSE"
x_desc "X$MESSAGES"

The three tables are going to be examined in separate sections below: X$KSUPR, X$KSUSE, X$MESSAGES.

X$KSUPR

The GDB output:

+x_desc "X$KSUPR"
++printf "result = 0x%x\n", (int)qerfxArrayMaxSize("X$KSUPR", (int)strlen("X$KSUPR"), (int *)$ctx)
result = 0x9a537260
++printf "start_addr = 0x%x\n", *(long *)($ctx+18)
start_addr = 0x9a537260
++info symbol *(int *)($ctx+18)
No symbol matches *(int *)($ctx+18).
++p/a (long[10])(**(long *)($ctx+18))
$1 = {0x9a588070, 0x9a5895e0, 0x9a58ab50, 0x9a58c0c0, 0x9a58d630, 0x9a58eba0, 0x9a590110, 0x9a591680, 0x9a592bf0, 0x9a594160}
++printf "row_count = %d\n", *(int *)($ctx+20)
row_count = 600

The SQL*Plus output:

SQL> select addr from x$ksupr where rownum<=10;

ADDR
----------------
000000009A588070
000000009A5895E0
000000009A58AB50
000000009A58C0C0
000000009A58D630
000000009A58EBA0
000000009A590110
000000009A591680
000000009A592BF0
000000009A594160

10 rows selected.

Thus, the result of the function is a pointer to an array storing pointers to X$KSUPR rows (0x9a588070, 0x9a5895e0, 0x9a58ab50, etc.). The function also conveniently returns the number of rows of the relevant fixed array:

++printf "row_count = %d\n", *(int *)($ctx+20)
row_count = 600

SQL> select count(*) from x$ksupr;

  COUNT(*)
----------
       600

SQL> select value from v$parameter where name='processes';

VALUE
--------------------------------------------------------------------------------
600

X$KSUSE

The GDB output:

+x_desc "X$KSUSE"
++printf "result = 0x%x\n", (int)qerfxArrayMaxSize("X$KSUSE", (int)strlen("X$KSUSE"), (int *)$ctx)
result = 0x39c
++printf "start_addr = 0x%x\n", *(long *)($ctx+18)
start_addr = 0x9ae5a808
++info symbol *(int *)($ctx+18)
No symbol matches *(int *)($ctx+18).
++p/a (long[10])(**(long *)($ctx+18))
$2 = {0x9a8ec740, 0x9a8eef28, 0x9a8f1710, 0x9a8f3ef8, 0x9a8f66e0, 0x9a8f8ec8, 0x9a8fb6b0, 0x9a8fde98, 0x9a900680, 0x9a902e68}
++printf "row_count = %d\n", *(int *)($ctx+20)
row_count = 924

This time around the function returns the number of rows (0x39c=924) of the corresponding array.

The SQL*Plus output:

SQL> select addr from x$ksuse where rownum<=10;

ADDR
----------------
000000009A8EC740
000000009A8EEF28
000000009A8F1710
000000009A8F3EF8
000000009A8F66E0
000000009A8F8EC8
000000009A8FB6B0
000000009A8FDE98
000000009A900680
000000009A902E68

10 rows selected.

SQL> select count(*) from x$ksuse;

  COUNT(*)
----------
       924

SQL> select value from v$parameter where name='sessions';

VALUE
--------------------------------------------------------------------------------
924

X$MESSAGES

The GDB output:

+x_desc "X$MESSAGES"
++printf "result = 0x%x\n", (int)qerfxArrayMaxSize("X$MESSAGES", (int)strlen("X$MESSAGES"), (int *)$ctx)
result = 0x212
++printf "start_addr = 0x%x\n", *(long *)($ctx+18)
start_addr = 0x152e5760
++info symbol *(int *)($ctx+18)
ksbsdt in section .rodata of /u01/app/oracle/product/19.3.0/dbhome_1/bin/oracle
++p/a (long[10])(**(long *)($ctx+18))
$3 = {0xee3e20 <ksl_pdb_event_stats_extend>, 0x152ef148, 0x13be18f8, 0x11, 0xee4ad0 <kslwo_compute_sys_thresholds_bg_action>, 0x152ef160, 0x13be18f8, 0x1, 0x713b1f0 <kslwo_process_sys_wait_bg_action>, 0x152ef178}
++printf "row_count = %d\n", *(int *)($ctx+20)
row_count = 530

The output above is very different from both X$KSUPR and X$KSUSE - the starting address is the actual address of the ksbsdt structure defined in the Oracle binary:

[oracle@rac1 bin]$ readelf -s oracle | grep 152e5760
203009: 00000000152e5760 16992 OBJECT  GLOBAL DEFAULT   17 ksbsdt
218757: 00000000152e5760 16992 OBJECT  GLOBAL DEFAULT   17 ksbsdt

The SQL*Plus output:

SQL> select addr from x$messages where rownum<=10;

ADDR
----------------
00000000152E5760
00000000152E5780
00000000152E57A0
00000000152E57C0
00000000152E57E0
00000000152E5800
00000000152E5820
00000000152E5840
00000000152E5860
00000000152E5880

10 rows selected.

SQL> select count(*) from x$messages;

  COUNT(*)
----------
       530

Conclusion

The qerfxArrayMaxSize function can be used to determine the starting address of X$ tables residing in SGA or Oracle binary. The return value of the function is not very consistent: the function returned the starting address for X$KSUPR and the number of rows for X$KSUSE/X$MESSAGES. It might be the case that the function returns the void type and the results are inconsistent because we are just examining $rax. By contrast, the memory area defined by the third parameter always stores the starting address and the number of rows for the tables I tested it with. I consider it is reliable for these types of tables (non-UGA/non-PGA based). I am disposed to think that X$KQFTA.KQFTATYP and maybe KQFTAFLG determine whether it is an SGA/PGA/UGA/Oracle binary based table:

SQL> select kqftanam, kqftatyp, kqftaflg
  2    from x$kqfta
  3   where kqftanam in ('X$KSUPR', 'X$KSUSE', 'X$MESSAGES')
  4  /

KQFTANAM                         KQFTATYP   KQFTAFLG
------------------------------ ---------- ----------
X$KSUSE                                 2          1
X$KSUPR                                 2          0
X$MESSAGES                              1          0

However, more experiments need to be conducted to confirm that. I initially started looking at this because I found that I could not determine the static structure behind X$MESSAGES using my xinfo tool:

[oracle@rac1 ~]$ xinfo list 'X$MESSAGES' --with-kqftap
+------------+-----+------------+------------+-----------------+---------+-----+-----+-----+-----+-----------------------------------------------------+
|        obj | ver |    nam_ptr | nam        | xstruct_nam_ptr | xstruct | typ | flg | rsz | coc | kqftap                                              |
+------------+-----+------------+------------+-----------------+---------+-----+-----+-----+-----+-----------------------------------------------------+
| 4294950992 |   3 | 0x14b845e0 | X$MESSAGES |      0x14b845ec | ksbsd   |   1 |   0 |  32 |   6 | {'xstruct_ptr': '0x14a26180', 'xstruct': 'ksbsd_c'} |
+------------+-----+------------+------------+-----------------+---------+-----+-----+-----+-----+-----------------------------------------------------+

There are structures such as ksbsd/ksbsd_c but there is no explicit ksbsdt. qerfxArrayMaxSize can be used to identify this missing structure.

Computing ORA_HASH

I wrote a short Python function to compute a hash value for a given expression. The return value of the function matches the return value of ORA_HASH on inputs I tested it with in Oracle 23c on Linux x86-64.

In its simplest form, the invocation can be as follows:

>>> ora_hash(b'test')
2662839991

SQL> select ora_hash('test');

ORA_HASH('TEST')
----------------
      2662839991

Handling non-string datatypes

In case of a non-string datatype, some conversion should be performed. ORA_HASH accepts a variety of datatypes. I use NUMBER and DATE in the examples below. The procedure is essentially the same with both datatypes: need to take the internal representation of a value in Oracle and pass it to Python. It should be possible to handle any other supported datatypes following the same procedure.

NUMBER

SQL> select ora_hash(2023);

ORA_HASH(2023)
--------------
    2671887358

SQL> select dump(2023, 16);

DUMP(2023,16)
---------------------
Typ=2 Len=3: c2,15,18

>>> ora_hash(b'\xc2\x15\x18')
2671887358

DATE

SQL> select ora_hash(to_date('2023-03-01 12:34:56', 'yyyy-mm-dd hh24:mi:ss'));

ORA_HASH(TO_DATE('2023-03-0112:34:56','YYYY-MM-DDHH24:MI:SS'))
--------------------------------------------------------------
                                                     112410422
                                                     
SQL> select dump(to_date('2023-03-01 12:34:56', 'yyyy-mm-dd hh24:mi:ss'), 16);

DUMP(TO_DATE('2023-03-0112:34:56
--------------------------------
Typ=13 Len=8: e7,7,3,1,c,22,38,0

>>> ora_hash(b'\xe7\x07\x03\x01\x0c\x22\x38\x00')
112410422

Specifying max_bucket and seed_value

These parameters are also supported.

MAX_BUCKET

SQL> select ora_hash('abracadabra', 255);

ORA_HASH('ABRACADABRA',255)
---------------------------
                         82

>>> ora_hash(b'abracadabra', 255)
82

SEED_VALUE

SQL> select ora_hash('abracadabra', power(2,32)-1, 123);

ORA_HASH('ABRACADABRA',POWER(2,32)-1,123)
-----------------------------------------
                               4012392341

>>> ora_hash(b'abracadabra', seed=123)
4012392341

MAX_BUCKET and SEED_VALUE

SQL> select ora_hash('abracadabra', 255, 123);

ORA_HASH('ABRACADABRA',255,123)
-------------------------------
                            149

>>> ora_hash(b'abracadabra', 255, 123)
149

X$ tables: reading X$KQFCO from Oracle binary

Column definition structures are stored in kqftap:

[oracle@db-21 ~]$ xinfo list 'X$KCCCP' -o json --with-kqftap
{
  "457": {
    "obj": 4294951392,
    "ver": 5,
    "nam_ptr": 383999376,
    "nam": "X$KCCCP",
    "xstruct_nam_ptr": 383999384,
    "xstruct": "kctcpx",
    "typ": 5,
    "flg": 0,
    "rsz": 552,
    "coc": 25,
    "kqftap": {
      "xstruct_ptr": 382889376,
      "cb2_ptr": 75535856,
      "xstruct": "kctcpx_c",
      "cb2": "kctxcp"
    }
  }
}

For example, the structure kctcpx_c defines X$KCCCP's columns:

[oracle@db-21 bin]$ readelf -s oracle | grep -E -A1 -w 'Symbol|kctcpx_c' --no-group-separator
Symbol table '.dynsym' contains 225083 entries:
   Num:    Value          Size Type    Bind   Vis      Ndx Name
Symbol table '.symtab' contains 402534 entries:
   Num:    Value          Size Type    Bind   Vis      Ndx Name
 80301: 0000000016d77100  1664 OBJECT  LOCAL  DEFAULT   17 kctcpx_c
 80302: 0000000016d77780  2304 OBJECT  LOCAL  DEFAULT   17 kctmtx_c

Here is what it looks like:

[oracle@db-21 bin]$ objdump -s --start-address=0x0000000016d77100 --stop-address=$((0x0000000016d77100+1664)) oracle

oracle:     file format elf64-x86-64

Contents of section .rodata:
 16d77100 04000000 00000000 9c95e616 00000000  ................
 16d77110 17090000 00000000 00000000 00000000  ................
 16d77120 00000000 00000000 08000000 00000000  ................
 16d77130 00000000 00000000 00000000 00000000  ................
 16d77140 04000000 00000000 60d6d715 00000000  ........`.......
 16d77150 020b0000 00000000 00000000 00000000  ................
 16d77160 00000000 00000000 04000000 00000000  ................
 16d77170 00000000 00000000 00000000 00000000  ................
 16d77180 07000000 00000000 b0960f16 00000000  ................
 16d77190 020b0000 00000000 00000000 00000000  ................
 16d771a0 00000000 00000000 04000000 00000000  ................
 16d771b0 00000000 00000000 00000000 00000000  ................
 16d771c0 06000000 00000000 5c8de716 00000000  ........\.......
 16d771d0 020b0000 00000000 00000000 00000000  ................
 16d771e0 00000000 00000000 02000000 00000000  ................
 16d771f0 00000000 00000000 00000000 00000000  ................
 16d77200 05000000 00000000 6c82e916 00000000  ........l.......
 16d77210 02000100 00000000 00000000 00000000  ................
 16d77220 00000000 00000000 04000000 00000000  ................
 16d77230 0c020000 00000000 00000000 00000000  ................

Each column is defined within 64 bytes:

[oracle@db-21 bin]$ for a in 16e6959c 15d7d660 160f96b0 16e78d5c 16e9826c
> do
>   objdump -s --start-address=0x$a --stop-address=$((0x$a+16)) oracle
> done

oracle:     file format elf64-x86-64

Contents of section .rodata:
 16e6959c 41444452 00000000 6b71665f 66696c6c  ADDR....kqf_fill

oracle:     file format elf64-x86-64

Contents of section .rodata:
 15d7d660 494e4458 00000000 49535355 45440000  INDX....ISSUED..

oracle:     file format elf64-x86-64

Contents of section .rodata:
 160f96b0 494e5354 5f494400 71656573 46696e64  INST_ID.qeesFind

oracle:     file format elf64-x86-64

Contents of section .rodata:
 16e78d5c 434f4e5f 49440000 5352435f 434f4e5f  CON_ID..SRC_CON_

oracle:     file format elf64-x86-64

Contents of section .rodata:
 16e9826c 4350544e 4f000000 43505354 41000000  CPTNO...CPSTA...

Here is the structure of X$KCCCP:

SQL> desc x$kcccp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 CON_ID                                             NUMBER
 CPTNO                                              NUMBER
 CPSTA                                              NUMBER
 CPFLG                                              NUMBER
 CPDRT                                              NUMBER
 CPRDB                                              NUMBER
 CPLRBA_SEQ                                         NUMBER
 CPLRBA_BNO                                         NUMBER
 CPLRBA_BOF                                         NUMBER
 CPODR_SEQ                                          NUMBER
 CPODR_BNO                                          NUMBER
 CPODR_BOF                                          NUMBER
 CPODS                                              VARCHAR2(20)
 CPODT                                              VARCHAR2(20)
 CPODT_I                                            NUMBER
 CPHBT                                              NUMBER
 CPRLS                                              VARCHAR2(20)
 CPRLC                                              NUMBER
 CPMID                                              NUMBER
 CPSDR_SEQ                                          NUMBER
 CPSDR_BNO                                          NUMBER
 CPSDR_ADB                                          NUMBER

And the actual X$KQFCO rows:

SQL> select kqfcocno,
  2         kqfconam,
  3         kqfcosiz,
  4         kqfcodty,
  5         kqfcotyp,
  6         kqfcomax,
  7         kqfcolsz,
  8         kqfcolof,
  9         kqfcooff,
 10         kqfcoidx,
 11         kqfcoipo
 12    from x$kqfta t,
 13         x$kqfco c
 14   where t.kqftanam = 'X$KCCCP'
 15     and c.kqfcotab = t.indx
 16   order by 1;

  KQFCOCNO KQFCONAM     KQFCOSIZ   KQFCODTY   KQFCOTYP   KQFCOMAX   KQFCOLSZ   KQFCOLOF   KQFCOOFF   KQFCOIDX   KQFCOIPO
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1 ADDR                8         23          9          0          0          0          0          0          0
         2 INDX                4          2         11          0          0          0          0          0          0
         3 INST_ID             4          2         11          0          0          0          0          0          0
         4 CON_ID              2          2         11          0          0          0          0          0          0
         5 CPTNO               4          2          0          0          0          0        524          1          0
         6 CPSTA               4          2         11          0          0          0          0          0          0
         7 CPFLG               4          2         11          0          0          0          4          0          0
         8 CPDRT               4          2         11          0          0          0          8          0          0
         9 CPRDB               4          2         11          0          0          0        548          0          0
        10 CPLRBA_SEQ          4          2         11          0          0          0         12          0          0
        11 CPLRBA_BNO          4          2         11          0          0          0         16          0          0
        12 CPLRBA_BOF          2          2         11          0          0          0         20          0          0
        13 CPODR_SEQ           4          2         11          0          0          0         24          0          0
        14 CPODR_BNO           4          2         11          0          0          0         28          0          0
        15 CPODR_BOF           2          2         11          0          0          0         32          0          0
        16 CPODS              20          1          5          0          0          0        484          0          0
        17 CPODT              20          1          5          0          0          0        504          0          0
        18 CPODT_I             4          2         11          0          0          0         44          0          0
        19 CPHBT               4          2         11          0          0          0         60          0          0
        20 CPRLS              20          1          5          0          0          0        528          0          0
        21 CPRLC               4          2         11          0          0          0         48          0          0
        22 CPMID               4          2         11          0          0          0         64          0          0
        23 CPSDR_SEQ           4          2         11          0          0          0        104          0          0
        24 CPSDR_BNO           4          2         11          0          0          0        108          0          0
        25 CPSDR_ADB           2          2         11          0          0          0        112          0          0

25 rows selected.

The xinfo tool has a describe command that produces the same data:

[oracle@db-21 ~]$ xinfo desc 'X$KCCCP'
+-----+------------+------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-------------+
| cno |    nam_ptr | nam        | siz | dty | typ | max | lsz | lof | off | idx | ipo | kqfcop_indx |
+-----+------------+------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-------------+
|   1 | 0x16e6959c | ADDR       |   8 |  23 |   9 |   0 |   0 |   0 |   0 |   0 |   0 |           0 |
|   2 | 0x15d7d660 | INDX       |   4 |   2 |  11 |   0 |   0 |   0 |   0 |   0 |   0 |           0 |
|   3 | 0x160f96b0 | INST_ID    |   4 |   2 |  11 |   0 |   0 |   0 |   0 |   0 |   0 |           0 |
|   4 | 0x16e78d5c | CON_ID     |   2 |   2 |  11 |   0 |   0 |   0 |   0 |   0 |   0 |           0 |
|   5 | 0x16e9826c | CPTNO      |   4 |   2 |   0 |   0 |   0 |   0 | 524 |   1 |   0 |           0 |
|   6 | 0x16e98274 | CPSTA      |   4 |   2 |  11 |   0 |   0 |   0 |   0 |   0 |   0 |           0 |
|   7 | 0x16e9827c | CPFLG      |   4 |   2 |  11 |   0 |   0 |   0 |   4 |   0 |   0 |           0 |
|   8 | 0x16e98284 | CPDRT      |   4 |   2 |  11 |   0 |   0 |   0 |   8 |   0 |   0 |           0 |
|   9 | 0x16e9828c | CPRDB      |   4 |   2 |  11 |   0 |   0 |   0 | 548 |   0 |   0 |           0 |
|  10 | 0x16e98294 | CPLRBA_SEQ |   4 |   2 |  11 |   0 |   0 |   0 |  12 |   0 |   0 |           0 |
|  11 | 0x16e982a0 | CPLRBA_BNO |   4 |   2 |  11 |   0 |   0 |   0 |  16 |   0 |   0 |           0 |
|  12 | 0x16e982ac | CPLRBA_BOF |   2 |   2 |  11 |   0 |   0 |   0 |  20 |   0 |   0 |           0 |
|  13 | 0x16e982b8 | CPODR_SEQ  |   4 |   2 |  11 |   0 |   0 |   0 |  24 |   0 |   0 |           0 |
|  14 | 0x16e982c4 | CPODR_BNO  |   4 |   2 |  11 |   0 |   0 |   0 |  28 |   0 |   0 |           0 |
|  15 | 0x16e982d0 | CPODR_BOF  |   2 |   2 |  11 |   0 |   0 |   0 |  32 |   0 |   0 |           0 |
|  16 | 0x16e982dc | CPODS      |  20 |   1 |   5 |   0 |   0 |   0 | 484 |   0 |   0 |           0 |
|  17 | 0x16e982e4 | CPODT      |  20 |   1 |   5 |   0 |   0 |   0 | 504 |   0 |   0 |           0 |
|  18 | 0x16e982ec | CPODT_I    |   4 |   2 |  11 |   0 |   0 |   0 |  44 |   0 |   0 |           0 |
|  19 | 0x16e982f4 | CPHBT      |   4 |   2 |  11 |   0 |   0 |   0 |  60 |   0 |   0 |           0 |
|  20 | 0x16e982fc | CPRLS      |  20 |   1 |   5 |   0 |   0 |   0 | 528 |   0 |   0 |           0 |
|  21 | 0x16e98304 | CPRLC      |   4 |   2 |  11 |   0 |   0 |   0 |  48 |   0 |   0 |           0 |
|  22 | 0x16e9830c | CPMID      |   4 |   2 |  11 |   0 |   0 |   0 |  64 |   0 |   0 |           0 |
|  23 | 0x16e98314 | CPSDR_SEQ  |   4 |   2 |  11 |   0 |   0 |   0 | 104 |   0 |   0 |           0 |
|  24 | 0x16e98320 | CPSDR_BNO  |   4 |   2 |  11 |   0 |   0 |   0 | 108 |   0 |   0 |           0 |
|  25 | 0x16e9832c | CPSDR_ADB  |   2 |   2 |  11 |   0 |   0 |   0 | 112 |   0 |   0 |           0 |
+-----+------------+------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-------------+

ORA-00855 PGA_AGGREGATE_TARGET cannot be set because of insufficient physical memory

Given the following amount of memory and %target parameters, what PGA_AGGREGATE_TARGET (PAT) can be set for this system?

SQL> sho parameter ga%target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 200M
sga_target                           big integer 16640M
SQL> !grep Mem /proc/meminfo
MemTotal:       32053636 kB
MemFree:         2063524 kB
MemAvailable:    5843840 kB

Oracle allows to set the value to more than MemAvailable:

SQL> alter system set pga_aggregate_target=5843841K;

System altered.

At the same time, an attempt to set the PAT value to 6GB fails with ORA-855:

SQL> alter system set pga_aggregate_target=6G;
alter system set pga_aggregate_target=6G
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00855: PGA_AGGREGATE_TARGET cannot be set because of insufficient physical
memory.

with the following message written to the DB alert log:

pga_aggregate_target cannot be set to 6442450944 due to SGA memory requirement and the physical memory size.

The description of the error is self-explanatory:

SQL> !oerr ora 855
00855, 00000, "PGA_AGGREGATE_TARGET cannot be set because of insufficient physical memory."
// *Cause:  PGA_AGGREGATE_TARGET value was too high for the current system global area (SGA) size and amount of physical memory available.
// *Action: Reduce the SGA size or increase the physical memory size.

Which, however, doesn't explain what the maximum PAT value allowed for this system.

During my experiments, I found out that Oracle calls the ksmc_physmem_pga_target function internally that returns the maximum PAT value. The function takes the total host memory as input, which in my case would be 32053636KB (or 0x7a4661000 in hex). We can use gdb to show the value:

(gdb) printf "0x%lx\n", (long) ksmc_physmem_pga_target(0x7a4661000)
0x1686120cc

Which is 6,046,163,148 bytes. oradebug outputs just 4 last bytes and it is not reliable here (the same value is shown in the trace file):

SQL> oradebug call ksmc_physmem_pga_target 0x7a4661000
Function returned 686120CC

The trace file:

Oradebug command 'call ksmc_physmem_pga_target 0x7a4661000' console output:
Function returned 686120CC

Oracle developers must have used the x format in oradebug:

(gdb) printf "0x%x\n", (long) ksmc_physmem_pga_target(0x7a4661000)
0x686120cc

The PAT value can be validated in SQL*Plus:

SQL> alter system set pga_aggregate_target=6046163148;

System altered.

While setting the value to more than that is not allowed:

SQL> alter system set pga_aggregate_target=6046163149;
alter system set pga_aggregate_target=6046163149
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00855: PGA_AGGREGATE_TARGET cannot be set because of insufficient physical
memory.

The internal algorithm used by Oracle may change, but at the time of my experiments in 19.18 the maximum PGA value seems to be calculated as follows:

max PAT = (TotalMemory * _pga_limit_physmem_perc / 100 - SGA_TARGET) * 100 / _pga_limit_target_perc

Description:

• max PAT: PGA_AGGREGATE_TARGET max value
• TotalMemory: total host memory (MemTotal in /proc/meminfo)

• _pga_limit_physmem_perc: the parameter limiting total PGA and SGA (90% by default, in other words Oracle reserves 10% for OS and everything else):

  SQL> select indx, ksppdesc from x$ksppi where ksppinm='_pga_limit_physmem_perc';
  
        INDX KSPPDESC
  ---------- --------------------------------------------------------------------------------
         246 default percent of physical memory for pga_aggregate_limit and SGA
         
  SQL> select ksppstvl from x$ksppsv where indx=246;
  
  KSPPSTVL
  --------------------------------------------------------------------------------
  90
  
      

• _pga_limit_target_perc: the default percent of PAT for pga_aggregate_limit (200% by default):

  SQL> select indx, ksppdesc from x$ksppi where ksppinm='_pga_limit_target_perc';
  
        INDX KSPPDESC
  ---------- --------------------------------------------------------------------------------
         234 default percent of pga_aggregate_target for pga_aggregate_limit
  
  SQL> select ksppstvl from x$ksppsv where indx=234;
  
  KSPPSTVL
  --------------------------------------------------------------------------------
  200
      

Substituting the values from the sample system to the formula, we get the expected value we experimentally found previously:

max PAT = (32053636 * 1024 * 90 / 100 - 16640 * 1024 * 1024) * 100 / 200 = 6046163148.8

Please note that this formula applies when the value is set while the instance is up and running. It is still possible to set a higher value in spfile and bounce the instance. It will work producing the following output in the alert log:

2023-03-18T13:20:50.763260+00:00
**********************************************************************
PGA_AGGREGATE_TARGET specified is high
Errors in file /u01/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_ora_275709.trc  (incident=14402):
ORA-00700: soft internal error, arguments: [pga physmem limit], [6046163149], [6046163148], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/racdb/racdb1/incident/incdir_14402/racdb1_ora_275709_i14402.trc

Where the second argument of the ORA-00700 error (6046163149) is the actual PAT value, the third argument (6046163148) is the max PAT value calculated by the formula above. In such a scenario, it is not possible to set the PAT value to itself:

SQL> sho parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 12092326298
pga_aggregate_target                 big integer 6046163149
SQL> alter system set pga_aggregate_target=6046163149;
alter system set pga_aggregate_target=6046163149
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00855: PGA_AGGREGATE_TARGET cannot be set because of insufficient physical
memory.

X$ tables: table-level callbacks and column definitions (without parsing)

Thus far, I have reviewed the kqftab structure which is used to build the majority of tables in X$KQFTA. It is not clear yet where actual columns are coming from. There is an additional structure called kqftap that has the extra information.

[oracle@db-21 bin]$ readelf -s oracle | grep -E -A1 -w 'Symbol|kqftap' --no-group-separator
Symbol table '.dynsym' contains 225083 entries:
   Num:    Value          Size Type    Bind   Vis      Ndx Name
225049: 0000000016cf4d00 40736 OBJECT  GLOBAL DEFAULT   17 kqftap
225050: 0000000007b02100   256 FUNC    GLOBAL DEFAULT   13 l9_ippsRLEGetInUseTable_8
Symbol table '.symtab' contains 402534 entries:
   Num:    Value          Size Type    Bind   Vis      Ndx Name
351482: 0000000016cf4d00 40736 OBJECT  GLOBAL DEFAULT   17 kqftap
351483: 0000000012aef270   800 FUNC    GLOBAL DEFAULT   14 qesxlGetPayloadData

Here is what it looks like:

[oracle@db-21 bin]$ objdump -s --start-address=0x0000000016cf4d00 --stop-address=$((0x0000000016cf4d00+40736)) oracle

oracle:     file format elf64-x86-64

Contents of section .rodata:
 16cf4d00 00000000 00000000 00efcf16 00000000  ................
 16cf4d10 7039670e 00000000 00000000 00000000  p9g.............
 16cf4d20 00000000 00000000 00f2cf16 00000000  ................
 16cf4d30 00000000 00000000 00000000 00000000  ................
 16cf4d40 00000000 00000000 00f4cf16 00000000  ................
 16cf4d50 00000000 00000000 00000000 00000000  ................
 16cf4d60 00000000 00000000 80f5cf16 00000000  ................
 16cf4d70 00000000 00000000 00000000 00000000  ................
 16cf4d80 00000000 00000000 80f7cf16 00000000  ................
 16cf4d90 903b670e 00000000 00000000 00000000  .;g.............
..

It is made of 32-byte rows. The third column in the output points to the X$-column structure - the structure describing the corresponding X$ table:

[oracle@db-21 bin]$ for a in 16cfef00 16cff200 16cff400 16cff580
> do
>   nm oracle | grep $a
> done
0000000016cfef00 r kqfta_c
0000000016cff200 r kqfvi_c
0000000016cff400 r kqfvt_c
0000000016cff580 r kqfdt_c

[oracle@db-21 ~]$ xinfo list | head -10
+------------+-----+------------+-------------------------------+-----------------+---------------------------+-----+------+--------+-----+
|        obj | ver |    nam_ptr | nam                           | xstruct_nam_ptr | xstruct                   | typ |  flg |    rsz | coc |
+------------+-----+------------+-------------------------------+-----------------+---------------------------+-----+------+--------+-----+
| 4294950912 |   6 | 0x16282d00 | X$KQFTA                       |      0x16e33810 | kqftv                     |   4 |    0 |     80 |  11 |
| 4294950913 |   3 | 0x16e33818 | X$KQFVI                       |      0x16e33810 | kqftv                     |   1 |    0 |     80 |   7 |
| 4294951149 |   3 | 0x16e33820 | X$KQFVT                       |      0x16e33828 | kqftp                     |   1 |    0 |     32 |   5 |
| 4294950914 |   4 | 0x16e33830 | X$KQFDT                       |      0x16e33838 | kqfdt                     |   1 |    0 |     40 |   7 |
| 4294951036 |   6 | 0x16e33840 | X$KQFCO                       |      0x16e33848 | kqfcc                     |   4 |    2 |     80 |  17 |
| 4294952712 |   2 | 0x16e33850 | X$KQFOPT                      |      0x16e3385c | kqfopt                    |   1 |    0 |     24 |   6 |
| 4294952922 |   3 | 0x16e33864 | X$KYWMPCTAB                   |      0x16e33870 | kywmpctab                 |   4 |    0 |     88 |  12 |

Both kqftab and kqftap define tables and corresponding column structures in the same order: X$KQFTA's columns are described in kqfta_c, X$KQFVI's columns are described in kqfvi_c, etc.

Then, some X$ tables require additional processing, so that there are callback functions:

[oracle@db-21 bin]$ nm oracle | grep -E 'e673970|e673b90'
000000000e673970 T kqftbl_cb
000000000e673b90 T kqftco

The xinfo tool was enhanced to output the corresponding kqftap rows when the --with-kqftap option is specified:

[oracle@db-21 ~]$ xinfo list 'X$KSMLRU' --with-kqftap -o json
{
  "88": {
    "obj": 4294951099,
    "ver": 7,
    "nam_ptr": 383991380,
    "nam": "X$KSMLRU",
    "xstruct_nam_ptr": 383991392,
    "xstruct": "ksmlr",
    "typ": 4,
    "flg": 0,
    "rsz": 112,
    "coc": 18,
    "kqftap": {
      "xstruct_ptr": 382474144,
      "cb1_ptr": 132689584,
      "xstruct": "ksmlru_c",
      "cb1": "ksmlrs"
    }
  }
}

I will explain how to extract the column definitions in the next post.

Virtual X$ tables

Some of X$ tables are not coming from the kqftab structure. For example, the xinfo tool outputs only 1,272 tables in 21.5, whereas X$KQFTA has 1,377 rows:

[oracle@db-21 sf_exchange]$ xinfo list -o json |
>   jq -r '.[] | .nam' |
>   awk '{printf "%4d %s\n",NR,$0}'
   1 X$KQFTA
   2 X$KQFVI
   3 X$KQFVT
   4 X$KQFDT
   5 X$KQFCO
..
1271 X$BCAPPLY_STATS
1272 X$FSDDBFS

What about extra 105 rows? Let me show what the output of X$KQFTA is like in my database:

SQL> select * from x$kqfta;

ADDR                   INDX    INST_ID     CON_ID   KQFTAOBJ   KQFTAVER KQFTANAM                         KQFTATYP   KQFTAFLG   KQFTARSZ   KQFTACOC
---------------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ----------
0000000016CDBF20          0          1          0 4294950912          6 X$KQFTA                                 4          0         80         11
0000000016CDBF70          1          1          0 4294950913          3 X$KQFVI                                 1          0         80          7
..
0000000016CF4C00       1270          1          0 4294956360          2 X$BCAPPLY_STATS                         4          2        260         16
0000000016CF4C50       1271          1          0 4294956225          2 X$FSDDBFS                               4          0       1144         14
000000008F8127C0       1272          1          0 4294953644          0 X$KSIPC_PROC_STATS                      9         18          0          0
000000008F812810       1273          1          0 4294953645          0 X$KSIPC_INFO                            9         18          0          0
000000008F812860       1274          1          0 4294952215          1 X$KSXPTESTTBL                          10          2        146         15
000000008F8128B0       1275          1          0 4294952216          0 X$KSXP_STATS                            9         18          0          0
000000008F812900       1276          1          0 4294952217          0 X$SKGXP_PORT                            9         18          0          0
000000008F812950       1277          1          0 4294952218          0 X$SKGXP_CONNECTION                      9         18          0          0
000000008F8129A0       1278          1          0 4294952219          0 X$SKGXP_MISC                            9         18          0          0
000000008F8129F0       1279          1          0 4294952227          1 X$KTCNQROW                              9          2       1480         47
..
000000008F8147F0       1375          1          0 4294954950          3 X$DIAG_VTEST_EXISTS                    10         34       1536         13
000000008F814840       1376          1          0 4294954964          3 X$DIAG_VADR_CONTROL                    10         34       1072         21

1377 rows selected.

It can be seen that starting at INDX=1,272 (which is row 1,273 because INDX starts at 0) the ADDR value is quite different - it is 0x8F8127C0. It is not an address from the Oracle binary anymore:

SQL> select *
  2    from x$ksmsp
  3   where to_number('8F8127C0','XXXXXXXXXXXXXXXX')
  4           between to_number(ksmchptr,'XXXXXXXXXXXXXXXX')
  5               and to_number(ksmchptr,'XXXXXXXXXXXXXXXX') + ksmchsiz - 1;

ADDR                   INDX    INST_ID     CON_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F6C5E50DC10     106576          1          1          1          1 KQF runtime def  000000008F8127B0       8416 perm              0 000000008F101000

The address refers to the KQF runtime def area of SGA. The X$ tables starting from INDX=1,272 are what Oracle is called virtual tables in its code. Of course, the INDX value is version dependent but these virtual tables always come after other X$ tables defined in kqftab. The starting address of the virtual tables can be obtained from the kqftvrt_ SGA variable:

SQL> oradebug dumpvar sga kqftvrt_
struct kqftv* kqftvrt_ [0600A7408, 0600A7410) = 8F8127C0 00000000

The virtual tables are built inside the kqfbldtvrt function.