What’s new with Oracle database 12.1.0.2.200114 versus 12.1.0.2.200414

This blogpost takes a look at the technical differences between Oracle database 12.1.0.2 PSU 200114 (january 2020) and 200414 (april 2020). This gives technical specialists an idea of the differences, and gives them the ability to assess if the PSU impacts anything.

Parameters
The list of parameters removed (first) and parameters added (second) is remarkable long.
It’s striking that a lot of solutions for bugs made configurable (_bug[0-9]*_.*) have been removed, and probably returned back as ‘spare parameters’.
Also some in memory (_inmemory.*) parameters have been removed.
Also some documented parameters; exafusion_enabled and optimizer_adaptive_plans and optimizer_adaptive_statistics, plus some standby parameters I wasn’t aware of existing.

The new parameters are all undocumented (starting with an underscore). Most of them are essentially the undocumented named parameters returned to the pool of spare parameters. Some that are named seem to be related to ‘data protection’: (_sga_data_protection_disabled, _sga_dp_bytes_per_alloc, _sga_dp_precise_errors), I am not sure what the actual data protection feature is here. Some are in-memory related, shared IPC (ksxp_skgxp) and some others. There’s one “bugswitch” added again: _bug18411216_gcr_min_free_memory_hard_limit.

parameters unique in version 12.1.0.2.200114 versus 12.1.0.2.200414

NAME
--------------------------------------------------
_asm_cancel_alert_time
_bug12963364_spacebg_sync_segblocks
_bug19146597_df_hist_offl_override
_bug20480623_posting_ips
_bug20534663_ksxp_save_sockets_pat
_bug21214994_lm_max_lms_block_time
_bug21214994_lm_sndq_flush_int
_bug21893830_influx_scn_waits
_bug21918948_incremental_purge_size
_bug21977186_lm_hb_cfio_timeout
_bug24587802_asm_auto_online_interval
_bug24717183_lmhb_procdump_cputime_limit
_bug24717183_lmhb_procdump_runtime_limit
_bug24966594_gcs_disable_imc_preallocation
_bug28572834_xt_preproc_timeout
_bug29825525_bct_public_dba_buffer_dynresize_delay
_cache_fusion_pipelined_updates
_cell_offload_hybrid_processing
_dskm_single_instance
_force_cloud_hcc_on
_gcr_min_free_memory_hard_limit
_inmemory_journal_options
_inmemory_journal_scan
_inmemory_private_journal_maxexts
_inmemory_retention_time
_ksxp_save_sockets
_ksxp_xrc_cm_port
_memory_adi_bytes_per_alloc
_memory_adi_enabled
_memory_adi_precise_errors
_min_sys_percentage
_one-hundred-and-forty-eighth_spare_parameter
_optimizer_ads_for_pq
_optimizer_use_feedback_for_join
exafusion_enabled
optimizer_adaptive_plans
optimizer_adaptive_statistics
standby_pdb_source_file_dblink
standby_pdb_source_file_directory

parameters unique in version 12.1.0.2.200414 versus 12.1.0.2.200114

NAME
--------------------------------------------------
_bug18411216_gcr_min_free_memory_hard_limit
_eighteenth_spare_parameter
_eighty-seventh_spare_parameter
_eighty-sixth_spare_parameter
_eighty-third_spare_parameter
_external_keystore_credential_location
_fourteenth_spare_parameter
_gcs_disable_imc_preallocation
_inmemory_cu_timeout
_inmemory_jscan
_ksxp_skgxp_spare_param4
_ksxp_skgxp_spare_param5
_min_sys_percetange
_one-hundred-and-fiftieth_spare_parameter
_one-hundred-and-fifty-fifth_spare_parameter
_one-hundred-and-fifty-first_spare_parameter
_one-hundred-and-fifty-fourth_spare_parameter
_one-hundred-and-fifty-second_spare_parameter
_one-hundred-and-fifty-sixth_spare_parameter
_one-hundred-and-fifty-third_spare_parameter
_one-hundred-and-forty-ninth_spare_parameter
_one-hundred-and-forty-seventh_spare_parameter
_one-hundred-and-forty-third_spare_parameter
_one-hundred-and-thirtieth_spare_parameter
_ping_wait_for_log_force
_seventeenth_spare_parameter
_sga_data_protection_disabled
_sga_dp_bytes_per_alloc
_sga_dp_precise_errors
_sixteenth_spare_parameter
_thirteenth_spare_parameter
_twelfth_spare_parameter
_twentieth_spare_parameter
_twenty-first_spare_parameter
_twenty-fourth_spare_parameter
_twenty-second_spare_parameter
_twenty-seventh_spare_parameter
_twenty-sixth_spare_parameter
_twenty-third_spare_parameter

V$/GV$ fixed view column removal
A column named GUID has been removed from a number of BACKUP related views:

v$ tables columns unique to 12.1.0.2.200114 versus 12.1.0.2.200414

NAME												     COLUMN_NAME
---------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
V_$BACKUP_DATAFILE										     GUID
V_$BACKUP_PIECE 										     GUID
V_$BACKUP_SET											     GUID
V_$BACKUP_SPFILE										     GUID
V_$DATAFILE_COPY										     GUID
V_$PROXY_DATAFILE										     GUID

gv$ tables columns unique to 12.1.0.2.200114 versus 12.1.0.2.200414

NAME												     COLUMN_NAME
---------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
GV_$BACKUP_DATAFILE										     GUID
GV_$BACKUP_PIECE										     GUID
GV_$BACKUP_SET											     GUID
GV_$BACKUP_SPFILE										     GUID
GV_$DATAFILE_COPY										     GUID
GV_$PROXY_DATAFILE										     GUID

Statistics
Also quite remarkable are 14 statistics that have been made redundant with the april PSU, 6 in-memory related statistics, 3 cell/exadata related and 5 GCS (glocal cache services/RAC) related.

sysstat statistics unique to 12.1.0.2.200114 versus 12.1.0.2.200414

NAME
----------------------------------------------------------------------------------------------------
IM repopulate (doublebuffering) CUs
IM repopulate (doublebuffering) CUs requested
IM repopulate (incremental) CUs
IM repopulate blocks now valid (ATC)
IM repopulate optimized
IM scan CUs doublebuffering
cell XT granule IO bytes saved by HDFS tbs extent map scan
cell XT granule IO bytes saved by storage index
cell physical IO bytes eligible for smart IOs
gcs affinity lock failures
gcs affinity lock grants
gcs anti-locks created
gcs read-mostly lock failures
gcs read-mostly lock grants

sysstat statistics unique to 12.1.0.2.200414 versus 12.1.0.2.200114

NAME
----------------------------------------------------------------------------------------------------
IM zzzz spare1
IM zzzz spare2
IM zzzz spare3
IM zzzz spare4
IM zzzz spare5
IM zzzz spare6
cell statistics spare1
cell statistics spare2
cell statistics spare3
spare statistic 1
spare statistic 2
spare statistic 3
spare statistic 4
spare statistic 5

DBA/CDB tables
There are also 3 DBA/CDB tables that have been removed. These seem to be dealing with big data, the name HIVE suggests hive metadata.

dba tables unique to 12.1.0.2.200114 versus 12.1.0.2.200414

NAME
----------------------------------------------------------------------------------------------------
DBA_HIVE_COLUMNS
DBA_HIVE_DATABASES
DBA_HIVE_TABLES

cdb tables unique to 12.1.0.2.200114 versus 12.1.0.2.200414

NAME
----------------------------------------------------------------------------------------------------
CDB_HIVE_COLUMNS
CDB_HIVE_DATABASES
CDB_HIVE_TABLES

DBA/CDB table columns
There is one column: TBS_INHERITED_FROM that is added to DBA_ILMOBJECTS.

dba tables columns unique to 12.1.0.2.200414 versus 12.1.0.2.200114

NAME												     COLUMN_NAME
---------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
DBA_ILMOBJECTS											     TBS_INHERITED_FROM

And it is removed from CDB_ILMNOBJECTS

cdb tables column unique to 12.1.0.2.200114 versus 12.1.0.2.200414

NAME												     COLUMN_NAME
---------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
CDB_ILMOBJECTS											     TBS_INHERITED_FROM

Functions
I grouped the removed and added functions by the first 3 letters, otherwise the list would be too long to be meaningful.
I think a fair conclusion is there still has been improvements made to 12.1.0.2 between the january and april PSU.
The main area’s where the removed and added functions are visible for are:
Removed:
– jzn: JSON; jzn0Dom/jznBson/jznDom/jznEng/jznOson/jznParser/jznoct, unsure what these sub-categories are.
– kdz: archive compression (HCC); kdzcol (column?)
– ktm: kernel transaction in memory
– kub: kernel utility ‘b’; this is actually ‘kubs’: kernel utility big data sql
– kdm: kernel data in memory; this is actually kdmo: kernel data in-memory data layer optimizer
Added:
– jzn: JSON; jznEng/jznt/jzntok (tokeniser?)
– kts: kernel transaction segment management; ktsima (in memory?)/ktsl (lob)
– skg: system kernel generic; this is actually skgzib: infiniband code
– kdm: kernel data in memory; this is actually kdmo: kernel data in-memory data layer optimizer
– kfd: kernel automatic storage management

code symbol names unique in version 12.1.0.2.200114 versus 12.1.0.2.200414

NAME                                               RESOLVE                                            ANNOTATION                                                                         COUNT(*)
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------------------------------------- ----------
ctc                                                ctc                                                create table or cluster                                                                   1
def                                                def                                                ??                                                                                        1
dgl                                                dgl                                                ??                                                                                        1
hor                                                hor                                                ??                                                                                        1
kcl                                                kcl                                                kernel cache lock manager/buffer cache                                                    1
kcv                                                kcv                                                kernel cache recovery                                                                     1
kdb                                                kdb                                                kernel data block structures and manipulation                                             1
kdt                                                kdt                                                kernel data table insert                                                                  1
kfD                                                (kf)D                                              kernel automatic storage management ??                                                    1
kfc                                                kfc                                                kernel automatic storage management cache                                                 1
kff                                                kff                                                kernel automatic storage management KFF                                                   1
kfm                                                kfm                                                kernel automatic storage management node monitor interface implementation                 1
kgx                                                kgx                                                kernel generic mutex/communication                                                        1
kjx                                                kjx                                                kernel lock management cross instance                                                     1
kkb                                                kkb                                                kernel compile table                                                                      1
kkd                                                (kk)d                                              kernel compile ??                                                                         1
kkn                                                kkn                                                kernel compile analyze command                                                            1
kks                                                kks                                                kernel compile shared objects (cursor)                                                    1
kkt                                                kkt                                                kernel compile triggers                                                                   1
kmg                                                (km)g                                              kernel multi threaded/mman ??                                                             1
kmm                                                kmm                                                kernel multi threaded/mman monitor                                                        1
kpx                                                (kp)x                                              kernel programmatic interface ??                                                          1
kqr                                                kqr                                                kernel query dictionary/row cache                                                         1
krd                                                krd                                                kernel redo datafiles (media and standby)                                                 1
krf                                                krf                                                kernel redo flashback                                                                     1
ksd                                                ksd                                                kernel service  debug internal errors                                                     1
ksr                                                ksr                                                kernel service  reliable messages                                                         1
kte                                                kte                                                kernel transaction extent management                                                      1
ktg                                                (kt)g                                              kernel transaction ??                                                                     1
ktr                                                ktr                                                kernel transaction read consistency                                                       1
ktu                                                ktu                                                kernel transaction undo                                                                   1
kza                                                kza                                                kernel security audit                                                                     1
kzp                                                kzp                                                kernel security privileges                                                                1
msq                                                msq                                                generating sql statements                                                                 1
ozi                                                ozi                                                ??                                                                                        1
pmu                                                pmu                                                ??                                                                                        1
qcs                                                qcs                                                query compile semantic analysis (parser)                                                  1
qct                                                qct                                                query compile type check operations                                                       1
qkn                                                qkn                                                query kernel allocate                                                                     1
qmu                                                (qm)u                                              query XDB ??                                                                              1
qxi                                                (qx)i                                              query execution ??                                                                        1
rfr                                                rfr                                                ??                                                                                        1
seq                                                seq                                                sequence numbers                                                                          1
slp                                                (sl)p                                              operating system (dependent) library ??                                                   1
tbs                                                tbs                                                ??                                                                                        1
upd                                                upd                                                update                                                                                    1
xsE                                                (xs)E                                              olap analytic workspace ??                                                                1
fpl                                                fpl                                                ??                                                                                        2
jsk                                                (js)k                                              job scheduing ??                                                                          2
kbc                                                kbc                                                kernel backup backup appliance containers                                                 2
kcc                                                kcc                                                kernel cache controlfile                                                                  2
kfr                                                kfr                                                kernel automatic storage management recovery                                              2
kgg                                                kgg                                                kernel generic generic routines                                                           2
kjf                                                (kj)f                                              kernel lock management ??                                                                 2
kjm                                                kjm                                                kernel lock management RAC multiple LMS                                                   2
kjz                                                kjz                                                kernel lock management DIAG                                                               2
knl                                                knl                                                kernel replication streams replication                                                    2
kql                                                kql                                                kernel query library cache                                                                2
ksp                                                ksp                                                kernel service  parameter                                                                 2
kws                                                kws                                                kernel streams advanced queuing                                                           2
kxs                                                kxs                                                kernel execution shared cursor                                                            2
pfr                                                pfr                                                PLSQL Code Execution runtime                                                              2
pld                                                (pl)d                                              PLSQL infrastructure ??                                                                   2
psd                                                psd                                                PLSQL infrastructure programmatic interfaces to/from                                      2
qcu                                                qcu                                                query compile utility                                                                     2
qka                                                qka                                                query kernel allocation                                                                   2
qxx                                                (qx)x                                              query execution ??                                                                        2
sks                                                (sk)s                                              operating system dependent kernel ??                                                      2
ssk                                                ssk                                                operating system dependent system kernel                                                  2
kcr                                                kcr                                                kernel cache recovery                                                                     3
kfn                                                kfn                                                kernel automatic storage management networking subsystem                                  3
kfx                                                (kf)x                                              kernel automatic storage management ??                                                    3
kgn                                                (kg)n                                              kernel generic ??                                                                         3
kkf                                                (kk)f                                              kernel compile ??                                                                         3
kkk                                                (kk)k                                              kernel compile ??                                                                         3
kkx                                                kkx                                                kernel compile execute                                                                    3
kti                                                kti                                                kernel transaction in memory undo                                                         3
ktt                                                ktt                                                kernel transaction tablespace                                                             3
ora                                                ora                                                ??                                                                                        3
phn                                                phn                                                PLSQL semantics                                                                           3
sku                                                (sk)u                                              operating system dependent kernel ??                                                      3
__i                                                __i                                                ??                                                                                        4
isi                                                isi                                                ??                                                                                        4
kdl                                                kdl                                                kernel data lob                                                                           4
kgh                                                kgh                                                kernel generic heap manager                                                               4
kpu                                                kpu                                                kernel programmatic interface user                                                        4
ksw                                                ksw                                                kernel service  workgroup                                                                 4
eva                                                eva                                                evaluate                                                                                  5
kfi                                                (kf)i                                              kernel automatic storage management ??                                                    5
kkz                                                kkz                                                kernel compile security                                                                   5
klx                                                (kl)x                                              kernel loader ??                                                                          5
kxf                                                kxf                                                kernel execution parallel query                                                           5
prm                                                prm                                                parameter handling with sql layer                                                         5
qcp                                                qcp                                                query compile parse                                                                       5
ins                                                ins                                                insert                                                                                    6
kkp                                                kkp                                                kernel compile compilation                                                                6
krs                                                krs                                                kernel redo standby/dataguard (?)                                                         6
krv                                                krv                                                kernel redo recovery                                                                      6
mde                                                (md)e                                              multidimension (spatial) ??                                                               6
prs                                                prs                                                parse                                                                                     6
qos                                                qos                                                query optimizer statistics                                                                6
dbg                                                dbg                                                debug and diagnostics                                                                     7
hos                                                hos                                                ??                                                                                        7
kjc                                                kjc                                                kernel lock management communication                                                      7
ktf                                                ktf                                                kernel transaction flashback support                                                      7
kud                                                (ku)d                                              kernel utility ??                                                                         7
kdp                                                kdp                                                kernel data archive compression: pcode                                                    8
kcb                                                kcb                                                kernel cache buffers                                                                      9
kfk                                                kfk                                                kernel automatic storage management KFK                                                   9
kgl                                                kgl                                                kernel generic library cache management                                                   9
kgz                                                (kg)z                                              kernel generic ??                                                                         9
kke                                                kke                                                kernel compile cost engine                                                                9
ksm                                                ksm                                                kernel service  memory                                                                    9
kwq                                                kwq                                                kernel advanced queuing                                                                  10
kdi                                                kdi                                                kernel data index                                                                        11
ksf                                                ksf                                                kernel service  functions                                                                11
kds                                                kds                                                kernel data seek/scan                                                                    12
qjs                                                (q)js                                              query ??                                                                                 12
ksz                                                ksz                                                kernel service  oracle storage server (OSS) server layer                                 13
kgf                                                kgf                                                kernel generic ASM                                                                       14
kfg                                                kfg                                                kernel automatic storage management diskgroups                                           15
ksx                                                ksx                                                kernel service  cross instance                                                           15
kcf                                                kcf                                                kernel cache file management                                                             16
kko                                                kko                                                kernel compile optimizer                                                                 16
kkq                                                kkq                                                kernel compile query                                                                     16
kna                                                kna                                                kernel replication apply                                                                 16
kpd                                                (kp)d                                              kernel programmatic interface ??                                                         16
kxd                                                kxd                                                kernel execution exadata modules                                                         16
skg                                                skg                                                operating system dependent kernel generic                                                17
kbr                                                (kb)r                                              kernel backup ??                                                                         18
sag                                                sag                                                ??                                                                                       18
krb                                                krb                                                kernel redo backup/restore                                                               20
kew                                                kew                                                kernel event AWR                                                                         21
qer                                                qer                                                query execute rowsource                                                                  22
qmx                                                qmx                                                query XDB XML Objects                                                                    22
kol                                                (ko)l                                              kernel objects ??                                                                        24
kok                                                kok                                                kernel objects kernel side                                                               25
kup                                                kup                                                kernel utility datapump                                                                  26
ksi                                                ksi                                                kernel service  instance locking                                                         27
kts                                                kts                                                kernel transaction segment management                                                    31
qes                                                qes                                                query execute services                                                                   36
kfd                                                kfd                                                kernel automatic storage management disk                                                 43
qks                                                qks                                                query kernel sql                                                                         43
kdm                                                kdm                                                kernel data in-memory data layer                                                         48
kub                                                (ku)b                                              kernel utility ??                                                                        53
ktm                                                ktm                                                kernel transaction transaction monitor (smon)                                            70
kdz                                                kdz                                                kernel data archive compression                                                         134
jzn                                                jzn                                                json                                                                                    244

code symbol names unique in version 12.1.0.2.200414 versus 12.1.0.2.200114

NAME                                               RESOLVE                                            ANNOTATION                                                                         COUNT(*)
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------------------------------------- ----------
Ext                                                Ext                                                ??                                                                                        1
R_S                                                R_S                                                ??                                                                                        1
ctc                                                ctc                                                create table or cluster                                                                   1
dim                                                (di)m                                              PLSQL diana ??                                                                            1
jsv                                                (js)v                                              job scheduing ??                                                                          1
kcb                                                kcb                                                kernel cache buffers                                                                      1
kds                                                kds                                                kernel data seek/scan                                                                     1
kes                                                kes                                                kernel event SQL manageability                                                            1
kfc                                                kfc                                                kernel automatic storage management cache                                                 1
kff                                                kff                                                kernel automatic storage management KFF                                                   1
kfg                                                kfg                                                kernel automatic storage management diskgroups                                            1
kfk                                                kfk                                                kernel automatic storage management KFK                                                   1
kfr                                                kfr                                                kernel automatic storage management recovery                                              1
kfz                                                kfz                                                kernel automatic storage management zecurity subsystem                                    1
kgs                                                kgs                                                kernel generic service                                                                    1
kke                                                kke                                                kernel compile cost engine                                                                1
kko                                                kko                                                kernel compile optimizer                                                                  1
kks                                                kks                                                kernel compile shared objects (cursor)                                                    1
kpd                                                (kp)d                                              kernel programmatic interface ??                                                          1
ksl                                                ksl                                                kernel service  latching and post-wait                                                    1
ksm                                                ksm                                                kernel service  memory                                                                    1
ksx                                                ksx                                                kernel service  cross instance                                                            1
kub                                                (ku)b                                              kernel utility ??                                                                         1
kud                                                (ku)d                                              kernel utility ??                                                                         1
kxf                                                kxf                                                kernel execution parallel query                                                           1
kze                                                (kz)e                                              kernel security ??                                                                        1
kzr                                                kzr                                                kernel security virtual private database                                                  1
kzv                                                (kz)v                                              kernel security ??                                                                        1
nzh                                                (nz)h                                              network security ??                                                                       1
nzr                                                (nz)r                                              network security ??                                                                       1
pfr                                                pfr                                                PLSQL Code Execution runtime                                                              1
qcs                                                qcs                                                query compile semantic analysis (parser)                                                  1
qjs                                                (q)js                                              query ??                                                                                  1
qka                                                qka                                                query kernel allocation                                                                   1
qke                                                qke                                                query kernel expressions                                                                  1
qkn                                                qkn                                                query kernel allocate                                                                     1
qos                                                qos                                                query optimizer statistics                                                                1
qxi                                                (qx)i                                              query execution ??                                                                        1
ztc                                                (zt)c                                              security encryption ??                                                                    1
kcr                                                kcr                                                kernel cache recovery                                                                     2
kgc                                                (kg)c                                              kernel generic ??                                                                         2
kgf                                                kgf                                                kernel generic ASM                                                                        2
kgl                                                kgl                                                kernel generic library cache management                                                   2
kjz                                                kjz                                                kernel lock management DIAG                                                               2
kkq                                                kkq                                                kernel compile query                                                                      2
kng                                                (kn)g                                              kernel replication ??                                                                     2
krb                                                krb                                                kernel redo backup/restore                                                                2
qks                                                qks                                                query kernel sql                                                                          2
kbr                                                (kb)r                                              kernel backup ??                                                                          3
kfn                                                kfn                                                kernel automatic storage management networking subsystem                                  3
dbg                                                dbg                                                debug and diagnostics                                                                     4
kdz                                                kdz                                                kernel data archive compression                                                           4
kti                                                kti                                                kernel transaction in memory undo                                                         4
qcp                                                qcp                                                query compile parse                                                                       4
qer                                                qer                                                query execute rowsource                                                                   4
nzd                                                (nz)d                                              network security ??                                                                       5
krv                                                krv                                                kernel redo recovery                                                                      6
kna                                                kna                                                kernel replication apply                                                                  7
qmx                                                qmx                                                query XDB XML Objects                                                                     7
nzb                                                (nz)b                                              network security ??                                                                       8
nzo                                                (nz)o                                              network security ??                                                                       8
ktm                                                ktm                                                kernel transaction transaction monitor (smon)                                             9
kok                                                kok                                                kernel objects kernel side                                                               10
krs                                                krs                                                kernel redo standby/dataguard (?)                                                        13
kfd                                                kfd                                                kernel automatic storage management disk                                                 15
kdm                                                kdm                                                kernel data in-memory data layer                                                         20
skg                                                skg                                                operating system dependent kernel generic                                                21
kts                                                kts                                                kernel transaction segment management                                                    23
jzn                                                jzn                                                json                                                                                     56

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: