Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

What’s new with Oracle database 19.6 versus 19.5

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)

VirtualBox 6.1.2

About a month after the release of VirtualBox 6.1 we get the release of VirtualBox 6.1.2, a maintenance release.

The downloads and changelog are in the usual places.

So far I’ve only tried it on a Windows 10 host at work, but it looks fine.

Remember, if you use Vagrant 2.2.6 and this is your first time using VirtualBox 6.1.x you will need to do a couple of config changes to Vagrant, as discussed in this post by Simon Coter. I’m sure once Vagrant 2.2.7 is released this will no longer be necessary.

Happy upgrading! </p />
</p></div>

    	  	<div class=

Initialising PL/SQL associative arrays in 18c and later

I can never remember how to initialise PL/SQL associative arrays and thought I’d write a short post about it. This is primarily based on an article on Oracle’s Ask Tom site, plus a little extra detail from Steven Feuerstein. Associative arrays were previously known as index-by tables, by the way.

Associative arrays before 18c

Prior to Oracle 18c, you had to initialise an associative array in a slightly cumbersome way, like so:

DECLARE
    TYPE array_t IS
        TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
    v_the_array   array_t;
    v_index       PLS_INTEGER;
BEGIN
    v_the_array(1) := 'one';
    v_the_array(2) := 'two';
    v_the_array(3) := 'three';
    v_the_array(9) := 'nine';
    v_index := v_the_array.first;
    WHILE ( v_index IS NOT NULL ) LOOP
        dbms_output.put_line('v_the_array(' || v_index || '): ' || v_the_array(v_index));
        v_index := v_the_array.next(v_index);
    END LOOP;
END;
/

There are slight variations to the theme as explained in example 5-3 of the Oracle Database PL/SQL language reference you might find interesting. You end up having to repeat yourself a lot, as you can see immediately below the BEGIN keyword.

Oracle 18c simplifies this task

With 18c the syntax reminds me a little of Perl, as in you define the type first; in the next step you add a variable to your anonymous code block, which you initialise in the same step:

DECLARE
    TYPE array_t IS
        TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;

    -- this is new for 18c, see Steven Feuerstein's article here:
    -- https://blogs.oracle.com/oraclemagazine/easy-initializing-for-records-an...
    v_the_array   array_t := array_t(
        1 => 'one', 
        2 => 'two', 
        3 => 'three',    -- note gap here ;)
        9 => 'nine');

    v_index       PLS_INTEGER;
BEGIN
    v_index := v_the_array.first;

    WHILE ( v_index IS NOT NULL ) LOOP
        dbms_output.put_line('v_the_array(' || v_index || '): ' || v_the_array(v_index));
        v_index := v_the_array.next(v_index);
    END LOOP;

END;
/ 

This way you can define the array in a much nicer looking way and with less code. I also find it more readable.

You are of course not limited to using PLS_INTEGER for indexing the array, you can index differently:

DECLARE
    TYPE capitals_t IS
        TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
    v_capitals   capitals_t := capitals_t(
        'France' => 'Paris', 
        'Belgium' => 'Brussels', 
        'Austria' => 'Vienna');

    v_index      VARCHAR2(100);
BEGIN
    v_index := v_capitals.first;
    WHILE ( v_index IS NOT NULL ) LOOP
        dbms_output.put_line('The capital of ' || v_index || ' is ' 
                             || v_capitals(v_index));

        v_index := v_capitals.next(v_index);
    END LOOP;

END;
/ 

Once I wrapped my head around this it all made sense, and I think I’ll use this syntax from now on.

Oracle Database 19c Automatic Indexing – Need Another Index (Another Brick in The Wall Part 2)

I previously discussed how Automatic Indexing can effectively cleverly reorder an existing index if it means it can now use the new index to satisfy new SQL predicates. In this post, we’ll explore this example further with some new workloads. So, we previously ran SQL queries with SQL predicates in the following combinations: CODE1=42 and […]

help.sql: Show TPT Script Purpose and Syntax

If you use my TPT scripts for Oracle troubleshooting, I have some good news for you. I had created a help.sql script years ago for showing me a quick index of my scripts, with syntax examples, straight from sqlplus command line. BUT, it was just an empty template, I never got to populating help.sql to cover my most used scripts.
Recently Tomasz Sroka, who had attended my Oracle troubleshooting training, mentioned that he had taken matters into his own hands and documented about 100 of my scripts in the help command.

help.sql: Show TPT Script Purpose and Syntax

If you use my TPT scripts for Oracle troubleshooting, I have some good news for you. I had created a help.sql script years ago for showing me a quick index of my scripts, with syntax examples, straight from sqlplus command line. BUT, it was just an empty template, I never got to populating help.sql to cover my most used scripts.
Recently Tomasz Sroka, who had attended my Oracle troubleshooting training, mentioned that he had taken matters into his own hands and documented about 100 of my scripts in the help command.

Online Statistics Gathering Disabled by Column Specific METHOD_OPT Table Statistics Preference

I have come across a quirk where the presence of a table statistics preference that specifies METHOD_OPT that is specific to some columns disables online statistics gathering.  This behaviour is at least not documented.  I have reproduced this in Oracle version 12.1.0.2 and 19.3.

Demonstration 

I will create two identical tables, but on the first table, I will specify a table statistic preference to collect a histogram on column C.

#eeeeee; border: 0px solid #000000; font-family: "courier new"; font-size: 98%; line-height: 1.0; overflow: auto; padding-left: 2px; padding-right: 2px; width: 95%;">set serveroutput on verify on autotrace off
CREATE TABLE t1(a number, b varchar2(1000), c number);
CREATE TABLE t2(a number, b varchar2(1000), c number);
exec dbms_stats.set_table_prefs(user,'t1','METHOD_OPT','FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 254 C');

To make the test rerunnable, I will truncate each table, delete any statistics (because truncate does not delete statistics) and then populate the table again in direct-path mode.

#eeeeee; border: 0px solid #000000; font-family: "courier new"; font-size: 98%; line-height: 1.0; overflow: auto; padding-left: 2px; padding-right: 2px; width: 95%;">TRUNCATE TABLE t1;
EXEC dbms_stats.delete_table_stats(user,'T1');
INSERT /*+APPEND*/ INTO t1
SELECT ROWNUM a, TO_CHAR(TO_DATE(rownum,'J'),'Jsp') b, CEIL(SQRT(rownum)) c
FROM dual CONNECT BY level <= 1e5;

TRUNCATE TABLE t2;
EXEC dbms_stats.delete_table_stats(user,'T2');
INSERT /*+APPEND*/ INTO t2
SELECT ROWNUM a, TO_CHAR(TO_DATE(rownum,'J'),'Jsp') b, CEIL(SQRT(rownum)) c
FROM dual CONNECT BY level <= 1e5;
COMMIT;

I expect to get statistics on both tables.

#eeeeee; border: 0px solid #000000; font-family: "courier new"; font-size: 98%; line-height: 1.0; overflow: auto; padding-left: 2px; padding-right: 2px; width: 95%;">alter session set nls_date_Format = 'hh24:mi:ss dd/mm/yy';
column table_name format a10
column column_name format a11
SELECT table_name, num_rows, last_analyzed FROM user_tables WHERE table_name LIKE 'T_' ORDER BY 1;
SELECT table_name, column_name, num_distinct, histogram, num_buckets FROM user_tab_columns WHERE table_name LIKE 'T_' ORDER BY 1,2;

But I only get table and column statistics on T2, the one without the statistics preference.

#eeeeee; border: 0px solid #000000; font-family: "courier new"; font-size: 98%; line-height: 1.0; overflow: auto; padding-left: 2px; padding-right: 2px; width: 95%;">TABLE_NAME   NUM_ROWS LAST_ANALYZED
---------- ---------- -----------------
T1
T2 100000 10:08:30 15/01/20

Table Column
Name Name NUM_DISTINCT HISTOGRAM NUM_BUCKETS
----- ------ ------------ --------------- -----------
T1 A NONE
T1 B NONE
T1 C NONE
T2 A 100000 NONE 1
T2 B 98928 NONE 1
T2 C 317 NONE 1

It appears that I don't get statistics on T1 because I have specified a METHOD_OPT table statistic preference that is specific to some named columns. It doesn't have to specify creating a histogram, it might be preventing a histogram from being created.
For example, this preference does not disable online statistics collection.

#eeeeee; border: 0px solid #000000; font-family: "courier new"; font-size: 98%; line-height: 1.0; overflow: auto; padding-left: 2px; padding-right: 2px; width: 95%;">EXEC dbms_stats.set_table_prefs(user,'t2','METHOD_OPT','FOR ALL COLUMNS SIZE 1');

But these preferences do disable online statistics collection.

#eeeeee; border: 0px solid #000000; font-family: "courier new"; font-size: 98%; line-height: 1.0; overflow: auto; padding-left: 2px; padding-right: 2px; width: 95%;">EXEC dbms_stats.set_table_prefs(user,'t2','METHOD_OPT','FOR COLUMNS SIZE 1 B C');
EXEC dbms_stats.set_table_prefs(user,'t2','METHOD_OPT','FOR COLUMNS SIZE 1 A B C');

I have not found any other statistics preferences (for other DBMS_STATS parameters) that cause this behaviour.

Conclusion 

Table preferences are recommended as a method of controlling statistics collection declaratively and consistently. You don't have to specify parameters to DBMS_STATS into scripts that collect statistics ad-hoc. The table statistics preferences provide a method that every time statistics are collected on a particular table, they are collected consistently, albeit in a way that may be different from the default.
However, take the example of an ETL process loading data into a data warehouse. If you rely on online statistics gathering to collect table statistics as a part of a data load process, you must now be careful not to disable statistics collection during the load with METHOD_OPT statistics preferences.

Autonomous & 19c Addendum

(Updated 21st January 2020) In the Oracle Autonomous Data Warehouse, in order to make statistics collection as self-managing as possible, Oracle sets two undocumented parameters.

  • _optimizer_gather_stats_on_load_hist=TRUE - so that histograms are created on every column when online statistics are gathered
  • _optimizer_gather_stats_on_load_all=TRUE - so that online statistics are collected for every direct-path insert, not just the first one.

Creating a column specific METHOD_OPT statistics preference disables this behaviour.
From 19c, on Engineered systems, Oracle collects Real-Time statistics.  The METHOD_OPT preference does not disable this behaviour, but you only get Real-Time statistics if you have conventional statistics in the first place.  If you don't get statistics after a bulk load, then you won't get Real-Time statistics until conventional statistics have been explicitly gathered, for example, by the maintenance job.

(Update 22nd January 2020) This has been filed as bug 30787109.

Video : Oracle REST Data Services (ORDS) : SQL Developer Web

Today’s video is a quick run through SQL Developer Web, introduced in Oracle REST Data Services (ORDS) 19.4.

For those that prefer the written word, this is based on the following article.

You can find all my other ORDS content here.

The reluctant star of today’s video is Tuomas Pystynen, who was held at gunpoint whilst filming this. </p />
</p></div>

    	  	<div class=

Drop Column bug

When I was a child I could get lost for hours in an encyclopedia because I’d be looking for one topic, and something in it would make me want to read another, and another, and …

The same thing happens with MOS (My  Oracle Support) – I search for something and the search result throws up a completely irrelvant item that looks much more interesting so I follow a hyperlink, which mentions a couple of other notes, and a couple of hours later I can’t remember what I had started looking for.

Today’s note is a side effect of that process. A comment made yesterday about count(*)/count(1) referenced Oracle bug “19450314: UNNECESSARY INVALIDATIONS IN 12C”, and when I searched MOS for more information on this bug I discovered bug 30404639 : TRIGGER DOES NOT WORK CORRECTLY AFTER ALTER TABLE DROP UNUSED COLUMN. The impact of this bug is easy to demonstrate, and the ramifications are as follows:

Exercise extreme care with the “alter table drop column” command in 18c and above.

The problem is easy to work around, but the impact of not knowing about it could be catastrophic if your pre-production testing wasn’t quite good enough. Here’s a little demonstration script – the bug note says the problem appeared in 18.3 but I ran this test against 19.3. The script is a modified version of the SQL in the bug note:


create table t1 (c0 varchar2(30), c1 varchar2(30), c2 varchar2(30), c3 varchar2(30), c4 varchar2(30));
create table t2 (c_log varchar2(30));

create or replace trigger t1_ariu
after insert or update on t1
for each row
begin
        IF :new.c3 is not null then
                insert into t2 values (:new.c3);
        end if;
end;
/

spool drop_col_bug_18c.lst

insert into t1(c3) values ('Inserting c3 - should log'); 
select * from t2;
 
insert into t1(c4) values ('Inserting c4 - should not log'); 
select * from t2;

prompt  ===================================
prompt  Drop some columns in two steps then
prompt  truncate t2 and repeat the test
prompt  ===================================
 
alter table t1 set unused (c1, c2);
alter table t1 drop unused columns;

truncate table t2;

insert into t1(c3) values ('Inserting c3 - should log'); 
select * from t2;
 
insert into t1(c4) values ('Inserting c4 - should not log'); 
select * from t2;
 

The code is very simple. It creates a couple of tables an “after row” trigger on one of them to copy one column value across to the other table on an insert or update provided the new column value is not null.

To check that the trigger is (at least in part) behaving the code does two inserts – one which should copy a value and one which should not – and we see that the copy takes place as expected.

Now comes the critical part. We mark two of the columns in the table as unused, then drop all unused columns, truncate the second table and repeat the inserts.

If you run the test on 12.2.0.1 then you should find that the second run behaves just like the first run. If you’re running 18c or 19c be prepared for the following:


insert into t1(c3) values ('Inserting c3 - should log')
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [insChkBuffering_1], [4], [4], [], [], [], [], [], [], [], [], []

no rows selected

insert into t1(c4) values ('Inserting c4 - should not log')
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [insChkBuffering_1], [4], [4], [], [], [], [], [], [], [], [], []

This is not good – but it gets worse. If your application starts consistently breaking with an ORA-00600 error that’s going to annoy a lot of users for (we hope) a brief interval, but if your application keeps running and corrupting your data that’s a much bigger problem. Re-run the whole script (dropping the two tables first) but change it to mark just one of the two columns as unused, and you’ll get results for the second pass that look like this:


Table truncated.


1 row created.


no rows selected


1 row created.


C_LOG
------------------------------
Inserting c4 - should not log

1 row selected.

The trigger seems to “lose count” of the columns in the table (presumably it’s compiled to refer to something like “column_position = 3” and doesn’t adjust on the “drop column” – the linked bug notes on MOS refer to the problem being associated with the project to increase fine-grained dependencies) so it manages to survive with one column dropped because there’s still a “column 3” which happens now to be the column that used to be “column 4”.

Workaround

There is a simple workaround if you run into this problem after modifying your production system (and before you’ve corrupted a huge amount of data – recompile the trigger manually immediately after the drop completes: “alter trigger t1_ariu compile”.

Refinement

The problem seems to appear only if the following two conditions are true:

  • you use a two-step approach to dropping a column, viz: set unused then drop. If you simply issue “alter table t1 drop column c1” (with or without a “checkpoint NNN”) then the problem does not appear. It’s a great shame that in the past I’ve given advice that setting columns unused and dropping them later is a better option than doing an immediate drop.
  • you drop columns that appear earlier in the table than the highest position column mentioned in the trigger. But this isn’t something you should gamble on, particularly since the workaround is so easy to implement, because the order the columns appear in the table declaration isn’t necessarily the internal column ordering so you might get it wrong (not that I’ve tried to test for that threat) – and what if there are some selective materialized view logs where you don’t explicitly create triggers and forget to cater for.

I don’t expect anyone to be dropping columns in production systems with any great frequency, and you would expect such a significant operation to be tested quite carefully, but it’s easy to envisage a scenario where the testing might be split into two pieces viz:

  1. test the application on a pre-prod version of the database where a table has been created as a subset of the production data without the column that’s due to be dropped
  2. test how long it takes to execute the actual drop on a (minimal) recovered backup of production, but don’t test the new production code on the resulting table.

Sometimes it’s easy to overlook things that “obviously” won’t need testing, especially when it’s something that has always worked in the past with no special treatment required.

Footnote

If you try running this model on LiveSQL you’ll find that the code stops and the web page reports “Error: Internal Server Error” so you can’t tell that the problem is exactly the same there – but it seems quite likely that it is.

Given how easy it is to bypass the problem I haven’t bothered to do any further research on the issue – is it only related to insert and update trigger, and do they have to be after row for the update, and what about before row delete triggers (with materialized view logs in mind).

 

“Oracle Performance Diagnostics and Tuning” Seminar: Berlin, Germany 31 March – 1 April 2020

  I’m very excited to be running my high acclaimed “Oracle Performance Diagnostics and Tuning” Seminar in Berlin, Germany on 31 March – 1 April 2020 in conjunction with DOUG. If you enjoyed my “Oracle Indexing Internals and Best Practices” Seminar, you will simply love this seminar