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
Frits Hoogland Weblog 