This post is about one of the fundamentally important properties of a database: how IO is done. The test case I studied is doing a simple full table scan of a single large table. In both Oracle and postgres the table doesn’t have any indexes or constraints, which is not a realistic example, but this doesn’t change the principal topic of the study: doing a table scan.

I used a publicly available dataset from the US bureau of transportation statistics called FAF4.5.1_database.zip
The zipped file is 347MB, unzipped size 1.7GB.

In both cases Oracle Linux 7.7 (64 bit) is used, running in VirtualBox, with the storage being a USB3 SSD. Number of CPUs is 4, memory size is 6G. Filesystem type: xfs.
The Oracle version used is Oracle 19.5, the Postgresql version used is 12.1.
For Postgresql, the postgresql.conf file is not changed, except for max_parallel_workers_per_gather which is set to 0 to make postgres use a single process.
For Oracle, the parameters that I think are important: filesystemio_options=’setall’. Oracle is used filesystem based (so no ASM).

This is the table definition for Oracle:

create table faf451 (
  fr_origin varchar2(3),
  dms_orig varchar2(3),
  dms_dest varchar2(3),
  fr_dest varchar2(3),
  fr_inmode varchar2(1),
  dms_mode varchar2(1),
  fr_outmode varchar2(1),
  sctg2 varchar2(2),
  trade_type varchar2(1),
  tons number,
  value number,
  tmiles number,
  curval number,
  wgt_dist number,
  year varchar2(4)
);

This is the table definition for Postgresql:

create table faf451 (
  fr_origin varchar(3),
  dms_orig varchar(3),
  dms_dest varchar(3),
  fr_dest varchar(3),
  fr_inmode varchar(1),
  dms_mode varchar(1),
  fr_outmode varchar(1),
  sctg2 varchar(2),
  trade_type varchar(1),
  tons double precision,
  value double precision,
  tmiles double precision,
  curval double precision,
  wgt_dist double precision,
  year varchar(4)
);

In order for the data to be easy loadable into postgres using copy from, I had to remove ‘””‘ (double double quotes) for the empty numeric fields. In oracle I could say “optionally enclosed by ‘”‘”. For Oracle I used an external table definition to load the data.

Now, before doing any benchmarks, I have an idea where this is going. Oracle is using direct IO (DIO) so linux page cache management and “double buffering” are avoided. Also, oracle will be doing asynchronous IO (AIO), which means submitting is separated from waiting for the notification that the submitted IOs are ready, and on top of that oracle will submit multiple IO requests at the same time. And again on top of that, oracle does multi-block IO, which means that instead of requesting each 8K database block individually, it will group adjacent blocks and request for these in one go, up to a size of combined blocks of 1MB, which means it can requests up to 128 8K blocks in one IO. Postgres will request every block synchronous, so 1 8K block at a time, and waiting for each request to finish. That makes me have a strong idea where this is going.

It should be noted that postgres explicitly is depending on the operating system page cache for buffering as a design principle. Because of DIO, blocks that are read by oracle are not cached in the operating system page cache.

I executed my benchmark in the following way:
– A run for every size is executed 5 times.
– At the start of every run for a certain size (so before every “batch” of 5 runs), the page cache is flushed: (echo 3 > /proc/sys/vm/drop_caches).
– Before each individual run, the database cache is flushed (systemctl restart postgresql-12 for postgres, alter system flush buffer_cache for oracle).

I started off with 2G from the dataset, and then simply performed a ‘copy from’ again to load the same dataset into the table in postgres. Oracle required a bit more of work. Oracle was able to save the same data in way less blocks; the size became 1.18G. In order to have both postgres and oracle scan the same amount of data, I calculated roughly how much rows I needed to add to the table to make it 2G, and copied that table to save it as a 2G table, so I could insert that table to increase the size of the test table by 2G. This way in both oracle and postgres I could test with a 2G table and add 2G at a time until I reached 20G.

These are the results. As you can see in the legenda: oracle is orange, postgres is blue.
postgres oracle scan results(click graph to load full picture)

What we see, is that postgres is a bit slower with the first run of 5 for the smaller dataset sizes, which becomes less visible with larger datasets.
Also, postgres is way faster if the dataset fits into the page cache and it has been read into it. This is logical because postgres explicitly uses the page cache as a secondary cache, and the test is the only activity on this server, so it hasn’t been flushed by other activity.

What was totally shocking to me, is postgres is performing alike oracle and both roughly are able to perform at the maximum IO speed of my disk: 300MB/s, especially when the dataset is bigger, alias beyond the page cache size.

It wasn’t shocking that oracle could reach the total bandwidth of the disk: oracle uses all the techniques to optimise IO for bandwidth. But how can postgres do the same, NOT deploying these techniques, reading 8K at a time??

The first thing to check is whether postgres is doing something else than I suspected. This can simply be checked with strace:

poll_wait(3, [{EPOLLIN, {u32=18818136, u64=18818136}}], 1, -1) = 1
recvfrom(11, "Q\0\0\0!select count(*) from faf451"..., 8192, 0, NULL, NULL) = 34
lseek(20, 0, SEEK_END)                  = 335740928
lseek(20, 0, SEEK_END)                  = 335740928
kill(1518, SIGUSR1)                     = 0
pread64(5, "\f\0\0\0\310ILc\0\0\0\0D\1\210\1\0 \4 \0\0\0\0\230\237\312\0000\237\312\0"..., 8192, 846061568) = 8192
pread64(5, "\f\0\0\0HcLc\0\0\0\0D\1\210\1\0 \4 \0\0\0\0\230\237\312\0000\237\312\0"..., 8192, 846069760) = 8192
pread64(5, "\f\0\0\0\260|Lc\0\0\0\0D\1\210\1\0 \4 \0\0\0\0\230\237\312\0000\237\312\0"..., 8192, 846077952) = 8192
pread64(5, "\f\0\0\0000\226Lc\0\0\0\0D\1\210\1\0 \4 \0\0\0\0\230\237\312\0000\237\312\0"..., 8192, 846086144) = 8192
…etc…

The above strace output shows only 4 rows of pread64() calls, but this goes on. So no “secret” optimisation there.

Luckily, my VM has a new enough version of Linux for it to be able to use eBPF, so I can use biosnoop. Biosnoop is a tool to look at IO on one of the lower layers of the linux kernel, the block device interface (hence ‘bio’). This is the biosnoop output:

# /usr/share/bcc/tools/biosnoop
TIME(s)        COMM           PID    DISK    T  SECTOR    BYTES   LAT(ms)
0.000000000    postmaster     4143   sdb     R  66727776  708608     5.51
0.006419000    postmaster     4143   sdb     R  66731720  77824     11.06
0.006497000    postmaster     4143   sdb     R  66734432  786432    11.03
0.011550000    postmaster     4143   sdb     R  66731872  1310720   16.17
0.013470000    postmaster     4143   sdb     R  66729160  1310720   18.86
0.016439000    postmaster     4143   sdb     R  66735968  1310720   14.61
0.019220000    postmaster     4143   sdb     R  66738528  786432    15.20

Wow…so here it’s doing IOs of up to 1MB! So somewhere between postgres itself and the block device, the IOs magically grew to sizes up to 1MB…that’s weird. The only thing that sits between postgres and the block device is the linux kernel, which includes page cache management.

To get an insight into that, I ran ‘perf record -g -p PID’ during the scan, and then perf report to look at the recorded perf data. This is what is I found:

Samples: 21K of event 'cpu-clock', Event count (approx.): 5277000000
  Children      Self  Command     Shared Object       Symbol                                                                  ◆
-   41.84%     3.63%  postmaster  libpthread-2.17.so  [.] __pread_nocancel                                                    ▒
   - 38.20% __pread_nocancel                                                                                                  ▒
      - 38.08% entry_SYSCALL_64_after_hwframe                                                                                 ▒
         - 37.95% do_syscall_64                                                                                               ▒
            - 35.87% sys_pread64                                                                                              ▒
               - 35.51% vfs_read                                                                                              ▒
                  - 35.07% __vfs_read                                                                                         ▒
                     - 34.97% xfs_file_read_iter                                                                              ▒
                        - 34.69% __dta_xfs_file_buffered_aio_read_3293                                                        ▒
                           - 34.32% generic_file_read_iter                                                                    ▒
                              - 21.10% page_cache_async_readahead                                                             ▒
                                 - 21.04% ondemand_readahead                                                                  ▒
                                    - 20.99% __do_page_cache_readahead                                                        ▒
                                       + 14.14% __dta_xfs_vm_readpages_3179                                                   ▒
                                       + 5.07% __page_cache_alloc                                                             ▒
                                       + 0.97% radix_tree_lookup                                                              ▒
                                       + 0.54% blk_finish_plug                                                                ▒

If you look at rows 13-15 you see that the kernel is performing readahead. This is an automatic function in the linux kernel which looks if the requests are sequential of nature, and when that’s true performs readahead, so that the scan is made faster.

For the difference between Oracle database versions 12.2.0.1.191015 and 12.2.0.1.200114 this too follows the line of a low amount of differences.

There have been two spare parameters that have been changed to named undocumented parameters, and no data dictionary changes.

parameters unique in version 12.2.0.1.191015 versus 12.2.0.1.200114

NAME
--------------------------------------------------
_fifth_spare_parameter
_one-hundred-and-forty-eighth_spare_parameter

parameters unique in version 12.2.0.1.200114 versus 12.2.0.1.191015

NAME
--------------------------------------------------
_bug29825525_bct_public_dba_buffer_dynresize_delay
_enable_ptime_update_for_sys

On the C function side, there have been a group of AWR functions that have been removed and a group of SGA management functions, among other functions. There functions that have been added are random and diverse.

code symbol names unique in version 12.2.0.1.191015 versus 12.2.0.1.200114

NAME                                                         RESOLVE                                                      ANNOTATION
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
R_CR_entropy_resource_init                                   R_CR_entropy_resource_init                                   ??
kcbzdra                                                      (kcbz)dra                                                    kernel cache buffers subroutines for kcb ??
kdmsCreateSampleInvBlkList                                   (kdm)sCreateSampleInvBlkList                                 kernel data in-memory data layer ??
kdmsFillSampleList                                           (kdm)sFillSampleList                                         kernel data in-memory data layer ??
kewmfdms_flush_drmsum                                        (kewm)fdms_flush_drmsum                                      kernel event AWR metrics ??
kewmgaeidct                                                  (kewm)gaeidct                                                kernel event AWR metrics ??
kewmusmdb_update_smdbuf                                      (kewm)usmdb_update_smdbuf                                    kernel event AWR metrics ??
kewramcs_app_map_condbid_str                                 (kewr)amcs_app_map_condbid_str                               kernel event AWR repository ??
kewramvn_append_mdb_vvwname                                  (kewr)amvn_append_mdb_vvwname                                kernel event AWR repository ??
kewrccsq_collect_csql                                        (kewr)ccsq_collect_csql                                      kernel event AWR repository ??
kewrfosp2_fos_mdb_part2                                      (kewrf)osp2_fos_mdb_part2                                    kernel event AWR repository flush ??
kewrfosp3_fos_mdb_part3                                      (kewrf)osp3_fos_mdb_part3                                    kernel event AWR repository flush ??
kewrgcfes_get_cacheid_from_enum_str                          (kewr)gcfes_get_cacheid_from_enum_str                        kernel event AWR repository ??
kewrggd_get_group_descriptor                                 (kewr)ggd_get_group_descriptor                               kernel event AWR repository ??
kewrggf_grp_get_flags                                        (kewr)ggf_grp_get_flags                                      kernel event AWR repository ??
kewrggh_grp_get_handle                                       (kewr)ggh_grp_get_handle                                     kernel event AWR repository ??
kewrggmc_grp_get_member_count                                (kewr)ggmc_grp_get_member_count                              kernel event AWR repository ??
kewrgltn_gen_lrgtest_tab_name                                (kewr)gltn_gen_lrgtest_tab_name                              kernel event AWR repository ??
kewrgvm_grp_valid_member                                     (kewr)gvm_grp_valid_member                                   kernel event AWR repository ??
kewrice_is_cache_enabled                                     (kewr)ice_is_cache_enabled                                   kernel event AWR repository ??
kewrmfp_map_flush_phase                                      (kewr)mfp_map_flush_phase                                    kernel event AWR repository ??
kewrmplvl_map_snap_level                                     (kewr)mplvl_map_snap_level                                   kernel event AWR repository ??
kewrpfbue_pdb_from_buffer_entry                              (kewr)pfbue_pdb_from_buffer_entry                            kernel event AWR repository ??
kewrptsq_prep_topsql                                         (kewr)ptsq_prep_topsql                                       kernel event AWR repository ??
kewrrc_release_cache                                         (kewr)rc_release_cache                                       kernel event AWR repository ??
kewrsaobn_set_all_objnames                                   (kewr)saobn_set_all_objnames                                 kernel event AWR repository ??
kewrsonie_set_object_names_in_entry                          (kewr)sonie_set_object_names_in_entry                        kernel event AWR repository ??
kewrsqlc_sql_iscolored_cb                                    (kewr)sqlc_sql_iscolored_cb                                  kernel event AWR repository ??
kgskltyp                                                     (kgsk)ltyp                                                   kernel generic service resource manager ??
kkeutlCopyAllocatorState                                     (kke)utlCopyAllocatorState                                   kernel compile cost engine ??
kkeutlIsAllocStructureSame                                   (kke)utlIsAllocStructureSame                                 kernel compile cost engine ??
kmgs_check_uninited_comp                                     (kmgs)_check_uninited_comp                                   kernel multi threaded/mman manage (sga) space (?) ??
kmgs_dump_partial_inuse_list_comp                            (kmgs)_dump_partial_inuse_list_comp                          kernel multi threaded/mman manage (sga) space (?) ??
kmgs_dump_quiesce_list                                       (kmgs)_dump_quiesce_list                                     kernel multi threaded/mman manage (sga) space (?) ??
kmgs_dump_resize_summary                                     (kmgs)_dump_resize_summary                                   kernel multi threaded/mman manage (sga) space (?) ??
kmgs_fill_start_sizes                                        (kmgs)_fill_start_sizes                                      kernel multi threaded/mman manage (sga) space (?) ??
kmgs_get_min_cache_grans                                     (kmgs)_get_min_cache_grans                                   kernel multi threaded/mman manage (sga) space (?) ??
kmgs_getgran_from_comp_pg                                    (kmgs)_getgran_from_comp_pg                                  kernel multi threaded/mman manage (sga) space (?) ??
kmgs_init_sgapga_comps                                       (kmgs)_init_sgapga_comps                                     kernel multi threaded/mman manage (sga) space (?) ??
kmgs_nvmksmid_2_kcbpoolid                                    (kmgs)_nvmksmid_2_kcbpoolid                                  kernel multi threaded/mman manage (sga) space (?) ??
kmgs_recv_and_donor_are_caches                               (kmgs)_recv_and_donor_are_caches                             kernel multi threaded/mman manage (sga) space (?) ??
kmgs_shrink_gran                                             (kmgs)_shrink_gran                                           kernel multi threaded/mman manage (sga) space (?) ??
kmgs_update_param_manual_helper                              (kmgs)_update_param_manual_helper                            kernel multi threaded/mman manage (sga) space (?) ??
kmgs_update_resize_summary                                   (kmgs)_update_resize_summary                                 kernel multi threaded/mman manage (sga) space (?) ??
kmgsb_in_range                                               (kmgs)b_in_range                                             kernel multi threaded/mman manage (sga) space (?) ??
kmgsdpgl                                                     (kmgs)dpgl                                                   kernel multi threaded/mman manage (sga) space (?) ??
kmgsset_timestamp                                            (kmgs)set_timestamp                                          kernel multi threaded/mman manage (sga) space (?) ??
krvxgtf                                                      (krvx)gtf                                                    kernel redo recovery extract ??
krvxrte                                                      (krvx)rte                                                    kernel redo recovery extract ??
kslsesftcb_int                                               (ksl)sesftcb_int                                             kernel service  latching and post-wait ??
ksmg_estimate_sgamax                                         (ksm)g_estimate_sgamax                                       kernel service  memory ??
ktcxbFlgPrint                                                (ktc)xbFlgPrint                                              kernel transaction control component ??
kzagetcid                                                    (kza)getcid                                                  kernel security audit  ??
kzekmdcw                                                     (kz)ekmdcw                                                   kernel security ??
qeroiFirstPart                                               (qeroi)FirstPart                                             query execute rowsource extensibel indexing query component ??
qksbgUnderOFE                                                (qksbg)UnderOFE                                              query kernel sql bind (variable) management(?) ??
ri_entcb_cmd_func                                            ri_entcb_cmd_func                                            ??
zt_yield_entropy_source_cb                                   (zt)_yield_entropy_source_cb                                 security encryption ??

code symbol names unique in version 12.2.0.1.200114 versus 12.2.0.1.191015

NAME                                                         RESOLVE                                                      ANNOTATION
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
apagwnrn                                                     (apa)gwnrn                                                   SQL Access Path Analysis ??
apagwnrnprd                                                  (apa)gwnrnprd                                                SQL Access Path Analysis ??
apatwnrn                                                     (apa)twnrn                                                   SQL Access Path Analysis ??
kafcpy_one_row                                               (kaf)cpy_one_row                                             kernel access fetch ??
kcbz_eff_bsz                                                 (kcbz)_eff_bsz                                               kernel cache buffers subroutines for kcb ??
kdilm_row_diskcompress_policy_type                           (kdil)m_row_diskcompress_policy_type                         kernel data index load ??
kdsReadAheadSafe                                             (kds)ReadAheadSafe                                           kernel data seek/scan ??
kfdFreeReqs                                                  (kfd)FreeReqs                                                kernel automatic storage management disk ??
kfdp_getNormalFgCnt                                          (kfdp)_getNormalFgCnt                                        kernel automatic storage management disk PST ??
kghunalo                                                     (kgh)unalo                                                   kernel generic heap manager ??
kjcts_syncseq_incident_dump                                  (kjc)ts_syncseq_incident_dump                                kernel lock management communication ??
kkfdIsXlate                                                  (kkfd)IsXlate                                                kernel compile fast dataflow (PQ DFO) ??
kkoRowNumLimit_Int                                           (kko)RowNumLimit_Int                                         kernel compile optimizer ??
kkoWnRowNumLimit                                             (kko)WnRowNumLimit                                           kernel compile optimizer ??
kkoarFreeStats                                               (kkoar)FreeStats                                             kernel compile optimizer automatic (sql) reoptimisation ??
kkqgbpValidPredCB                                            (kkqgbp)ValidPredCB                                          kernel compile query  group by placement ??
kkqoreApplyFKR                                               (kkqore)ApplyFKR                                             kernel compile query  or-expansion ??
kkqstIsOneToOneFunc                                          (kkq)stIsOneToOneFunc                                        kernel compile query  ??
kkquReplSCInMWithRefCB                                       (kkqu)ReplSCInMWithRefCB                                     kernel compile query  subquery unnesting ??
kkqvtOpnInView                                               (kkqvt)OpnInView                                             kernel compile query  vector transformation ??
kokujJsonSerialize                                           (kok)ujJsonSerialize                                         kernel objects kernel side ??
kpdbCheckCommonprofileCbk                                    (kpdb)CheckCommonprofileCbk                                  kernel programmatic interface pluggable database ??
kpdbSyncCreateProfile                                        (kpdbSync)CreateProfile                                      kernel programmatic interface pluggable database DBMS_PDB.KPDBSYNC SYNC_PDB ??
krvfptai_PutTxAuditInfo                                      (krv)fptai_PutTxAuditInfo                                    kernel redo recovery ??
krvtab                                                       (krvt)ab                                                     kernel redo recovery log miner viewer support ??
krvxdsr                                                      (krvx)dsr                                                    kernel redo recovery extract ??
ksmg_estimate_nonimc_sga_size                                (ksm)g_estimate_nonimc_sga_size                              kernel service  memory ??
ktspFetchMeta1                                               (ktsp)FetchMeta1                                             kernel transaction segment management segment pagetable ??
kzekmckdcw                                                   (kz)ekmckdcw                                                 kernel security ??
kzekmckdcw_cbk                                               (kz)ekmckdcw_cbk                                             kernel security ??
opiBindReorderInfo                                           (opi)BindReorderInfo                                         oracle program interface ??
qcpiJsonSerialize                                            (qcpi)JsonSerialize                                          query compile parse interim ??
qcsSqnLegalCB                                                (qcs)SqnLegalCB                                              query compile semantic analysis (parser) ??
qergiSetFirstPartFlag                                        (qergi)SetFirstPartFlag                                      query execute rowsource granule iterator (partitioning? or PX granules?) ??
qeroiFindGranuleIter                                         (qeroi)FindGranuleIter                                       query execute rowsource extensibel indexing query component ??
qesblZero                                                    (qesbl)Zero                                                  query execute services bloom filter ??
qjsnIsDollarOnly                                             (qjsn)IsDollarOnly                                           query json ??
qjsnJsonCreatDom                                             (qjsn)JsonCreatDom                                           query json ??
qjsn_ferrh                                                   (qjsn)_ferrh                                                 query json ??
qkaGetClusteringFactor                                       (qka)GetClusteringFactor                                     query kernel allocation ??
qkaIsRTRIMRequiredForViewCol                                 (qka)IsRTRIMRequiredForViewCol                               query kernel allocation ??
qksopCheckConstOrOptWithBindInAndChains                      (qksop)CheckConstOrOptWithBindInAndChains                    query kernel sql compilter operand processing ??
qksqbCorrToNonParent                                         (qksqb)CorrToNonParent                                       query kernel sql Query compilation for query blocks ??
qksvcCloneHJPred                                             (qksvc)CloneHJPred                                           query kernel sql Virtual Column ??

(disclaimer: I can’t look at the sourcecode, which means I look at the oracle executable with normal, modern tools. This also means that there’s a of stuff that I don’t see, for example if functionality has been added inside an existing function, then that’s totally invisible to me)

This post was created when trying to understand how the Oracle executable works. Specifically the logwriter, which, if it is posted by a process, which is done using semop(), signals that process back using semop() if the logwriter happens to be in post/wait mode, and is not using the ‘scalable logwriter mode’, which means it is not using additional worker processes.

To be more specific, I tried investigating something that is not Oracle specific, but specific to the usage of semaphores on linux with an executable for which you do not have the source code and is not compiled with debugging symbols.

I attached to the process using gdb, and put a break on semop:

$ gdb -p 1000
...
(gdb) break semop
Breakpoint 1 at 0x7fb92b0410c0: file ../sysdeps/unix/syscall-template.S, line 81.
(gdb) c
Continuing.

A word here: you probably will not see “file ../sysdeps/unix/syscall-template.S, line 81.”. This is because I installed the following debuginfo packages:

kernel-uek-debuginfo-4.14.35-1902.9.2.el7uek.x86_64
nss-softokn-debuginfo-3.44.0-5.0.1.el7.x86_64
kernel-uek-debuginfo-common-4.14.35-1902.9.2.el7uek.x86_64
glibc-debuginfo-common-2.17-292.0.1.el7.x86_64
libaio-debuginfo-0.3.109-13.el7.x86_64
numactl-debuginfo-2.0.12-3.el7_7.1.x86_64
glibc-debuginfo-2.17-292.0.1.el7.x86_64

When using Oracle linux (version 7), this is actually really easy, you add the debug info packages repo by adding the file /etc/yum.repos.d/debug.repo, and put this in the file:

[ol7_debuginfo]
name=Oracle Linux 7 debuginfo
baseurl=http://oss.oracle.com/ol7/debuginfo
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1

You now even can use the ‘debuginfo-install’ executable that gdb tells you to do. A word of warning too: this repository is not very closely maintained by oracle (sadly, I blogged about this in the past), so things might be missing. For example, the debuginfo package for the libgcc on my system can not be found by yum. Another issue I encountered, was that when I tried installing the debuginfo package for my kernel, I couldn’t just say debuginfo-install kernel-uek, because that installed the debuginfo package for the latest kernel. So I had to specifically point it to my exact kernel version. When installing the kernel debuginfo package, which is very bulky, another word of warning: the repo (at least for me) is limited to a very low bandwidth, so downloading the file (+200MB) took a long time.

I installed this with the idea to have all system variables, like the ones for semaphores, present, so I could look into them. This turned out not to be the case:

Continuing.

Breakpoint 1, semop () at ../sysdeps/unix/syscall-template.S:81
81	T_PSEUDO (SYSCALL_SYMBOL, SYSCALL_NAME, SYSCALL_NARGS)
(gdb)

Gdb broke execution because it encountered semop. Now let’s investigate. Because of the pseudo system call handler, we can only indirectly investigate the semop call. But how to know what to investigate? That’s where the manpages come in:

$ man semop
SEMOP(2)                Linux Programmer's Manual                  SEMOP(2)

NAME
       semop, semtimedop - System V semaphore operations

SYNOPSIS
       #include <sys/types.h>
       #include <sys/ipc.h>
       #include <sys/sem.h>

       int semop(int semid, struct sembuf *sops, unsigned nsops);

       int semtimedop(int semid, struct sembuf *sops, unsigned nsops,
                      struct timespec *timeout);

   Feature Test Macro Requirements for glibc (see feature_test_macros(7)):

       semtimedop(): _GNU_SOURCE

So, semop takes 3 arguments, the semid as integer, a struc sembuf that holds the actual operation to be executed and the number of operations in the sembuf.
We still can investigate this, by knowing how the arguments are passed to a function:
– The first argument is in the CPU register $rdi
– The second argument is in the CPU register $rsi
– The third argument is in the CPU register $rdx
Well, let’s look at our session:

(gdb) p $rdi
$1 = 229376
(gdb) p $rsi
$2 = 140721769476432
(gdb) p $rdx
$3 = 1

So, the simple information is available directly, the semid is 229376, and there is 1 operation.
Let’s look at semid 229376 (warning: you have to have access to the semaphore array to be able to see it):

$ ipcs -si 229376

Semaphore Array semid=229376
uid=54321	 gid=54321	 cuid=54321	 cgid=54321
mode=0600, access_perms=0600
nsems = 250
otime = Sun Jan 19 16:36:30 2020
ctime = Sun Jan 19 15:52:22 2020
semnum     value      ncount     zcount     pid
0          1          0          0          3524
1          9065       0          0          3524
2          13900      0          0          3524
3          32766      0          0          3524
4          0          0          0          0
5          0          0          0          0
6          0          1          0          9340
7          0          1          0          9347
8          0          1          0          9356
9          0          0          0          0
10         0          1          0          10146
11         0          1          0          10163
12         0          1          0          30940
13         0          1          0          10189
14         0          1          0          10189
15         0          1          0          0
...and so on...

Okay, so in order to understand what that semop call does, we need to look into the struct.

But this is what gdb says:

(gdb) p $rsi
$4 = 140721769476432

Wait a minute, didn’t the main page say: struct sembuf *sops? That asterisk (‘*’) means it’s a pointer. Let’s try that:

(gdb) p * $rsi
$5 = 65574

Well…not sure what that means…

(gdb) ptype *$rsi
type = int

Ah…it thinks it’s an integer, and displays that… That’s not very helpful.

You can cast (declare a variable to be of a certain type, not the magician type of thing) a variable, so let’s try that:

(gdb) p (struct sembuf *) $rsi
No struct type named sembuf.

Mhhh, despite installing all these debuginfo packages, it turns out the struct definition is not available.

But I really want to know the semaphore information!

I found this gdb feature:

(gdb) help add-symbol-file
Load symbols from FILE, assuming FILE has been dynamically loaded.
Usage: add-symbol-file FILE ADDR [-s <SECT> <SECT_ADDR> -s <SECT> <SECT_ADDR> ...]
ADDR is the starting address of the file's text.
The optional arguments are section-name section-address pairs and
should be specified if the data and bss segments are not contiguous
with the text.  SECT is a section name to be loaded at SECT_ADDR.

So, I can add symbols from a file, provided that file is dynamically loadable. What if I create a mini file with the definition of sembuf? Would that work??

First create a very small c program that only defines a sembuf variable:

$ cat semh.c
#include <sys/sem.h>
struct sembuf mysembuf;

That’s two lines, that really is small, isn’t it?
Then compile it, but do not link it, we only need the object file:

$ gcc -c -g semh.c -o semh.o

(the ‘-c’ switch makes it only compile, not linking)

Now we got an object file semh.o. Let’s try to “side-load” that:

(gdb) add-symbol-file semh.o 0
add symbol table from file "semh.o" at
	.text_addr = 0x0
(y or n) y
Reading symbols from /home/oracle/pin-3.11-97998-g7ecce2dac-gcc-linux/semh.o...done.
(gdb)

(you have to say ‘y’ for it to load the symbol table at address 0x0)

Now let’s try casting again:

(gdb) print (struct sembuf *) $rsi
$3 = (struct sembuf *) 0x7ffc5714f150

And now we can ask gdb to print the casted variable:

(gdb) p *$3
$5 = {sem_num = 38, sem_op = 1, sem_flg = 0}

And that’s because it knows how it looks like:

(gdb) ptype *$3
type = struct sembuf {
    unsigned short sem_num;
    short sem_op;
    short sem_flg;
}

Now this information can be used to find the process the semop call is executed for:

$ ipcs -si 229376 | grep ^38
38         0          1          0          32324

So process 32324.

For the difference between Oracle database versions 18.8 and 18.9 this too follows the line of a low amount of differences.

As always, there are some parameters that have changed from being undocumented spare to being undocumented with a name.

Also, the DBA and CDB table (DBA|CDB)_REGISTRY_BACKPORTS is back again. The disappearance of this table in 18.8 turned out to be a bug. There is a patch for 18.8 if you need this table.

parameters unique in version 18.8 versus 18.9

NAME
--------------------------------------------------
_eighth_spare_parameter
_one-hundred-and-forty-eighth_spare_parameter
_second_spare_parameter

parameters unique in version 18.9 versus 18.8

NAME
--------------------------------------------------
_bug29825525_bct_public_dba_buffer_dynresize_delay
_enable_ptime_update_for_sys
_session_modp_list

dba tables unique to 18.8 versus 18.9

dba tables unique to 18.9 versus 18.8

NAME
----------------------------------------------------------------------------------------------------
DBA_REGISTRY_BACKPORTS

cdb tables unique to 18.8 versus 18.9

cdb tables unique to 18.9 versus 18.8

NAME
----------------------------------------------------------------------------------------------------
CDB_REGISTRY_BACKPORTS

On the C function side, it becomes apparent that this update is truly an update, roughly the number of functions that have gone and appeared are the same. A large portion of the functions that are removed in 18.9 are functions that have to do with AWR, and some other functions dealing with ASM and others.

The functions that have been added seem to be quite diverse,

code symbol names unique in version 18.8 versus 18.9

NAME                                                         RESOLVE                                                      ANNOTATION
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
R_CR_entropy_resource_init                                   R_CR_entropy_resource_init                                   ??
kcfis_clear_lob                                              (kcfis)_clear_lob                                            kernel cache file management intelligent storage ??
kds_update_turbo_scan_pivot_statistics                       (kds)_update_turbo_scan_pivot_statistics                     kernel data seek/scan ??
kewramcs_app_map_condbid_str                                 (kewr)amcs_app_map_condbid_str                               kernel event AWR repository ??
kewramvn_append_mdb_vvwname                                  (kewr)amvn_append_mdb_vvwname                                kernel event AWR repository ??
kewraxc_attribute_xcb                                        (kewr)axc_attribute_xcb                                      kernel event AWR repository ??
kewrbtsg_build_topseg                                        (kewr)btsg_build_topseg                                      kernel event AWR repository ??
kewrccsq_collect_csql                                        (kewr)ccsq_collect_csql                                      kernel event AWR repository ??
kewrgcfes_get_cacheid_from_enum_str                          (kewr)gcfes_get_cacheid_from_enum_str                        kernel event AWR repository ??
kewrmplvl_map_snap_level                                     (kewr)mplvl_map_snap_level                                   kernel event AWR repository ??
kewrpfbue_pdb_from_buffer_entry                              (kewr)pfbue_pdb_from_buffer_entry                            kernel event AWR repository ??
kewrptsq_prep_topsql                                         (kewr)ptsq_prep_topsql                                       kernel event AWR repository ??
kewrrdsi_rank_dstat_item                                     (kewr)rdsi_rank_dstat_item                                   kernel event AWR repository ??
kewrrtsq_rank_topsql                                         (kewr)rtsq_rank_topsql                                       kernel event AWR repository ??
kewrsaobn_set_all_objnames                                   (kewr)saobn_set_all_objnames                                 kernel event AWR repository ??
kewrsonie_set_object_names_in_entry                          (kewr)sonie_set_object_names_in_entry                        kernel event AWR repository ??
kewrsqlc_sql_iscolored_cb                                    (kewr)sqlc_sql_iscolored_cb                                  kernel event AWR repository ??
kfatknHsh                                                    (kfa)tknHsh                                                  kernel automatic storage management alias operations ??
kfdDskComputeHashForRow                                      (kfd)DskComputeHashForRow                                    kernel automatic storage management disk ??
kfioSrMsgBuf_ack                                             (kfio)SrMsgBuf_ack                                           kernel automatic storage management translation I/O layer ??
kfkcrRefresh                                                 (kfk)crRefresh                                               kernel automatic storage management KFK ??
kfnFreeKfnpnmMem                                             (kfn)FreeKfnpnmMem                                           kernel automatic storage management networking subsystem ??
kkqtutlSelItemMatches                                        (kkqt)utlSelItemMatches                                      kernel compile query  table ??
kkquIsCorrTransInList                                        (kkqu)IsCorrTransInList                                      kernel compile query  subquery unnesting ??
krvxgtf                                                      (krvx)gtf                                                    kernel redo recovery extract ??
krvxrsr_SkipRedo                                             (krvx)rsr_SkipRedo                                           kernel redo recovery extract ??
ksmg_estimate_sgamax                                         (ksm)g_estimate_sgamax                                       kernel service  memory ??
kzagetcid                                                    (kza)getcid                                                  kernel security audit  ??
lxCharsetIsByteUnique                                        (l)xCharsetIsByteUnique                                      core library functions ??
qeroiFirstPart                                               (qeroi)FirstPart                                             query execute rowsource extensibel indexing query component ??
qkspmTravInit                                                (qkspm)TravInit                                              query kernel sql plan management ??
ri_entcb_cmd_func                                            ri_entcb_cmd_func                                            ??
zt_yield_entropy_source_cb                                   (zt)_yield_entropy_source_cb                                 security encryption ??

code symbol names unique in version 18.9 versus 18.8

NAME                                                         RESOLVE                                                      ANNOTATION
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
apagwnrn                                                     (apa)gwnrn                                                   SQL Access Path Analysis ??
apagwnrnprd                                                  (apa)gwnrnprd                                                SQL Access Path Analysis ??
apatwnrn                                                     (apa)twnrn                                                   SQL Access Path Analysis ??
kafcpy_one_row                                               (kaf)cpy_one_row                                             kernel access fetch ??
kcbz_eff_bsz                                                 (kcbz)_eff_bsz                                               kernel cache buffers subroutines for kcb ??
kdilm_row_diskcompress_policy_type                           (kdil)m_row_diskcompress_policy_type                         kernel data index load ??
kdsReadAheadSafe                                             (kds)ReadAheadSafe                                           kernel data seek/scan ??
kewrcc_bind_cb                                               (kewr)cc_bind_cb                                             kernel event AWR repository ??
kewrcc_check_columns                                         (kewr)cc_check_columns                                       kernel event AWR repository ??
kewrcc_fetch_cb                                              (kewr)cc_fetch_cb                                            kernel event AWR repository ??
kfdp_getNormalFgCnt                                          (kfdp)_getNormalFgCnt                                        kernel automatic storage management disk PST ??
kfkIsAFDLoaded                                               (kfk)IsAFDLoaded                                             kernel automatic storage management KFK ??
kgskltyp                                                     (kgsk)ltyp                                                   kernel generic service resource manager ??
kjcts_syncseq_incident_dump                                  (kjc)ts_syncseq_incident_dump                                kernel lock management communication ??
kkoRowNumLimit_Int                                           (kko)RowNumLimit_Int                                         kernel compile optimizer ??
kkoWnRowNumLimit                                             (kko)WnRowNumLimit                                           kernel compile optimizer ??
kkqljpUpdateXplAnn                                           (kkq)ljpUpdateXplAnn                                         kernel compile query  ??
kkqoreAndDriver                                              (kkqore)AndDriver                                            kernel compile query  or-expansion ??
kpcxdrBindReorderInfo                                        (kp)cxdrBindReorderInfo                                      kernel programmatic interface ??
kpdbCheckCommonprofileCbk                                    (kpdb)CheckCommonprofileCbk                                  kernel programmatic interface pluggable database ??
kpdbSyncCreateProfile                                        (kpdbSync)CreateProfile                                      kernel programmatic interface pluggable database DBMS_PDB.KPDBSYNC SYNC_PDB ??
kpossGetEncNonTemplateOverflow                               (kpo)ssGetEncNonTemplateOverflow                             kernel programmatic interface oracle ??
kpossSetEncNonTemplateOverflow                               (kpo)ssSetEncNonTemplateOverflow                             kernel programmatic interface oracle ??
kpossTemplateMatch_                                          (kpo)ssTemplateMatch_                                        kernel programmatic interface oracle ??
kpossTemplateSet                                             (kpo)ssTemplateSet                                           kernel programmatic interface oracle ??
kpussTemplateSend                                            (kpu)ssTemplateSend                                          kernel programmatic interface user ??
kpuxcSessionSignatureRecv                                    (kpuxc)SessionSignatureRecv                                  kernel programmatic interface user db replay? ??
kpuxcSessionTemplateSend                                     (kpuxc)SessionTemplateSend                                   kernel programmatic interface user db replay? ??
kpuxcSessionTemplatesFree                                    (kpuxc)SessionTemplatesFree                                  kernel programmatic interface user db replay? ??
krvxdsr                                                      (krvx)dsr                                                    kernel redo recovery extract ??
ksmg_estimate_nonimc_sga_size                                (ksm)g_estimate_nonimc_sga_size                              kernel service  memory ??
ksp_init_modp_send                                           (ksp)_init_modp_send                                         kernel service  parameter ??
ksp_modp_get_enckeyvals                                      (ksp)_modp_get_enckeyvals                                    kernel service  parameter ??
ksp_modp_get_keyvals                                         (ksp)_modp_get_keyvals                                       kernel service  parameter ??
ksp_modp_set_enckeyvals                                      (ksp)_modp_set_enckeyvals                                    kernel service  parameter ??
ksp_modp_set_keyvals                                         (ksp)_modp_set_keyvals                                       kernel service  parameter ??
ksp_modp_update_sign                                         (ksp)_modp_update_sign                                       kernel service  parameter ??
kspdecbuf                                                    (ksp)decbuf                                                  kernel service  parameter ??
kspencbuf                                                    (ksp)encbuf                                                  kernel service  parameter ??
qergiSetFirstPartFlag                                        (qergi)SetFirstPartFlag                                      query execute rowsource granule iterator (partitioning? or PX granules?) ??
qeroiFindGranuleIter                                         (qeroi)FindGranuleIter                                       query execute rowsource extensibel indexing query component ??
qjsnIsDollarOnly                                             (qjsn)IsDollarOnly                                           query json ??
qjsnJsonSerialize_optim                                      (qjsn)JsonSerialize_optim                                    query json ??
qkaIsRTRIMRequiredForViewCol                                 (qka)IsRTRIMRequiredForViewCol                               query kernel allocation ??
qksopCheckConstOrOptWithBindInAndChains                      (qksop)CheckConstOrOptWithBindInAndChains                    query kernel sql operand processing ??

(disclaimer: I can’t look at the sourcecode, which means I look at the oracle executable with normal, modern tools. This also means that there’s a of stuff that I don’t see, for example if functionality has been added inside an existing function, then that’s totally invisible to me)

As expected, there aren’t any really drastic differences between Oracle database version 19.5 and 19.6. Now that I am doing these series on differences for all the versions every quarter the new release updates are coming out, there is a certain line, and this release does follow that.

As always, there are some parameters that have changed from being undocumented spare to being undocumented with a name. There is one documented parameter that was added: optimizer_session_type, which has gone official from “_optimizer_auto_index_allow”; see bug 29632611.

parameters unique in version 19.5 versus 19.6

NAME
--------------------------------------------------
_fifth_spare_parameter
_one-hundred-and-fifty-fifth_spare_parameter
_one-hundred-and-fifty-fourth_spare_parameter
_one-hundred-and-fifty-sixth_spare_parameter
_optimizer_auto_index_allow
_sixth_spare_parameter

parameters unique in version 19.6 versus 19.5

NAME
--------------------------------------------------
_bug29825525_bct_public_dba_buffer_dynresize_delay
_bug30224950_kjac_direct_path_enabled
_enable_ptime_update_for_sys
_path_prefix_create_dir
_smart_log_threshold_usec
optimizer_session_type

No data dictionary changes have been found.

On the C function side, it becomes apparent that there’s development taking place. For the functions that have been removed, there’s a bunch that deal with ASM (kfd), some with kubs which has to do with big data SQL and some XDB/XML stuff.

There’s way more functions that gotten into the newest release update. There’s a lot of unknowns, ASM, application continuity, lots of big data SQL, JSON, polymorphic table functions, XML, and something that looks like it has to do with SIMD processing (skfSIMD) for which I don’t know what the ‘f’ is there (functions?). There’s also a whole batch of functions starting with ‘rest_nhp’, I yet have to look into these.

code symbol names unique in version 19.5 versus 19.6

NAME                                                         RESOLVE                                                      ANNOTATION
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
jskqJobQReadDiskCbk                                          (js)kqJobQReadDiskCbk                                        job scheduing ??
kcbo_incr_doc                                                (kcbo)_incr_doc                                              kernel cache buffers object queue ??
kcfis_clear_lob                                              (kcfis)_clear_lob                                            kernel cache file management intelligent storage ??
kcoclnup602                                                  (kco)clnup602                                                kernel cache operation ??
kewraxc_attribute_xcb                                        (kewr)axc_attribute_xcb                                      kernel event AWR repository ??
kfMaxParityHoles                                             (kf)MaxParityHoles                                           kernel automatic storage management ??
kfdDskComputeHashForRow                                      (kfd)DskComputeHashForRow                                    kernel automatic storage management disk ??
kfdDskTableCbInternal                                        (kfd)DskTableCbInternal                                      kernel automatic storage management disk ??
kfgComputeGrpProp                                            (kfg)ComputeGrpProp                                          kernel automatic storage management diskgroups ??
kfgGrpTableCbInternal                                        (kfg)GrpTableCbInternal                                      kernel automatic storage management diskgroups ??
kgds_dump_callers_sub                                        (kgds)_dump_callers_sub                                      kernel generic vos generic stack trace ??
kgfdDiscoverSubmit                                           (kgfd)DiscoverSubmit                                         kernel generic ASM io subsystem driver ??
kgh_size_sanity_check                                        (kgh)_size_sanity_check                                      kernel generic heap manager ??
kjccspbat                                                    (kjc)cspbat                                                  kernel lock management communication ??
kkeTbRowCPUCost                                              (kke)TbRowCPUCost                                            kernel compile cost engine ??
kkqjePatchCol                                                (kkqj)ePatchCol                                              kernel compile query  join analysis ??
krbbCountThreads                                             (krbb)CountThreads                                           kernel redo backup/restore creation of a backuppiece ??
ksadmb                                                       ksadmb                                                       kernel service  asynchronous messages deallocate message buffer
ksdhng_wdat_rem_dup                                          (ksdhng)_wdat_rem_dup                                        kernel service  debug internal errors hang analyze ??
kubsprqcrio_close                                            (kubsprq)crio_close                                          kernel utility big data sql parquet ??
kubsprqcrio_read                                             (kubsprq)crio_read                                           kernel utility big data sql parquet ??
kubsprqcrio_seek                                             (kubsprq)crio_seek                                           kernel utility big data sql parquet ??
kubsprqioAdvise                                              (kubsprq)ioAdvise                                            kernel utility big data sql parquet ??
kubsprqioClose                                               (kubsprq)ioClose                                             kernel utility big data sql parquet ??
kubsprqioOpen                                                (kubsprq)ioOpen                                              kernel utility big data sql parquet ??
kubsprqioRead                                                (kubsprq)ioRead                                              kernel utility big data sql parquet ??
kzagetcid                                                    (kza)getcid                                                  kernel security audit  ??
qeroiFirstPart                                               (qeroi)FirstPart                                             query execute rowsource extensibel indexing query component ??
qjsnGetBinFromOSON                                           (qjsn)GetBinFromOSON                                         query json ??
qksptfOCIerr                                                 (qksptf)OCIerr                                               query kernel sql polymorphic table functions compilation ??
qmxgniCheckLOBSize                                           (qmx)gniCheckLOBSize                                         query XDB XML Objects ??
qmxgniHasNodeIdOrCSX                                         (qmx)gniHasNodeIdOrCSX                                       query XDB XML Objects ??
qmxgniReturnString                                           (qmx)gniReturnString                                         query XDB XML Objects ??
qmxgniUnderEvent                                             (qmx)gniUnderEvent                                           query XDB XML Objects ??
rwssid_andv                                                  (rws)sid_andv                                                row source ??

code symbol names unique in version 19.6 versus 19.5

NAME                                                         RESOLVE                                                      ANNOTATION
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
CJPathConsumer2D_create                                      CJPathConsumer2D_create                                      ??
CJPathConsumer2D_staticFinalize                              CJPathConsumer2D_staticFinalize                              ??
CJPathConsumer2D_staticInitialize                            CJPathConsumer2D_staticInitialize                            ??
Java_sun_dc_pr_PathStroker_cInitialize2D                     Java_sun_dc_pr_PathStroker_cInitialize2D                     ??
PC2D__cleanup                                                PC2D__cleanup                                                ??
PC2D__enumCoObs                                              PC2D__enumCoObs                                              ??
PC2D_appendCubic                                             PC2D_appendCubic                                             ??
PC2D_appendLine                                              PC2D_appendLine                                              ??
PC2D_appendQuad                                              PC2D_appendQuad                                              ??
PC2D_beginPath                                               PC2D_beginPath                                               ??
PC2D_beginSubpath                                            PC2D_beginSubpath                                            ??
PC2D_className                                               PC2D_className                                               ??
PC2D_closedSubpath                                           PC2D_closedSubpath                                           ??
PC2D_copy                                                    PC2D_copy                                                    ??
PC2D_endPath                                                 PC2D_endPath                                                 ??
PC2D_useProxy                                                PC2D_useProxy                                                ??
crio_close                                                   crio_close                                                   ??
crio_read                                                    crio_read                                                    ??
crio_seek                                                    crio_seek                                                    ??
dbnest_etime_op                                              (dbnest)_etime_op                                            dbnest ??
dcpr_PCClosePath                                             dcpr_PCClosePath                                             ??
dcpr_PCCubicTo                                               dcpr_PCCubicTo                                               ??
dcpr_PCLineTo                                                dcpr_PCLineTo                                                ??
dcpr_PCMoveTo                                                dcpr_PCMoveTo                                                ??
dcpr_PCPathDone                                              dcpr_PCPathDone                                              ??
dcpr_PCQuadTo                                                dcpr_PCQuadTo                                                ??
dmqlDeserLogicalCheck                                        dmqlDeserLogicalCheck                                        ??
dmsarProdDump                                                dmsarProdDump                                                ??
dmsspKroneckerChk                                            dmsspKroneckerChk                                            ??
dmsspProdDump                                                dmsspProdDump                                                ??
dtbfti_find_tobedrp_ix                                       (dtb)fti_find_tobedrp_ix                                     drop table ??
jskqCountClassesUsingService                                 (js)kqCountClassesUsingService                               job scheduing ??
jskqJobQueuePDBReload                                        (js)kqJobQueuePDBReload                                      job scheduing ??
jskqUpdateQueueNextRunTime                                   (js)kqUpdateQueueNextRunTime                                 job scheduing ??
jznDecodeOSON4IMCJMetaD                                      (jzn)DecodeOSON4IMCJMetaD                                    json ??
jznOctDistFnmAllocSz                                         (jzn)OctDistFnmAllocSz                                       json ??
jznoctGetArraySizeI                                          (jzn)octGetArraySizeI                                        json ??
jznoctIsTopScalar                                            (jzn)octIsTopScalar                                          json ??
kciVARCHAR2sz                                                (kci)VARCHAR2sz                                              kernel cache oracle text ??
kcoclnup                                                     (kco)clnup                                                   kernel cache operation ??
kcrfw_alfs_sl_emulate                                        (kcrfw_alfs)_sl_emulate                                      kernel cache recovery file write/broadcast SCN adaptive log file sync ??
kcrfw_alfs_sl_save_redowrite_time                            (kcrfw_alfs)_sl_save_redowrite_time                          kernel cache recovery file write/broadcast SCN adaptive log file sync ??
kcrfw_alfs_sl_update_mode                                    (kcrfw_alfs)_sl_update_mode                                  kernel cache recovery file write/broadcast SCN adaptive log file sync ??
kcrfw_dax_is_smart_log                                       (kcrfw)_dax_is_smart_log                                     kernel cache recovery file write/broadcast SCN ??
kewrcc_bind_cb                                               (kewr)cc_bind_cb                                             kernel event AWR repository ??
kewrcc_check_columns                                         (kewr)cc_check_columns                                       kernel event AWR repository ??
kewrcc_fetch_cb                                              (kewr)cc_fetch_cb                                            kernel event AWR repository ??
kfGetParityFailureMask                                       (kf)GetParityFailureMask                                     kernel automatic storage management ??
kfParityCalP                                                 (kf)ParityCalP                                               kernel automatic storage management ??
kfParityCalP0                                                (kf)ParityCalP0                                              kernel automatic storage management ??
kfParityCalQ                                                 (kf)ParityCalQ                                               kernel automatic storage management ??
kfParityCalQ0                                                (kf)ParityCalQ0                                              kernel automatic storage management ??
kfdBlockContentCheck                                         (kfd)BlockContentCheck                                       kernel automatic storage management disk ??
kfdp_getNormalFgCnt                                          (kfdp)_getNormalFgCnt                                        kernel automatic storage management disk PST ??
kfknodecFreeCb                                               (kfk)nodecFreeCb                                             kernel automatic storage management KFK ??
kfnASMBnRunning                                              (kfn)ASMBnRunning                                            kernel automatic storage management networking subsystem ??
kglLockWaitTime                                              (kglLock)WaitTime                                            kernel generic library cache management library cache lock ??
kgskltyp                                                     (kgsk)ltyp                                                   kernel generic service resource manager ??
kgwsm_reset                                                  (kg)wsm_reset                                                kernel generic ??
kjac_dp_alloc                                                (kjac)_dp_alloc                                              kernel lock management application continuity  ??
kjac_dp_bind_tim                                             (kjac)_dp_bind_tim                                           kernel lock management application continuity  ??
kjac_dp_bind_uin                                             (kjac)_dp_bind_uin                                           kernel lock management application continuity  ??
kjac_dp_comp_bin                                             (kjac)_dp_comp_bin                                           kernel lock management application continuity  ??
kjac_dp_comp_uin                                             (kjac)_dp_comp_uin                                           kernel lock management application continuity  ??
kjac_dp_enabled_cbk                                          (kjac)_dp_enabled_cbk                                        kernel lock management application continuity  ??
kjac_dp_free                                                 (kjac)_dp_free                                               kernel lock management application continuity  ??
kjac_dp_insert_init                                          (kjac)_dp_insert_init                                        kernel lock management application continuity  ??
kjac_dp_md_init                                              (kjac)_dp_md_init                                            kernel lock management application continuity  ??
kjac_dp_open                                                 (kjac)_dp_open                                               kernel lock management application continuity  ??
kjac_dp_recover                                              (kjac)_dp_recover                                            kernel lock management application continuity  ??
kjac_dp_update                                               (kjac)_dp_update                                             kernel lock management application continuity  ??
kjac_dp_update_init                                          (kjac)_dp_update_init                                        kernel lock management application continuity  ??
kjac_dp_update_state                                         (kjac)_dp_update_state                                       kernel lock management application continuity  ??
kjac_dpctx_alloc                                             (kjac)_dpctx_alloc                                           kernel lock management application continuity  ??
kjac_dpctx_free                                              (kjac)_dpctx_free                                            kernel lock management application continuity  ??
kjac_dpinfo_close                                            (kjac)_dpinfo_close                                          kernel lock management application continuity  ??
kjac_dpinfo_open                                             (kjac)_dpinfo_open                                           kernel lock management application continuity  ??
kjac_sql_ltxid_tabid                                         (kjac)_sql_ltxid_tabid                                       kernel lock management application continuity  ??
kjac_sql_materialize_part                                    (kjac)_sql_materialize_part                                  kernel lock management application continuity  ??
kjac_trace_event_cbk                                         (kjac)_trace_event_cbk                                       kernel lock management application continuity  ??
kjfclmdrsc                                                   (kj)fclmdrsc                                                 kernel lock management ??
kjfclmdsgvbi                                                 (kj)fclmdsgvbi                                               kernel lock management ??
kjgcr_GetTopCPU                                              (kj)gcr_GetTopCPU                                            kernel lock management ??
kjgcr_RunSyncTask                                            (kj)gcr_RunSyncTask                                          kernel lock management ??
kjgcr_SlaveReqGetSlot                                        (kj)gcr_SlaveReqGetSlot                                      kernel lock management ??
kkbati_add_tbd_ix                                            (kkb)ati_add_tbd_ix                                          kernel compile table ??
kkbdti_drop_tbd_ix                                           (kkb)dti_drop_tbd_ix                                         kernel compile table ??
kkdlVirtColCB                                                (kkdl)VirtColCB                                              kernel compile dictionary lookup ??
kkoUpdOrdTabWithOrdSubqHints                                 (kko)UpdOrdTabWithOrdSubqHints                               kernel compile optimizer ??
kkqjfCopyFroNoChn                                            (kkqjf)CopyFroNoChn                                          kernel compile query  join analysis join factorization ??
kpdbcCheckRedoApplyNeeded                                    (kpdb)cCheckRedoApplyNeeded                                  kernel programmatic interface pluggable database ??
kpoxcAppContPDBNotify                                        (kpo)xcAppContPDBNotify                                      kernel programmatic interface oracle ??
kpudpParquetInt96                                            (kpudp)ParquetInt96                                          kernel programmatic interface user DPAPI Load ??
ksfqsrfn                                                     (ksfq)srfn                                                   kernel service  functions sequential file io interface ??
kswsLdiToInt                                                 (ksws)LdiToInt                                               kernel service  workgroup services ??
ksws_alb_colocation_tag_cbk                                  (ksws)_alb_colocation_tag_cbk                                kernel service  workgroup services ??
ksws_alb_mark_stale_colocated_sessions                       (ksws)_alb_mark_stale_colocated_sessions                     kernel service  workgroup services ??
ksws_alb_set_session_colocation                              (ksws)_alb_set_session_colocation                            kernel service  workgroup services ??
kubsBUFioAdvise                                              (kubs)BUFioAdvise                                            kernel utility big data sql ??
kubsBUFioClose                                               (kubs)BUFioClose                                             kernel utility big data sql ??
kubsBUFioOpen                                                (kubs)BUFioOpen                                              kernel utility big data sql ??
kubsBUFioRead                                                (kubs)BUFioRead                                              kernel utility big data sql ??
kubsCRioODM_close                                            (kubs)CRioODM_close                                          kernel utility big data sql ??
kubsCRioODM_finish                                           (kubs)CRioODM_finish                                         kernel utility big data sql ??
kubsCRioODM_finishHelper                                     (kubs)CRioODM_finishHelper                                   kernel utility big data sql ??
kubsCRioODM_init                                             (kubs)CRioODM_init                                           kernel utility big data sql ??
kubsCRioODM_initHelper                                       (kubs)CRioODM_initHelper                                     kernel utility big data sql ??
kubsCRioODM_opcsetupgp                                       (kubs)CRioODM_opcsetupgp                                     kernel utility big data sql ??
kubsCRioODM_openHelper                                       (kubs)CRioODM_openHelper                                     kernel utility big data sql ??
kubsCRioODM_read                                             (kubs)CRioODM_read                                           kernel utility big data sql ??
kubsCRioODM_readHelper                                       (kubs)CRioODM_readHelper                                     kernel utility big data sql ??
kubsCRioREST_allocNextFromDSArray                            (kubs)CRioREST_allocNextFromDSArray                          kernel utility big data sql ??
kubsCRioREST_close                                           (kubs)CRioREST_close                                         kernel utility big data sql ??
kubsCRioREST_finish                                          (kubs)CRioREST_finish                                        kernel utility big data sql ??
kubsCRioREST_httpInit                                        (kubs)CRioREST_httpInit                                      kernel utility big data sql ??
kubsCRioREST_httpRequest                                     (kubs)CRioREST_httpRequest                                   kernel utility big data sql ??
kubsCRioREST_id                                              (kubs)CRioREST_id                                            kernel utility big data sql ??
kubsCRioREST_id_add                                          (kubs)CRioREST_id_add                                        kernel utility big data sql ??
kubsCRioREST_init                                            (kubs)CRioREST_init                                          kernel utility big data sql ??
kubsCRioREST_read                                            (kubs)CRioREST_read                                          kernel utility big data sql ??
kubsCRioREST_validate_url                                    (kubs)CRioREST_validate_url                                  kernel utility big data sql ??
kubsCRioSPescape                                             (kubs)CRioSPescape                                           kernel utility big data sql ??
kubsCRioURLencode                                            (kubs)CRioURLencode                                          kernel utility big data sql ??
kubsCRio_get_cell_wallet_path                                (kubs)CRio_get_cell_wallet_path                              kernel utility big data sql ??
kubsCRio_get_rdbms_wallet_path                               (kubs)CRio_get_rdbms_wallet_path                             kernel utility big data sql ??
kubsavroGetDecimalTpeProp                                    (kubsavro)GetDecimalTpeProp                                  kernel utility big data sql avro ??
kubsprqGetBoolProp                                           (kubsprq)GetBoolProp                                         kernel utility big data sql parquet ??
kubsprqtGetData                                              (kubsprq)tGetData                                            kernel utility big data sql parquet ??
kubsprqtGetDataByte                                          (kubsprq)tGetDataByte                                        kernel utility big data sql parquet ??
kubsprqtRead                                                 (kubsprq)tRead                                               kernel utility big data sql parquet ??
kubsprqtReadVarint                                           (kubsprq)tReadVarint                                         kernel utility big data sql parquet ??
kubsprquReadZVarint                                          (kubsprq)uReadZVarint                                        kernel utility big data sql parquet ??
kubsxGetConfigList                                           (kubs)xGetConfigList                                         kernel utility big data sql ??
kubsxiGetLocalCreds                                          (kubs)xiGetLocalCreds                                        kernel utility big data sql ??
kubsxiGetNextCell                                            (kubs)xiGetNextCell                                          kernel utility big data sql ??
kupaxisosf                                                   (kup)axisosf                                                 kernel utility datapump ??
kwqaexgcur                                                   (kwqa)exgcur                                                 kernel advanced queuing  administration ??
kwqaexgcurobc                                                (kwqa)exgcurobc                                              kernel advanced queuing  administration ??
kxes_sqlcomp_check_1                                         (kx)es_sqlcomp_check_1                                       kernel execution ??
kxes_sqlcomp_check_macro                                     (kx)es_sqlcomp_check_macro                                   kernel execution ??
kzvCheckGrantInRoot_cbk                                      (kz)vCheckGrantInRoot_cbk                                    kernel security ??
kzvGrantedCommonlyInRoot                                     (kz)vGrantedCommonlyInRoot                                   kernel security ??
ntt_pptlv                                                    (ntt)_pptlv                                                  network transport (drivers) tcp/ip ??
odm_apg                                                      (odm)_apg                                                    oracle disk manager ??
odm_fpg                                                      (odm)_fpg                                                    oracle disk manager ??
odm_get_creds                                                (odm)_get_creds                                              oracle disk manager ??
odm_get_proxy                                                (odm)_get_proxy                                              oracle disk manager ??
odm_oua                                                      (odm)_oua                                                    oracle disk manager ??
odm_pec                                                      (odm)_pec                                                    oracle disk manager ??
odm_printf                                                   (odm)_printf                                                 oracle disk manager ??
odm_wrf                                                      (odm)_wrf                                                    oracle disk manager ??
ph2osfm_setup_function_metadata                              (ph2)osfm_setup_function_metadata                            PLSQL phase 2 (semantic analyzer) ??
ph2scs_semantic_checks_for_sqm                               (ph2)scs_semantic_checks_for_sqm                             PLSQL phase 2 (semantic analyzer) ??
qctoj_obadi4DateTimeStuff                                    (qcto)j_obadi4DateTimeStuff                                  query compile type check operations operators ??
qergiSetFirstPartFlag                                        (qergi)SetFirstPartFlag                                      query execute rowsource granule iterator (partitioning? or PX granules?) ??
qeroiFindGranuleIter                                         (qeroi)FindGranuleIter                                       query execute rowsource extensibel indexing query component ??
qertrcGetEncMaxLen                                           (qer)trcGetEncMaxLen                                         query execute rowsource ??
qerupFindChiHC                                               (qerup)FindChiHC                                             query execute rowsource update ??
qerupUpdStatsCbk                                             (qerup)UpdStatsCbk                                           query execute rowsource update ??
qerxjConvertToBinaryNumber                                   (qerxj)ConvertToBinaryNumber                                 query execute rowsource json ??
qerxjConvertToInterval                                       (qerxj)ConvertToInterval                                     query execute rowsource json ??
qjsnJSON_DML_OptimOccurred                                   (qjsn)JSON_DML_OptimOccurred                                 query json ??
qjsnOKToApplySkipCpOSONOptim                                 (qjsn)OKToApplySkipCpOSONOptim                               query json ??
qjsnRetBinaryNumber                                          (qjsn)RetBinaryNumber                                        query json ??
qjsnRetInterval                                              (qjsn)RetInterval                                            query json ??
qjsn_can_skip_cpy_oson_bytes                                 (qjsn)_can_skip_cpy_oson_bytes                               query json ??
qjsn_can_skip_is_json_validation                             (qjsn)_can_skip_is_json_validation                           query json ??
qjsngGetBinFromOSON                                          (qjsn)gGetBinFromOSON                                        query json ??
qjsngRaiseSQLError                                           (qjsn)gRaiseSQLError                                         query json ??
qjsngStringToDateDty3                                        (qjsn)gStringToDateDty3                                      query json ??
qjsnlobCheckLobPatch                                         (qjsn)lobCheckLobPatch                                       query json ??
qkaIsRTRIMRequiredForViewCol                                 (qka)IsRTRIMRequiredForViewCol                               query kernel allocation ??
qksopCheckConstOrOptWithBindInAndChains                      (qksop)CheckConstOrOptWithBindInAndChains                    query kernel sql compilter operand processing ??
qksopFindQbByName                                            (qksop)FindQbByName                                          query kernel sql operand processing ??
qksopSplitPreds                                              (qksop)SplitPreds                                            query kernel sql operand processing ??
qksopVisitPredsInt                                           (qksop)VisitPredsInt                                         query kernel sql operand processing ??
qksopVisitPredsPostOrder                                     (qksop)VisitPredsPostOrder                                   query kernel sql compilter operand processing ??
qksptfContainNestedScalar                                    (qksptf)ContainNestedScalar                                  query kernel sql polymorphic table functions compilation ??
qksptfDescribePTF                                            (qksptf)DescribePTF                                          query kernel sql polymorphic table functions compilation ??
qksptfOCIerr_                                                (qksptf)OCIerr_                                              query kernel sql polymorphic table functions compilation ??
qksptfSQM_Check_Errors                                       (qksptf)SQM_Check_Errors                                     query kernel sql polymorphic table functions compilation ??
qksptfSQM_CleanupTDOs                                        (qksptf)SQM_CleanupTDOs                                      query kernel sql polymorphic table functions compilation ??
qksptfSQM_Describe                                           (qksptf)SQM_Describe                                         query kernel sql polymorphic table functions compilation ??
qksptfSQM_ExpRepl_                                           (qksptf)SQM_ExpRepl_                                         query kernel sql polymorphic table functions compilation ??
qksptfSQM_GetTxt                                             (qksptf)SQM_GetTxt                                           query kernel sql polymorphic table functions compilation ??
qksptfSQM_Init                                               (qksptf)SQM_Init                                             query kernel sql polymorphic table functions compilation ??
qksptfSQM_Laz_                                               (qksptf)SQM_Laz_                                             query kernel sql polymorphic table functions compilation ??
qksptfSQM_Model_Check                                        (qksptf)SQM_Model_Check                                      query kernel sql polymorphic table functions compilation ??
qksptfSQM_Parse                                              (qksptf)SQM_Parse                                            query kernel sql polymorphic table functions compilation ??
qksptfSQM_Parse_errors                                       (qksptf)SQM_Parse_errors                                     query kernel sql polymorphic table functions compilation ??
qksptfSQM_PstPrc                                             (qksptf)SQM_PstPrc                                           query kernel sql polymorphic table functions compilation ??
qksptfSQM_PstPrc1_                                           (qksptf)SQM_PstPrc1_                                         query kernel sql polymorphic table functions compilation ??
qksptfSQM_QbcRelExp_                                         (qksptf)SQM_QbcRelExp_                                       query kernel sql polymorphic table functions compilation ??
qksptfSQM_RplExpA                                            (qksptf)SQM_RplExpA                                          query kernel sql polymorphic table functions compilation ??
qksptfSQM_RplExpB                                            (qksptf)SQM_RplExpB                                          query kernel sql polymorphic table functions compilation ??
qksptfSQM_SetIQB_                                            (qksptf)SQM_SetIQB_                                          query kernel sql polymorphic table functions compilation ??
qksptfSQM_SetSQM_                                            (qksptf)SQM_SetSQM_                                          query kernel sql polymorphic table functions compilation ??
qksptfSQM_Template                                           (qksptf)SQM_Template                                         query kernel sql polymorphic table functions compilation ??
qksptfSQM_rm_vqb                                             (qksptf)SQM_rm_vqb                                           query kernel sql polymorphic table functions compilation ??
qksptfSubExprContainScalar                                   (qksptf)SubExprContainScalar                                 query kernel sql polymorphic table functions compilation ??
qmxtigCheckLOBSize                                           (qmxt)igCheckLOBSize                                         query XDB XML Objects XML ??
qmxtigCreXMLImgFromLob                                       (qmxt)igCreXMLImgFromLob                                     query XDB XML Objects XML ??
qmxtigGetXMLImgInfo                                          (qmxt)igGetXMLImgInfo                                        query XDB XML Objects XML ??
qmxtigImgNdsCnv                                              (qmxt)igImgNdsCnv                                            query XDB XML Objects XML ??
qmxtigReturnString                                           (qmxt)igReturnString                                         query XDB XML Objects XML ??
qmxtigUnderEvent                                             (qmxt)igUnderEvent                                           query XDB XML Objects XML ??
qmxtigUpdateReplayChecksum                                   (qmxt)igUpdateReplayChecksum                                 query XDB XML Objects XML ??
qmxtigValidateXmlImage                                       (qmxt)igValidateXmlImage                                     query XDB XML Objects XML ??
qmxtigValidateXmlImgBody                                     (qmxt)igValidateXmlImgBody                                   query XDB XML Objects XML ??
qmxtigValidateXmlImgHdr                                      (qmxt)igValidateXmlImgHdr                                    query XDB XML Objects XML ??
qxxqGetCredentials                                           (qx)xqGetCredentials                                         query query execution ??
qxxqGetProxy                                                 (qx)xqGetProxy                                               query query execution ??
qxxqGetSSLWallet                                             (qx)xqGetSSLWallet                                           query query execution ??
rest_nhpavail                                                rest_nhpavail                                                ??
rest_nhpconnect                                              rest_nhpconnect                                              ??
rest_nhpdisconnect                                           rest_nhpdisconnect                                           ??
rest_nhpflush                                                rest_nhpflush                                                ??
rest_nhpfree                                                 rest_nhpfree                                                 ??
rest_nhpmalloc                                               rest_nhpmalloc                                               ??
rest_nhprealloc                                              rest_nhprealloc                                              ??
rest_nhprecv                                                 rest_nhprecv                                                 ??
rest_nhprequestauth                                          rest_nhprequestauth                                          ??
rest_nhpsend                                                 rest_nhpsend                                                 ??
rest_nhptrace                                                rest_nhptrace                                                ??
rest_nhpwalletauth                                           rest_nhpwalletauth                                           ??
rwssecondtop                                                 (rws)secondtop                                               row source ??
setJPathConsumer2D                                           setJPathConsumer2D                                           ??
skfParityGDiv                                                (sk)fParityGDiv                                              operating system dependent kernel ??
skfParityGMul                                                (sk)fParityGMul                                              operating system dependent kernel ??
skfSIMDAvail                                                 (sk)fSIMDAvail                                               operating system dependent kernel ??
skfSIMDCalP                                                  (sk)fSIMDCalP                                                operating system dependent kernel ??
skfSIMDCalP0                                                 (sk)fSIMDCalP0                                               operating system dependent kernel ??
skfSIMDCalQ                                                  (sk)fSIMDCalQ                                                operating system dependent kernel ??
skfSIMDCalQ0                                                 (sk)fSIMDCalQ0                                               operating system dependent kernel ??
skfSIMDMultiplicationLookupTable                             (sk)fSIMDMultiplicationLookupTable                           operating system dependent kernel ??
skfSIMDRecoverOneDataFromQ                                   (sk)fSIMDRecoverOneDataFromQ                                 operating system dependent kernel ??
skfSIMDRecoverTwoDataFromPQ                                  (sk)fSIMDRecoverTwoDataFromPQ                                operating system dependent kernel ??

(disclaimer: I can’t look at the sourcecode, which means I look at the oracle executable with normal, modern tools. This also means that there’s a of stuff that I don’t see, for example if functionality has been added inside an existing function, then that’s totally invisible to me)

This post is about how to use gdb, which is a debugger, so very simplistically put an aid for looking at C programs, as a profiler. I use gdb quite a lot for profiling because it’s the easiest way for profiling for me.

Lots of people which I know use other tools like perf, systemtap and dtrace for the same purpose and that’s fine. Each tools has its own advantages and disadvantages. One disadvantage of gdb is that it’s using ptrace to attach to a process, which makes it dead slow from a machine perspective, because everything it then does goes via another process, which is the debugger. That is how the debugger works.

Also lots of people use gdb like I do, and use basic functionality, which is breaking at functions, which makes it possible to find out the sequence of how functions are called, generating backtraces (stack traces) to understand the stack and maybe looking at functions arguments.

But there is more that you can do. Way more actually. These examples use the gdb that comes with RHEL7/OL7 via yum.

You can break conditionally at a function, which means that a break will be executed only if the condition is met. For example:

break sltrgatime64 if $caller_is("ksbabs")

This only breaks at a function called sltrgatime64 if it is called from a function that is called ksbabs.

But for what I wanted to do and investigate, I didn’t only want to do “something” when the sltrgatime64 function was called from ksbabs, I wanted be able to distinguish the two times this was called as the first one and the second one.

I knew that the “first time” was actually right after a function with the name of “dbktFlush” was called. So here’s what I did:

set $status = -1

break dbktFlush if $caller_is("ksbabs")
commands
 silent
 set $status = 0
 c
end

break sltrgatime64 if $caller_is("ksbabs")
commands
 silent
 if $status == 0
   set $status = 1
   printf "first invocation\n"
   c
 end
 if $status == 1
  set $status = 2
  printf "second invocation\n"
  c
 end
 if $status > 1
  printf "error. third or more invocations\n"
 end
end

This is much more advanced than using a single command, but I think for anyone who is able to program a few languages is reasonably simple to understand.

There’s two breaks, and both are conditional, the function from which the function to break on must be ksbabs.
In the break of dbktFlush the (convenience) variable $status is set to 0, indicating the the first invocation for the other break.

In the second break, there are 3 if statements. The first one picks up the variable $status set to 0, indicating the first invocation and then sets it to 1 for the second invocation and the second if picks up the second invocation indicated by the variable $status set to 1 and sets it to 2, so if I didn’t look carefully and there are more invocations of sltrgatime64, I will get notified.

If breaks would be activated when the second call to sltrgatime64 was executed, it would not come across dbktFlush, and thus the $status variable would (still) be set to -1, and the break would skip all ifs.

Of course this is a very specific case, you should tailor it for your own needs. However, this was the actual investigation I done to investigate the function of the above sltrgatime64 calls.

In fact, I gone a little deeper and wanted to understand what the timing of of these two reads of the wall clock time did. For that, I used the python interface in gdb, for which the support seems to be compiled in in the rpm based version.

You can use python, and use variables in python that remain state between gdb interactions. I used a dead simple interaction; calling time.time() to time the invocation of the sltrgatime64 executed right after dbktFlush was called:

python import time
python var_time=time.time()

break dbktFlush if $caller_is("ksbabs")
commands
 silent
 set $status = 0
 c
end

break sltrgatime64 if $caller_is("ksbabs")
commands
 silent
 if $status == 0
   set $status = 1
   python print time.time()-var_time
   python var_time=time.time()
   c
  else
   c
 end
end

If you set single logwriter to true and execute this against the logwriter, you will see that the logwriter tries to keep a 3 second pace by adjusting the sleep time on semtimedop().

My actual goal was to try to find out what is exactly timed for the message ‘Warning: log write elapsed time ????ms, size ?KB’ in the logwriter trace file, which Nenad Noveljic proved is not only about the actual IO time.

To be honest, I done it clumsy by going backward measuring all the calls that measured wall clock time and should have put a break on write and look at the backtrace. When I executed a backtrace of the write call that wrote the trace file entry, I could see this line was written in kcrfw_postprocess_write().

This is the gdb script with the timing:

python import time

break skgstmGetEpochTs if $caller_is("kcrfw_redo_write_driver")
commands
 silent
 python var_time=time.time()
 c
end

break kcrfw_do_write
 commands
 silent
 print $rip
 c
end

break skgstmGetEpochTs if $caller_is("kcrfw_postprocess_write")
commands
 silent
 shell sleep 1
 python print time.time()-var_time
 c
end

What does gdb script does, is always set the python variable var_time, which includes the idle cycles of the logwriter. If it goes into the function kcrfw_do_write it actually is going to write. That is also when potentially the line “Warning: log write elapsed time 1024ms, size 0KB” could be produced.
when the function skgstmGetEpochTs is called in the function kcrfw_postprocess_write, it executes a sleep for 1 second, and then prints the current time minus the time obtained when kcrfw_redo_write_driver was entered.

What this proofs, is that the timing for the log write time elapsed time warning is done from the start of the kcrfw_redo_write_driver function. That means that everything that the logwriter needs to do, excluding the the last bit of what the logwriter performs, posting the foregrounds, is timed, including dealing with the internal structures that keep track of the redo strands and the latching of it.

The Oracle database log writer is the process that fundamentally influences database change performance. Under normal circumstances the log writer must persist the changes made to the blocks before the actual change is committed. Therefore, it’s vitally important to understand what the log writer is exactly doing. This is widely known by the Oracle database community.

The traditional method for looking at log writer performance is looking at the wait event ‘log file parallel write’ and the CPU time, and comparing that to the ‘log file sync’ alias “commit wait time”. If ‘log file parallel write’ and ‘log file sync’ roughly match, a commit is waiting on the log writer IO latency, if it isn’t then it’s unclear, and things get vague.

Now we get to the gist of this blogpost: since Oracle 12, there are additional statistics in V$SESSTAT and v$SYSSTAT that describe where the log writer spending it’s time on. However, and sadly, these are not documented by Oracle. The statistics are:

– redo write gather time
– redo write schedule time
– redo write finish time
– redo write time (usec)
– redo write total time

Probably ‘redo write time (usec)’ doesn’t belong to these statistics because the timing is as good as the same as ‘redo write finish time’. Also the statistic ‘redo write time (usec)’ is updated by ‘kcrfw_postprocess_write’ right after the main writing function (kcrfw_do_write), whilst the other redo write timing statistics are updated/made visible by ksu_dispatch_tac outside of the actual writing functions, at the approximate rate of every 3 seconds.

All these statistics contain cumulative timing figure in microseconds (usec).

All these timings start at the start of the function kcrfw_redo_write_driver. This is the main function that handles all the redo writing, including investigating the fixed area structure (kcrfs_) and the shared pool structures (made visible using X$KCRFSTRAND) to see if there are changes.

1. redo write gather time.
This times reading the structures that control allocation of the redo structures, validating foreground processes are done writing the change vectors and applying these to the blocks, updating the LWN SCN. Part of this also happens when there is nothing to write, but then the time is not added to this statistic, only if it found something to write.

2. redo write schedule time.
This includes the time of 1, but now it found something to write, entered the writing function (kcrfw_do_write), and updated the ksfd IO related structures for writing.

3. redo write finish time.
This includes the time of 1 and 2, but this additionally times all the IO related functions for writing the changes it found in the public redo strands, as indicated by the shared pool and fixed area structures.

4. redo write time (usec).
This probably is not part of this group of statistics, but it works in exactly the same way: it’s a cumulative figure, it counts time since the start of the kcrfw_redo_write_driver and includes a tiny bit more code, it times until it has left the kcrfw_do_write function and enters the kcrfw_postprocess_write function.

5. redo write total time.
This includes the time of 1, 2 and 3, but additionally times the kcrfw_post function, which performs the signalling of the foreground processes that have committed and wait for a semop() signal.

This means that the Oracle database since Oracle 12 provides the timing information to see where the log writer is spending it’s time on. This can prevent a lot of guesswork.

This post is about a change in how the time is measured for the event ‘log file parallel write’. This is important for the performance tuning of any change activity in an Oracle database, because with the default commit settings, a foreground session that commits changes waits in the wait event ‘log file sync’, which is a wait on logwriter activity, for which the wait event ‘log file parallel write’ always has been the indicator of the time spend on IO.

Log file sync
First things first: a foreground session normally waits on the wait event ‘log file sync’ when it commits waiting for its change vectors to be written to the online redologfile(s) by the logwriter. It is wrong to always assume a ‘log file sync’ will be present. If, somehow, the logwriter manages to increase the ON DISK SCN to or beyond the foreground session’s commit SCN, there will be no ‘log file sync’ wait event.

I found the absence of the ‘log file sync’ wait event by artificially slowing the committing session down. However, with the improvements in parallelism in the Oracle code, and even more with the improvements on the hardware layer for lowering write latency (especially with persistent memory), this might be a situation that is actually visible in the near future. This is true for at least version 11.2.0.4 and higher (probably earlier too, but these are versions that I verified).

Log file parallel write
However, this post is about the event that in a lot of cases is the culprit for the ‘log file sync’ time: it’s about the ‘log file parallel write’ wait event, which takes place in the logwriter (when the logwriter itself writes, it could delegate that to the logwriter worker processes, LGnn), which normally would be measured by Oracle database tuners to validate logwriter IO latency in case of foreground sessions showing high ‘log file sync’ waits indicating waiting on the logwriter.

I was looking at the logwriter code path using the ‘debug trace’ tool from intel pin tools and used Tanel Poder’s snapper using begin and end snapshots on the logwriter process on Linux, non ASM, Oracle 19.5. I put the logwriter into single logwriter mode (“_use_single_log_writer”=true) and performed redo, which is bound to be picked up by the logwriter.

There I noticed the statistics indicated a write to have been taking place, whilst there was no ‘log file parallel write’ wait event. Weird… So I dug deeper and looked at the functions taking place. These are the functions that I found: https://gitlab.com/snippets/1926207

The important bits are:
– In the function ksfdaio() the IO request is submitted:
ksfdaio>ksfdgo>ksfd_skgfqio>skgfqio>skgfr_lio_listio64>io_submit
– And in the function ksfdblock() the IO request result is read (“reaped”):
ksfdblock>ksfdpoll>ksfdwtio>skgfrwat>skgfrliopo>io_getevents

But no wait event is called *at all!*

This is a clear change from earlier versions, where the logwriter write would always yield a ‘log file parallel write’ wait event. But when did this change happen? To answer that question, I installed version 12.1.0.2 (no patches) to look at the code path and found: https://gitlab.com/snippets/1926211

The important bits are:
– The wait interface is invoked: kslwtbctx()
– In the function ksfdgo() the IO request is submitted:
ksfd_sbio>ksfdgo>ksfd_skgfqio>skgfqio>skgfr_lio_listio64>io_submit
– And in the function ksfdwtio() to IO request result is read (“reaped”):
ksfd_sbio>ksfdwtio>skgfrwat>skgfospo>skgfrliopo>io_getevents
– And the wait interface is ended: kslwtectx()

Ah! So a clear change! So when did this change happen? I installed 12.2.0.1 and found the same ‘waiting’ codepath, then installed 18.3, and found the waiting codepath. So the change probably happened in Oracle 19! So I installed 19.3, and found the waiting codepath once again!! So was it a change in PSU 19.4 or 19.5? I installed 19.5, and found the waiting codepath too.

O.M.G. Why am I seeing different behaviour between two databases on the exact same version? Well, I set some undocumented parameters, so I replicated these in my newly installed database…and it didn’t change.

What else is different? Well…one is a multi-tenant database, and the other isn’t…but surely that doesn’t change the IO codepath?? Of course this is easy to check, so I dropped the non-CDB database and created a CDB one, and…it now showed the same “non waiting” codepath.

So, apparently, using the oracle database in multi-tenant mode changes the IO codepath behaviour. I checked the codepath going down in versions, and it turns out this change appeared in 12.2.0.1.0, so essentially in Oracle 12.2 (Oracle 18 and 19 are actually Oracle 12.2.0.2 and Oracle 12.2.0.3). To be clear on this: I didn’t find this change in 12.1.0.2.

Does this mean the wait events ‘log file parallel write’ does not show up at all? No, if the call io_getevents after io_submit does not return all submitted IOs, it must wait for it, and at that point invoke the wait interface and then go into a blocking io_getevents call, so the actual waiting is timed. This is shown in this snippet: https://gitlab.com/snippets/1926212

This shows ksfdblock>ksfdpoll>ksfdwtio>skgfrwat>skgfrliopo>io_getevents, which returns 0 (what means that it didn’t found any IOs in the completion queue), which then returns up to the ksfdpoll function, invokes the wait interface, kslwtbctx, and then dives to io_getevents again, but now in blocking mode to wait for the IO.

I also replayed the tests on ASM, which shows the exact same (non-)waiting behaviour.

Conclusion
For Oracle versions 12.1.0.2 up to 19.5, the wait event ‘log file parallel write’ includes both the submission of the IO request (io_submit call) and the waiting for the submitted IOs to finish (io_getevents call). This means the waiting time of the event in this case is the total latency of the IO request, including operating system processing.

Starting from version 12.2, only if the multi-tenant option is used, this is changed, and the wait event ‘log file parallel write’ only is shown if the submitted IOs are not available after they are submitted, and thus the logwriter process has to wait for them. If the wait is shown, the time in the event is the actual waiting time for the IOs to finish from the perspective of the logwriter process, not the IO latency time.

Of course if you have multiple log groups on disks with different latencies, this wait event waits for all IOs to finish, which means it times the slowest IO of all submitted IOs.

This blogpost is about Oracle database and grid infrastructure software homes, which patches should be applied to which homes, and what it then looks like. This is fully documented by MyOracleSupport notes, but you will see that with version 18 and up this is unclear.

I keep a script-set that automatically installs and patches the Oracle database software and creates a database. This script-set is called vagrant-builder, and it can install any version with any PSU applied between 11.2.0.2 up to 19.5, which is the latest PSU of the latest version, with a few exceptions: for 11.2.0.2 and 11.2.0.3 I only created an install for the base version and the latest PSU for the database, and version 12.1.0.1 is left out entirely.

I recently reviewed my installs and verified everything is carried out correctly. First a simple overview of what I think should be applied on the database and grid infrastructure install:

Version  Grid               Database
-------- ------             ----------
11.2.0.2 -                  DB PSU
11.2.0.3 -                  DB PSU+OJVM
11.2.0.4 GI PSU+JDBC patch  DB PSU+OJVM
12.1.0.2 GI PSU+JDBC patch  DB PSU+OJVM
12.2.0.1 GI PSU             DB PSU+OJVM
18       GI PSU             DB PSU+OJVM
19       GI PSU             DB PSU+OJVM

(‘-‘ means not investigated)
My idea of what should be applied is based on MOS note 1929745.1: Oracle recommended patches.

Grid patches, JDBC patch
The JDBC patch for grid infrastructure 11.2.0.4 and 12.1.0.2 is a patch that updates java classes. Therefore the patch is a generic one, the java classes do not contain operating system dependent machine code. The table in the MOS note also show differences for the JDBC patch between = januari 2014 = july 2016 and higher. No surprise there. It’s especially easy because all versions in premier support as of the date of this blogpost do not need the JDBC patch.

Grid patches, GI PSU
For the GI PSU, there are a lot of patches that contain the GI PSU, because outside of the GI patch itself, there are also combination patches that for example contain both the GI and the DB PSU. I like to keep it as simple as I can. Therefore, I stick to MOS note 2118136.2: Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), bundle patches, Patchsets and Base releases, and look at the following:
– versions 11.2.0.4/12.1.0.2: Oracle Database PSU, SPU(CPU), Bundle Patches (Versions 12.1 & lower), version (11.2.0.4/12.1.0.2), GI PSU column.
– versions 12.2.0.1 and up: Oracle Database Updates, version (12.2.0.1,18.0.0.0,19.0.0.0), GI Update column.

Database patches, DB PSU
Here too there are multiple patches that can be used to apply the DB PSU, and I stick with MOS note 2118136.2: Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), bundle patches, Patchsets and Base releases, and look at the following:
– versions 11.2.0.4/12.1.0.2: Oracle Database PSU, SPU(CPU), Bundle Patches (Versions 12.1 & lower), version (11.2.0.4/12.1.0.2), PSU column.
– versions 12.2.0.1 and up: Oracle Database Updates, version (12.2.0.1,18.0.0.0,19.0.0.0), DB Update column.

Database patches, OJVM
There are multiple MOS documents talking about the database JavaVM patch, and there are multiple patches, but here I stick with MOS note 2118136.2: Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), bundle patches, Patchsets and Base releases once again, and look at the following:
– OJVM Update/PSU/Bundle Patches, 11.2.0.4/12.1.0.2/12.2.0.1/18.0.0.0/19.0.0.0, OJVM Update.

Okay. So we got the table above that is based on MOS note 1929745.1, and we got all the patches organised in MOS note 2118136.2. So that’s nice and simple, right?

Well, not entirely…

As I said, I was checking up on the latest PSU installs. This is the ‘opatch lspatches’ overview of 12.1.0.2 and 12.2.0.1:

-- 12.1.0.2
GI 190716
---------
23727148;
29509318;OCW PATCH SET UPDATE 12.1.0.2.190716 (29509318)
29494060;Database Patch Set Update : 12.1.0.2.190716 (29494060)
29423125;ACFS PATCH SET UPDATE 12.1.0.2.190716 (29423125)
26983807;WLM Patch Set Update: 12.1.0.2.180116 (26983807)
DB 190716
---------
29774383;Database PSU 12.1.0.2.190716, Oracle JavaVM Component (JUL2019)
29494060;Database Patch Set Update : 12.1.0.2.190716 (29494060)
-- 12.2.0.1
GI 191015
---------
30138470;Database Oct 2019 Release Update : 12.2.0.1.191015 (30138470)
30122828;ACFS OCT 2019 RELEASE UPDATE 12.2.0.1.0 (30122828)
30122814;OCW OCT 2019 RELEASE UPDATE 12.2.0.1.191015 (30122814)
30093408;TOMCAT RELEASE UPDATE 12.2.0.1.0(ID:190208.0920) (30093408)
26839277;DBWLM RELEASE UPDATE 12.2.0.1.0(ID:170913) (26839277
DB 191015
---------
30133625;OJVM RELEASE UPDATE: 12.2.0.1.191015 (30133625)
30138470;Database Oct 2019 Release Update : 12.2.0.1.191015 (30138470)

For the database (DB), we see two patches, which is exactly what is expected.
– The database patch, which is called release update with one and patch set update with the other.
– The OJVM update which also named differently between the two versions, but very much recognisable as such.
I think it’s bad that the names vary, but this is totally expected.

For the grid infrastructure (GI), we see 5 patches in both situations, but these are not the same patches between the homes!
– The unnamed patch in the 12.1.0.2 home is the JDBC patch, which should only be applied to 12.1.0.2, not to higher versions.
– The OCW patch (oracle clusterware).
– The database patch.
– The ACFS patch. The version indication in the patch name changed.
– The WLM/DBWLM patch. Sadly the name changed, and the name with 12.2.0.1 is actually quite useless, I can’t tell the actual version, I have to look up the patch number.
– Starting from version 12.2.0.1, there is a tomcat installation in the grid home, as this patch indicates. The name here is not helpful because it doesn’t indicate the actual version, like with the DBWLM patch.

So, outside of in my opinion bad naming, and a weird inclusion of a competing product of Oracle (Tomcat versus Weblogic), this still follows the rules of logic.

Now let’s look at the same output for version 18 and 19:

--18
GI 18.8
30116128;ACFS RELEASE UPDATE 18.8.0.0.0 (30116128)
30113775;OCW RELEASE UPDATE 18.8.0.0.0 (30113775)
30112122;Database Release Update : 18.8.0.0.191015 (30112122)
30093398;TOMCAT RELEASE UPDATE 18.0.0.0.0 (30093398)
28655963;DBWLM RELEASE UPDATE 18.4.0.0.0 (28655963)
27923415;OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)
DB 18.8
30133603;OJVM RELEASE UPDATE: 18.8.0.0.191015 (30133603)
30112122;Database Release Update : 18.8.0.0.191015 (30112122)
28090553;OCW RELEASE UPDATE 18.3.0.0.0 (28090553)
--19
GI 19.5
30125133;Database Release Update : 19.5.0.0.191015 (30125133)
30122167;ACFS RELEASE UPDATE 19.5.0.0.0 (30122167)
30122149;OCW RELEASE UPDATE 19.5.0.0.0 (30122149)
29401763;TOMCAT RELEASE UPDATE 19.0.0.0.0 (29401763)
DB 19.5
30128191;OJVM RELEASE UPDATE: 19.5.0.0.191015 (30128191)
30125133;Database Release Update : 19.5.0.0.191015 (30125133)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

I must say that the naming in general looks more consistent, that is a good thing!

For the database (DB) we see THREE patches (I suspected 2):
– The database release update patch, which nicely reports its version.
– The OVM patch, which also reports its version.
– This is weird. One of the grid infrastructure patches, the “OCW” patch, is applied to the database home. In fact, this is applied to the base release. Because it’s not a patch that is documented to be needed to be applied, this will sit at this version and never be updated. However, this unexpected patch is consistently applied to the base release for both version 18 and 19.

For the grid home (GI) we see an inconsistent number of patches (!) between 18.8 and 19.5. This is partly as expected, but I found an weird patch applied too. Let’s go over the patches:
– The ACFS patch is totally expected and appropriately named.
– The OCW patch is expected and appropriately named too.
– The Database Release Update patch is expected and appropriately named.
– The TOMCAT patch is expected. I don’t understand why it can’t have the RU numbering, but at least it’s consistent between 18 and 19.
– The DBWLM patch is only applied to the 18 home. As far as I understand, this is how it’s supposed to be, DBWLM is not regularly updated like the other ones above, so it’s okay to have an older version of it with the other patches, and if there isn’t a patch to apply, it can simply not be there, like with version 19 in this case.
– The OJVM patch puzzles me. I don’t know what to think of it. Also, it’s only applied to the base release of version 18, not to the base release of version 19. This, very much like the OCW patch, will never be updated. But I just don’t understand, this patches the java virtual machine in the database, which for GI is the ASM instance, for which, as far as I know, the java virtual machine isn’t used.

Conclusion.
The naming of the patches as visible with “opatch lspatches” has certainly improved with version 18 and higher. Still it would be helpful if the grid infrastructure tomcat patch would follow the same naming of the other patches.

I am in doubts about the inclusion of two patches in the base releases of versions 18 and 19:
The OJVM patch inclusion in the base release of grid infrastructure of version 18 only.
The OCW patch inclusion in the base release of the database of version 18 and 19.

Addendum.
After debating this on twitter and with my colleagues, I found that my OCW assumptions were incorrect. The grid infrastructure patch versions 12.2 and up reasonably clearly describes that the OCW sub-patch that is part of the GI PSU/RU patch should be applied to the database home too (if cluster ware is used for that home). So that means that if you got another version of the database home than the grid infrastructure home and it is used with grid infrastructure, you should download the grid infrastructure PSU/RU patch and apply the OCW sub patch to the oracle database home, because the OCW patch is not in the database PSU/RU patch.
Because the database home patch itself is also in the grid infrastructure PSU/RU patch, I see no reason to bother downloading the database patch, and now only download and use the grid infrastructure PSU/RU patch, because that contains the database home patch as well as the OCW patch.

Addendum 2.
The OCW patch being installed into the grid infrastructure home and additionally in the database home turns out to be a change with PSU 11.2.0.4.5. Before that, the OCW patch did exist with the grid infrastructure home patch, but couldn’t be applied to the database home. This change was documented in the patch readme, but wasn’t really heavily marketed by Oracle.

Addendum 3.
Despite OCW being documented as being additionally applied to the database home for 11.2.0.4(.5) and higher, in my tests it was not possible to apply the 11.2.0.4 OCW patch to a database home for any 11.2.0.4 PSU; opatch fails with a dependency problem. It does succeed starting from 12.1.0.2 (12.1.0.1 not tested) and higher.

I have to say that when looking in the 11.2.0.4 OCW patch metadata, it says ‘rac’, so the OCW patch might succeed when the home is explicitly installed for a cluster database. I tested with grid infrastructure installed for a single machine (“siha”).

This blogpost is about an inconsistency I found in the X$ tables X$KQFTA and X$KQFCO. This is very specific. If you don’t care about that, you can skip this post.

The Oracle database’s “dynamic performance views” are views that are prefixed with “GV$” and “V$”. The “G” with “GV$” stands for “global” and gets you the results from its “V$” equivalent for all instances. In most cases, but that’s not the purpose of this blog. In most cases, the “V$” version simply is the “GV$” view with inst_id (instance id, used in RAC to specify the instance id) set to the current instance. I’ll refer to both simply as “V$” for simplicity.

The “V$” views are mostly build on top of “X$” tables. The “X$” tables are Oracle internal tables, and not officially supported, therefore you should use the “V$” views.

One of the things that certain “X$” tables expose is information about the “X$” tables and their columns theirselves. These “X$” tables are visible in “X$KQFTA” (kernel query fixed tables tables) for the “X$” tables, and “X$KQFCO” (kernel query fixed tables columns) for the columns of the “X$” tables. I learned about these tables a long time ago from Kyle Hailey, Anjo Kolk and James Morle regarding direct memory attachment for wait events, the reason being the ability to find the memory address for certain “X$” tables to fetch the data these hold directly. We are talking about the 1990s here. In fact, Kyle later put the history of how he learned about it in the “Oracle insights: tales of the oaktable” book.

I gather information about every PSU of every Oracle database version on linux once it comes out. This is how I am able to produce the posts about the difference between a current and a previous PSU. One of the things that I gather is information about X$ tables and their columns. In order to obtain the columns, I use X$KQFTA and X$KQFCO and join these in the way that is publicly known:

select t.kqftanam, c.kqfconam
from x$kqfta t, x$kqfco c
where t.inst_id = 1
and c.inst_id = 1
and t.indx = c.kqfcotab;

And this worked well…

…until I produced a difference report of versions 12.1.0.2.191015 and 12.1.0.2.190716, where the report reported the following difference:

x$ tables columns unique to 12.1.0.2.190716 versus 12.1.0.2.191015

NAME                                                                                                 COLUMN_NAME
---------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
X$KQFTVRTTST0                                                                                        ADDR
X$KQFTVRTTST0                                                                                        CHGROWID
X$KQFTVRTTST0                                                                                        CHGROWOP
X$KQFTVRTTST0                                                                                        COL0_0
X$KQFTVRTTST0                                                                                        COL0_1
X$KQFTVRTTST0                                                                                        COL0_2
X$KQFTVRTTST0                                                                                        COL0_3
X$KQFTVRTTST0                                                                                        COL0_4
...and so on...

This could happen, these are columns that seem to have vanished with 12.1.0.2.191015. However, I decided to check it, although I haven’t got access to the 12.1.0.2.191015 PSU, because it’s a PSU version only available for paid extended support.

I looked at the columns of X$KQFTVRTST0 in my current database, which happens to be an ancient 11.2.0.2 database. However, much to my surprise, this gave me radically different columns:

SYS@o112 AS SYSDBA> desc X$KQFTVRTTST0
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ADDR                                                                                                                       RAW(8)
 INDX                                                                                                                       NUMBER
 INST_ID                                                                                                                    NUMBER
 COLA                                                                                                                       VARCHAR2(64)
 COLB                                                                                                                       NUMBER

Okay, something weird is going on here.

First let’s look in which versions X$KQFTVRTTST0 is present:

ORAVER@o112 > @x_table_in_versions.sql

DATABASE_VERSION     NAME
-------------------- --------------------------------------------------------------------------------------------------------------------------------
11.2.0.1.0           X$KQFTVRTTST0
11.2.0.2.0           X$KQFTVRTTST0
11.2.0.2.12          X$KQFTVRTTST0
11.2.0.3.0           X$KQFTVRTTST0
11.2.0.3.15          X$KQFTVRTTST0
11.2.0.4.0           X$KQFTVRTTST0
11.2.0.4.1           X$KQFTVRTTST0
11.2.0.4.160119      X$KQFTVRTTST0
11.2.0.4.160419      X$KQFTVRTTST0
11.2.0.4.160719      X$KQFTVRTTST0
11.2.0.4.161018      X$KQFTVRTTST0
11.2.0.4.170418      X$KQFTVRTTST0
11.2.0.4.170718      X$KQFTVRTTST0
11.2.0.4.170814      X$KQFTVRTTST0
11.2.0.4.180116      X$KQFTVRTTST0
11.2.0.4.180417      X$KQFTVRTTST0
11.2.0.4.180717      X$KQFTVRTTST0
11.2.0.4.190115      X$KQFTVRTTST0
11.2.0.4.191015      X$KQFTVRTTST0
11.2.0.4.2           X$KQFTVRTTST0
11.2.0.4.3           X$KQFTVRTTST0
11.2.0.4.4           X$KQFTVRTTST0
11.2.0.4.5           X$KQFTVRTTST0
11.2.0.4.6           X$KQFTVRTTST0
11.2.0.4.7           X$KQFTVRTTST0
11.2.0.4.8           X$KQFTVRTTST0
12.1.0.2.0           X$KQFTVRTTST0
12.1.0.2.1           X$KQFTVRTTST0
12.1.0.2.160119      X$KQFTVRTTST0
12.1.0.2.160419      X$KQFTVRTTST0
12.1.0.2.160719      X$KQFTVRTTST0
12.1.0.2.161018      X$KQFTVRTTST0
12.1.0.2.170117      X$KQFTVRTTST0
12.1.0.2.170418      X$KQFTVRTTST0
12.1.0.2.170718      X$KQFTVRTTST0
12.1.0.2.170814      X$KQFTVRTTST0
12.1.0.2.171017      X$KQFTVRTTST0
12.1.0.2.180116      X$KQFTVRTTST0
12.1.0.2.180417      X$KQFTVRTTST0
12.1.0.2.181016      X$KQFTVRTTST0
12.1.0.2.190115      X$KQFTVRTTST0
12.1.0.2.190416      X$KQFTVRTTST0
12.1.0.2.190716      X$KQFTVRTTST0
12.1.0.2.191015      X$KQFTVRTTST0
12.1.0.2.3           X$KQFTVRTTST0
12.1.0.2.4           X$KQFTVRTTST0
12.1.0.2.5           X$KQFTVRTTST0
12.2.0.1.0           X$KQFTVRTTST0
12.2.0.1.170620      X$KQFTVRTTST0
12.2.0.1.170718      X$KQFTVRTTST0
12.2.0.1.170814      X$KQFTVRTTST0
12.2.0.1.171017      X$KQFTVRTTST0
12.2.0.1.180116      X$KQFTVRTTST0
12.2.0.1.180417      X$KQFTVRTTST0
12.2.0.1.180717      X$KQFTVRTTST0
12.2.0.1.181016      X$KQFTVRTTST0
12.2.0.1.190115      X$KQFTVRTTST0
12.2.0.1.190416      X$KQFTVRTTST0
12.2.0.1.190716      X$KQFTVRTTST0
12.2.0.1.191015      X$KQFTVRTTST0
18.3                 X$KQFTVRTTST0
18.4                 X$KQFTVRTTST0
18.5                 X$KQFTVRTTST0
18.6                 X$KQFTVRTTST0
18.7                 X$KQFTVRTTST0
18.8                 X$KQFTVRTTST0
19.2                 X$KQFTVRTTST0
19.3                 X$KQFTVRTTST0
19.4                 X$KQFTVRTTST0
19.5                 X$KQFTVRTTST0

So, X$KQFVRTTST0 appeared with 11.2.0.1. Now let’s take a look at how the columns look like in X$KQFVRTTST0 looks in version 11.2.0.1:

ORAVER@o112 > @x_table_cols_in_versions.sql

DATABASE_VERSION	       TABLE_NAME		      COLUMN_NAME
------------------------------ ------------------------------ ----------------------------------------
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_9
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_8
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_7
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_6
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_5
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_4
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_3
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_2
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_1
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_0
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_9
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_8
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_7
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_6
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_5
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_4
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_3
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_2
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_1
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_0
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_9
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_8
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_7
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_6
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_5
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_4
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_3
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_2
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_1
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_0
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_9
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_8
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_7
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_6
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_5
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_4
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_3
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_2
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_1
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_0
11.2.0.1.0		       X$KQFTVRTTST0		      CHGROWOP
11.2.0.1.0		       X$KQFTVRTTST0		      CHGROWID
11.2.0.1.0		       X$KQFTVRTTST0		      COL_OPERATION
11.2.0.1.0		       X$KQFTVRTTST0		      INST_ID
11.2.0.1.0		       X$KQFTVRTTST0		      INDX
11.2.0.1.0		       X$KQFTVRTTST0		      ADDR

These are the columns which were shown in the difference report that said they were removed. Now let’s look at the columns in version 19.5:

ORAVER@o112 > @x_table_cols_in_versions.sql

DATABASE_VERSION               TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ----------------------------------------
19.5                           X$KQFTVRTTST0                  COL3_9
19.5                           X$KQFTVRTTST0                  COL3_8
19.5                           X$KQFTVRTTST0                  COL3_7
19.5                           X$KQFTVRTTST0                  COL3_6
19.5                           X$KQFTVRTTST0                  COL3_5
19.5                           X$KQFTVRTTST0                  COL3_4
19.5                           X$KQFTVRTTST0                  COL3_3
19.5                           X$KQFTVRTTST0                  COL3_2
19.5                           X$KQFTVRTTST0                  COL3_1
19.5                           X$KQFTVRTTST0                  COL3_0
19.5                           X$KQFTVRTTST0                  COL2_9
19.5                           X$KQFTVRTTST0                  COL2_8
19.5                           X$KQFTVRTTST0                  COL2_7
19.5                           X$KQFTVRTTST0                  COL2_6
19.5                           X$KQFTVRTTST0                  COL2_5
19.5                           X$KQFTVRTTST0                  COL2_4
19.5                           X$KQFTVRTTST0                  COL2_3
19.5                           X$KQFTVRTTST0                  COL2_2
19.5                           X$KQFTVRTTST0                  COL2_1
19.5                           X$KQFTVRTTST0                  COL2_0
19.5                           X$KQFTVRTTST0                  COL1_9
19.5                           X$KQFTVRTTST0                  COL1_8
19.5                           X$KQFTVRTTST0                  COL1_7
19.5                           X$KQFTVRTTST0                  COL1_6
19.5                           X$KQFTVRTTST0                  COL1_5
19.5                           X$KQFTVRTTST0                  COL1_4
19.5                           X$KQFTVRTTST0                  COL1_3
19.5                           X$KQFTVRTTST0                  COL1_2
19.5                           X$KQFTVRTTST0                  COL1_1
19.5                           X$KQFTVRTTST0                  COL1_0
19.5                           X$KQFTVRTTST0                  COL0_9
19.5                           X$KQFTVRTTST0                  COL0_8
19.5                           X$KQFTVRTTST0                  COL0_7
19.5                           X$KQFTVRTTST0                  COL0_6
19.5                           X$KQFTVRTTST0                  COL0_5
19.5                           X$KQFTVRTTST0                  COL0_4
19.5                           X$KQFTVRTTST0                  COL0_3
19.5                           X$KQFTVRTTST0                  COL0_2
19.5                           X$KQFTVRTTST0                  COL0_1
19.5                           X$KQFTVRTTST0                  COL0_0
19.5                           X$KQFTVRTTST0                  CHGROWOP
19.5                           X$KQFTVRTTST0                  CHGROWID
19.5                           X$KQFTVRTTST0                  COL_OPERATION
19.5                           X$KQFTVRTTST0                  CON_ID
19.5                           X$KQFTVRTTST0                  INST_ID
19.5                           X$KQFTVRTTST0                  INDX
19.5                           X$KQFTVRTTST0                  ADDR

These are exactly the same! So the columns for X$KQFTVRTTST0 that I obtained from the database are the same for all versions in my data, but when I look in an actual database using ‘describe’ I see ADDR, INDX, INST_ID and COLA and COLB. So my data seems to be wrong!

My first reaction was to change the columns for X$KQFTVRTST0 for every version to correct this. But then I realised that I had to investigate this deeper to understand the actual problem, there might be more X$ tables involved.

The first thing to do, is to see if I can reproduce the issue in my database. This can be quite easily be done by describing X$KQFTVRTTST0 to see the columns, and then query these using the above query that demonstrates the join between X$KQFTA and X$KQFCO by adding a filter on ‘X$KQFTVRTST0’:

SYS@o112 AS SYSDBA> desc X$KQFTVRTTST0
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ADDR                                                                                                                       RAW(8)
 INDX                                                                                                                       NUMBER
 INST_ID                                                                                                                    NUMBER
 COLA                                                                                                                       VARCHAR2(64)
 COLB                                                                                                                       NUMBER
SYS@o112 AS SYSDBA> l
  1  select t.kqftanam, c.kqfconam
  2  from x$kqfta t, x$kqfco c
  3  where t.inst_id = 1
  4  and c.inst_id = 1
  5  and t.indx = c.kqfcotab
  6* and kqftanam = 'X$KQFTVRTTST0'
SYS@o112 AS SYSDBA> /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$KQFTVRTTST0                  ADDR
X$KQFTVRTTST0                  INDX
X$KQFTVRTTST0                  INST_ID
X$KQFTVRTTST0                  COL_OPERATION
X$KQFTVRTTST0                  CHGROWID
X$KQFTVRTTST0                  CHGROWOP
X$KQFTVRTTST0                  COL0_0
X$KQFTVRTTST0                  COL0_1
X$KQFTVRTTST0                  COL0_2
X$KQFTVRTTST0                  COL0_3
X$KQFTVRTTST0                  COL0_4
X$KQFTVRTTST0                  COL0_5
X$KQFTVRTTST0                  COL0_6
X$KQFTVRTTST0                  COL0_7
X$KQFTVRTTST0                  COL0_8
X$KQFTVRTTST0                  COL0_9
X$KQFTVRTTST0                  COL1_0
X$KQFTVRTTST0                  COL1_1
X$KQFTVRTTST0                  COL1_2
X$KQFTVRTTST0                  COL1_3
X$KQFTVRTTST0                  COL1_4
X$KQFTVRTTST0                  COL1_5
X$KQFTVRTTST0                  COL1_6
X$KQFTVRTTST0                  COL1_7
X$KQFTVRTTST0                  COL1_8
X$KQFTVRTTST0                  COL1_9
X$KQFTVRTTST0                  COL2_0
X$KQFTVRTTST0                  COL2_1
X$KQFTVRTTST0                  COL2_2
X$KQFTVRTTST0                  COL2_3
X$KQFTVRTTST0                  COL2_4
X$KQFTVRTTST0                  COL2_5
X$KQFTVRTTST0                  COL2_6
X$KQFTVRTTST0                  COL2_7
X$KQFTVRTTST0                  COL2_8
X$KQFTVRTTST0                  COL2_9
X$KQFTVRTTST0                  COL3_0
X$KQFTVRTTST0                  COL3_1
X$KQFTVRTTST0                  COL3_2
X$KQFTVRTTST0                  COL3_3
X$KQFTVRTTST0                  COL3_4
X$KQFTVRTTST0                  COL3_5
X$KQFTVRTTST0                  COL3_6
X$KQFTVRTTST0                  COL3_7
X$KQFTVRTTST0                  COL3_8
X$KQFTVRTTST0                  COL3_9

Bingo! Describe tells me about COLA and COLB, which is the actual situation, and querying X$KQFTA and X$KQFCO gives me different, wrong columns.

First let’s investigate if we can find the correct columns in X$KQFCO, and see what the difference is between X$KQFTA.INDX and X$KQFCO.KQFCOTAB:

SYS@o112 AS SYSDBA> select indx from x$kqfta where kqftanam = 'X$KQFTVRTTST0';

      INDX
----------
       841

SYS@o112 AS SYSDBA> select kqfcotab from x$kqfco where kqfconam = 'COLA';

  KQFCOTAB
----------
       842

Aha! So the join is off by one! So, that must mean that if I decrease KQFCOTAB by one, I do get the correct output:

SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1  select t.kqftanam, c.kqfconam
  2  from x$kqfta t, x$kqfco c
  3  where t.inst_id = 1
  4  and c.inst_id = 1
  5  and t.indx = c.kqfcotab-1
  6* and kqftanam = 'X$KQFTVRTTST0'
  7  /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$KQFTVRTTST0                  ADDR
X$KQFTVRTTST0                  INDX
X$KQFTVRTTST0                  INST_ID
X$KQFTVRTTST0                  COLA
X$KQFTVRTTST0                  COLB

Bingo! The next thing to do is see if this is only a problem for X$KQFTVRTTST0, or a bigger problem. Let’s take the next table based on X$KQFTA.INDX, and see if this shows the same problem:

SYS@o112 AS SYSDBA> select kqftanam from x$kqfta where indx = 842;

KQFTANAM
------------------------------
X$DIAG_ADR_CONTROL
SYS@o112 AS SYSDBA> desc X$DIAG_ADR_CONTROL
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ADDR                                                                                                                       RAW(8)
 INDX                                                                                                                       NUMBER
 INST_ID                                                                                                                    NUMBER
 ADR_PATH_IDX                                                                                                               VARCHAR2(445)
 ADR_HOME                                                                                                                   VARCHAR2(445)
 ADRID                                                                                                                      NUMBER
 SHORTP_POLICY                                                                                                              NUMBER
 LONGP_POLICY                                                                                                               NUMBER
 LAST_MOD_TIME                                                                                                              TIMESTAMP(9) WITH TIME ZONE
 LAST_AUTOPRG_TIME                                                                                                          TIMESTAMP(9) WITH TIME ZONE
 LAST_MANUPRG_TIME                                                                                                          TIMESTAMP(9) WITH TIME ZONE
 ADRDIR_VERSION                                                                                                             NUMBER
 ADRSCHM_VERSION                                                                                                            NUMBER
 ADRSCHMV_SUMMARY                                                                                                           NUMBER
 ADRALERT_VERSION                                                                                                           NUMBER
 CREATE_TIME                                                                                                                TIMESTAMP(9) WITH TIME ZONE
SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1   select t.kqftanam, c.kqfconam
  2   from x$kqfta t, x$kqfco c
  3   where t.inst_id = 1
  4   and c.inst_id = 1
  5   and t.indx = c.kqfcotab
  6*  and kqftanam = 'X$DIAG_ADR_CONTROL'
SYS@o112 AS SYSDBA> /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$DIAG_ADR_CONTROL             ADDR
X$DIAG_ADR_CONTROL             INDX
X$DIAG_ADR_CONTROL             INST_ID
X$DIAG_ADR_CONTROL             COLA
X$DIAG_ADR_CONTROL             COLB

Clearly the columns are wrong, these are the columns of X$KQFTVRTTST0! Let’s subtract X$KQFCO.KQFCOTAB by one:

SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1   select t.kqftanam, c.kqfconam
  2   from x$kqfta t, x$kqfco c
  3   where t.inst_id = 1
  4   and c.inst_id = 1
  5   and t.indx = c.kqfcotab-1
  6*  and kqftanam = 'X$DIAG_ADR_CONTROL'
SYS@o112 AS SYSDBA> /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$DIAG_ADR_CONTROL             ADDR
X$DIAG_ADR_CONTROL             INDX
X$DIAG_ADR_CONTROL             INST_ID
X$DIAG_ADR_CONTROL             ADR_PATH_IDX
X$DIAG_ADR_CONTROL             ADR_HOME
X$DIAG_ADR_CONTROL             ADRID
X$DIAG_ADR_CONTROL             SHORTP_POLICY
X$DIAG_ADR_CONTROL             LONGP_POLICY
X$DIAG_ADR_CONTROL             LAST_MOD_TIME
X$DIAG_ADR_CONTROL             LAST_AUTOPRG_TIME
X$DIAG_ADR_CONTROL             LAST_MANUPRG_TIME
X$DIAG_ADR_CONTROL             ADRDIR_VERSION
X$DIAG_ADR_CONTROL             ADRSCHM_VERSION
X$DIAG_ADR_CONTROL             ADRSCHMV_SUMMARY
X$DIAG_ADR_CONTROL             ADRALERT_VERSION
X$DIAG_ADR_CONTROL             CREATE_TIME

And this is correct again!

Now let’s verify the highest X$KQFTA.INDX number in the version of this database:

SYS@o112 AS SYSDBA> select max(indx) from x$kqfta;

 MAX(INDX)
----------
       927

SYS@o112 AS SYSDBA> select kqftanam from x$kqfta where indx = 927;

KQFTANAM
------------------------------
X$DIAG_VTEST_EXISTS

SYS@o112 AS SYSDBA> desc X$DIAG_VTEST_EXISTS
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ADDR                                                                                                                       RAW(8)
 INDX                                                                                                                       NUMBER
 INST_ID                                                                                                                    NUMBER
 ADR_PATH_IDX                                                                                                               VARCHAR2(445)
 ADR_HOME                                                                                                                   VARCHAR2(445)
 PROBLEM_ID                                                                                                                 NUMBER
 PROBLEM_KEY                                                                                                                VARCHAR2(552)
 FIRST_INCIDENT                                                                                                             NUMBER
 FIRSTINC_TIME                                                                                                              TIMESTAMP(9) WITH TIME ZONE
 LAST_INCIDENT                                                                                                              NUMBER
 LASTINC_TIME                                                                                                               TIMESTAMP(9) WITH TIME ZONE
SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1  select t.kqftanam, c.kqfconam
  2  from x$kqfta t, x$kqfco c
  3  where t.inst_id = 1
  4  and c.inst_id = 1
  5  and t.indx = c.kqfcotab
  6* and kqftanam = 'X$DIAG_VTEST_EXISTS'
SYS@o112 AS SYSDBA> /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$DIAG_VTEST_EXISTS            ADDR
X$DIAG_VTEST_EXISTS            INDX
X$DIAG_VTEST_EXISTS            INST_ID
X$DIAG_VTEST_EXISTS            ADR_PATH_IDX
X$DIAG_VTEST_EXISTS            ADR_HOME
X$DIAG_VTEST_EXISTS            INCIDENT_ID
X$DIAG_VTEST_EXISTS            PROBLEM_ID
X$DIAG_VTEST_EXISTS            CREATE_TIME
X$DIAG_VTEST_EXISTS            CLOSE_TIME
X$DIAG_VTEST_EXISTS            STATUS
X$DIAG_VTEST_EXISTS            FLAGS
X$DIAG_VTEST_EXISTS            FLOOD_CONTROLLED
X$DIAG_VTEST_EXISTS            ERROR_FACILITY
X$DIAG_VTEST_EXISTS            ERROR_NUMBER
X$DIAG_VTEST_EXISTS            ERROR_ARG1
X$DIAG_VTEST_EXISTS            ERROR_ARG2
X$DIAG_VTEST_EXISTS            ERROR_ARG3
X$DIAG_VTEST_EXISTS            ERROR_ARG4
X$DIAG_VTEST_EXISTS            ERROR_ARG5
X$DIAG_VTEST_EXISTS            ERROR_ARG6
X$DIAG_VTEST_EXISTS            ERROR_ARG7
X$DIAG_VTEST_EXISTS            ERROR_ARG8
X$DIAG_VTEST_EXISTS            SIGNALLING_COMPONENT
X$DIAG_VTEST_EXISTS            SIGNALLING_SUBCOMPONENT
X$DIAG_VTEST_EXISTS            SUSPECT_COMPONENT
X$DIAG_VTEST_EXISTS            SUSPECT_SUBCOMPONENT
X$DIAG_VTEST_EXISTS            ECID
X$DIAG_VTEST_EXISTS            IMPACT
X$DIAG_VTEST_EXISTS            IMPACT_STR
X$DIAG_VTEST_EXISTS            STATUS_STR
X$DIAG_VTEST_EXISTS            ERROR_ARG9
X$DIAG_VTEST_EXISTS            ERROR_ARG10
X$DIAG_VTEST_EXISTS            ERROR_ARG11
X$DIAG_VTEST_EXISTS            ERROR_ARG12

So that’s wrong, let’s try subtracting by one:

SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1  select t.kqftanam, c.kqfconam
  2  from x$kqfta t, x$kqfco c
  3  where t.inst_id = 1
  4  and c.inst_id = 1
  5  and t.indx = c.kqfcotab-1
  6* and kqftanam = 'X$DIAG_VTEST_EXISTS'
SYS@o112 AS SYSDBA> /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$DIAG_VTEST_EXISTS            ADDR
X$DIAG_VTEST_EXISTS            INDX
X$DIAG_VTEST_EXISTS            INST_ID
X$DIAG_VTEST_EXISTS            ADR_PATH_IDX
X$DIAG_VTEST_EXISTS            ADR_HOME
X$DIAG_VTEST_EXISTS            PROBLEM_ID
X$DIAG_VTEST_EXISTS            PROBLEM_KEY
X$DIAG_VTEST_EXISTS            FIRST_INCIDENT
X$DIAG_VTEST_EXISTS            FIRSTINC_TIME
X$DIAG_VTEST_EXISTS            LAST_INCIDENT
X$DIAG_VTEST_EXISTS            LASTINC_TIME

And correct again!

So at least starting from X$KQFTA.INDX = 841 up to the highest INDX number, 927, X$KQFCO.KQFCOTAB is off by one.

Starting from there, I decided to test INDX = 1, which was consistent with KQFCOTAB. Then I decided to go down in INDX number starting from 841 being the number of X$KQFTVRTTST0. In my database number 840 is X$KTCNQROW, which was off by one, then I found something weird:

SYS@o112 AS SYSDBA> select kqftanam from x$kqfta where indx = 839;

KQFTANAM
------------------------------
X$SKGXP_MISC

SYS@o112 AS SYSDBA> desc X$SKGXP_MISC
ERROR:
ORA-00942: table or view does not exist
SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1      select t.kqftanam, c.kqfconam
  2      from x$kqfta t, x$kqfco c
  3      where t.inst_id = 1
  4      and c.inst_id = 1
  5      and t.indx = c.kqfcotab
  6*    and kqftanam = 'X$SKGXP_MISC'
SYS@o112 AS SYSDBA> /

no rows selected

So the X$ view X$SKGXP_MISC exists in X$KQFTA, but doesn’t exist in reality, nor in X$KQFCO. Please mind this is a separate, different issue from the X$KQFCO.KQFCOTAB inconsistency this blogpost is about. My current thinking is the issue of existing in X$KQFTA and not queryable and (rightfully because of that), not available in X$KQFCO, might be either just a screwup/bug, or the X$ tables being reserved in the X$ table array, but not actually created, maybe because of setting during compilation. Just a hunch, I don’t know.

The same issue of being visible in X$KQFTA and not existing is true for:
838, X$SKGXP_CONNECTION
837, X$SKGXP_PORT
836, X$KSXP_STATS

INDX number 835 is X$KSXPTESTTBL. This table has the X$KQFCO.KQFCOTAB off by one issue:

SYS@o112 AS SYSDBA> select kqftanam from x$kqfta where indx = 835;

KQFTANAM
------------------------------
X$KSXPTESTTBL
SYS@o112 AS SYSDBA> desc X$KSXPTESTTBL
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ADDR                                                                                                                       RAW(8)
 INDX                                                                                                                       NUMBER
 INST_ID                                                                                                                    NUMBER
 KSXPTESTF1                                                                                                                 NUMBER
 KSXPTESTF2                                                                                                                 VARCHAR2(10)
 KSXPTESTF3                                                                                                                 NUMBER
 KSXPTESTF4                                                                                                                 NUMBER
 KSXPTESTF5                                                                                                                 RAW(8)
 KSXPTESTF6                                                                                                                 DATE
 KSXPTESTF7                                                                                                                 TIMESTAMP(3)
 KSXPTESTF8                                                                                                                 TIMESTAMP(3) WITH TIME ZONE
 KSXPTESTF9                                                                                                                 VARCHAR2(10)
 KSXPTESTF10                                                                                                                RAW(2)
 KSXPTESTF11                                                                                                                RAW(10)
SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1  select t.kqftanam, c.kqfconam
  2  from x$kqfta t, x$kqfco c
  3  where t.inst_id = 1
  4  and c.inst_id = 1
  5  and t.indx = c.kqfcotab
  6* and t.kqftanam = 'X$KSXPTESTTBL'
SYS@o112 AS SYSDBA> /

no rows selected
SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1  select t.kqftanam, c.kqfconam
  2  from x$kqfta t, x$kqfco c
  3  where t.inst_id = 1
  4  and c.inst_id = 1
  5  and t.indx = c.kqfcotab-1
  6* and t.kqftanam = 'X$KSXPTESTTBL'
SYS@o112 AS SYSDBA> /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$KSXPTESTTBL                  ADDR
X$KSXPTESTTBL                  INDX
X$KSXPTESTTBL                  INST_ID
X$KSXPTESTTBL                  KSXPTESTF1
X$KSXPTESTTBL                  KSXPTESTF2
X$KSXPTESTTBL                  KSXPTESTF3
X$KSXPTESTTBL                  KSXPTESTF4
X$KSXPTESTTBL                  KSXPTESTF5
X$KSXPTESTTBL                  KSXPTESTF6
X$KSXPTESTTBL                  KSXPTESTF7
X$KSXPTESTTBL                  KSXPTESTF8
X$KSXPTESTTBL                  KSXPTESTF9
X$KSXPTESTTBL                  KSXPTESTF10
X$KSXPTESTTBL                  KSXPTESTF11

After X$KSXPTESTTBL, INDX number 834 is X$CELL_NAME:

SYS@o112 AS SYSDBA> select kqftanam from x$kqfta where indx = 834;

KQFTANAM
------------------------------
X$CELL_NAME
SYS@o112 AS SYSDBA> desc X$CELL_NAME
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ADDR                                                                                                                       RAW(8)
 INDX                                                                                                                       NUMBER
 INST_ID                                                                                                                    NUMBER
 CELLPATH_CELLROW                                                                                                           VARCHAR2(400)
 CELLHASHVAL_CELLROW                                                                                                        NUMBER
SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1  select t.kqftanam, c.kqfconam
  2  from x$kqfta t, x$kqfco c
  3  where t.inst_id = 1
  4  and c.inst_id = 1
  5  and t.indx = c.kqfcotab
  6* and t.kqftanam = 'X$CELL_NAME'
SYS@o112 AS SYSDBA> /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$CELL_NAME                    ADDR
X$CELL_NAME                    INDX
X$CELL_NAME                    INST_ID
X$CELL_NAME                    CELLPATH_CELLROW
X$CELL_NAME                    CELLHASHVAL_CELLROW

And with X$CELL_NAME the X$KQFTA.INDX=X$KQFCO.KQFCOTAB link is restored.

So, this means that starting with X$KSXPTESTTBL the X$KQFCO.KQFCOTAB number is off by one. In fact, I tested version 18 (18.4), and found that the value X$KQFTA.INDX is 1217, so much higher, but still with X$KSXPTESTTBL the inconsistency of X$KQFCO.KQFCOTAB being one too high to correctly join with X$KQFTA.INDX.

Conclusion.
This means that based on my current investigations, it seems that starting from Oracle database version 11.2.0.1 up to version 19 and current PSU, starting with the X$KQFTA.INDX value of the table X$KSXPTESTTBL in X$KQFTA, the X$KQFCO.KQFCOTAB value needs to be subtracted by one to get the correct fields from X$KQFCO.KQFCOTAB to join to X$KQFTA.INDX.

A lot of X$ tables are not impacted, the majority of the tables have an X$KQFTA.INDX value lower than X$KSXPTESTTBL, but there are a reasonable amount of tables that are impacted, a lot of them related to ADR (starting with X$DIAG).

The reason for showing an additional oddity with X$SKGXP_MISC, X$SKGXP_CONNECTION, X$SKGXP_PORT and X$KSXP_STATS is that the X$ table where the join problem starts is X$KSXPTESTTBL. All these tables have something to do with cross instance IPC communication (skgxp/ksxp), and a lot of them have the weird property that they don’t exist whilst are visible in X$KQFTA, this might have something to do with X$KQFCO.KQFCOTAB being off by one. Of course this is something I can’t check, because it requires source code access, but I thought it would be good to mention.

It’s interesting to see that the join of X$KQFTA.INDX to X$KQFCO.KQFCOTAB is present in Oracle’s data dictionary with the view V$INDEXED_FIXED_COLUMN:

SQL> select view_definition from v$fixed_view_definition where view_name = 'GV$INDEXED_FIXED_COLUMN';

VIEW_DEFINITION
--------------------------------------------------------------------------------
select c.inst_id,kqftanam, kqfcoidx, kqfconam, kqfcoipo, c.con_id  from x$kqfco
c, x$kqfta t where t.indx = c.kqfcotab and kqfcoidx != 0

Which by now should be clear is not correct for X$ tables with an X$KQFTA.INDX number equal or higher than X$KSXPTESTTBL.

%d bloggers like this: