Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Misleading Execution Plan

A couple of weeks ago I published a note about an execution plan which showed the details of a scalar subquery in the wrong place (as far as the typical strategies for interpreting execution plans are concerned). In a footnote to the article I commented that Andy Sayer had produced a simple reproducible example of the anomaly based around the key features of the query supplied in the original posting and had emailed it to me.  With his permission (and with some minor modifications) I’ve reproduced it below:


rem
rem     Script:         misplaced_subq_plan.sql
rem     Author:         Andrew Sayer
rem     Dated:          May 2019
rem

drop table recursive_table;
drop table lookup_t;
drop table join_t;

@@setup

set linesize 180
set pagesize 60

create table recursive_table (
        my_id           number constraint rt_pk primary key,
        parent_id       number,
        fk_col          number
);

insert into recursive_table 
select 
        rownum, 
        nullif(rownum-1,0)      parent_id, 
        mod(rownum,10) 
from 
        dual 
connect by 
        rownum <=100
;

prompt  ==================================================
prompt  Note that fk_col will be zero for 1/10 of the rows
prompt  ==================================================

create table lookup_t(
        pk_col number  constraint lt_pk primary key,
        value varchar2(30 char)
)
;

insert into lookup_t 
select 
        rownum, 
        rpad('x',30,'x') 
from 
        dual 
connect by 
        rownum <=100
;

create table join_t(
        pk_col number primary key,
        col_10 number,
        value varchar2(100 char)
);

insert into join_t 
select 
        rownum, mod(rownum,10), rpad('x',30,'x') 
from 
        dual 
connect by 
        rownum <=1000 --> comment to avoid WordPress format problem.
;

execute dbms_stats.gather_table_stats(null,'recursive_table')
execute dbms_stats.gather_table_stats(null,'lookup_t')
execute dbms_stats.gather_table_stats(null,'join_t')

prompt	================================
prompt	note that pk_col will never be 0
prompt	================================

set serverout off
alter session set statistics_level=all;

var x number
exec :x := 1

spool misplaced_subq_plan

select  /* distinct */ 
        r.my_id, j.value, r.ssq_value
from    (
	select 
		my_id, parent_id, fk_col, 
		(select l.value from lookup_t l where l.pk_col = r.parent_id) ssq_value 
        from 
		recursive_table r 
	connect by 
		prior my_id = parent_id 
	start with 
		my_id = :x
	) r
join    join_t j
on	r.fk_col = j.pk_col
/

select * from table(dbms_xplan.display_cursor(format=>'allstats projection'));

set serveroutput on

spool off

The code generates, populates, and queries three tables:

  • recursive_table is used in a “connect by” query to generate some data.
  • lookup_t is used in a scalar subquery in the select list of the “connect by” query.
  • join_t is then joined to the result of the “connect by” query to eliminate some rows.

The construction allows us to see a difference between the number of rows returned and the number of times the scalar subquery is executed, and makes it easy to detect an anomaly in the presentation of the execution plan. And here is the execution plan from an 18.3 instance:

--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                 |      1 |        |     90 |00:00:00.01 |     170 |       |       |          |
|   1 |  TABLE ACCESS BY INDEX ROWID              | LOOKUP_T        |    100 |      1 |     99 |00:00:00.01 |     102 |       |       |          |
|*  2 |   INDEX UNIQUE SCAN                       | LT_PK           |    100 |      1 |     99 |00:00:00.01 |       3 |       |       |          |
|*  3 |  HASH JOIN                                |                 |      1 |      2 |     90 |00:00:00.01 |     170 |  1123K|  1123K|     1/0/0|
|   4 |   VIEW                                    |                 |      1 |      2 |    100 |00:00:00.01 |     125 |       |       |          |
|*  5 |    CONNECT BY NO FILTERING WITH START-WITH|                 |      1 |        |    100 |00:00:00.01 |      23 |  9216 |  9216 |     2/0/0|
|   6 |     TABLE ACCESS FULL                     | RECURSIVE_TABLE |      1 |    197 |    100 |00:00:00.01 |      23 |       |       |          |
|   7 |   TABLE ACCESS FULL                       | JOIN_T          |      1 |      1 |   1000 |00:00:00.01 |      45 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("L"."PK_COL"=:B1)
   3 - access("R"."FK_COL"="J"."PK_COL")
   5 - access("PARENT_ID"=PRIOR NULL)
       filter("MY_ID"=:X)

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "L"."VALUE"[VARCHAR2,120]
   2 - "L".ROWID[ROWID,10]
   3 - (#keys=1) "R"."MY_ID"[NUMBER,22], "J"."VALUE"[VARCHAR2,400], "R"."SSQ_VALUE"[VARCHAR2,120], "J"."VALUE"[VARCHAR2,400]
   4 - "R"."MY_ID"[NUMBER,22], "R"."FK_COL"[NUMBER,22], "R"."SSQ_VALUE"[VARCHAR2,120]
   5 - "PARENT_ID"[NUMBER,22], "MY_ID"[NUMBER,22], "FK_COL"[NUMBER,22], "R"."PARENT_ID"[NUMBER,22], PRIOR NULL[22], LEVEL[4]
   6 - "MY_ID"[NUMBER,22], "PARENT_ID"[NUMBER,22], "FK_COL"[NUMBER,22]
   7 - "J"."PK_COL"[NUMBER,22], "J"."VALUE"[VARCHAR2,400]

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan


In a typical execution plan with scalar subqueries in the select list, the sub-plans for the scalar subqueries appear in the plan before the main query – and in this plan you can see the scalar subquery here at operations 1 and 2.

But the scalar subquery is in the select list of a non-mergeable view (operations 4, 5, 6). We can see that this view generates 100 rows (A-rows of operation 4) and the scalar subquery starts 100 times (Starts of operation 1) – so we can infer that the subquery ran for each row generated by the view.

The problem, though, is that the result set from the view is joined to another table, eliminating some rows and reducing the size of the result set; so if we don’t look carefully at all the details of the plan we appear to have a driving query that produces a result set of 90 rows (at operation 3), but manages to execute the scalar subquery just above it in the plan more times than there are rows in the result set.

It’s easy to unpick what’s really happening in this very simple query with a very short plan – but much harder to do so in the original case where the scalar subquery appeared “outside” the hash join when it actually executed inside a complex subplan that generated the second input (proble table) for the hash join.

As a further little note – if you look at the Column Projection Information you’ll see that operation 4 is where Oracle first projects ‘r.ssq_value[varchar2,120]’ which is the column created by the execution of the sub-plan at operation 1.

Arguably the execution plan should have look more like:


Plan hash value: 2557600799

--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                 |      1 |        |     90 |00:00:00.01 |     170 |       |       |          |
|*  1 |  HASH JOIN                                |                 |      1 |      2 |     90 |00:00:00.01 |     170 |  1123K|  1123K|     1/0/0|
|   2 |   VIEW                                    |                 |      1 |      2 |    100 |00:00:00.01 |     125 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID            | LOOKUP_T        |    100 |      1 |     99 |00:00:00.01 |     102 |       |       |          |
|*  4 |     INDEX UNIQUE SCAN                     | LT_PK           |    100 |      1 |     99 |00:00:00.01 |       3 |       |       |          |
|*  5 |    CONNECT BY NO FILTERING WITH START-WITH|                 |      1 |        |    100 |00:00:00.01 |      23 |  9216 |  9216 |     2/0/0|
|   6 |     TABLE ACCESS FULL                     | RECURSIVE_TABLE |      1 |    100 |    100 |00:00:00.01 |      23 |       |       |          |
|   7 |   TABLE ACCESS FULL                       | JOIN_T          |      1 |      1 |   1000 |00:00:00.01 |      45 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------

Inevitably, there are cases where the sub-plan for a scalar subquery appears much closer to its point of operation rather than being moved to the top of the execution plan. So any time you have scalar subqueries in select lists inside in-line views keep a careful lookout for where they appear and how many times they run in the execution plan. And don’t forget that giving every query block a name will help you track down your migrating subqueries.

Footnote

If you’re wondering why the Column Projection Information reports s.ssq_value as varchar2(120) when I’ve declared the column as varchar2(30), my declaration is 30 CHAR, and the database (by default) is running with a multi-byte character set that allows a maximum of 4 bytes per character.

Update (22nd May 201)

Following the comment from Iudith Mentzel below about clever optimisations, primary keys, and related inferences I thought it worth pointing out that it is possible to modify the demonstration query to get the same plan (shape) with different Start counts. We note that instead of putting the scalar subquery inside the inline view we would get the same result if we passed the parent_id to the outer query block and ran the scalar subquery there:

select  /* distinct */ 
        r.my_id, j.value,
        (select l.value from lookup_t l where l.pk_col = r.parent_id) ssq_value 
from    (
        select 
                my_id, parent_id, fk_col
        from 
                recursive_table r 
        connect by 
                prior my_id = parent_id 
        start with 
                my_id = :x
        ) r
join    join_t j
on      r.fk_col = j.pk_col
/

This gives us the following execution plan (with rowsource execution statistics):


Plan hash value: 2557600799

--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                 |      1 |        |     90 |00:00:00.03 |      29 |       |       |          |
|   1 |  TABLE ACCESS BY INDEX ROWID              | LOOKUP_T        |     90 |      1 |     89 |00:00:00.01 |      97 |       |       |          |
|*  2 |   INDEX UNIQUE SCAN                       | LT_PK           |     90 |      1 |     89 |00:00:00.01 |       8 |       |       |          |
|*  3 |  HASH JOIN                                |                 |      1 |      2 |     90 |00:00:00.03 |      29 |  1695K|  1695K|     1/0/0|
|   4 |   VIEW                                    |                 |      1 |      2 |    100 |00:00:00.01 |       7 |       |       |          |
|*  5 |    CONNECT BY NO FILTERING WITH START-WITH|                 |      1 |        |    100 |00:00:00.01 |       7 |  6144 |  6144 |     2/0/0|
|   6 |     TABLE ACCESS FULL                     | RECURSIVE_TABLE |      1 |    100 |    100 |00:00:00.01 |       7 |       |       |          |
|   7 |   TABLE ACCESS FULL                       | JOIN_T          |      1 |      1 |   1000 |00:00:00.01 |      22 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------

Note that the plan hash values are the same even though (mechanically) the real order of activity is dramatically different. But now we can see that the scalar subquery (operations 1 and 2) starts 90 times – once for each row returned by the hash join at operation 3, and we have done slightly fewer buffer visits (97 compared to 102) for that part of the plan.

It’s a pity, though, that when you start poking at a plan and looking too closely there are always new buggy bits to see. With the scalar subquery now at its optimal position (and maybe it will eventually get there without a manual rewrite) the arithmetic of “summing up the plan” has gone wrong for (at least) the Buffers column. In the new plan the 97 buffer visits attributed to operation 1 (and its descendents) should have been added to the 29 buffer visits attributed to the hash join (and its descendents) at operation 3 to get a total of 126; instead the 97 have just disappeared from the query total.

By comparison, and reading the operations in the original plan a suitable order, we see the view at operation 4 reporting 109 buffers which comes from 7 for its “obvious” descendents plus the 102 from operation 1 that actually happen inside the view. Then the hash join at operation 3 reports 131 buffers which is the 109 from the view plus the 22 from the tablescan at operation 7, and that 131 buffers is the final figure for the query.

So, for this particular example, it doesn’t matter what you do, the execution plan and its stats try to confuse you.

Remove Files with Force and Other Bad Ideas

Almost every Linux or Unix person has seen the help forum post from a novice looking for an answer to a frustrating problem and the arrogant fool that responds with “Just type in rm -rf / and it will fix the problem.” For anyone who is part of the “do no harm” technical community, this can make us wish for a way to revoke the arrogant fool’s privileges to the internet— permanently.

Recycling Commands

This leads me to a great post from Carlos Robles this last week on using aliases with docker commands. As with many command line utilities, certain commands and arguments are re-used, but may or may not have safety protocols built in to protect you from human error. When considering the use of rm -rf and that docker may be the first experience with it for SQL DBAs, we should consider the difference in using it with the docker utility and how it is used in file management.

The rm -rf file management utility breaks down to remove with the -rf standing for recursive and force. The arguments are important, as this states to follow through the entire directory and any sub-directories and force removal files. As I teach everyone in my Linux sessions- Linux treats EVERYTHING AS A FILE.

If we run through the example Carlos suggested for removing Docker containers, but with my own spin on it, this is what we’d experience:

#bfe6ff; color: #000120;">https://dbakevlar.com/wp-content/uploads/2019/05/docker_rm-300x101.jpg 300w, https://dbakevlar.com/wp-content/uploads/2019/05/docker_rm-768x258.jpg 768w" sizes="(max-width: 650px) 100vw, 650px" />

Note what was done in the above example:

  • I wrote the alias to the .bash_profile
  • I executed my .bash_profile to put the change into effect.
  • I then listed out my containers
  • I used the alias to remove the Kellyn container
  • Lastly, I attempted to run the alias without an argument of container name or ID. Note that it failed as safety protocols are written into the utility.

If this is the first introduction to rm -rf, you might think this is how it works everywhere, which leaves you in a vulnerable state as an administrator in the Linux world. If we create a second example of log files being written to the $home/dockerlogs/ directory and you, as the administrator are expected to remove files on a regular interval, you might want to create an alias to eliminate some of the writing.

  1. Using alias’ save time
  2. Using alias’ may remove the awareness of the dangers of the command underneath it.

Using our steps from the first example, if we did the same type of alias work for it:

  • Write the alias rmlogs=’rm -rf ~/dockerlogs’ to the .bash_profile
  • It will now be in the .bash_profile whenever we log in.
  • We list out our files in the ~/dockerlogs directory
  • We then issue the command rmlogs/…. But at that moment, we accidently hit the enter button…or our coworker throws some papers on our desk and it hits the enter key….or we’re at home and our cat jumps onto the keyboard….you get the idea.
  • We now face that all log files have been forcefully removed from the directory.

How do we avoid these types of dangers with the Linux command rm -rf with its more pertinent dangers at the OS level where protocols haven’t been built in like the docker utility?

Build a Script

You can create a bash script, (or Powershell, but in my example, I’m going to use BASH because, well, I’m a Linux person, this is what we do…</p />
</p></div>

    	  	<div class=

Generate your Oracle Secure External Password Store wallet from your tnsnames.ora

Want to connect passwordless with SQLcl to your databases from a single location? Here is a script that creates the Secure External Password Store wallet credentials for each service declared in the tnsnames, as well as shell aliases for it (as bash does autocompletion). The idea is to put everything (wallet, sqlcl,…) in one single directory that you must protect of course because read access to the files is sufficient to connect to your databases.

Download the latest SQLcl from:

SQLcl Downloads

And install the Oracle Client if you do not have it already:

Oracle Instant Client Downloads

Now here is my script that:

  • reads the tnsnames.ora (define the location)
  • define sqlnet.ora and tnsnames.ora (ifile to the original one)
  • creates the password wallet
  • generates a script to define all aliases
  • create a login.sql

All that is located in the sqlcl directory (here under my $HOME) and the aliases have everything to point here (TNS_ADMIN and SQLPATH)

# this is where your tnsnames.ora is found
TNS_ADMIN=/etc
# unzip -d ~ sqlcl-19.1.0.094.1619.zip
#
# if "Error Message = no ocijdbc18 in java.library.path" see https://martincarstenbach.wordpress.com/2019/05/20/using-the-secure-external-password-store-with-sqlcl/
#
alias sqlcl='TNS_ADMIN=~/sqlcl SQLPATH=~/sqlcl ~/sqlcl/bin/sql -L -oci'
#
cat > ~/sqlcl/sqlnet.ora <WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY="$HOME/sqlcl")))
SQLNET.WALLET_OVERRIDE=TRUE
CAT
#
cat > ~/sqlcl/tnsnames.ora <ifile=$TNS_ADMIN/tnsnames.ora
CAT
#
cat > ~/sqlcl/login.sql <<'CAT'
set exitcommit off pagesize 5000 linesize 300 trimspool on sqlprompt "_user'@'_connect_identifier> "
set sqlformat ansiconsole
CAT
#
read -p "Enter SYS password to store in the wallet: " -s PASSWORD
# Create the wallet
mkstore -wrl ~/sqlcl -create <$PASSWORD
$PASSWORD
END
# Add services to wallet
awk -F"," '/^[^ #\t].*=/{sub(/=.*/,""); for (i=1;i<=NF;i++){print $i}}' $TNS_ADMIN/tnsnames.ora | while read service
do
echo "=== Adding $service to wallet for passwordless connection like: /@$service as sysdba"
mkstore -wrl ~/sqlcl -createCredential $service SYS <$PASSWORD
$PASSWORD
$PASSWORD
END
done
# list services from wallet
{
mkstore -wrl ~/sqlcl -listCredential <$PASSWORD
END
} | awk '/^[0-9]+: /{print "alias sysdba_"tolower($2)"="q"TNS_ADMIN=~/sqlcl SQLPATH=~/sqlcl ~/sqlcl/bin/sql -L -oci /@"toupper($2)" as sysdba"q}' q="'" qq='"' | sort | tee ~/sqlcl/
services.sh
#
unset PASSWORD

Then just source the generated services.sh to create aliases for each service (like sysdba_xxx). This example creates connections as sysdba with the SYS authentication, but it is highly recommended to have your own user. Of course the idea here is that the same password is used on all databases, but that again can be customized.

When I don’t want to use an alias (from a script for example) I also have a chmod u+x script in my path to run sqlcl with this environment

TNS_ADMIN=~/sqlcl SQLPATH=~/sql ~/sqlcl/bin/sql -L -oci ${@:-/nolog}

and SQLcl has also autocompletion for the connect command (from the tnsnames.ora).

If you have a “no ocijdbc18 in java.library.path” message, then look at Martin Bach blog:

Using the Secure External Password store with sqlcl

If you have credentials to connect to the Oracle Cloud, use the downloaded wallet instead of creating one with mkstore.

Using the Secure External Password store with sqlcl

Sometimes it is necessary to invoke a SQL script in bash or otherwise in an unattended way. SQLcl has become my tool of choice because it’s really lightweight and can do a lot. If you haven’t worked with it yet, you really should give it a go.

So how does one go about invoking SQL scripts from the command line these days? There’s an age-old problem with unattended execution: how do you authenticate against the database? There are many ways to do so, some better than others. This post shows how to use the Secure External Password Store with SQLcl. As always, there is more than one way to do this, @FranckPachot recently wrote about a different approach on Medium which you might want to check out as well.

Please don’t store passwords in scripts

I have seen passwords embedded in shell scripts far too often, and that’s something I really don’t like for many, many reasons. Thankfully Oracle offers an alternative to storing clear text passwords in the form of the Secure External Password Store (SEPS).This post explains one of many ways to use a wallet to use sqlcl to connect to a database. It assumes that a Secure External Password store is set up with the necessary credentials. Components referenced in this post are:

  • sqlcl 19.1
  • Instant Client Basic 18.5
  • Oracle XE 18.4

The SEPS wallet is found in /home/oracle/seps with its corresponding tnsnames.ora and sqlnet.ora in /home/oracle/seps/tns. I have set TNS_ADMIN to /home/oracle/seps/tns and ensured that sqlnet.ora points to the correct wallet location.

First attempt

The first attempt at using sqlcl with the wallet resulted in the following error:

$ /home/oracle/sqlcl/bin/sql -L /@xepdb1

SQLcl: Release 19.1 Production on Fri May 17 05:56:56 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

  USER          = 
  URL           = jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclexe)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XEPDB1)))
  Error Message = ORA-01017: invalid username/password; logon denied

I provided the -L flag to prevent sqlcl from asking me for different credentials after a failed login attempt. Using the -verbose flag in the next attempt I confirmed that sqlcl was indeed using my tnsnames.ora file in the directory specified by $TNS_ADMIN.

Investigating

So I started investigating … The first place to go to is the documentation, however I didn’t find anything relevant in the command line reference or FAQ shown on the product’s landing page. I then cast my net wider and found a few things on My Oracle Support (they didn’t apply to my version of sqlcl) and the Oracle forums.

I tried various things to get the thin client to cooperate with using the wallet but didn’t pursue that route further after learning about the option to use the OCI JDBC driver. After experimenting a little more I got on the right track.

Second attempt

The consensus in the Oracle forum posts I found seems to be to use the OCI flag when invoking the tool. So I tried that next:

$ /home/oracle/sqlcl/bin/sql -L -oci /@xepdb1

SQLcl: Release 19.1 Production on Fri May 17 06:09:29 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

  USER          = 
  URL           = jdbc:oracle:oci8:@xepdb1
  Error Message = no ocijdbc18 in java.library.path
  USER          = 
  URL           = jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclexe)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XEPDB1)))
  Error Message = ORA-01017: invalid username/password; logon denied

No success yet, but there’s an important clue in the output the first URL indicates that indeed an OCI connection was tried, except that a shared library was missing from Java’s library path. I guessed correctly that ocijdbc18 is part of the instant client 18 basic installation. After installing the RPM for the latest 18c instant client I confirmed libocijdb18.so was part of the package.

From what I understand java doesn’t pick up the configuration created by ldconfig and you either have to set java.library.path manually (as in java -Djava.library.path=…) or set LD_LIBRARY_PATH. The latter is easier, and it gave me the desired result:

$ export LD_LIBRARY_PATH=/usr/lib/oracle/18.5/client64/lib:$LD_LIBRARY_PATH
$ echo "select user from dual" | /home/oracle/sqlcl/bin/sql -L -oci /@xepdb1

SQLcl: Release 19.1 Production on Fri May 17 06:15:29 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Fri May 17 2019 06:15:32 -04:00

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

USER                                                                                                                            
------------------------------------------------------------------------
MARTIN                                                                                                                    

Disconnected from Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

Result! I can use sqlcl to connect to a database using a wallet.

3200 Clever hackers are in my PC; wow!!

Hackers are clever people; they must be to hack other people and take over their private data and steal identities and money. I have to draw the limit at the number of hackers who claim to be in my PC....[Read More]

Posted by Pete On 19/05/19 At 10:08 PM

Do you know what you are measuring with pgbench?

pgbench flamegraph

pgbench is a benchmark application for PostgreSQL. You define some parameters for the workload (read-only, volume of data, number of threads, cursor sharing, …) and measure the number of transactions per second. Pgbench is used a lot when one wants to compare two alternative environments, like different postgres version, different platform, different table design,…

However, a scientific approach should go beyond the simple correlation between the observed performance (transactions per seconds) and the configuration. Without a clear analysis and explanation on the cause-consequence, we cannot extrapolate from a single set of observations to a general recommendation. The goal of this post is to show what is behind this ‘transaction per second’ measure.

pgio flamegraphs

I’ll run another benchmark tool focused at the platform: Kevin Closson pgio, which is designed exactly for this analysis. Rather than trying to simulate all layers of an application (like pgbench) we can focus at a specific component: the PostgreSQL shared buffer cache, or the OS filesystem cache, or the storage access,…

I’m using Brendan Gregg FlameGraph here to visualize the full stack sampled by perf record

brendangregg/FlameGraph

with the following flags:

perf record --call-graph dwarf -F99 -e cpu-cycles -a

I’ve compiled PostgreSQL server with the following flags:

./configure CFLAGS=" -fno-omit-frame-pointer" --enable-debug

pgbench

I’ve initialized the pgbench database with a small scale (about 100MB) as it is the only setting where we can focus the pgbench activity: with a small size, I’ll have no physical reads:

pgbench --initialize --scale=8 pgio

In the same idea, I run a read-only workload, with 12 threads:

pgbench --no-vacuum --select-only --protocol=prepared --client=12 --jobs=12 --time=120 pgio &

Then, after waiting a few minutes for the warm-up, I record perf events:

sudo perf record --call-graph dwarf -F99 -e cpu-cycles -a \
-o /tmp/perf.data sleep 60

The result is parsed to produce a flamegraph of stack samples:

sudo perf script -i /tmp/perf.data | ./stackcollapse-perf.pl | ./flamegraph.pl --width=1200 --hash --cp

Here is the result (.svg)

This is what happened in the system during the pgbench test. Pgbench, the client, spends its time on PQsendQueryPrepared and PQconsumeInput, which is the minimum that can be done with an OLTP-like well-tuned application. I’ve run with ‘--protocol=prepared’ to avoid parsing overhead which is not what I want to measure.

The postgres process is running the backend. And this is where we can realize that the real database work (run DML and commit) is not where this pgbench spending its time. Less than 15% of samples in the backend executor (ExecScan) and 6% on the CommitTransaction (even if it is a select-only workload there’s a commit here). Remains the ReadyForQuery and pq_getbyte which are about frontend-backend communication.

If you run a benchmark to measure something else than the network roundtrips and context switches involved in the client/server communication, then this pgbench workload is not the right tool.

If you benchmark to compare the CPU and RAM activity, for example because you want to choose the best compute shape from your cloud provider, then you need to run something that is focused at this activity, in a sustainable way.

pgio

I’ll use Kevin Closson ‘pgio’ which is the same approach as his ‘SLOB’ for Oracle:

SLOB Resources

The settings in pgio.conf are similar in size and number of threads (I don’t want physical I/O and this stays in cache):

UPDATE_PCT=0
RUN_TIME=60
NUM_SCHEMAS=1
NUM_THREADS=12
WORK_UNIT=255
UPDATE_WORK_UNIT=8
SCALE=100M
DBNAME=pgio
CONNECT_STRING=pgio
CREATE_BASE_TABLE=TRUE

The setup and run is easy, and again I record perf events after a little warmup:

sh ./setup.sh
sh ./runit.sh &
sudo perf record --call-graph dwarf -F99 -e cpu-cycles -a \
-o /tmp/perf.data sleep 60

Same flamegraph (using same colors):

sudo perf script -i /tmp/perf.data | ./stackcollapse-perf.pl | ./flamegraph.pl --width=1200 --hash --cp > /tmp/perf.svg

And here is the .svg result:

There’s no frontend work here because of all runs from a PL/pgSQL loop and then no roundtrip, network and context switch is there to influence my measures. Most of the activity is in the query executor, accessing the shared buffers. This is what you want if you want to compare some platform configurations like:

  • cloud compute shapes
  • NUMA
  • large pages
  • memory settings
  • filesystem cache
  • compression / encryption
  • various intel security bugs mitigation patches

And instead of ‘transaction per second’ pgio will measure the number of buffers read per second and the cache hits.

In summary…

Pgbench is not the tool if you want to measure specific platform components, or the postgres components interfacing with the system (buffer cache, WAL, writer, …). Pgbench can be used to test the database for the application. But in all case, one number like ‘transactions per second’ is not sufficient. FlameGraph can help to visualize what is involved behind this measure.

Hi GG, this approach(pathfinder) can be used with GTT.

Hi GG, this approach(pathfinder) can be used with GTT. You can fill relevant data in the script.sql before the statement that is taggued.

PASS Summit 2019 Learning Pathways

Hello from Atlanta, where I’m preparing for tomorrow’s SQL Saturday and arrived for the great news announcing this year’s PASS Summit 2019 Learning Pathways.

These sessions are two or more sessions to provide a more complete learning opportunity for the attendee.  I’ll be part of two of these pathways:

Technical Leadership: Becoming a Technical Leader with Denise McInerney

Linux for SQL Server Professionals: Empowering the SQL Professional with Linux Scripting

I’m looking forward both to the first, a leadership talk, as we rarely see a focus on how to forge a path in a technical career without often sacrificing our deeper technical skills.  This session should be intriguing on how to build our career and build it in a way that makes us successful and still work with what made us passionate about the career to begin with.

My Linux session is going to focus on BASH scripting-  how to set up a Linux host properly for more effective script management, how to script in BASH to do more with less code, how to reuse code vs. reinventing the wheel and build scripts that will stand the test of time.  It’s not about taking away from Powershell, but enhancing your skills to make more of the Linux backbone that is behind Azure and SQL Server 2017, (and soon, 2019).

Do PASS Summit right, get into the pathways.

 



Tags:  , , ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBAKevlar [PASS Summit 2019 Learning Pathways], All Right Reserved. 2019.

Hacking together faster INSERTs

Most developers tools out there have some mechanism to unload a table into a flat file, either as CSV, or Excel, and some even allow you to unload the data as INSERT statements. The latter is pretty cool because it’s a nice way of having a self-contained file that does not need Excel or DataPump or any tool additional to the one you’re probably using to unload the data.

SQLcl and SQL Developer are perhaps the easiest to utilize for such an extract. You simply add the pseudo-hint INSERT to get the output as insert statements. For example:


SQLcl: Release 18.4 Production on Wed Apr 17 17:05:49 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Wed Apr 17 2019 17:05:49 +08:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.6.0.0.0

SQL> select /*insert*/ * from t where rownum <= 10;
REM INSERTING into T
SET DEFINE OFF;
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_FILE#_BLOCK#','INDEX');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_OBJ3','INDEX');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_TS1','INDEX');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_CON1','INDEX');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','IND$','TABLE');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','CDEF$','TABLE');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','C_TS#','CLUSTER');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_CCOL2','INDEX');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_PROXY_DATA$','INDEX');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_CDEF4','INDEX');
...
...

That’s pretty cool but no matter what tool you use, ultimately you end up with a set of insert statements that will load 1 row at a time into the database. For a small number of rows, that is no problem. But for larger numbers of rows then you might sitting around for a long time waiting for that script to finish. There is two things that are going to slow us down here.

1) Parsing

Every insert is a brand new statement since it contains literal values. Even for a simple INSERT, that’s a lot of parsing work for the database. This is easily solved with a couple of ALTER SESSION wrappers at the head and tail of the script.


alter session set cursor_sharing = force;
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_FILE#_BLOCK#','INDEX');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_OBJ3','INDEX');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_TS1','INDEX');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_CON1','INDEX');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','IND$','TABLE');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','CDEF$','TABLE');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','C_TS#','CLUSTER');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_CCOL2','INDEX');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_PROXY_DATA$','INDEX');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_CDEF4','INDEX');
...
...
alter session set cursor_sharing = exact;

That will replace the literals with bind variable values and hence all of the INSERTs will be sharable.

2) Row by Row

A set of INSERT statements is inherently a row by row operation. One INSERT = one row. Of course, we developers have all had drummed into our programming minds over the years that database operations should be done in sets not row by row. But how can we get to that utopia with set of insert statements? We could totally rewrite the script with bind arrays and the like, but that’s a big job. So here is my quick hack to get convert single row inserts into multiple row inserts without too much effort.

A set of individual INSERTS such as


Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_FILE#_BLOCK#','INDEX');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_OBJ3','INDEX');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_TS1','INDEX');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','I_CON1','INDEX');
Insert into T (OWNER,OBJECT_NAME,OBJECT_TYPE) values ('SYS','IND$','TABLE');

can also be written as a multi-table INSERT, where all insert targets are the same table and all WHEN conditions are omitted and hence are implicitly true. The above set of INSERTs can be phrased as a single statement like this:


insert all
into t(OWNER,OBJECT_NAME,OBJECT_TYPE)  values (q'{SYS}',q'{I_FILE#_BLOCK#}',q'{INDEX}')
into t(OWNER,OBJECT_NAME,OBJECT_TYPE)  values (q'{SYS}',q'{I_OBJ3}',q'{INDEX}')
into t(OWNER,OBJECT_NAME,OBJECT_TYPE)  values (q'{SYS}',q'{I_TS1}',q'{INDEX}')
into t(OWNER,OBJECT_NAME,OBJECT_TYPE)  values (q'{SYS}',q'{I_CON1}',q'{INDEX}')
into t(OWNER,OBJECT_NAME,OBJECT_TYPE)  values (q'{SYS}',q'{IND$}',q'{TABLE}')
select * from dual;

You could write some awk/sed etc to take an existing INSERT script and make it into a multiple table one. I took a slightly different approach and whipped up some PL/SQL to allow pipelining the appropriate multi-table inserts out to a spool file. Here is the function I wrote to do it.


create or replace function as_insert(p_query varchar2, p_batch int default 10) return sys.odcivarchar2list pipelined as
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
    n number := 0;
    
    l_tname varchar2(200) := substr(p_query,instr(p_query,' ',-1,1)+1);
    l_collist varchar2(32000);
    l_colval varchar2(32000);
    l_dml varchar2(32000);
    
    l_nls sys.odcivarchar2list := sys.odcivarchar2list();
    
begin
   if l_tname is null then l_tname := '@@TABLE@@'; end if;

   select value
   bulk collect into l_nls
   from v$nls_parameters
   where parameter in (   
      'NLS_DATE_FORMAT',
      'NLS_TIMESTAMP_FORMAT',
      'NLS_TIMESTAMP_TZ_FORMAT')
   order by parameter;

    execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss'' ';
    execute immediate 'alter session set nls_timestamp_format=''yyyy-mm-dd hh24:mi:ssff'' ';
    execute immediate 'alter session set nls_timestamp_tz_format=''yyyy-mm-dd hh24:mi:ssff tzr'' ';

    dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
    dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

    for i in 1 .. l_colCnt loop
        dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
        l_collist := l_collist || l_descTbl(i).col_name||',';
    end loop;
    l_collist := 'into '||l_tname||'('||rtrim(l_collist,',')||')';

    l_status := dbms_sql.execute(l_theCursor);

    pipe row('alter session set cursor_sharing = force;');
    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
       n := n + 1;
  
       if mod(n,p_batch) = 1 then
          pipe row('insert all ');
       end if;
       
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            if l_columnValue is null then
              l_colval := l_colval || 'null,';
            elsif l_descTbl(i).col_type in (1,8,9,96,112) then
              l_colval := l_colval || 'q''{'||l_columnValue ||'}''' || ',';
            elsif l_descTbl(i).col_type in (2,100,101) then
              l_colval := l_colval || l_columnValue || ',';
            elsif l_descTbl(i).col_type in (12) then
              l_colval := l_colval || 'to_date('''||l_columnValue||''',''yyyy-mm-dd hh24:mi:ss'')' || ',';
            elsif l_descTbl(i).col_type in (180) then
              l_colval := l_colval || 'to_timestamp('''||l_columnValue||''',''yyyy-mm-dd hh24:mi:ssff'')' || ',';
            elsif l_descTbl(i).col_type in (181) then
              l_colval := l_colval ||'to_timestamp_tz('''||l_columnValue||''',''yyyy-mm-dd hh24:mi:ssff tzr'')' || ',';
            elsif l_descTbl(i).col_type in (231) then
              l_colval := l_colval || 'to_timestamp('''||l_columnValue||''',''yyyy-mm-dd hh24:mi:ssff'')' || ',';
            elsif l_descTbl(i).col_type in (182) then
              l_colval := l_colval || 'to_yminterval('''||l_columnValue||''')' || ',';
            elsif l_descTbl(i).col_type in (183) then
              l_colval := l_colval ||'to_dsinterval('''||l_columnValue||''')'  || ',';
            end if;
        end loop;
        l_colval := rtrim(l_colval,',')||')';
        pipe row( l_collist  );
        pipe row( '  values ('||l_colval );
        if mod(n,p_batch) = 0 then
          pipe row('select * from dual;');
        end if;
        l_colval := null;
    end loop;
    if n = 0 then
      pipe row( 'No data found ');
    elsif mod(n,p_batch) != 0 then
      pipe row('select * from dual;');
    end if;
    pipe row('alter session set cursor_sharing = exact;');

    execute immediate 'alter session set nls_date_format='''||l_nls(1)||''' ';
    execute immediate 'alter session set nls_timestamp_format='''||l_nls(2)||''' ';
    execute immediate 'alter session set nls_timestamp_tz_format='''||l_nls(3)||''' ';
    return;
end;
/

and here’s an example of the output you’d expect to see from it


alter session set cursor_sharing = force;
insert all
into scott.emp(EMPNO,ENAME,SAL)
  values (7369,q'{SMITH}',800)
into scott.emp(EMPNO,ENAME,SAL)
  values (7499,q'{ALLEN}',1600)
into scott.emp(EMPNO,ENAME,SAL)
  values (7521,q'{WARD}',1250)
into scott.emp(EMPNO,ENAME,SAL)
  values (7566,q'{JONES}',2975)
into scott.emp(EMPNO,ENAME,SAL)
  values (7654,q'{MARTIN}',1250)
into scott.emp(EMPNO,ENAME,SAL)
  values (7698,q'{BLAKE}',2850)
into scott.emp(EMPNO,ENAME,SAL)
  values (7782,q'{CLARK}',2450)
into scott.emp(EMPNO,ENAME,SAL)
  values (7788,q'{SCOTT}',3000)
into scott.emp(EMPNO,ENAME,SAL)
  values (7839,q'{KING}',5000)
into scott.emp(EMPNO,ENAME,SAL)
  values (7844,q'{TURNER}',1500)
select * from dual;
insert all
into scott.emp(EMPNO,ENAME,SAL)
  values (7876,q'{ADAMS}',1100)
into scott.emp(EMPNO,ENAME,SAL)
  values (7900,q'{JAMES}',950)
into scott.emp(EMPNO,ENAME,SAL)
  values (7902,q'{FORD}',3000)
into scott.emp(EMPNO,ENAME,SAL)
  values (7934,q'{MILLER}',1300)
select * from dual;
alter session set cursor_sharing = exact;

Lets do a performance test on a large sample set. In this test, I’m loading a little over one million rows into an empty table, where the performance harness for each INSERT mechanism looks like the following to output the start and end times for the script:


select systimestamp from dual;
alter session set cursor_sharing = force;
set feedback off


[ Set of INSERT, or set of MULTI-TABLE inserts with 50 rows per INSERT]
set feedback on
alter session set cursor_sharing = exact;
select systimestamp from dual;

Running each gave the following results.


SQL> @c:\tmp\normal_insert.sql

SYSTIMESTAMP
---------------------------------------
17-APR-19 03.39.53.189000 PM +08:00


Session altered.


Session altered.


SYSTIMESTAMP
---------------------------------------
17-APR-19 03.43.47.920000 PM +08:00


SQL> @c:\tmp\multi_insert.sql

SYSTIMESTAMP
---------------------------------------
17-APR-19 03.43.40.402000 PM +08:00


Session altered.


Session altered.


SYSTIMESTAMP
---------------------------------------
17-APR-19 03.44.27.319000 PM +08:00

So that’s a nice four-fold speed boost, down from 3mins 54seconds to less than 50seconds.

Just to reiterate – I’m not suggesting you need to go this level for all INSERT scripts. Most of the time I just add the cursor_sharing options to my scripts, and that makes them plenty fast enough for the job. But if you have got a large number of inserts to do, then converting them to a multi-table equivalent might give you a nice boost.

Here’s the video version

I ‘fixed’ execution plan regression with optimizer_features_enable, what to do next?

Here is a simple example of using Mauro Pagano ‘pathfinder’ tool where you don’t really want to run the query, but just get the execution plan with all variations of optimizer settings. That’s something I used many times in situations similar to this one:

  • the database was upgraded, say from 11.2.0.4 to 19.3
  • one (or a few) SQL statements have problematic performance regression
  • the execution plan (in 19.3) is different than from the previous version (11.2.0.4) — you get both with SQL Tuning Sets or AWR
  • you set optimizer_features_enable to 11.2.0.4 and the old plan with acceptable performance is back

That’s a quick workaround, thanks to this unique Oracle Optimizer feature which let us run the latest version of the database with a previous version of the optimizer code. But the goal is not to stay long like this. Once the service is made acceptable again with this temporary setting, the second step is to understand which bug or feature is responsible for the change. Then, at least, the workaround can be limited to only one underscore setting instead of the generic optimizer_features_enable which sets hundreds of them. The third step then will be to fix the root cause, of course, and understanding what was wrong will help.

This post is about the second step — going from the general optimizer_features_enable to a unique focused setting.

This is something I wanted to write for a long time but I was always in a rush when encountering this kind of problem. But I’m currently attending Mike Dietrich upgrade workshop at AOUG conference in Vienna and this, the change of execution plan, is addressed by the exercises. Mike exposes the tools that can be used to compare the performance before and after the upgrade: capture the statements and performance statistics and compare them, and to fix them with SQL Plan Management.

The workshop instructions are on Mike’s blog:

HOL 19c - Main Index Page

If you did the workshop you have seen that the query sql_id=13dn4hkrzfpdy has a different execution plan between 11g and 19c and the idea of the lab is to fix the previous plan with a SQL Plan Baseline. That’s perfect, but I was curious about the reason for this execution plan change. There are many new features or fixes between 11.2.0.4 and 19.3 and one is probably responsible for that.

This is where Mauro Pagano ‘pathfinder’ can be used. Setting optimizer_features_enable is a shortcut to set all individual features or fixes, and pathfinder will try each of them one by one.

The query with a plan regression was:

SQL Details:
-----------------------------
Object ID : 34
Schema Name : TPCC
Container Name : Unknown (con_dbid: 72245725)
SQL ID : 13dn4hkrzfpdy
Execution Frequency : 3273
SQL Text :
SELECT COUNT(DISTINCT (S_I_ID)) FROM ORDER_LINE, STOCK
WHERE OL_W_ID = :B2 AND OL_D_ID = :B4 AND (OL_O_ID < :B3
) AND OL_O_ID >= (:B3 - 20) AND S_W_ID = :B2 AND S_I_ID =
OL_I_ID AND S_QUANTITY < :B1

The plan before and after, as reported by AWR Diff Report are the following:

And my goal is to understand which feature or fix control, when disabled, gets back to the plan hash value 954326358 instead of 3300316041

I installed sqldb360 (open sourced by Carlos Sierra and Mauro Pagano), which contains pathfinder:

git clone https://github.com/sqldb360/sqldb360.git
cd ./sqldb360/sql/

I changed the script.sql to put my query there with an EXPLAIN PLAN because I don’t want to execute it (which would require parameters):

alter session set current_schema=TPCC;
explain plan for
SELECT /* ^^pathfinder_testid */
COUNT(DISTINCT (S_I_ID)) FROM ORDER_LINE, STOCK
WHERE OL_W_ID = :B2 AND OL_D_ID = :B4 AND (OL_O_ID < :B3
) AND OL_O_ID >= (:B3 - 20) AND S_W_ID = :B2 AND S_I_ID =
OL_I_ID AND S_QUANTITY < :B1

By default, pathfinder executes the query and gets the execution plan with dbms_xplan.display_cursor, using the tag in the comment to identify it.

Here I’m doing an EXPLAIN PLAN and then I changed the pathfinder.sql to use dbms_xplan.display. My change in the ‘xplan driver’ is the following:

I’ve left the old query, but add the following one to be executed:

-- my addition there
PRO .
PRO SELECT RPAD('explain plan', 11) inst_child, plan_table_output
PRO FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', NULL, 'ADVANCED'))
-- done
PRO /

Then running pathfinder:

[oracle@hol]$ sqlplus / as sysdba @ pathfinder.sql '"/ as sysdba"'

This takes some time to test all settings for optimizer underscore parameters (632 ones here in 19.3) and fix controls (1459 here):

The result is a zip file containing an index and the detail of each test.

The index (00001_pathfinder_upgr_20190515_1113_index.html) has one line per combination and it is easy to search from the plan hash value:

My old plan is chosen when _optimizer_partial_join_eval is set to false:

And now, I have a better workaround. Instead of setting the optimizer_feature_enable, I can set only:

ALTER SESSION SET "_optimizer_partial_join_eval" = FALSE;

Of course, my search for the plan hash value also highlights which versions set the same:

The goal of this post is to show the tool. If you want to know more about Partial Join Evaluation, Google tells me that I blogged about this in the past:

Partial Join Evaluation in Oracle 12c - Blog dbi services

The query here, a count(distinct) on a join, is subject to this optimization which changes the join to a semi-join.

If I can change the query, maybe I’ll prefer to disable it with a hint. If I click on the baseline plan from the pathfinder index, I can see the plan with hints:

Then probably a NO_PARTIAL_JOIN can disable this feature.

Side remark: you can see OPTIMIZER_FEATURES_ENABLE(‘19.1.0') but I told you that I’m on 19.3, right? And that this is the pathfinder baseline without any session setting. I didn’t expect 19.3 there because Release Updates should not add features that change the execution plan. But I expected something like ‘19.0.0’. The magic of the new release model…

In summary:

  • Pathfinder is easy to run, give it a try when you need to understand why an execution plan has changed.
  • Do the Mike Dietrich hands-on lab: upgrade is something to exercise before doing it in production.
  • Since 8i, the Oracle Optimizer developers add a flag for any change, in order to give us the possibility to enable or disable the feature or the fix. And you control it at instance, session or query level. This is a unique feature you do not find on other database systems. And it can save your business because a critical regression can always happen after an upgrade.
  • AOUG conference had a great idea with the ‘workshop and live-demo’ day before the conference day. Fewer attendees and more interaction with the speakers.