Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

The holistic SQL tuning series

I did a set of articles for Oracle Magazine on a more holistic view of SQL tuning. What do I mean by “holistic”? It was a reflection of a common problem that I see when questions come into AskTOM, or when people in the community approach me at conferences, namely, there is an inclination to dive straight into the deepest levels of the tuning exercise:

  • “What index should I create?”
  • “Should I increase the parallel degree?”

etc etc. And as technical practitioners, it is an easy trap to fall into. We too often fail to step back and approach the problem from its true requirement – that of, satisfying a business need. We might end up deep in the code, but we should probably not start there.

So I have consolidated the 4-part series here to provide my blueprint for tackling SQL tuning to give the most business success, which ultimately is the goal of the technology solutions we build.

Part 1 – The Business Requirement

https://blogs.oracle.com/oraclemagazine/a-higher-level-perspective-on-sql-tuning

Part 2 – Tuning the User Experience, finding problematic SQL

https://blogs.oracle.com/oraclemagazine/a-higher-level-perspective-on-sql-tuning-part-2

Part 3 – Tuning the SQL statement, the True Execution Plan

https://blogs.oracle.com/oraclemagazine/a-higher-level-perspective-on-sql-tuning-part-3

Part 4 – Common causes for poor performance

https://blogs.oracle.com/oraclemagazine/a-higher-level-perspective-on-sql-tuning-part-4

 

Enjoy!

Oracle memory troubleshooting using analysis on heapdumps

This blogpost is about analysing Oracle heap dumps. It is an extension to earlier work, Tanel Poder’s heap dump analyzer. So hat tip to Tanel, he’s done the hard work, I merely increased the presentation options. The heap analyser script that I wrote analyses Oracle heapdumps from the trace file that the dump was written to by the Oracle database. Because the heap dump representation is the same between PGA and SGA memory, it can work on both. The reason for this is that memory management is done by the same memory manager, and is commonly called ‘kgh’ (kernel generic heap) managed memory.

Please mind that for PGA analysis, not all memory is managed by the kgh memory manager. For example memory used for networking (sqlnet) is allocated totally outside of the kgh memory manager.

Let’s take the output of a PGA heap dump at level 29 (PGA, UGA, CGA, top call heaps, call heaps, session heap; executed via ‘alter session set events ‘immediate trace name heapdump level 29”):

$ ./heap_analyze.awk /u01/app/oracle/diag/rdbms/tt/tt/trace/tt_ora_6515.trc
Heap dump analyzer v0.1 by Frits Hoogland
heap size totals (all sizes in bytes)
==============================================================================================
heap name                       total size
----------------------------------------------------------------------------------------------
top call heap                       393096
callheap                             36200
pga heap                           1016816
top uga heap                        262048
session heap                        261744
------------------------------------------
total                              1969904

top 5 allocation by total size per alloc reason per heap
==============================================================================================
heap             alloc reason            #chunks       total size
----------------------------------------------------------------------------------------------
top call heap                                  5           354456
top call heap    callheap                     11            37616
top call heap    perm                          2             1024
heap             alloc reason            #chunks       total size
----------------------------------------------------------------------------------------------
callheap                                      10            35920
callheap         ctxtcds: kksQui               1              160
callheap         perm                          1              120
heap             alloc reason            #chunks       total size
----------------------------------------------------------------------------------------------
pga heap         perm                         44           290256
pga heap         Fixed UGA heap                1           226232
pga heap         diag pga                     21           214048
pga heap                                       7            62616
pga heap         kfkio bucket                  1            40984
heap             alloc reason            #chunks       total size
----------------------------------------------------------------------------------------------
top uga heap     session heap                  4           261928
top uga heap     perm                          1              120
heap             alloc reason            #chunks       total size
----------------------------------------------------------------------------------------------
session heap     perm                          3            63608
session heap                                   8            63528
session heap     koh-kghu sessi                5            28288
session heap     kxsFrame4kPage                6            24912
session heap     kxsFrame8kPage                3            24744

top 5 allocations by total size per alloc reason, type, chunk size per heap
==============================================================================================
heap             alloc reason            #chunks       type       chunk size       total size
----------------------------------------------------------------------------------------------
top call heap                                  1       free           131048           131048
top call heap                                  2       free            65512           131024
top call heap                                  1       free            63496            63496
top call heap                                  1       free            28888            28888
top call heap    callheap                      6   freeable             4224            25344
heap             alloc reason            #chunks       type       chunk size       total size
----------------------------------------------------------------------------------------------
callheap                                       6       free             4184            25104
callheap                                       2       free             4192             8384
callheap                                       1       free             1784             1784
callheap                                       1       free              648              648
callheap         ctxtcds: kksQui               1   freeable              160              160
heap             alloc reason            #chunks       type       chunk size       total size
----------------------------------------------------------------------------------------------
pga heap         Fixed UGA heap                1   recreate           226232           226232
pga heap         perm                          1       perm            71720            71720
pga heap         diag pga                      1   freeable            55048            55048
pga heap         diag pga                      2   freeable            27456            54912
pga heap                                       1       free            47296            47296
heap             alloc reason            #chunks       type       chunk size       total size
----------------------------------------------------------------------------------------------
session heap     perm                          1       perm            43584            43584
session heap                                   1       free            36544            36544
session heap     kxsFrame4kPage                6   freeable             4152            24912
session heap     kxsFrame8kPage                3   freeable             8248            24744
session heap                                   1       free            20352            20352
heap             alloc reason            #chunks       type       chunk size       total size
----------------------------------------------------------------------------------------------
top uga heap     session heap                  3   freeable            65512           196536
top uga heap     session heap                  1   recreate            65392            65392
top uga heap     perm                          1       perm              120              120

top 5 allocations by total size per heap, alloc reason, type, chunk size
==============================================================================================
heap             alloc reason            #chunks       type       chunk size       total size
----------------------------------------------------------------------------------------------
pga heap         Fixed UGA heap                1   recreate           226232           226232
top uga heap     session heap                  3   freeable            65512           196536
top call heap                                  1       free           131048           131048
top call heap                                  2       free            65512           131024
pga heap         perm                          1       perm            71720            71720

I figured that the first thing you want to see, are the heaps that are in the dump, and their sizes. That’s what is visible in rows 2-12.
I summed the heap sizes, which might make sense, or it might not.
In this case, with a heap dump that includes PGA, UGA and CGA plus session and call heaps, it means there are heaps in the dump that are part of another heap that is in the dump. So the total size here is bogus. This means that you need to have an understanding of what is actually dumped.

The next section, top 5 allocations by total size per alloc reason per dump, shows a per-heap summary by the allocation reason in the dump. If there’s no alloc reason, it’s free memory. Because this is a heap dump of a session that has done nothing (I just started sqlplus and ran the dump of its own PGA memory), you see that a lot of memory chunks are free memory. If you look closely to the allocation reasons, you can see that the ‘top call heap’ has a memory section that is called ‘callheap’ which is slightly larger than the ‘callheap’ section in the heap totals, and the ‘top uga heap’ section has a memory section called ‘session heap’ that is slightly larger than the ‘session heap’ section in the heap totals. In this case, it means that you can actually see the subheads in the parent heap allocation totals. The subheap size must be slightly larger in the parent heap because of headers in the memory allocations which are needed memory management. Please mind that this is based on my knowledge of how process memory is created, the only way to be absolutely sure is that a heap is part of another heap is to look at the memory addresses. This output only shows the heap names, not the addresses. The purpose of this section is to have an understanding of where memory is allocated to in a heap.

The following section, top 5 allocations by total size per alloc reason, type, chunk size per heap shows a per-heap summary by reason, type and chunksize, so you can investigate if specific types and sizes of chunks are causing issues or weird behaviour.

The last section is the same as the previous section, but doesn’t do it per heap. This is identical to what Tanel’s heapdump_analyzer shows.

You can find the heap_analyze.awk script here: https://gitlab.com/FritsHoogland/oracle_memory_analyze/blob/master/heap_analyze.awk

This is the output of a dump of the SGA of my small test database (oradebug dump heapdump 2):

$ ./heap_analyze.awk /u01/app/oracle/diag/rdbms/tt/tt/trace/tt_ora_9461.trc
Heap dump analyzer v0.1 by Frits Hoogland
heap size totals (all sizes in bytes)
==============================================================================================
heap name                       total size
----------------------------------------------------------------------------------------------
sga heap(1,0)                    285210904
sga heap(1,3)                     83885560
------------------------------------------
total                            369096464

top 5 allocation by total size per alloc reason per heap
==============================================================================================
heap             alloc reason            #chunks       total size
----------------------------------------------------------------------------------------------
sga heap(1,0)    perm                         22        188068904
sga heap(1,0)                                141         17975320
sga heap(1,0)    SO private sga               17         14268008
sga heap(1,0)    KQR PO                     8271          7568912
sga heap(1,0)    KGLHD                      9381          7023960
heap             alloc reason            #chunks       total size
----------------------------------------------------------------------------------------------
sga heap(1,3)                                 51         14120288
sga heap(1,3)    SQLA^6d9b8a7e               337          1380352
sga heap(1,3)    SQLA^31cc505b               167           684032
sga heap(1,3)    SQLA^aab93e92               162           663552
sga heap(1,3)    PLDIA^191e0a8d              155           634880

top 5 allocations by total size per alloc reason, type, chunk size per heap
==============================================================================================
heap             alloc reason            #chunks       type       chunk size       total size
----------------------------------------------------------------------------------------------
sga heap(1,0)    perm                          1       perm         15937496         15937496
sga heap(1,0)    perm                          1       perm         15931312         15931312
sga heap(1,0)    perm                          1       perm         15811464         15811464
sga heap(1,0)    perm                          1       perm         15741952         15741952
sga heap(1,0)    perm                          1       perm         15723584         15723584
heap             alloc reason            #chunks       type       chunk size       total size
----------------------------------------------------------------------------------------------
sga heap(1,3)                                  1       free          9883208          9883208
sga heap(1,3)                                  4     R-free           839480          3357920
sga heap(1,3)    SQLA^6d9b8a7e               336  freeableU             4096          1376256
sga heap(1,3)                                  1     R-free           839360           839360
sga heap(1,3)    SQLA^31cc505b               166  freeableU             4096           679936

top 5 allocations by total size per heap, alloc reason, type, chunk size
==============================================================================================
heap             alloc reason            #chunks       type       chunk size       total size
----------------------------------------------------------------------------------------------
sga heap(1,0)    perm                          1       perm         15937496         15937496
sga heap(1,0)    perm                          1       perm         15931312         15931312
sga heap(1,0)    perm                          1       perm         15811464         15811464
sga heap(1,0)    perm                          1       perm         15741952         15741952
sga heap(1,0)    perm                          1       perm         15723584         15723584

It’s interesting to see only subpool 1 sub-sub pool 1 and 3 are used. Subpool 1,1 contains a lot of permanent allocations and a lot of allocations that might have a more permanent nature, like KQR (dictionary cache) allocations, Subpool 1,3 seems to have allocations that are deemed more transient in nature, like SQLA (sql area) allocations. This might be wildly different in databases that are actually heavily used, this is an idle lab database.

Please mind it’s important to understand that dumping the shared pool requires obtaining the respective latches, so doing that on a live production system might lead to (severe) issues. Only do this if you know what you are doing. For the PGA there can only be one using process by definition, but be careful there too, if you request a PGA dump you are interacting with memory that is deemed private by the process that is using that.

If you require more rows to be shown than the 5 that are shown, set the ‘group_sum_nr’ variable to the amount you need on row 4 of the script.

Clustering_Factor

A few days ago I published a little note of a script I wrote some time ago to estimate the clustering_factor of an index before it had been built. At the time I pointed out that one of its limitations was that it would not handle cases where you were planning to set the table_cached_blocks preference, but a couple of days later I decided that I’d write another version of the code that would cater for the new feature – and that’s how I made an embarrassing discovery.

Having reviewed a number of notes I’ve published about the table_cached_blocks preference and its impact on the clustering_factor I’ve realised the what I’ve written has always been open to two interpretations – the one that I had in mind as I was writing, and the correct one.  I made this discovery because I had written a simple SQL statement – using the match_recognize() mechanism – to do what I considered to  be the appropriate calculation. After testing the query with a few sets of sample data that produced the correct results I emailed Stew Ashton (my “go-to” person for match_recognize() questions) asking if he would do a sanity check on the code because it was rather slow and I wondered if there was a better way of writing it.

His reply was roughly:

“I’ve read the notes you and Richard Foote have written about the clustering_factor and table_cached_blocks, and this isn’t doing what your description says it should.”

Then he explained what he had inferred from what I had written … and it made more sense than what I had been thinking when I wrote it. He also supplied some code to implement his interpretation – so I designed a couple of data models that would produce the wrong prediction for whichever piece of code implemented the wrong interpretation. His code gave the right answers, mine didn’t.

So here’s the difference in interpretation – the wrong one first – using 16 as a discussion value for the table_cached_blocks:

  • WRONG interpretation:  As you walk through index entries in order remember the last 16 rowids (that’s rowid for the rows in the table that the index is pointing to) you’ve seen. If the current rowid has a block id component that doesn’t match the block id from one of the remembered 16 rowids then increment the counter for the clustering_factor.
    • The simplicity of this algorithm means you can fix a “circular” array of 16 entries and keep walking around the circle overwriting the oldest entry each time you read a new one. It’s a pity that it’s the wrong idea because there’s a simple (though massively CPU -intensive match_recognize() strategy for implementing it – and if you were using an internal library mechanism during a proper gather_index_stats() it could be incredibly efficient.
  • RIGHT interpretation: set up an array for 16 block ids, each with an associated “row-number”. Walk through the index in order – giving each entry a row-number as you go. Extract the block id from the current entry and search through the array for a matching block id.  If you find a match then update its entry with the current row-number (so you can remembr how recently you saw the block id); if you don’t find a match then replace the entry that has the smallest (i.e. greatest distance into the past) row-number with the current block id and row-number and increment the counter for the clustering_factor.

The first piece of code that Stew Ashton sent me was an anonymous PL/SQL block that included some hard-coded fragments and embedded SQL to use a test table and index that I had defined, but he then sent a second piece of code that creates a generic function that uses dynamic SQL to construct a query against a table and an index definition that you want to test. The latter is the code I’ve published (with permission) below:


create or replace function predict_clustering_factor(
/*
Function to predict the clustering factor of an index,
taking into account the intended value of
the TABLE_CACHED_BLOCKS parameter of DBMS_STATS.SET_TABLE_PREFS.

Input is the table name, the list of column names
and the intended value of TABLE_CACHED_BLOCKS.

The function collects the last N block ids (not the last N entries).
When there is no more room, it increments the clustering factor
and replaces the least recently used block id with the current one.

Note: here a "block id" is a rowid with the row_number portion set to 0.
It is effectively a "truncated" rowid.
*/
  p_table_name in varchar2,
  p_column_list in varchar2,
  p_table_cached_blocks in number
) return number authid current_user is

  rc sys_refcursor;
  type tt_rids is table of rowid;
  lt_rids tt_rids;
  
  type t_block_list is record(
    rid rowid,
    last_hit number
  );

  type tt_block_list is table of t_block_list;
  lt_block_list tt_block_list := new tt_block_list();

  l_rid rowid;
  l_clustering_factor number := 0;
  b_block_found boolean;
  l_rn number := 0;
  l_oldest_hit number;
  i_oldest_hit binary_integer := 0;
  
  function truncated_rid(p_rid in rowid) return rowid is
    rowid_type number;
    object_number NUMBER;
    relative_fno NUMBER;
    block_number NUMBER;
    row_number NUMBER;
    rid rowid;

  begin

    DBMS_ROWID.ROWID_INFO (
      p_rid,
      rowid_type,
      object_number,
      relative_fno,
      block_number,
      row_number
    );

    rid := DBMS_ROWID.ROWID_CREATE (
      rowid_type,
      object_number,
      relative_fno,
      block_number,
      0
    );

    return rid;

  end truncated_rid;
  
begin
  if p_table_cached_blocks != trunc(p_table_cached_blocks)
  or p_table_cached_blocks not between 1 and 255 then
    raise_application_error(
      -20001, 
      'input parameter p_table_cached_blocks must be an integer between 1 and 255'
    );
  end if;

  open rc for 'select rowid from '||p_table_name||' order by '||p_column_list||', rowid';
  loop
    fetch rc bulk collect into lt_rids limit 1000;

    for irid in 1..lt_rids.count loop
      l_rn := l_rn + 1;
      l_rid := truncated_rid(lt_rids(irid));
      b_block_found := false;
      l_oldest_hit := l_rn;

      if l_rn = 1 then
        l_clustering_factor := l_clustering_factor + 1;
        lt_block_list.extend;
        lt_block_list(1).rid := l_rid;
        lt_block_list(1).last_hit := l_rn;

      else

        for i in 1..lt_block_list.count loop
          if l_oldest_hit > lt_block_list(i).last_hit then
            l_oldest_hit := lt_block_list(i).last_hit;
            i_oldest_hit := i;
          end if;
          if lt_block_list(i).rid = l_rid then
            b_block_found := true;
            lt_block_list(i).last_hit := l_rn;
            exit;
          end if;
        end loop;

        if not b_block_found then
          l_clustering_factor := l_clustering_factor + 1;
          if lt_block_list.count < p_table_cached_blocks then
            lt_block_list.extend;
            lt_block_list(lt_block_list.count).rid := l_rid;
            lt_block_list(lt_block_list.count).last_hit := l_rn; 
          else         
            lt_block_list(i_oldest_hit).rid := l_rid;
            lt_block_list(i_oldest_hit).last_hit := l_rn;
          end if;
        end if;

      end if;

    end loop;
    exit when rc%notfound;
  end loop;

  close rc;
  return l_clustering_factor;

exception when others then
  if rc%isopen then
    close rc;
  end if;
  raise;

end predict_clustering_factor;
/

After executing the above to create the function, here’s an example of usage:

rem
rem     Script:         clustering_factor_est_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Oct 2019
rem
rem     Last tested
rem             19.3.0.0
rem             12.2.0.1
rem

create table t1
as
with generator as (
        select
                rownum id
        from dual
        connect by
                level <= 1e4 -- > comment to avoid WordPress format issue
)
select
        rownum                                  id,
        cast(rownum as varchar2(10))            v1,
        trunc(dbms_random.value(0,10000))       rand,
        rpad('x',100,'x')                       padding
from
        generator       v1,
        generator       v2
where
        rownum <= 1e6 -- > comment to avoid WordPress format issue
/

-- -------------------------------------------------------------------

SQL> execute dbms_output.put_line('Predicted cf for t1(rand, id): ' || predict_clustering_factor('t1','rand, id',16))
Predicted cf for t1(rand, id): 997218
Elapsed: 00:00:07.54

SQL> execute dbms_output.put_line('Predicted cf for t1(rand, id): ' || predict_clustering_factor('t1','rand, id',255))
Predicted cf for t1(rand, id): 985607
Elapsed: 00:00:50.61

You’ll notice that the larger the setting for the “table_cached_blocks” parameter the more time it takes to predict the clustering_factor – and it was all CPU time in my example. This isn;t surprising given the need to search through an array holding the previous history. In this example the table t1 holds 1,000,000 rows, and the number and scatter of distinct values is so arranged that the code will hardly ever find a cached block id – essentially it’s the sort of index that isn’t going to cause much of confusion to the optimizer and isn’t likely to need special attention to make the optimizer use it when it should and ignore it when it’s inappropriate.

Finally a cut-n-paste to show the accuracy of the two predictions:

SQL> create index t1_i on t1(rand, id);
Elapsed: 00:00:02.96

SQL> execute dbms_stats.set_table_prefs(null,'t1','table_cached_blocks',16)
Elapsed: 00:00:00.01

SQL> execute dbms_stats.gather_index_stats(null,'t1_i')
Elapsed: 00:00:09.55

SQL> select clustering_factor from user_indexes where index_name = 'T1_I';

CLUSTERING_FACTOR
-----------------
           997218

Elapsed: 00:00:00.11

SQL> execute dbms_stats.set_table_prefs(null,'t1','table_cached_blocks',255)
Elapsed: 00:00:00.01

SQL> execute dbms_stats.gather_index_stats(null,'t1_i')
Elapsed: 00:00:07.80

SQL> select clustering_factor from user_indexes where index_name = 'T1_I';

CLUSTERING_FACTOR
-----------------
           985607

Elapsed: 00:00:00.00

Both match perfectly – but you might notice that creating the index and gathering the stats was much faster than predicting the clustering factor for the case where we set table_cached_blocks = 255.

(If you’re wondering, my “simple but irrelevant” match_recognize() query took 370 CPU second to complete for table_cached_blocks = 200 – and a limit on march_recognize() meant that 200 was the maximum value I was allowed to use – so now you know why I emailed Stew Ashton (and just for lagniappe. he also told me about a simple workaround for the 200 limit)).

 

 

October 2nd, #SQLSatOregon in Portland and the #SQLTrain!

I’m still not sure where October went….well, actually I do-  it was a flurry of travel, writing, (but not blogging!) and working with customers on a ton of new projects for the next quarter.  With the end of October, comes the preparation for PASS Summit.  Last year, I had to pass on the SQL Train, but this year?

SQL Train is the annual pilgrimage from SQL Saturday Oregon, in Portland, to PASS Summit in Seattle via Amtrak train.  Chris Hyde is the poor, tortured soul responsible for organizing this, but I hear that if you say you’ll volunteer, it’s like Hotel California-  you can check in, but you can never check out!

I will be speaking at SQL Saturday Oregon, talking automation and DevOps, before heading to Seattle on the train.  This event always has a huge turnout of fantastic speakers, getting an early prep on for Summit.  We commonly stay at hotels close to each other, if not at the same one and then head to Portland’s Union Station to take the train to Seattle on Sunday.  I believe we have two cars booked for the trip and two cars of hyped up on caffeine, techie geeks should be fun!

I have a few sessions at PASS Summit this year, both of the sessions will be part of the new Learning Pathways, (multiple sessions, leading to deeper learning vs. higher level, single sessions.)  I’ll blog more about that later this next week in preparation, but just be prepared-  any attendees should consider bringing their laptop with access to a Linux server to make the most of the session, (or a Linux docker container or Azure BASH cloud shell…some kind of terminal access to a Linux environment!)

Have a great week!

 



Tags:  ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright ©  [October 2nd, #SQLSatOregon in Portland and the #SQLTrain!], All Right Reserved. 2019.

ANSI Plans

Here’s a thought that falls somewhere between philosophical and pragmatic. It came up while I was playing around with a problem from the Oracle database forum that was asking about options for rewriting a query with a certain type of predicate. This note isn’t really about that question but the OP supplied a convenient script to demonstrate their requirement and I’ve hi-jacked most of the code for my own purposes so that I can ask the question:

Should the presence of an intermediate view name generated by the optimizer in the course of cost-based query transformation cause two plans, which are otherwise identical and do exactly the same thing, to have different plan hash values ?

To demonstrate the issue let’s start with a simple script to create some data and generate an execution plan.


rem
rem     Script:         or_expand_plans.sql
rem     Author:         Jonathan Lewis
rem     Dated           Oct 2019
rem
rem     Last tested
rem             19.3.0.0
rem             12.2.0.1
rem
rem     Notes:
rem     Data creation copied from ODC
rem     https://community.oracle.com/thread/4297365
rem

create table t as   
select 1 as id, to_date('2019-10-11', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual  
union all  
select 2 as id, to_date('2019-10-12', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual  
union all  
select 3 as id, to_date('2019-10-15', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual  
union all  
select 4 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual  
;  

create table t_others as  
select 1 as id, to_date('2019-10-12', 'YYYY-MM-DD') as lastupdated, 'Blue'  as color, 'Zone 7' as zoneid from dual  
union all  
select 2 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'Red'   as color, 'Zone 7' as zoneid from dual  
union all  
select 3 as id, to_date('2019-10-16', 'YYYY-MM-DD') as lastupdated, 'White' as color, 'Zone 7' as zoneid from dual  
union all  
select 4 as id, to_date('2019-10-17', 'YYYY-MM-DD') as lastupdated, 'Green' as color, 'Zone 7' as zoneid from dual  
;  

create table t_further_info as  
select 1 as id, to_date('2019-10-12', 'YYYY-MM-DD') as lastupdated, 'A' as typeinfo from dual  
union all   
select 2 as id, to_date('2019-10-14', 'YYYY-MM-DD') as lastupdated, 'C' as typeinfo from dual  
union all  
select 3 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'D' as typeinfo from dual  
union all  
select 4 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'E' as typeinfo from dual  
;  

prompt  ====================
prompt  "Traditional" syntax
prompt  ====================

explain plan for
select 
        /*+ or_expand */ 
        * 
from
        t,
        t_others        pt,
        t_further_info  fi  
/*
where   (
             t.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')   
         or pt.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')  
         or fi.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')  
        )  
*/
where   to_date('2019-10-21', 'YYYY-MM-DD') <= any(t.lastupdated, pt.lastupdated, fi.lastupdated)   
and     pt.id = t.id  
and     fi.id = t.id  
;

select * from table(dbms_xplan.display(null,null,'outline'));


You’ll see that I have a simple three-table join with the nasty little detail that I have a “non-join” predicates that may require Oracle to check across all three tables before it can decide whether or not a row should be discarded. I’ve shown two variants on a theme – they both have exactly the same effect but the ANY() presentation is just a little bit neater and more compact.

Essentially Oracle can use one of two strategies for this type of query/predicate; the first is to join all three tables and wait until the final join rowsource appears and then apply the check, or it can split the query into a union all of three separate queries where each query drives off a different table selecting only the rows from that table that match “its” part of the predicate.

In the latter case the second and third branches of the union all have to be modified to ensure that they discard any rows already returned by preceding parts of the union all; this can mean lots of new predicates appearing that use the lnnvl() function. (Of course there are variants between these two extremes, but the general principle is unchanged.)

The presence of the (incomplete) /*+ or_expand */ hint in my query is there to tell the optimizer that it should attempt to transform the query into the three-part union all. This, by the way, is a 12c feature, though older versions of Oracle could get similar effects in some cases from the /*+ use_concat */ hint. Here’s the plan, with outline,  I got from 12.2.0.1:


Plan hash value: 3181357500

----------------------------------------------------------------------------------------------
| Id  | Operation               | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                    |     3 |   270 |    17   (6)| 00:00:01 |
|   1 |  VIEW                   | VW_ORE_67EF6547    |     3 |   270 |    17   (6)| 00:00:01 |
|   2 |   UNION-ALL             |                    |       |       |            |          |
|*  3 |    HASH JOIN            |                    |     1 |    55 |     6   (0)| 00:00:01 |
|   4 |     MERGE JOIN CARTESIAN|                    |     4 |   144 |     4   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL  | T_FURTHER_INFO     |     1 |    13 |     2   (0)| 00:00:01 |
|   6 |      BUFFER SORT        |                    |     3 |    69 |     2   (0)| 00:00:01 |
|*  7 |       TABLE ACCESS FULL | T_OTHERS           |     3 |    69 |     2   (0)| 00:00:01 |
|*  8 |     TABLE ACCESS FULL   | T                  |     3 |    57 |     2   (0)| 00:00:01 |
|*  9 |    HASH JOIN            |                    |     2 |   182 |    11  (10)| 00:00:01 |
|  10 |     VIEW                | VW_JF_SET$FB5125FC |     2 |   156 |     9  (12)| 00:00:01 |
|  11 |      UNION-ALL          |                    |       |       |            |          |
|* 12 |       HASH JOIN         |                    |     1 |    42 |     4   (0)| 00:00:01 |
|* 13 |        TABLE ACCESS FULL| T_OTHERS           |     1 |    23 |     2   (0)| 00:00:01 |
|* 14 |        TABLE ACCESS FULL| T                  |     3 |    57 |     2   (0)| 00:00:01 |
|* 15 |       HASH JOIN         |                    |     1 |    42 |     4   (0)| 00:00:01 |
|* 16 |        TABLE ACCESS FULL| T                  |     1 |    19 |     2   (0)| 00:00:01 |
|  17 |        TABLE ACCESS FULL| T_OTHERS           |     4 |    92 |     2   (0)| 00:00:01 |
|  18 |     TABLE ACCESS FULL   | T_FURTHER_INFO     |     4 |    52 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$7C4216F7" "PT"@"SEL$1")
      LEADING(@"SEL$7C4216F7" "T"@"SEL$1" "PT"@"SEL$1")
      FULL(@"SEL$7C4216F7" "PT"@"SEL$1")
      FULL(@"SEL$7C4216F7" "T"@"SEL$1")
      USE_HASH(@"SEL$A4A33BE0" "T"@"SEL$1")
      LEADING(@"SEL$A4A33BE0" "PT"@"SEL$1" "T"@"SEL$1")
      FULL(@"SEL$A4A33BE0" "T"@"SEL$1")
      FULL(@"SEL$A4A33BE0" "PT"@"SEL$1")
      USE_HASH(@"SET$49E1C21B_3" "T"@"SEL$1")
      USE_MERGE_CARTESIAN(@"SET$49E1C21B_3" "PT"@"SEL$1")
      LEADING(@"SET$49E1C21B_3" "FI"@"SEL$1" "PT"@"SEL$1" "T"@"SEL$1")
      FULL(@"SET$49E1C21B_3" "T"@"SEL$1")
      FULL(@"SET$49E1C21B_3" "PT"@"SEL$1")
      FULL(@"SET$49E1C21B_3" "FI"@"SEL$1")
      USE_HASH(@"SEL$5FCD2D3C" "FI"@"SEL$1")
      LEADING(@"SEL$5FCD2D3C" "VW_JF_SET$FB5125FC"@"SEL$81DF0931" "FI"@"SEL$1")
      FULL(@"SEL$5FCD2D3C" "FI"@"SEL$1")
      NO_ACCESS(@"SEL$5FCD2D3C" "VW_JF_SET$FB5125FC"@"SEL$81DF0931")
      NO_ACCESS(@"SEL$67EF6547" "VW_ORE_67EF6547"@"SEL$67EF6547")
      OUTLINE(@"SET$49E1C21B_2")
      OUTLINE(@"SET$49E1C21B_1")
      OUTLINE(@"SEL$1")
      FACTORIZE_JOIN(@"SET$49E1C21B"("FI"@"SET$49E1C21B_2" "FI"@"SET$49E1C21B_1"))
      OUTLINE(@"SET$0E101D56")
      OUTLINE(@"SEL$81DF0931")
      OUTLINE(@"SEL$5AB42CD1")
      OR_EXPAND(@"SEL$1" (1) (2) (3))
      OUTLINE(@"SET$49E1C21B")
      OUTLINE_LEAF(@"SEL$67EF6547")
      FACTORIZE_JOIN(@"SET$49E1C21B"("FI"@"SET$49E1C21B_2" "FI"@"SET$49E1C21B_1"))
      OUTLINE_LEAF(@"SET$0E101D56")
      OUTLINE_LEAF(@"SEL$5FCD2D3C")
      OUTLINE_LEAF(@"SET$FB5125FC")
      OUTLINE_LEAF(@"SEL$A4A33BE0")
      OUTLINE_LEAF(@"SEL$7C4216F7")
      OUTLINE_LEAF(@"SET$49E1C21B_3")
      ALL_ROWS
      DB_VERSION('12.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("PT"."ID"="T"."ID" AND "FI"."ID"="T"."ID")
   5 - filter("FI"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
   7 - filter(LNNVL("PT"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss')))
   8 - filter(LNNVL("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss')))
   9 - access("FI"."ID"="ITEM_1")
  12 - access("PT"."ID"="T"."ID")
  13 - filter("PT"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
  14 - filter(LNNVL("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss')))
  15 - access("PT"."ID"="T"."ID")
  16 - filter("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))


This is a wonderful demonstration of how brilliant the optimizer can be.  The query has gone through several transformations and two of them have very high visibility. First, you can see the name VW_ORE_67EF6547 at operation 1. This is a view name that Oracle generates to express (cost-based) OR-Expansion” so the optimizer has clearly obeyed my hint. As a consequence of OR-expansion we can also see several examples of the lnnvl() function appearing in the Predicate Information section of the output.; we can also see the hint re-appearing in the completed form of OR_EXPAND(@”SEL$1″ (1) (2) (3)) in the Outline Data.

However, we don’t have the union all of three pieces that we might have expected; we have a union all of two pieces and the second piece is a hash join between the table t_further_info and a view called VW_JF_SET$FB5125FC. This view is the result of “join factorization”. The optimizer has taken the 2nd and 3rd sections of our union all view and decided that it would be cost-effective to “factor out” a common table, so this:

select from t_others, t, t_further_info ... where t_others.date_predicate ...
union all
select from t, t_others, t_further_info ... where t.date_predicate ...

changes to this:

select from 
        (
        select from t_others, t   ... where t_others.date_predicate ...
        union all
        select from t, t_others   ... where t.date_predicate ...
        ),
        t_further_info 
where   ...

Having said all that, I now have to change the code because the original query was written using “ANSI” style joins – like this:


explain plan for
select
        /*+ or_expand */
        * 
from
        t   
inner join 
        t_others       pt  
on      pt.id = t.id  
inner join 
        t_further_info fi  
on      fi.id = t.id  
where
        (
             t.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')   
         or pt.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')  
         or fi.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')  
        )
;

select  * from table(dbms_xplan.display(null,null,'outline'));


In the ANSI example I happen to have used the explicit “OR” list for the date predicates but that’s not really signficant . Here’s the plan produced by this query – and the first thing I’d like you to note is the Plan hash value:

Plan hash value: 3309788271

----------------------------------------------------------------------------------------------
| Id  | Operation               | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                    |     3 |   270 |    17   (6)| 00:00:01 |
|   1 |  VIEW                   | VW_ORE_31069B60    |     3 |   270 |    17   (6)| 00:00:01 |
|   2 |   UNION-ALL             |                    |       |       |            |          |
|*  3 |    HASH JOIN            |                    |     1 |    55 |     6   (0)| 00:00:01 |
|   4 |     MERGE JOIN CARTESIAN|                    |     4 |   144 |     4   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL  | T_FURTHER_INFO     |     1 |    13 |     2   (0)| 00:00:01 |
|   6 |      BUFFER SORT        |                    |     3 |    69 |     2   (0)| 00:00:01 |
|*  7 |       TABLE ACCESS FULL | T_OTHERS           |     3 |    69 |     2   (0)| 00:00:01 |
|*  8 |     TABLE ACCESS FULL   | T                  |     3 |    57 |     2   (0)| 00:00:01 |
|*  9 |    HASH JOIN            |                    |     2 |   182 |    11  (10)| 00:00:01 |
|  10 |     VIEW                | VW_JF_SET$997549B1 |     2 |   156 |     9  (12)| 00:00:01 |
|  11 |      UNION-ALL          |                    |       |       |            |          |
|* 12 |       HASH JOIN         |                    |     1 |    42 |     4   (0)| 00:00:01 |
|* 13 |        TABLE ACCESS FULL| T_OTHERS           |     1 |    23 |     2   (0)| 00:00:01 |
|* 14 |        TABLE ACCESS FULL| T                  |     3 |    57 |     2   (0)| 00:00:01 |
|* 15 |       HASH JOIN         |                    |     1 |    42 |     4   (0)| 00:00:01 |
|* 16 |        TABLE ACCESS FULL| T                  |     1 |    19 |     2   (0)| 00:00:01 |
|  17 |        TABLE ACCESS FULL| T_OTHERS           |     4 |    92 |     2   (0)| 00:00:01 |
|  18 |     TABLE ACCESS FULL   | T_FURTHER_INFO     |     4 |    52 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$D12FC97A" "PT"@"SEL$1")
      LEADING(@"SEL$D12FC97A" "T"@"SEL$1" "PT"@"SEL$1")
      FULL(@"SEL$D12FC97A" "PT"@"SEL$1")
      FULL(@"SEL$D12FC97A" "T"@"SEL$1")
      USE_HASH(@"SEL$09C9729D" "T"@"SEL$1")
      LEADING(@"SEL$09C9729D" "PT"@"SEL$1" "T"@"SEL$1")
      FULL(@"SEL$09C9729D" "T"@"SEL$1")
      FULL(@"SEL$09C9729D" "PT"@"SEL$1")
      USE_HASH(@"SET$E8D85587_3" "T"@"SEL$1")
      USE_MERGE_CARTESIAN(@"SET$E8D85587_3" "PT"@"SEL$1")
      LEADING(@"SET$E8D85587_3" "FI"@"SEL$2" "PT"@"SEL$1" "T"@"SEL$1")
      FULL(@"SET$E8D85587_3" "T"@"SEL$1")
      FULL(@"SET$E8D85587_3" "PT"@"SEL$1")
      FULL(@"SET$E8D85587_3" "FI"@"SEL$2")
      USE_HASH(@"SEL$95B99BAF" "FI"@"SEL$2")
      LEADING(@"SEL$95B99BAF" "VW_JF_SET$997549B1"@"SEL$BB7F1ECF" "FI"@"SEL$2")
      FULL(@"SEL$95B99BAF" "FI"@"SEL$2")
      NO_ACCESS(@"SEL$95B99BAF" "VW_JF_SET$997549B1"@"SEL$BB7F1ECF")
      NO_ACCESS(@"SEL$31069B60" "VW_ORE_31069B60"@"SEL$31069B60")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$1" >"SEL$2")
      OUTLINE(@"SEL$58A6D7F6")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SET$E8D85587_2")
      OUTLINE(@"SET$E8D85587_1")
      MERGE(@"SEL$58A6D7F6" >"SEL$3")
      OUTLINE(@"SEL$9E43CB6E")
      FACTORIZE_JOIN(@"SET$E8D85587"("FI"@"SET$E8D85587_2" "FI"@"SET$E8D85587_1"))
      OUTLINE(@"SET$6117B24C")
      OUTLINE(@"SEL$BB7F1ECF")
      OUTLINE(@"SEL$344003E3")
      OR_EXPAND(@"SEL$9E43CB6E" (1) (2) (3))
      OUTLINE(@"SET$E8D85587")
      OUTLINE_LEAF(@"SEL$31069B60")
      FACTORIZE_JOIN(@"SET$E8D85587"("FI"@"SET$E8D85587_2" "FI"@"SET$E8D85587_1"))
      OUTLINE_LEAF(@"SET$6117B24C")
      OUTLINE_LEAF(@"SEL$95B99BAF")
      OUTLINE_LEAF(@"SET$997549B1")
      OUTLINE_LEAF(@"SEL$09C9729D")
      OUTLINE_LEAF(@"SEL$D12FC97A")
      OUTLINE_LEAF(@"SET$E8D85587_3")
      ALL_ROWS
      DB_VERSION('12.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("FI"."ID"="T"."ID" AND "PT"."ID"="T"."ID")
   5 - filter("FI"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
   7 - filter(LNNVL("PT"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss')))
   8 - filter(LNNVL("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss')))
   9 - access("FI"."ID"="ITEM_1")
  12 - access("PT"."ID"="T"."ID")
  13 - filter("PT"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
  14 - filter(LNNVL("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss')))
  15 - access("PT"."ID"="T"."ID")
  16 - filter("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))


Is the plan for the “ANSI” version of the query the same as the plan for the “traditional” version? How carefully have you checked – after simply noting that the two Plan hash values were different.

The plans are the same – in that the mechanics are exactly the same and that ought to be the criterion on which we should judge them. But the hash values are different because of the change from traditional to ANSI syntax. The traditional form of the query starts life with a single query block while the ANSI form is considered to be two query blocks, so the initial ANSI query goes through three stages:


1) select from t1 join t2 join t3

2) select /*+ qb_name(sel$2) */ from (select /* qb_name(sel$1) */ from t1, t2), t3

3) select /*+ qb_name(sel$9E43CB6E) */ from t1, t2, t3

So the query is rapidly transformed to the equivalent traditional syntax but we  now have a query block name of SEL$9E43CB6E instead of SEL$1 that the traditional query (in the absence of a /*+ qb_name() */ hint would have had. This is why you see the difference in the two or_expand() hints in the Outline Data section. One reads: OR_EXPAND(@SEL$1 (1) (2) (3)), the other reads OR_EXPAND(@”SEL$9E43CB6E” (1) (2) (3)), and all the subseqent query block name differences follow on from this initial transformation. (Note: the value “sel$9e43cb6e” is derived from the input query block names of sel$1 and sel$2 that the new query block is derived from)

You may decide that this mismatch isn’t really terribly important. If you’ve modified the code to switch to ANSI style joins then you may be prepared to put in a little extra effort to check the plan in detail to see that it hasn’t changed; but it would be a lot nicer if the hash value wasn’t dependent on generated view names. You may recall that at one time Oracle had problems with plans that used materialized CTEs (“with” subqueries) because the plan hash value was affected by object names like sys_temp_0fd9d6791_dfc12da. The same principle ought, I think, to apply here.

If you don’t mind the ANSI/tradiational switch though, you might find that you’re less happy when you upgrade to 19c, because the same effect appears there too, only it’s worse. Not only do “identical” traditional and ANSI plans have different hash values, they don’t match the values from 12c because the generated name for the join factorization views (VW_JF) change in the upgrade. So if you’re depending on SQL Plan Baselines to reproduce 12c plans on 19c when you upgrade you may find cases where you know the stored baseline is giving you the same plan but Oracle thinks it isn’t and refuses to use it.

tl;dr

Plans which are functionally identical can have different plan hash values because the plans were reached through a different series of tranformations. In particular if you rewrite queries from “traditional” Oracle syntax to “ANSI” syntax you will find cases where the plan doesn’t change but the plan hash value does thanks to a change in the names of views generated by some transformations.

More significantly, if you upgrade from 12c to 19c there are case where the names of views generated by transformations may change, which could cause the optimizer to discard some of your carefully constructed SQL Plan Baselines as “not reproducible”.

Footnote

For reference, if I add the hint /*+ opt_param(‘_optimizer_join_factorization’ ‘false’) */ to the “traditional query then I get the following plan which shows more clearly the three branches that the original query has been split into – each section starting with a different choice for the driving table:


-------------------------------------------------------------------------------------------
| Id  | Operation               | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                 |     3 |   270 |    19   (6)| 00:00:01 |
|   1 |  VIEW                   | VW_ORE_67EF6547 |     3 |   270 |    19   (6)| 00:00:01 |
|   2 |   UNION-ALL             |                 |       |       |            |          |
|*  3 |    HASH JOIN            |                 |     1 |    55 |     7  (15)| 00:00:01 |
|*  4 |     HASH JOIN           |                 |     1 |    32 |     4   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL  | T               |     1 |    19 |     2   (0)| 00:00:01 |
|   6 |      TABLE ACCESS FULL  | T_FURTHER_INFO  |     4 |    52 |     2   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL   | T_OTHERS        |     4 |    92 |     2   (0)| 00:00:01 |
|*  8 |    HASH JOIN            |                 |     1 |    55 |     6   (0)| 00:00:01 |
|   9 |     MERGE JOIN CARTESIAN|                 |     4 |   144 |     4   (0)| 00:00:01 |
|* 10 |      TABLE ACCESS FULL  | T_OTHERS        |     1 |    23 |     2   (0)| 00:00:01 |
|  11 |      BUFFER SORT        |                 |     4 |    52 |     2   (0)| 00:00:01 |
|  12 |       TABLE ACCESS FULL | T_FURTHER_INFO  |     4 |    52 |     2   (0)| 00:00:01 |
|* 13 |     TABLE ACCESS FULL   | T               |     3 |    57 |     2   (0)| 00:00:01 |
|* 14 |    HASH JOIN            |                 |     1 |    55 |     6   (0)| 00:00:01 |
|  15 |     MERGE JOIN CARTESIAN|                 |     4 |   144 |     4   (0)| 00:00:01 |
|* 16 |      TABLE ACCESS FULL  | T_FURTHER_INFO  |     1 |    13 |     2   (0)| 00:00:01 |
|  17 |      BUFFER SORT        |                 |     3 |    69 |     2   (0)| 00:00:01 |
|* 18 |       TABLE ACCESS FULL | T_OTHERS        |     3 |    69 |     2   (0)| 00:00:01 |
|* 19 |     TABLE ACCESS FULL   | T               |     3 |    57 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SET$BB614FD2_3" "T"@"SET$BB614FD2_3")
      USE_MERGE_CARTESIAN(@"SET$BB614FD2_3" "PT"@"SET$BB614FD2_3")
      LEADING(@"SET$BB614FD2_3" "FI"@"SET$BB614FD2_3" "PT"@"SET$BB614FD2_3"
              "T"@"SET$BB614FD2_3")
      FULL(@"SET$BB614FD2_3" "T"@"SET$BB614FD2_3")
      FULL(@"SET$BB614FD2_3" "PT"@"SET$BB614FD2_3")
      FULL(@"SET$BB614FD2_3" "FI"@"SET$BB614FD2_3")
      USE_HASH(@"SET$BB614FD2_2" "T"@"SET$BB614FD2_2")
      USE_MERGE_CARTESIAN(@"SET$BB614FD2_2" "FI"@"SET$BB614FD2_2")
      LEADING(@"SET$BB614FD2_2" "PT"@"SET$BB614FD2_2" "FI"@"SET$BB614FD2_2"
              "T"@"SET$BB614FD2_2")
      FULL(@"SET$BB614FD2_2" "T"@"SET$BB614FD2_2")
      FULL(@"SET$BB614FD2_2" "FI"@"SET$BB614FD2_2")
      FULL(@"SET$BB614FD2_2" "PT"@"SET$BB614FD2_2")
      USE_HASH(@"SET$BB614FD2_1" "PT"@"SET$BB614FD2_1")
      USE_HASH(@"SET$BB614FD2_1" "FI"@"SET$BB614FD2_1")
      LEADING(@"SET$BB614FD2_1" "T"@"SET$BB614FD2_1" "FI"@"SET$BB614FD2_1"
              "PT"@"SET$BB614FD2_1")
      FULL(@"SET$BB614FD2_1" "PT"@"SET$BB614FD2_1")
      FULL(@"SET$BB614FD2_1" "FI"@"SET$BB614FD2_1")
      FULL(@"SET$BB614FD2_1" "T"@"SET$BB614FD2_1")
      NO_ACCESS(@"SEL$49E1C21B" "VW_ORE_67EF6547"@"SEL$67EF6547")
      OUTLINE(@"SEL$1")
      OR_EXPAND(@"SEL$1" (1) (2) (3))
      OUTLINE_LEAF(@"SEL$49E1C21B")
      OUTLINE_LEAF(@"SET$BB614FD2")
      OUTLINE_LEAF(@"SET$BB614FD2_1")
      OUTLINE_LEAF(@"SET$BB614FD2_2")
      OUTLINE_LEAF(@"SET$BB614FD2_3")
      ALL_ROWS
      OPT_PARAM('_optimizer_join_factorization' 'false')
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("PT"."ID"="T"."ID")
   4 - access("FI"."ID"="T"."ID")
   5 - filter("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
   8 - access("PT"."ID"="T"."ID" AND "FI"."ID"="T"."ID")
  10 - filter("PT"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
  13 - filter(LNNVL("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00',
              'syyyy-mm-dd hh24:mi:ss')))
  14 - access("PT"."ID"="T"."ID" AND "FI"."ID"="T"."ID")
  16 - filter("FI"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
  18 - filter(LNNVL("PT"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00',
              'syyyy-mm-dd hh24:mi:ss')))
  19 - filter(LNNVL("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00',
              'syyyy-mm-dd hh24:mi:ss')))

88 rows selected.

Although the “traditional” and “ANSI” plans still show a difference in their Plan hash values when join factorization is blocked, the absence of the join factorization view means that the plan hash values are now consistent between 12c to 19c (the output above came from 19.3.0.0 as you can see in the Outline information).

Video : Oracle REST Data Services (ORDS) : OAuth Client Credentials Authorization

Today’s video is a zip through the OAuth Client Credentials Authorization flow in Oracle REST Data Services (ORDS).

For those of you that are afraid of videos, this is one of the authentication and authorization methods discussed in this article.

You can get more information about ORDS here.

The star of today’s video is Øyvind Isene, who is trying to act all cool about being in one of my videos, when in fact I’ve made all his dreams come true. </p />
</p></div>

    	  	<div class=

PostgreSQL subtransactions, savepoints, and exception blocks

TL;DR: similar syntax but very different transaction semantic between Oracle and PostgreSQL procedural blocks

I posted a tricky Quiz on Twitter (unfortunately forgot to mention explicitely that I have a unique constraint on DEMO1.N):

The trick is that I didn’t precise on which database I run that. And I used on purpose a syntax that is valid both for Oracle (with the anonymous block in PL/SQL) and PostgreSQL (with the anonymous block in PL/pgSQL).

A compatible syntax does not mean that the semantic is the same. That’s the common issue with people who think that it is easy to port a database application or build a database-agnostic application. You can speak the same language without understanding the same meaning. The specifications for each implementation goes beyond the apparent standard syntax.

Exception block with Oracle

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=f0f4bf1c0e2e91c210e815d2ac67a688

db<>fiddle — Oracle 19c: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=f0f4bf1c0e2e91c210e815d2ac67a688

The PL/SQL block runs within an existing transaction and the exception block has nothing to do with the transaction control. This is only about branching to another code path when an exception occurs.

Then, what was previously inserted is still visible in the transaction, and can be committed or rolled back.

Exception block in Postgres

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=110d82eff25dde2823ff17b4fe9157d9

db<>fiddle — PostgreSQL 12: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=110d82eff25dde2823ff17b4fe9157d9

Here, the PL/pgSQL block runs as an atomic subtransaction. And when an exception is trapped, the whole block is rolled-back before executing the exception block. Actually, the block that has an exception handler is run in a “subtransaction” which is nothing else than setting a savepoint at the begin and rollback to this savepoint when entering the exception block.

This, of course, is documented:

When an error is caught by an EXCEPTION clause, the local variables of the PL/pgSQL function remain as they were when the error occurred, but all changes to persistent database state within the block are rolled back.

In those examples, the exception handler did not raise any error. If I re-raise the error in the exception block, the be behavior is the same between Oracle and PostgreSQL: all changes done by the block (including the exception block) are rolled back.

Re-raise In PostgreSQL:

do $$
begin
insert into DEMO1 (n) values (1);
insert into DEMO1 (n) values (42);
exception when others then
insert into DEMO2 select * from DEMO1;
raise;
end;
$$ language plpgsql;

ERROR: duplicate key value violates unique constraint "demo1_n_key"
DETAIL: Key (n)=(42) already exists.
CONTEXT: SQL statement "insert into DEMO1 (n) values (42)"
PL/pgSQL function inline_code_block line 4 at SQL statement
select * from DEMO2;
n
---
(0 rows)

Re-raise in Oracle:

begin
insert into DEMO1 (n) values (1);
insert into DEMO1 (n) values (42);
exception when others then
insert into DEMO2 select * from DEMO1;
raise;
end;/
ORA-00001: unique constraint (DEMO.SYS_C0093607) violated
ORA-06512: at line 6
ORA-06512: at line 3
select * from DEMO2;
no rows selected.

More info about this behavior in Oracle from Stew Ashton:

Statement-Level Atomicity

Basically, in Oracle, the call to the stored procedure follows statement-level atomicity where an internal savepoint is set before any statement (SQL or PL/SQL) and an unhandled exception (including a re-raise exception) rolls back to it. That’s different in PostgreSQL where no savepoint is set implicitly, and the session has to rollback the whole transaction when an error occurs. The savepoint set before the PL/pgSQL block is only to rollback changes before executing the exception block.

Postgres transaction control and exception blocks

But, then what happens if we commit within the code block? It is then impossible to ensure that “all changes to persistent database state within the block are rolled back” because what is committed (made visible to others) cannot be rolled-back. And that’s the main goal of intermediate commits.

This impossibility is implemented with “ ERROR: cannot commit while a subtransaction is active” in spi.c:

https://github.com/postgres/postgres/blob/master/src/backend/executor/spi.c#L220

and all this is, of course, documented with a small statement in https://www.postgresql.org/docs/current/plpgsql-transactions.html:

A transaction cannot be ended inside a block with exception handlers.

The specifications for it is also mentioned in the “Transaction control in procedures” hackers thread started by Peter Eisentraut when proposing this feature:

Re: [HACKERS] Transaction control in procedures

Limitations

As I understand it, this restriction is there to keep the semantics of the subtransaction when an exception block is present. With a savepoint at BEGIN and a rollback to savepoint at EXCEPTION. This semantic specification predates the introduction of transaction control in procedures. However, new requirements to take full advantage of the transaction control in procedures have been raised by Bryn Llewellyn (currently YugaByteDB developer advocate, former Oracle PL/SQL product manager): https://github.com/yugabyte/yugabyte-db/issues/2464

These use-cases are about encapsulating the database calls in stored procedures that, then, expose only the microservice API. For security, performance, and portability this API must be database-agnostic, and then:

  • all RDBMS-specific error messages must be trapped and translated to business messages and/or system logs. This must be done in an exception block that also covers the commit-as the commit can fail.
  • serialization errors at commit must be re-tried on the server, and that must be done also with an exception block that covers the commit.

Another reason to commit in a procedure is during a large bulk operation where we want intermediate commits. We may want to trap exceptions in this case as well and to retry some operations in case of errors.

If I try to code the commit and the exception, the “cannot commit while a subtransaction is active” error is raised as soon as the “commit” statement is encountered, before even trying to execute it:

create table DEMO(
n integer primary key deferrable initially deferred
);
create or replace procedure my_test(n int) as $$
begin
insert into DEMO(n) values(n);
commit;
exception when others then
raise notice '%',sqlerrm;
end;
$$ language plpgsql;
CREATE PROCEDURE
call my_test(1);
NOTICE: cannot commit while a subtransaction is active
CALL

If I remove the commit, I can catch the exceptions, but then I must handle the commit error in the client:

create or replace procedure my_test(n int) as $$
begin
insert into DEMO(n) values(n);
--commit;
exception when others then
raise notice '%',sqlerrm;
end;
$$ language plpgsql;
CREATE PROCEDURE
call my_test(1);
CALL
call my_test(1);
ERROR: duplicate key value violates unique constraint "demo_pkey"
DETAIL: Key (n)=(1) already exists.

Here the error message does not come from the exception block, but from the end of the command, because I am in autocommit mode. This is more visible from an explicit transaction:

begin transaction;
BEGIN
call my_test(1);
CALL
commit;
ERROR: duplicate key value violates unique constraint "demo_pkey"
DETAIL: Key (n)=(1) already exists.

Evolution

I think that the “A transaction cannot be ended inside a block with exception handlers” specification should be adapted to procedures. In my opinion, a commit should be allowed, ending the subtransaction and starting a new one. What was committed will never be rolled back. When an exception is raised, only the changes since the last commit should be rolled back.

Discussion about this should probably go in this hackers thread:

Re: PL/pgSQL - "commit" illegal in the executable section of a block statement that has an exception section

CBO Oddities – 1

I’ve decided to do a little rewriting and collating so that I can catalogue related ideas in an order that makes for a better narrative. So this is the first in a series of notes designed to help you understand why the optimizer has made a particular choice and why that choice is (from your perspective) a bad one, and what you can do either to help the optimizer find a better plan, or subvert the optimizer and force a better plan.

If you’re wondering why I choose to differentiate between “help the optimizer” and “subvert the optimizer” consider the following examples.

  • A query is joining two tables in the wrong order with a hash join when you know that a nested loop join in the opposite order would far better because you know that the data you want is very nicely clustered and there’s a really good index that would make access to that data very efficient. You check the table preferences and discover that the table_cached_blocks preference (see end notes) is at its default value of 1, so you set it to 16 and gather fresh stats on the indexes on the table. Oracle now recognises the effectiveness of this index and changes plan accordingly.
  • The optimizer has done a surprising transformation of a query, aggregating a table before joining to a couple of other tables when you were expecting it to use the joins to eliminate a huge fraction of the data before aggregating it.  After a little investigation you find that setting hidden parameter _optimizer_distinct_placement to false stops this happening.

You may find the distinction unnecessarily fussy, but I’d call the first example “helping the optimzier” – it gives the optimizer some truthful information about your data that is potentially going to result in better decisions in many different statements – and the second example “subverting the optimizer” – you’ve brute-forced it into not taking a path you didn’t like but at the same time you may have stopped that feature from appearing in other ways or in other queries. Of course, you might have minimised the impact of setting the parameter by using the opt_param() hint to apply the restriction to just this one query, nevertheless it’s possible that there is a better plan for the query that would have used the feature at some other point in the query if you’d managed to do something to help the optimizer rather than constraining it.

What’s up with the Optimizer

It’s likely that most of the articles will be based around interpreting execution plans since those are the things that tell us what the optimizer thinks will happen when it executes a statement, and within execution plans there are three critical aspects to consider –

  1. the numbers (most particularly Cost and Rows),
  2. the shape of the plan,
  3. the Predicate Information.

I want to use this note to make a couple of points about just the first of the three.

  • First – the estimates on any one line of an execution plan are “per start” of the line; some lines of an execution plan will be called many times in the course of a statement. In many cases the Rows estimate from one line of a plan will dictate the number of times that some other line of the plan will be executed – so a bad estimate of “how much data” can double up as a bad estimate of “how many times”, leading to a plan that looks efficient on paper but does far too much work at run-time. A line in a plan that looks a little inefficient may be fine if it executes only one, a line that looks very efficient may be a disaster if it executes a million time. Being able to read a plan and spot the places where the optimizer has produced a poor estimate of Rows is a critical skill – and there are many reasons why the optimizer produces poor estimates. Being able to spot poor estimates depends fairly heavily on knowing the data, but if you know the generic reasons for the optimizer producing poor estimates you’ve got a head start for recognising and addressing the errors when they appear.
  • Second – Cost is synonymous with Time. For a given instance at a given moment there is a simple, linear, relationship between the figure that the optimizer reports for the Cost of a statement (or subsection of a statement) and the Time that the optimizer reports. For many systems (those that have not run the calibrate_io procedure) the Time is simply the Cost multiplied by the time the optimizer thinks it will take to satisfy a single block read request, and the Cost is the optimizer’s estimate of the I/O requirement to satisfy the statement – with a fudge factor introduced to recognise the fact that a “single block” read request ought to complete in less time than a “multiblock” read request. Generally speaking the optimizer will consider many possible plans for a statement and pick the plan with the lowest estimated cost – but there is at least one exception to this rule, and it is an unfortunate weakness in the optimizer that there are many valid reasons why its estimates of Cost/Time are poor. Of course, you will note that the values that Oracle reports for the Time column are only accurate to the second – which isn’t particularly helpful when a single block read typically operates in the range of a few milliseconds.

To a large degree the optimizer’s task boils down to:

  • What’s the volume and scatter of the data I need
  • What access paths, with what wastage, are available to get to that data
  • How much time will I spend on I/O reading (and possibly discarding) data to extract the bit I want

Of course there are other considerations like the amount of CPU needed for a sort, the potential for I/O as sorts or hash joins, the time to handle a round-trip to a remote system, and RAC variations on the basic theme. But for many statements the driving issue is that any bad estimates of “how much data” and “how much (real) I/O” will lead to bad, potentially catastrophic, choices of execution plan. In the next article I’ll list all the different reasons (that I can think of at the time) why the optimizer can produce bad estimates of volume and time.

References for Cost vs. Time

References for table_cached_blocks:

 

Friday Philosophy – Top Ten Influencers in my Technical Career

Earlier this week I was sipping wine late at night and cogitating on what led me to where I am now. Part of that was the more specific topic of what, or rather who, influenced my technical development and career, especially early on. As a little game for myself, I decided to jot down the 10 first names I came up with and did not discard within 5 seconds. And then excluded those who’s influence had been negative!

It’s OK, don’t get your hopes up, you are not on the list.

That list was:

  • Cary Millsap
  • Craig Shallahamer
  • Mike Cox
  • Jonathan Lewis
  • Barry
  • Maria Colgan
  • Steven Feuerstein
  • Rachael Carmichael
  • Tim (OracleBase) Hall
  • Dominic Giles.
  • Richard Foote

I really hope you have heard of most of them. I’d be amazed if you know all of them. And yes, there are 11. I was, if you remember, sipping wine late at night. In the morning I looked at the list and thought about refining it or expanding it (and boy, I could expand it to 50 people plus in 10 minutes) but that was not my game. First 10, with very little analysis.

You know what is coming, I’m going to step through the list. I’m so obvious. But the reasons are not always so obvious (though some are, sorry). Remember, I was slightly drunk.

Cary Millsap. I detest Cary Millsap.

I’m joking of course! But a tiny little bit of me always goes “Grrrr” when I think of this man who is fundamentally a really nice person, very bright, and a wonderful presenter. Why? Well, he came up with OFA, the Optimal Flexible Architecture. This specified a logical, sensible way to lay out an Oracle database, it’s files and the directories they went in, file naming conventions etc such that the database was performant, easy to navigate, and you could have multiple databases on a server without confusion. And that could have been me! I came up with almost the exact same thing and I was damn proud of it. And 6 months after I came up with it and thought “I need to make a big thing of this and get some credit for my genius” – I came across OFA. I was gutted.

The thing is, Cary was one of the first people I came across who was putting out stuff to help all us DBA types back in the 1990’s.  I am sure I must have seen stuff he did that became the OFA and it influenced me. His OFA was first published a couple of years before I came up with my design, but I had not seen it. We did not really have the internet back then!

Cary did not influence me simply by producing great stuff, he made me realise that several people can come up with similar ideas and, actually, being “first” is nice – but really the key thing is to spread the knowledge. Making our jobs easier for everyone around you is really doing something for the community. Cary also came up with Method R for performance tuning which is great, but time to move on.

I sometimes mention I have a decent dose of dyslexia. In my mind Craig is Craig “Shalamar”. His last name is too long for me and I “spin” in the middle of his surname “Shallahamer”. Too many repeated letters (in my mind there are 2 m’s too). Thus when I only knew him from printed materials my brain would stick with the wrong name. Few people were putting out stuff in the early 90’s and because his stuff was so good he was a key, early source of received wisdom for me. Then in the late 90’s he disappeared, or at least from my view he did. But now he’s back and I’ve met him. He is about the only person (him and Kerry Osbourne, sorry Kerry) who I have been a little hem-touchy with  (go right to the end of that post). ie went “Oh wow! You are blah blah!” when meeting them (follow the link if you want to know what I mean). It’s OK, Craig let me off. I got him a beer. It was a free beer, it was at DOAG! One day I’ll actually buy him a beer to say thank you for all the help he gave me early on. I might even buy him two, but let’s not get too giddy.

Mike Cox is fundamentally a brilliant developer & incredibly smart and he will never, ever present. It’s not for him. He represents the huge number of very talented I.T people you never hear about as they just get on with the job. I worked with Mike when I was at Oracle in the early 90’s and again at the end of the 90’s when he {grudgingly} told his boss I was not totally useless. His boss knew that was high praise. I remember someone telling Mike his code did not work. Mike replied “Yes it does! I’ve checked it. Twice!”. His code worked. He is one of the few people I know who can write a page of PL/SQL and execute it and it does what he wants, first execution. But that is not what he taught me. He taught me that what we do is create solutions and the absolute one thing you have to do as a developer is create something the user wants to use. I.E. it makes their working life easier. Everything else is secondary. Thanks Mike.

If you are in the technical core Oracle RDBMS sphere and you do not know who Jonathan Lewis is, I’m stunned. His approach to methodically understanding problems and how Oracle works is second to none. I think there are one or two people as good as Jonathan is but personally I know of no one better. So that is why he influenced me? Well, yes and no. Oracle Names, those top people (and this is true in all disciplines) are people, just like all of us. Very talented but, fundamentally, normal people. Jonathan is a friend, I like chatting to him in the pub and we will discuss bread and chainsaws on twitter. And he has given me advice and help over the years, as a friend, and I very much appreciate that. And if it is not Oracle, sometimes I’m teaching him. If you meet those presenters and writers of good stuff then yes, of course, respect their skill. But don’t hero worship them. Most of them don’t actually like it. Treat them like regular people (because they ARE regular people) and you might make a friend.

I’ve written about Barry before (and no, I can’t for the life of me remember his last name). Barry taught me that you don’t need to be technically the best to be great at what you do. You need to care and you need to be willing to try and you need to be willing to learn. It’s all about attitude. In the little team we were in we had a guy who was technically superb. And no one liked him, as he was an arrogant and unhelpful bugger. Everyone liked Barry and asked him to help. Be like Barry. I try to be like Barry.

SQL Maria (She’ll probably never lose that nick name in the Oracle sphere) used to the product manager for the optimizer and I was a performance nerd, so of course I knew of Maria Colgan. The number of times she said to the audience “I’m not technical, I don’t understand this stuff…” and then gave a really good description of that stuff. She was a little liar! She knew her stuff (and still does), you can’t present like that and not know your topic. She was also one of the first product managers in Oracle I started chatting to, both about technical topics and as a friendly face. Oracle Names are just normal people and Oracle Names From Oracle are just normal people too. Who knew? Maria now looks after In Memory and stuff like that, but if you google her, the top hit is still “Maria Colgan Oracle Optimizer”. I wonder if Nigel Bayliss, who has been the PM for the optimizer for a few years now (and very good he is at it too) has a doll in a drawer with pins in it…

Steven Feurestein. I can’t spell his last name best out of three due to the aforementioned dyslexia. Anyone, and I mean ANYone, who was coding in PL/SQL in the mid 90’s onward probably had/has the Ant Book on their desk, Oracle PL/SQL Programming by Steven. I consumed the first edition of that book, pretty much working it to ruin as I referred to it over the years. I still have it and boy it is tatty. Thanks for that book Steven, and the ones that came after it. However, Steven has influenced me twice. He now works for Oracle, leading the Oracle Developer Advocates team which includes the Ask Tom team. And that’s sort of what I do now, advocate Oracle and the community. Only I don’t really get paid for it. Can I have a job Steven?

{Why did I not pick Tom Kyte? Looking back now he was a massive influence on me as he was on many others, he should be in the list. But he isn’t. So aren’t a lot of excellent people like Arup Nanda, Chris Antognini, Kevin Closson, Uwe Hess…}

I thought I had written a blog about Rachael Carmichael but it seems I have not. Rachel was really active in the Oracle presenting circuit back in the 90’s and early 2000’s and wrote/contributed to several books. I met her at one of my first UKOUG conferences when I was a presenting newbie. Rachael sort of took me under her wing and not only gave me good advice but also introduced me to several of the really well know presenters, a lot of who were in the Oak Table. Both of those things had a big influence on my career.

Rachael then decided she’d had enough of technology and followed a different path and swapped to working with animals. Because she wanted to. You can change career totally – if the current one has lost it’s sparkle, go find something else to do. I did not leave the Oracle sphere (I thought about it) but I decided to stop being mostly a technician and more an enabler, encouraged by Rachael’s example.

 

ORACLE_BASE must be one of the most visited and highest quality sources of Oracle technical information on the web. If you did not know, Tim Hall writes it all (I think he writes it all. Maybe he has a team held captive in his basement. I’ll ask him). If I need to check syntax or how a feature works, I google it and if an ORACLE-BASE page comes up I go there. Tim’s a great guy and a very good presenter – but don’t let him near an Oracle panel session. And oh boy don’t let him sit on one! Like me he is a biologist really, so an absolute top, fantastic bloke :-). Tim also has a very philosophical outlook on this Oracle technology bollocks, which I am sure encouraged me to do my Friday Philosophies.

Dominic Giles is a Master Product Manager for the Oracle Database here in the UK. I don’t know what you do to become a Master product manager, maybe just get old? For years Dom has been a real friend to the UKOUG and the conference circuit in general, doing great talks about the core RDBMS, what is new, what can and cannot be done. But the reason he really influenced me is he came to help us when I was working on the human genome project. Most consultants going on-site for a company would never tell the client to “just grow a pair and do it Martin”. Dom did. Bloody brilliant. We did know each other quite well at this point and it was a kick up the arse I needed. Be real with people, it’s so much more effective (if perhaps a little more risky?)

https://mwidlake.files.wordpress.com/2019/10/bowie.jpg?w=600&h=376 600w, https://mwidlake.files.wordpress.com/2019/10/bowie.jpg?w=150&h=94 150w" sizes="(max-width: 300px) 100vw, 300px" />


Unfortunately, Richard does not look like this anymore

Finally, and well done for reading this far, is Richard Foote. Actually, I reckon almost no one will have got through this whole list, my wife keeps telling me to split this post into 2 or 3 parts. But Richard will get this far, he wants to know what I say about him and if it includes anything about David Bowie. Richard is a bit of a Bowie fan, as am I. Bowie’s “Black Tie, White Noise” is playing as I type this. What Richard does not know about indexing you don’t need to know. I learnt a lot from him. But then I learnt a lot from many people, so why Richard?
This blog. I stole his layout for mine. In fact, before I changed the graphics for the banner and stretched the format it looked EXACTLY like Richard’s blog. Also, I liked Richard’s presenting style – Relaxed, jokey, but with good technical content. I sort of nicked that too. Part of me just want to be Richard, except for the being Australian bit </p />
</p></div>

    	  	<div class=

In Defence of Best Practices

https://oracle-base.com/blog/wp-content/uploads/2019/10/bird-159922_640-... 204w" sizes="(max-width: 174px) 85vw, 174px" />

The subject of “Best Practices” came up again yesterday in a thread on Twitter. This is a subject that rears its head every so often.

I understand all the arguments against the term “Best Practices”. There isn’t one correct way to do things. If there were it would be the only way, or automatic etc. It’s all situational etc. I really do understand all that. I’ve been in this conversation so many times over the years you wouldn’t believe it. I’ve heard all the various sentences and terms people would prefer to use rather than “Best Practice”, but here’s my answer to all that.

“Best practices are fine. Get over yourself and shut up!”

Tim Hall : 18th October 2019

I’ve said this more politely in many other conversations, including endless email chains etc.

When it comes down to it, people need guidance. A good best practice will give some context to suggest it is a starting point, and will give people directions for further information/investigation, but it’s targeted at people who don’t know enough about what they are doing and need help. Without a best practice they will do something really bad, and when shit happens they will blame the product. A good best practice can be the start of a journey for people.

I agree that the “Always do this because ‘just bloody do it!'” style of best practice is bad, but we all know that…

I just find the whole conversation so elitist. I spend half of my life Googling solutions (mostly non-Oracle stuff) and reading best practices and some of them are really good. Some of them have definitely improved my understanding, and left me in a position where I have a working production system that would otherwise not be working.

I’m sure this post will get a lot of reactions where people try and “explain to me” why I am wrong, and what I’m not understanding about the problems with best practices. As mentioned before, I really do know all that and I think you are wrong, and so do the vast majority of people outside your elitist echo chamber. Want to test that? Try these…

  • Write a post called “Best Practices for {insert subject of your choice}”. It will get more hits than anything else you’ve ever written.
  • Submit a conference session called “Best Practices for {insert subject of your choice}”. Assuming it gets through the paper selection, you will have more bums on seats than you’ve ever had before for that same subject.

Rather than wasting your life arguing about how flawed the term “Best Practices” is, why don’t you just write some good best practices? Show the world how they should be done, and start people on a positive journey. It’s just a term. Seriously. Get over yourself!

Cheers

Tim…

PS. I hope people from yesterday’s tweets don’t think this is directed at them. It’s really not. It’s the subject matter! This really is a subject I’ve revisited so many times over the years…

Updates

Due to repeatedly having to explain myself, here come some points people have raised and my reactions. I’m sure this list will grow as people insist on “educating me” about why I’m wrong.

I prefer “standard” or “normal” to “best”. As I said at the start of the post, I’ve heard just about every potential variation of this, and I just don’t care. They are all the same thing. They are all best practices. It’s just words. Yes, I know what “best” means, but that’s irrelevant. This is a commonly used term in tech and you aren’t getting rid of it, so own it!

I’ve seen people weaponize best practices. OK. So are you saying they won’t weaponize “standard practices” or “normal practices”? They won’t ever say, “So are you telling me you went against normal practices?”. Of course they will. Stupid people/companies will do stupid things regardless of the name.

But it’s not the “best”! Did you even read my post? I’m so tired of this. It’s a best practice to never use hints in SQL. I think that’s pretty solid advice. I do use hints in some SQL, but I always include a comment to explain why. I have deviated from best practice, but documented the reason why. If a person/company wants no deviation from best practice, they can remove it and have shit performance. That’s their choice. I’ve been transparent and explained my deviation. If this is not the way you work, you are wrong, not the best practice.

Most vendor best practice documents are crap. I have some sympathy for this, but I raise tickets against bad documentation, including best practices, and generally the reception to these has been good. The last one was a couple of weeks ago and the company (not Oracle) changed the docs the same day. I always recommend raising an SR/ticket/bug against bad documentation. It doesn’t take much time and you are improving things for yourself and everyone else. I feel like you can’t complain about the quality of the docs if you never point out the faults.


In Defence of Best Practices was first posted on October 18, 2019 at 9:38 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.