Search
Navigation
Top 60 Oracle Blogs
Oakies Blog Aggregator
Group by Elimination
Here’s a bug that was highlighted a couple of days ago on the Oracle Developer Community forum; it may be particularly worth thinking about if if you haven’t yet got up to Oracle 12c as it appeared in an optimizer feature that appeared in 12.2 (and hasn’t been completely fixed) even in the latest release of 19c (currently 19.6).
Oracle introduce “aggregate group by elimination” in 12.2, protected by the hidden parameter “_optimizer_aggr_groupby_elim”. The notes on MOS about the feature tell us that Oracle can eliminate a group by operation from a query block if a unique key from every table in the query block appears in the group by clause. Unfortunately there were a couple of gaps in the implementation in 12.2 that can produce wrong results. Here’s some code to model the problem.
rem
rem Script: group_by_elim_bug.sql
rem Author: Jonathan Lewis
rem Dated: Jan 2020
rem
create table ref_clearing_calendar(
calendar_name char(17),
business_date date,
update_ts timestamp (6) default systimestamp,
constraint pk_ref_clearing_calendar
primary key (business_date)
)
/
insert into ref_clearing_calendar (business_date)
select
sysdate + 10 * rownum
from
all_objects
where
rownum <= 40 -- > comment to avoid wordpress format issue
/
commit;
execute dbms_stats.gather_table_stats(null,'ref_clearing_calendar',cascade=>true)
set autotrace on explain
select
to_char(business_date,'YYYY') , count(*)
from
ref_clearing_calendar
group by
to_char(business_date,'YYYY')
order by
to_char(business_date,'YYYY')
/
set autotrace off
I’ve created a table with a primary key on a date column, and then inserted 40 rows which are spaced every ten days from the current date; this ensures that I will have a few dates in each of two consecutive years (future proofing the example!). Then I’ve aggregated to count the rows per year using the to_char({date column},’YYYY’) conversion option to extract the year from the date. (Side note: the table definition doesn’t follow my normal pattern as the example started life in the ODC thread.)
If you run this query on Oracle 12.2 you will find that it returns 40 (non-unique) rows and displays the following execution plan:
--------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 40 | 320 | 2 (50)| 00:00:01 | | 1 | SORT ORDER BY | | 40 | 320 | 2 (50)| 00:00:01 | | 2 | INDEX FULL SCAN| PK_REF_CLEARING_CALENDAR | 40 | 320 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------
The optimizer has applied “aggregate group by elimination” because it hasn’t detected that the primary key column that appears in the group by clause has been massaged in a way that means the resulting value is no longer unique.
Fortunately this problem with to_char() is fixed in Oracle 18.1 where the query returns two rows using the following execution plan (which I’ve reported from an instance of 19.5):
--------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 40 | 320 | 2 (50)| 00:00:01 | | 1 | SORT GROUP BY | | 40 | 320 | 2 (50)| 00:00:01 | | 2 | INDEX FULL SCAN| PK_REF_CLEARING_CALENDAR | 40 | 320 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------
Unfortunately there is still at least one gap in the implementation. Change the to_char(business_date) to extract(year from business_date) at all three points in the query, and even in 19.6 you’re back to the wrong results – inappropriate aggregate group by elimination and 40 rows returned.
There are a couple of workarounds, one is the hidden parameter _optimizer_aggr_groupby_elim to false at the system or session level, or through an opt_param() hint at the statement level (possibly injected through an SQL_Patch. The other option is to set a fix_control, again at the system, session, or statement level – but there’s seems to be little point in using the fix_control approach (which might be a little obscure for the next developer to see the code) when it seems to do the same as the explicitly named hidden parameter.
select
/*+ opt_param('_optimizer_aggr_groupby_elim','false') */
extract(year from business_date) , count(*)
from ,,,
select
/*+ opt_param('_fix_control','23210039:0') */
extract(year from business_date) , count(*)
from ...
One final thought about this “not quite fixed” bug. It’s the type of “oversight” error that gives you the feeling that there may be other special cases that might have been overlooked. The key question would be: are there any other functions (and not necessarily datetime functions) that might be applied (perhaps implicitly) to a primary or unique key that would produce duplicate results from distinct inputs – if so has the code that checks the validity of eliminating the aggregate operation been written to notice the threat.
Footnote
The problem with extract() has been raised as a bug on MOS, but it was not public at the time of writing this note.
Update (about 60 seconds after publication)
Re-reading my comment about “other functions” it occurred to me that to_nchar() might, or might not, behave the same way as to_char() in 19c – so I tested it … and got the wrong results in 19c.
Announcing SLOB 2.5.2.2
SLOB 2.5.2.2 is available via the SLOB Resources Page.
SLOB 2.5.2.2 is a bug-fix release. After announcing the undocumented Obfuscated Column Data Feature, a few SLOB users reported bugs. The bugs have been fixed in this release.
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, 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! 
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.
On-Line 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 on-line 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 1It 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 on-line 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 on-line 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 on-line 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 Addendum
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 on-line statistics are gathered
- _optimizer_gather_stats_on_load_all=TRUE - so that on-line statistics are collected for every direct-path insert, not just the first one.
Creating a column specific METHOD_OPT statistics preference disables this behaviour.
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.
.png){kind=small}
TweetsPopular content
Today's:
Monthly archive
- January 2020 (42)
- December 2019 (43)
- November 2019 (43)
- October 2019 (61)
- September 2019 (36)
- August 2019 (40)
- July 2019 (45)
- June 2019 (37)
- May 2019 (43)
- April 2019 (43)
- March 2019 (55)
- February 2019 (25)
- January 2019 (35)
- December 2018 (39)
- November 2018 (53)
- October 2018 (69)
- September 2018 (36)
- August 2018 (68)
- July 2018 (58)
- June 2018 (59)
- May 2018 (64)
- April 2018 (40)
- March 2018 (61)
- February 2018 (67)
- January 2018 (57)
- December 2017 (37)
- November 2017 (45)
- October 2017 (57)
- September 2017 (46)
- August 2017 (61)
Recent comments
1 year 51 weeks ago
2 years 11 weeks ago
2 years 15 weeks ago
2 years 16 weeks ago
2 years 20 weeks ago
2 years 41 weeks ago
3 years 10 weeks ago
3 years 39 weeks ago
4 years 24 weeks ago
4 years 24 weeks ago