Oracle: X$KSMFSV

Names of variables, address of their value in the SGA, their datatype and size of the datatype.

2024-08-15: It appears that sys needs to be connected to a CDB root, not a container, in order for x$ksmfsv to return any values.

Displaying the value of a variable

The column ksmfsadr can be joined to the column addr of x$ksmmem which allows determine the value of an SGA variable.

Note, the returned value is 8 bytes, even if the data type of the variable is different!

select
   var.ksmfsnam                                     var_nam,
   mem.ksmmmval                                     val_hex,
   to_number(mem.ksmmmval, rpad('x', 16, 'x'))      val_dec
from
   x$ksmfsv   var                                   join
   x$ksmmem   mem on var.ksmfsadr = mem.addr
where
   var.ksmfsnam like '%kywmpleq1%'

SCN number (kcsgscn_)

Such a variable is for example kcsgscn_ which stores the current SCN number:

select
   to_number(mem.ksmmmval, rpad('x', 16, 'x'))      value,
   vdb.current_scn 
from
   x$ksmfsv   var                                   join
   x$ksmmem   mem on var.ksmfsadr = mem.addr cross  join
   v$database vdb 
where
   var.ksmfsnam = 'kcsgscn_' -- SCN Number  
;

Latches

Many of these variables seem to store the address of latches which are exposed in x$kslld or x$kslltr.

select   
-- addr addr_hex,
-- to_number(addr    , rpad('x', 16, 'x')) addr,
   ksmfsnam                                variable_name,
   lld.kslldnam                            lld_name,
   lld.ksllddsp                            lld_description,   
   llt.kslltnam                            llt_name,
   to_number(ksmfsadr, rpad('x', 16, 'x')) address,
   ksmfstyp                                typ,
   ksmfssiz                                siz
from
   x$ksmfsv        var                                    left join
   x$kslld         lld on  var.ksmfsadr = lld.kslldadr    left join
   x$kslltr        llt on  var.ksmfsadr = llt.kslltaddr
where
   coalesce(lld.kslldadr, llt.kslltaddr) is not null
;

Dumping variable values with oradebug

ORADEBUG allows to print the value of a variable with the dumpvar command:

oradebug dumpvar kcsgscn

Datatype (column ksmfstyp)

ksmfstyp shows the type of the variable: Some of these types are basic memory units such as uword, ub4, int etc., others seem to refer to some kind of structs, such as ksllt, ksbsa, kzxctok etc. Also seen is void *, size_t, oratext * and many more.

In the case of the type being ksllt, it can be joined to x$kslld to list variables that are related to latches.

Displaying the value of string variables

First, we need to determine the addresses of string variables (i. e. where the type of the SGA variable is oratext *) we want to display:

select   
   var.ksmfsnam                                variable_name,
   to_number(var.ksmfsadr, rpad('x', 16, 'x')) ptr_to_addr_str,
   to_number(mem.ksmmmval, rpad('x', 16, 'x')) addr_str
from
   x$ksmfsv   var                              join
   x$ksmmem   mem on var.ksmfsadr = mem.addr 
where
   var.ksmfstyp = 'oratext *' -- TODO: or simply 'text *'
order by
   var.ksmfsnam
;
--
-- VARIABLE_NAME      PTR_TO_ADDR_STR   ADDR_STR
-- ------------------ --------------- ----------
-- gwm_spare_sga4_         1611732544          0
-- gwm_spare_sga5_         1611732552          0
-- krbdbutspitr_           1611465072          0
-- ksii2tmap_              1610654608 2471616472
-- ksitypstr_              1610661888 2478881768
-- ksm_ob_path_            1610618736 2551961328
-- ksm_obc_path_           1610618752 2551961360
-- ksm_obh_path_           1610618744 2551961416
-- ksmanotshnm_            1610618664          0
-- ksmanotshnme_           1610618672          0
-- kspstrs_buf_            1610659784 2479164480
-- ksuplsb_                1610648640 2471486880
-- kzekmrsafipspath_       1611553928 2281696384
-- qmhsthdr_               1611675576 2237062784

We now determine the PID of the main Oracle process …

$ ps -ef | grep -P "oracle$ORACLE_SID \(" | awk '{ print $2 }'
498

… so that we can attach to this process with GDB (GNU debugger) and then to examine its memory at the given string addresses:

$ gdb -p 498
…
(gdb) x/s 2551961328
0x981bd6f0:     "/opt/oracle"

Speculation about some variables

`gwm_spare_sga4_`	`oratext *`
`gwm_spare_sga5_`	`oratext *`
`kcbatt_`	`ub4`	As per Tanel Poder, accessing a block multiple times within the period in seconds specified by `kcbatt_` only increases the `tch` count of a buffer once.
`kcvdbn_`	`text *`	SID?
`ksetln_`	`text *`	`$ORACLE_HOME/rdbms/mesg/oraus.msb`
`ksii2tmap_`	`oratext *`
`ksitypstr_`	`oratext *`	The string `"ABCDEFGHIJKLMNOPQRSTUVWXYZ"`
`ksm_ob_path_`	`oratext *`	`/opt/oracle` on my current machine
`ksm_obc_path_`	`oratext *`	Path to Oracle home (?)
`ksm_obh_path_`	`oratext *`	Path to Oracle home (?)
`ksmanotshnm_`	`oratext *`
`ksmanotshnme_`	`oratext *`
`kspstrs_buf_`	`oratext *`	The string `"_appqos_qt"`. This value is incidentally (?) the first value returned by `select ksppinm from x$ksppi`.
`kstmrmtickcnt_`	`ub4`	Counts tenth of seconds. Seems to be started shortly after instance startup
`kstmlastHighResTime_`	`ub8`
`kstmlastLowResTime_`	`ub4`
`ksudbrmseccnt_`	`ub4`	Unix epoch
`ksuinstchk_proc_`	`ksupr *`	(instance check process?) This pointer seems to point to memory related to PMON
`ksufix_`	`ub8`	Value that records how often a fixed table was accessed? Seems to be increased by selecting from a fixed table.
`ksuplsb_`	`oratext *`
`kzekmrsafipspath_`	`oratext *`	The path to `$ORACLE_HOME/lib`
`qmhsthdr_`	`oratext *`	The string `"MS-Author-Via: DAV\r\nDAV: 1,2,<http://www.oracle.com/xdb/webdav/props>\r\n"`
`sdalfn_`	`text *`	Path of the alert log file (`$ORACLE_HOME/rdbms/log/alert_FREE.log`)
`sdscdd_`	`text *`	The cdump file (something like `"/opt/oracle/diag/rdbms/…/…/cdump"`)
`sdsddd_`	`text *`	Trace directory? (something like `$ORACLE_HOME/rdbms/log`). Compare with `sdsudd_`
`sdsudd_`	`text *`	Trace directory? (something like `$ORACLE_HOME/rdbms/log`). Compare with `sdsddd_`

ksudbrmseccnt_ (Unix Epoch)

ksudbrmseccnt_ stores the value of the Unix epoch (seconds since January 1st 1970):

select
   date '1970-01-01' + (1/24/60/60) * to_number(mem.ksmmmval, rpad('x', 16, 'x')) as "current time UTC"
from
   x$ksmfsv   var                                   join
   x$ksmmem   mem on var.ksmfsadr = mem.addr
where
   var.ksmfsnam = 'ksudbrmseccnt_'
;

kstmrmtickcnt_, kstmlastHighResTime_, kstmlastLowResTime_

select
   to_char(sysdate - (1/24/60/60) *to_number(mem.ksmmmval, rpad('x', 16, 'x')) / 10, 'yyyy-mm-dd hh24:mi:ss') ticks,
   to_char(startup_time                                                            , 'yyyy-mm-dd hh24:mi:ss') startup
from
   x$ksmfsv   var                                   join
   x$ksmmem   mem on var.ksmfsadr = mem.addr  cross join v$instance
where
   var.ksmfsnam = 'kstmrmtickcnt_'
;

select
   to_char(sysdate - (1/24/60/60) *to_number(mem.ksmmmval, rpad('x', 16, 'x')) / 1000/1000, 'yyyy-mm-dd hh24:mi:ss') ticks,
   to_char(startup_time                                                                   , 'yyyy-mm-dd hh24:mi:ss') startup
from
   x$ksmfsv   var                                   join
   x$ksmmem   mem on var.ksmfsadr = mem.addr  cross join v$instance
where
   var.ksmfsnam = 'kstmlastHighResTime_'
;

select
   to_char(sysdate - (1/24/60/60) *to_number(mem.ksmmmval, rpad('x', 16, 'x')) / 1000, 'yyyy-mm-dd hh24:mi:ss') ticks,
   to_char(startup_time                                                            , 'yyyy-mm-dd hh24:mi:ss') startup
from
   x$ksmfsv   var                                   join
   x$ksmmem   mem on var.ksmfsadr = mem.addr  cross join v$instance
where
   var.ksmfsnam = 'kstmlastLowResTime_'
;

TODO

Variable names that are not exposed:

select
   substr(val_hex, 1, 8) kcrf_lgwr_write_cnt_ , -- < This variable (or its name) is not found in x$ksmfsv (likely because it is a word)
   substr(val_hex, 9   ) kcrf_max_read_recsz_
from (
   select
      mem.ksmmmval                                     val_hex
   from
      x$ksmfsv   var                                   join
      x$ksmmem   mem on var.ksmfsadr = mem.addr
   where
      var.ksmfsnam = 'kcrf_max_read_recsz_'
);

ksbsa variables:

select
   to_number(m_0.ksmmmval, lpad('x', 16, 'x'))    id,         -- m_0_val,
             var.ksmfsnam                         var_nam,
             m_2.ksmmmval                         m_2_val,
             m_x.ksmmmval                         m_x_val,    -- Flags?
             v_x.ksmfsnam                         xnam,
             v_x.ksmfstyp                         xtyp,       -- Always ksbdp (or null)
       ltrim(m_1.ksmmmval, '0')                   ptr         -- m_1_val
from
   x$ksmfsv   var                                                                                                     join
   x$ksmmem   m_0 on to_char(to_number(var.ksmfsadr, 'XXXXXXXXXXXXXXXX') + 0,'fm0XXXXXXXXXXXXXXX')= m_0.addr          join
   x$ksmmem   m_1 on to_char(to_number(var.ksmfsadr, 'XXXXXXXXXXXXXXXX') + 8,'fm0XXXXXXXXXXXXXXX')= m_1.addr          join
   x$ksmmem   m_2 on to_char(to_number(var.ksmfsadr, 'XXXXXXXXXXXXXXXX') +16,'fm0XXXXXXXXXXXXXXX')= m_2.addr     left join
   x$ksmmem   m_x on m_1.ksmmmval = m_x.addr                                                                     left join
   x$ksmfsv   v_x on m_x.addr     = v_x.ksmfsadr
where
   var.ksmfstyp = 'ksbsa'
order by
-- m_2.ksmmmval -- index
   v_x.ksmfsnam
;

krdrsb_ckpt_action_

with cnt as (select (level-1) * 8 as val from dual connect by level <= 3)
select
   mem.addr,
   mem.ksmmmval                                     mem_val
from
   cnt                                                                                                           cross join
   x$ksmfsv   var                                                                                                      join
   x$ksmmem   mem on to_char(to_number(var.ksmfsadr, 'XXXXXXXXXXXXXXXX') + cnt.val,'fm0XXXXXXXXXXXXXXX')= mem.addr
where
   var.ksmfsnam like 'krdrsb_ckpt_action_'
;

alter system checkpoint;

Select continuous memory values

with cnt as (select (level-1) * 8 as val from dual connect by level <= 18)
select
-- var.ksmfsnam                                     var_nam,
   mem.addr,
   mem.ksmmmval                                     mem_val
from
   cnt                                                                                                           cross join
   x$ksmfsv   var                                                                                                      join
   x$ksmmem   mem on to_char(to_number(var.ksmfsadr, 'XXXXXXXXXXXXXXXX') + cnt.val,'fm0XXXXXXXXXXXXXXX')= mem.addr
where
   var.ksmfsnam like 'ksusrollat_'
;

Misc

Failed attempt to read gets and immedate_gets for a latch

select
-- var.ksmfsnam                                     var_nam,
   mem.addr,
   mem.ksmmmval                                     mem_val
from
   x$ksmfsv   var                                                                                                join
   x$ksmmem   mem on to_char(to_number(var.ksmfsadr, 'XXXXXXXXXXXXXXXX') + 1*8,'fm0XXXXXXXXXXXXXXX')= mem.addr
where
   var.ksmfsnam like 'ksusoln_' -- 'session allocation'
;


select
   to_char(          gets, 'xxxxxxxx') g,
   to_char(immediate_gets, 'xxxxxxxx') i
from
   v$latch
where
   name = 'session allocation';