Author Archives: Martin Bach

About Martin Bach

Oracle DBA and Linux enthusiast, part time author and presenter.

Installing Podman on Oracle Linux 8

Rather than having to use a search engine to read up on how to install podman on Oracle Linux 8, I thought I’d write the procedure down. Hopefully this saves you (and me) a few minutes next time the task comes up. I probably should write a short Ansible Playbook at some point, but that’s for another post.

The easiest way to install podman on Oracle Linux 8 is to install the entire podman module. If you haven’t used modules and application streams yet in Oracle Linux 8 yet, you can find more details in the Oracle Linux 8 documentation. Quoting from chapter 5, section “Use DNF Modules and Application Streams” in Managing Software in Oracle Linux Guide:

DNF introduces the concepts of modules, streams and profiles to allow for the management of different versions of software applications within a single operating system release. Modules can be used to group together many packages that comprise a single application and its dependencies.

This sounds exactly like what I want.

Installing the Podman Module

So what do the concepts of stream and module mean in practice? Podman is shipped as a module in the Application Stream (AppStream):

[root@ol8podman ~]# dnf module list container-tools:ol8
Last metadata expiration check: 0:12:47 ago on Fri 15 Jul 2022 10:13:15 BST.
Oracle Linux 8 Application Stream (x86_64)
Name            Stream  Profiles Summary                                                                 
container-tools ol8 [d] common [ Most recent (rolling) versions of podman, buildah, skopeo, runc, conmon,
                        d]        runc, conmon, CRIU, Udica, etc as well as dependencies such as containe
                                 r-selinux built and tested together, and updated as frequently as every 
                                 12 weeks.

Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled

Rather than installing the podman RPM on its own and figure out which other packages I need I went with the installation of the entire module:

[root@ol8podman ~]# dnf module install container-tools:ol8 
Last metadata expiration check: 0:15:25 ago on Fri 15 Jul 2022 10:13:15 BST.
Dependencies resolved.
=========================================================================================================
 Package                      Arch   Version                                     Repository         Size
=========================================================================================================
Installing group/module packages:
 buildah                      x86_64 1:1.24.2-4.module+el8.6.0+20665+a3b29bef    ol8_appstream     8.1 M
 cockpit-podman               noarch 43-1.module+el8.6.0+20665+a3b29bef          ol8_appstream     493 k
 conmon                       x86_64 2:2.1.0-1.module+el8.6.0+20665+a3b29bef     ol8_appstream      55 k
 container-selinux            noarch 2:2.179.1-1.module+el8.6.0+20665+a3b29bef   ol8_appstream      58 k
 containernetworking-plugins  x86_64 1:1.0.1-2.module+el8.6.0+20665+a3b29bef     ol8_appstream      18 M
 containers-common            x86_64 2:1-27.0.1.module+el8.6.0+20665+a3b29bef    ol8_appstream      67 k
 criu                         x86_64 3.15-3.module+el8.6.0+20665+a3b29bef        ol8_appstream     518 k
 crun                         x86_64 1.4.4-1.module+el8.6.0+20665+a3b29bef       ol8_appstream     209 k
 fuse-overlayfs               x86_64 1.8.2-1.module+el8.6.0+20665+a3b29bef       ol8_appstream      73 k
 libslirp                     x86_64 4.4.0-1.module+el8.6.0+20665+a3b29bef       ol8_appstream      70 k
 podman                       x86_64 2:4.0.2-6.module+el8.6.0+20665+a3b29bef     ol8_appstream      13 M
 python3-podman               noarch 4.0.0-1.module+el8.6.0+20665+a3b29bef       ol8_appstream     149 k
 runc                         x86_64 1:1.0.3-2.module+el8.6.0+20665+a3b29bef     ol8_appstream     3.0 M
 skopeo                       x86_64 2:1.6.1-2.module+el8.6.0+20665+a3b29bef     ol8_appstream     6.7 M
 slirp4netns                  x86_64 1.1.8-2.module+el8.6.0+20665+a3b29bef       ol8_appstream      51 k
 udica                        noarch 0.2.6-3.module+el8.6.0+20665+a3b29bef       ol8_appstream      49 k
Installing dependencies:
 checkpolicy                  x86_64 2.9-1.el8                                   ol8_baseos_latest 346 k
 cockpit-bridge               x86_64 264.1-1.0.1.el8                             ol8_baseos_latest 535 k
 fuse-common                  x86_64 3.3.0-15.0.2.el8                            ol8_baseos_latest  22 k
 fuse3                        x86_64 3.3.0-15.0.2.el8                            ol8_baseos_latest  55 k
 fuse3-libs                   x86_64 3.3.0-15.0.2.el8                            ol8_baseos_latest  95 k
 glib-networking              x86_64 2.56.1-1.1.el8                              ol8_baseos_latest 155 k
 gsettings-desktop-schemas    x86_64 3.32.0-6.el8                                ol8_baseos_latest 633 k
 json-glib                    x86_64 1.4.4-1.el8                                 ol8_baseos_latest 144 k
 libmodman                    x86_64 2.0.1-17.el8                                ol8_baseos_latest  36 k
 libnet                       x86_64 1.1.6-15.el8                                ol8_appstream      67 k
 libproxy                     x86_64 0.4.15-5.2.el8                              ol8_baseos_latest  75 k
 podman-catatonit             x86_64 2:4.0.2-6.module+el8.6.0+20665+a3b29bef     ol8_appstream     354 k
 policycoreutils-python-utils noarch 2.9-19.0.1.el8                              ol8_baseos_latest 253 k
 protobuf-c                   x86_64 1.3.0-6.el8                                 ol8_appstream      37 k
 python3-audit                x86_64 3.0.7-2.el8.2                               ol8_baseos_latest  87 k
 python3-chardet              noarch 3.0.4-7.el8                                 ol8_baseos_latest 195 k
 python3-idna                 noarch 2.5-5.el8                                   ol8_baseos_latest  97 k
 python3-libsemanage          x86_64 2.9-8.el8                                   ol8_baseos_latest 128 k
 python3-pip                  noarch 9.0.3-22.el8                                ol8_appstream      20 k
 python3-policycoreutils      noarch 2.9-19.0.1.el8                              ol8_baseos_latest 2.2 M
 python3-pysocks              noarch 1.6.8-3.el8                                 ol8_baseos_latest  34 k
 python3-pytoml               noarch 0.1.14-5.git7dea353.el8                     ol8_appstream      25 k
 python3-pyxdg                noarch 0.25-16.el8                                 ol8_appstream      94 k
 python3-requests             noarch 2.20.0-2.1.el8_1                            ol8_baseos_latest 123 k
 python3-setools              x86_64 4.3.0-3.el8                                 ol8_baseos_latest 624 k
 python3-setuptools           noarch 39.2.0-6.el8                                ol8_baseos_latest 163 k
 python3-urllib3              noarch 1.24.2-5.0.1.el8                            ol8_baseos_latest 177 k
 python36                     x86_64 3.6.8-38.module+el8.5.0+20329+5c5719bc      ol8_appstream      19 k
 shadow-utils-subid           x86_64 2:4.6-16.el8                                ol8_baseos_latest 112 k
 yajl                         x86_64 2.1.0-10.el8                                ol8_appstream      41 k
Installing module profiles:
 container-tools/common                                                                                 
Enabling module streams:
 container-tools                     ol8                                                                
 python36                            3.6                                                                

Transaction Summary
=========================================================================================================
Install  46 Packages

Total download size: 58 M
Installed size: 200 M

...

This is great! The current stable podman release at the time of writing is 4.1.1, so getting 4.0.2 doesn’t look too bad to me :)

Summary

Installing podman on Oracle Linux 8 is quite simple provided you are happy to install the entire podman module. The module provides a very convenient way to install recent releases for podman and its related tools (buildah, skopeo, …). Podman is important enough to get its own User Guide in the Oracle Linux 8 documentation set, the installation instructions I used when putting this post together can be found in chapter 2.

Avoid “Warning: Additional provider information from registry” for OCI Terraform Provider

After updating my main development workstation to Fedora 36 including all the tools I regularly use I noticed a change when working with Terraform code. The call to terraform init succeeded but was accompanied by a warning:

$ terraform version -no-color
Terraform v1.2.3
on linux_amd64
$ terraform init -no-color

Initializing the backend...

Initializing provider plugins...
- Finding latest version of hashicorp/oci...
- Installing hashicorp/oci v4.80.1...
- Installed hashicorp/oci v4.80.1 (signed by HashiCorp)

Terraform has created a lock file .terraform.lock.hcl to record the provider
selections it made above. Include this file in your version control repository
so that Terraform can guarantee to make the same selections by default when
you run "terraform init" in the future.


Warning: Additional provider information from registry

The remote registry returned warnings for registry.terraform.io/hashicorp/oci:
- For users on Terraform 0.13 or greater, this provider has moved to oracle/oci. 
  Please update your source in required_providers.

Terraform has been successfully initialized!

You may now begin working with Terraform. Try running "terraform plan" to see
any changes that are required for your infrastructure. All Terraform commands
should now work.

If you ever set or change modules or backend configuration for Terraform,
rerun this command to reinitialize your working directory. If you forget, other
commands will detect it and remind you to do so if necessary.

The “What’s new” section in the OCI Terraform Provider documentation mentions this change. It also describes how to switch the provider’s source to avoid this warning.

So here is what I did. I tend to put my provider details into main.tf, so this seemed like the best place to put the required_providers section:

provider "oci" {
  tenancy_ocid         = var.tenancy_ocid
  user_ocid            = var.user_ocid
  fingerprint          = var.key_fingerprint
  private_key_path     = var.private_key_path
  private_key_password = var.private_key_password
  region               = var.oci_region
}

terraform {
  required_providers {
    oci = {
      source  = "oracle/oci"
      version = ">= 4.0.0"
    }
  }
}

After adding the new terraform block I managed to use the oracle/oci provider and avoid the warning. The OCI driver version 4.80.1 was current at the time of writing.

$ terraform init -no-color

Initializing the backend...

Initializing provider plugins...
- Finding oracle/oci versions matching ">= 4.0.0"...
- Installing oracle/oci v4.80.1...
- Installed oracle/oci v4.80.1 (signed by a HashiCorp partner, key ID 1533A49284137CEB)

Partner and community providers are signed by their developers.
If you'd like to know more about provider signing, you can read about it here:
https://www.terraform.io/docs/cli/plugins/signing.html

Terraform has created a lock file .terraform.lock.hcl to record the provider
selections it made above. Include this file in your version control repository
so that Terraform can guarantee to make the same selections by default when
you run "terraform init" in the future.

Terraform has been successfully initialized!

You may now begin working with Terraform. Try running "terraform plan" to see
any changes that are required for your infrastructure. All Terraform commands
should now work.

If you ever set or change modules or backend configuration for Terraform,
rerun this command to reinitialize your working directory. If you forget, other
commands will detect it and remind you to do so if necessary.

And indeed, Terraform will now use the the oracle/oci driver:

$ terraform version -no-color
Terraform v1.2.3
on linux_amd64
+ provider registry.terraform.io/oracle/oci v4.80.1

Happy automating!

Generating Table DDL in Oracle Database

Generating table DDL is a common requirement. Unfortunately it’s not quite common enough for me to remember the syntax by heart, so this post serves as a reference to myself how to do this. Hopefully it saves you a few minutes, too.

I used Oracle SQLDeveloper Command-Line (SQLcl) version: 22.1.1.0 build: 22.1.1.131.0820 for this post, connecting to an Oracle 19c database running on Linux. The DDL command you are reading about later is not new to version 22.1, that one just so happens to be the most current version at the time of writing.

Here are the links for downloading SQLcl:

SQLcl 22.1 comes with a lot more cool stuff, Jeff Smith wrote about the details here.

DBMS_METADATA

Table DDL (and a lot of other DDL for that matter) can be generated using DBMS_METADATA. This package has been part of the database for quite some time and is documented in the PL/SQL Packages and Types Guide. In cases where SQLcl is unavailable it’s still a more than viable option to combine calls to DBMS_METADATA.set_transform_param() with DBMS_METADATA.get_ddl(). If you are in this position you might find the following sections in the PL/SQL Packages and Types Guide useful:

Tables 107-23 and 107-25 respectively (referenced above) are key to when it comes to understanding the options SQLcl offers. More on that later.

SQLcl is a LOT easier to use than DBMS_METADATA

SQLcl provides a shortcut to using the package: rather than calling DBMS_METADATA.GET_DDL() you can make use of the ddl command instead:

SQL> help ddl
DDL
---

DDL generates the code to reconstruct the object listed.  Use the type option
for materialized views. Use the save options to save the DDL to a file.

DDL [<object_name> [<type>] [SAVE <filename>]]

Let’s use the command with the ORDERS table Swingbench provides:

SQL> show user
USER is "SOE"
SQL> ddl orders

  CREATE TABLE "SOE"."ORDERS" 
   (	"ORDER_ID" NUMBER(12,0) CONSTRAINT "ORDER_ORDER_ID_NN" NOT NULL ENABLE, 
	"ORDER_DATE" TIMESTAMP (6) WITH LOCAL TIME ZONE CONSTRAINT "ORDER_DATE_NN" NOT NULL ENABLE, 
	"ORDER_MODE" VARCHAR2(8), 
	"CUSTOMER_ID" NUMBER(12,0) CONSTRAINT "ORDER_CUSTOMER_ID_NN" NOT NULL ENABLE, 
	"ORDER_STATUS" NUMBER(2,0), 
	"ORDER_TOTAL" NUMBER(8,2), 
	"SALES_REP_ID" NUMBER(6,0), 
	"PROMOTION_ID" NUMBER(6,0), 
	"WAREHOUSE_ID" NUMBER(6,0), 
	"DELIVERY_TYPE" VARCHAR2(15), 
	"COST_OF_DELIVERY" NUMBER(6,0), 
	"WAIT_TILL_ALL_AVAILABLE" VARCHAR2(15), 
	"DELIVERY_ADDRESS_ID" NUMBER(12,0), 
	"CUSTOMER_CLASS" VARCHAR2(30), 
	"CARD_ID" NUMBER(12,0), 
	"INVOICE_ADDRESS_ID" NUMBER(12,0), 
	 CONSTRAINT "ORDER_MODE_LOV" CHECK (order_mode in ('direct','online')) DEFERRABLE ENABLE NOVALIDATE, 
	 CONSTRAINT "ORDER_TOTAL_MIN" CHECK (order_total >= 0) DEFERRABLE ENABLE NOVALIDATE, 
	 CONSTRAINT "ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")
	  REFERENCES "SOE"."CUSTOMERS" ("CUSTOMER_ID") ON DELETE SET NULL ENABLE NOVALIDATE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 16 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ;
  CREATE UNIQUE INDEX "SOE"."ORDER_PK" ON "SOE"."ORDERS" ("ORDER_ID") REVERSE 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ;
ALTER TABLE "SOE"."ORDERS" ADD CONSTRAINT "ORDER_PK" PRIMARY KEY ("ORDER_ID")
  USING INDEX "SOE"."ORDER_PK"  ENABLE NOVALIDATE;

  CREATE INDEX "SOE"."ORD_CUSTOMER_IX" ON "SOE"."ORDERS" ("CUSTOMER_ID") REVERSE 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ;

  CREATE INDEX "SOE"."ORD_ORDER_DATE_IX" ON "SOE"."ORDERS" ("ORDER_DATE") REVERSE 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ;

  CREATE INDEX "SOE"."ORD_SALES_REP_IX" ON "SOE"."ORDERS" ("SALES_REP_ID") REVERSE 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ;

  CREATE INDEX "SOE"."ORD_WAREHOUSE_IX" ON "SOE"."ORDERS" ("WAREHOUSE_ID", "ORDER_STATUS") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ; 

Not only does the ddl command print the table DDL, it also includes the primary key as well as the table’s indexes! If you just want to recreate the table in a different (pluggable) database/same schema, then you’re done here. Optionally store the DDL in a file and put it into version control.

Customising Output

Sometimes however you might want to customise the output. That’s possible with SQLcl, too. The set ddl command can be used to that effect:

SQL> help set ddl
SET DDL
  SET DDL [[ PRETTY | SQLTERMINATOR | CONSTRAINTS | REF_CONSTRAINTS |
          CONSTRAINTS_AS_ALTER|OID | SIZE_BYTE_KEYWORD | PARTITIONING |
          SEGMENT_ATTRIBUTES | STORAGE | TABLESPACE | SPECIFICATION |
          BODY | FORCE | INSERT | |INHERIT | RESET] {on|off}
         ] | ON | OFF ]

Table 107-23 in the DBMS_METADATA package documentation describe the meaning of each of these. SQLTERMINATOR for example defines whether a SQL terminator such as the semi-colon or slash should be added after each statement. This defaults to FALSE in DBMS_METADATA, and TRUE in SQLcl. The current settings can be viewed using the show ddl command:

SQL> show ddl
STORAGE : ON
INHERIT : ON
EMIT_SCHEMA : ON
SQLTERMINATOR : ON
OID : ON
SPECIFICATION : ON
TABLESPACE : ON
SIZE_BYTE_KEYWORD : ON
PRETTY : ON
REF_CONSTRAINTS : ON
FORCE : ON
PARTITIONING : ON
CONSTRAINTS : ON
INSERT : ON
BODY : ON
CONSTRAINTS_AS_ALTER : ON
SEGMENT_ATTRIBUTES : ON

I don’t need the storage attributes, don’t want SQLcl to emit the schema and neither do I need the segment attributes. Although turning off SEGMENT_ATTRIBUTES disables STORAGE, too, I set this flag explicitly as a note to self.

SQL> set ddl STORAGE off
DDL Option STORAGE off
SQL> set ddl EMIT_SCHEMA off
DDL Option EMIT_SCHEMA off
SQL> set ddl SEGMENT_ATTRIBUTES off
DDL Option SEGMENT_ATTRIBUTES off

With the defaults changed to my use case the output is reduced quite a bit:

SQL> ddl orders

  CREATE TABLE "ORDERS" 
   (	"ORDER_ID" NUMBER(12,0) CONSTRAINT "ORDER_ORDER_ID_NN" NOT NULL ENABLE, 
	"ORDER_DATE" TIMESTAMP (6) WITH LOCAL TIME ZONE CONSTRAINT "ORDER_DATE_NN" NOT NULL ENABLE, 
	"ORDER_MODE" VARCHAR2(8), 
	"CUSTOMER_ID" NUMBER(12,0) CONSTRAINT "ORDER_CUSTOMER_ID_NN" NOT NULL ENABLE, 
	"ORDER_STATUS" NUMBER(2,0), 
	"ORDER_TOTAL" NUMBER(8,2), 
	"SALES_REP_ID" NUMBER(6,0), 
	"PROMOTION_ID" NUMBER(6,0), 
	"WAREHOUSE_ID" NUMBER(6,0), 
	"DELIVERY_TYPE" VARCHAR2(15), 
	"COST_OF_DELIVERY" NUMBER(6,0), 
	"WAIT_TILL_ALL_AVAILABLE" VARCHAR2(15), 
	"DELIVERY_ADDRESS_ID" NUMBER(12,0), 
	"CUSTOMER_CLASS" VARCHAR2(30), 
	"CARD_ID" NUMBER(12,0), 
	"INVOICE_ADDRESS_ID" NUMBER(12,0), 
	 CONSTRAINT "ORDER_MODE_LOV" CHECK (order_mode in ('direct','online')) DEFERRABLE ENABLE NOVALIDATE, 
	 CONSTRAINT "ORDER_TOTAL_MIN" CHECK (order_total >= 0) DEFERRABLE ENABLE NOVALIDATE, 
	 CONSTRAINT "ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")
	  REFERENCES "CUSTOMERS" ("CUSTOMER_ID") ON DELETE SET NULL ENABLE NOVALIDATE
   ) ;
  CREATE UNIQUE INDEX "ORDER_PK" ON "ORDERS" ("ORDER_ID") REVERSE 
  ;
ALTER TABLE "ORDERS" ADD CONSTRAINT "ORDER_PK" PRIMARY KEY ("ORDER_ID")
  USING INDEX "ORDER_PK"  ENABLE NOVALIDATE;

  CREATE INDEX "ORD_CUSTOMER_IX" ON "ORDERS" ("CUSTOMER_ID") REVERSE 
  ;

  CREATE INDEX "ORD_ORDER_DATE_IX" ON "ORDERS" ("ORDER_DATE") REVERSE 
  ;

  CREATE INDEX "ORD_SALES_REP_IX" ON "ORDERS" ("SALES_REP_ID") REVERSE 
  ;

  CREATE INDEX "ORD_WAREHOUSE_IX" ON "ORDERS" ("WAREHOUSE_ID", "ORDER_STATUS") 
  ;
SQL> 

And this is exactly what I wanted ;) Instead of listing all the specifics in the storage clause and segment management I can run this script on a different system with different tablespaces and other settings. The downside is that I’m now responsible for providing useful defaults.

Summary

SQLcl provides as super convenient shortcut to using DBMS_METADATA. There are many use cases for this function, from creating DDL for use with version control systems to data migrations the possibilities are almost endless.

Retrieving passwords from OCI Vault for use in Terraform

This post is written with the intention to complement the excellent “A comprehensive guide to managing secrets in your Terraform code” by Yevgeniy Brikman. Its aim is to detail how Oracle Cloud Infrastructure Vault (OCI Vault) can be used to securely store credentials and subsequently use them in Terraform scripts.

If you haven’t done so I recommend reading Yevgeni’s post to get some background information as to why storing passwords anywhere in code, even dot-configuration files, is a Truly Bad Idea. This article provides an example for his third technique: using a dedicated secrets store.

Never, ever, store any credentials in code. Just . don’t . do it. It’s disaster waiting to happen

– every security conscious person, always

Standard disclaimer: please be advised that creating cloud resources most likely costs you money, and keeping them running even more so. Don’t create any cloud resources unless you are authorised to spend that money and know about the implications of creating the resources mentioned in this post.

The problem with the Terraform state file

Whilst using OCI Vault for storing and retrieving secrets is without a doubt a great step towards safer code management, there is still an unsolved issue with Terraform: the state file is considered sensitive information by HashiCorp at the time of writing (2022-05-30). When using the local backend (eg the default) passwords and other sensitive information are stored in clear text in a JSON file. Storing sensitive information in clear text is very much counter-productive to the article’s goals. Alternative backends providing encryption at rest are most likely better suited. Please ensure you remain compliant with your IT security department’s policies regarding the Terraform state file.

Overview

In this article you can read how to create an Autonomous Database (ADB) instance using a tiny Terraform script. Compared to some other tutorials about the subject you won’t find the ADMIN password provided in the code.

Rather than providing the ADB instance’s ADMIN password as an environment variable, the password is retrieved from an OCI Vault secret and passed to the ADB resource. The ADB instance is just one potential use case for using OCI Vault in Terraform: anywhere secrets need to be used to create/maintain resources, the technique detailed for ADB applies as well.

Secrets in the context of OCI Vault are credentials such as passwords, certificates, SSH keys, or authentication tokens that you use with Oracle Cloud Infrastructure services. An OCI Vault Secret cannot be looked up as such: secrets are wrapped into what’s referred to as a secret bundle. A secret bundle consists of the secret contents, properties of the secret and secret version (such as version number or rotation state), and user-provided contextual metadata for the secret.

To keep this article short-ish, it is assumed that a secret has already been created and its Oracle Cloud Identifier (OCID) is known. The secret’s OCID is passed to the Terraform script via a variable.

An Autonomous Database instance is perfectly suited to demonstrate the use of a Terraform Data Source for looking up vault secrets as it does not require any supporting resources such as Virtual Cloud Networks, or any elaborate network security settings. The Terraform script will create a publicly accessible ADB instance protected by an Access Control List (ACL) allowing only specific IP addresses to connect. Furthermore, mutual TLS is enabled for even stronger security.

Using an OCI Vault Secret

Lookup operations in Terraform are performed using Data Sources. There are data sources for most cloud resources, including the aforementioned secret bundle. Provided the secret’s OCID is passed via a variable, the lookup using an oci_secrets_secretbundle data source could be performed as follows:

data "oci_secrets_secretbundle" "bundle" {

  secret_id = var.secret_ocid
}

Thankfully the OCI Terraform provider is smart enough to retrieve the current, active version of the secret. Once the secret has been retrieved, it can be used for the creation of an ADB instance. Since secrets are base64 encoded, they have to be decoded before they can be used. The following snippet demonstrates the use of the data source inside the ADB resource:

resource "oci_database_autonomous_database" "demo_adb_21c" {
  compartment_id              = var.compartment_ocid
  db_name                     = "DEMO"
  admin_password              = base64decode(data.oci_secrets_secretbundle.bundle.secret_bundle_content.0.content)
  cpu_core_count              = 1
  data_storage_size_in_tbs    = 1
  db_version                  = "21c"
  db_workload                 = "OLTP"
  display_name                = "ADB Free Tier 21c"
  is_free_tier                = true
  is_mtls_connection_required = true
  ocpu_count                  = 1
  whitelisted_ips             = var.allowed_ip_addresses
}

A call to terraform plan followed by a terraform apply will initiate the creation of the ADB instance. As long as the admin password complies with the password complexity rules of the ADB resource, the database will be created. Once its lifecycle status changed to running, the database will be accessible to IP addresses specified in var.allowed_addresses (a list of strings). Should you invoke the Terraform script from a Linux shell, this might be a way to set the variable:

$ export TF_VAR_allowed_ip_addresses='[ "1.2.3.4", "4.5.6.7" ]'
$ terraform plan -out myplan

Summary

Using OCI Vault to store sensitive information is a secure way to mitigate against many password-handling problems. The Terraform state file remains a concern, especially when using the local backend as it stores all information in clear text. The IT security department should be consulted as to how this potential security vulnerability should be treated. Other backends than the local backend exist and might suit the IT security team’s needs better.

Once a Vault secret has been looked up, it can be used in any Terraform resource. Referencing data sources should lead to more secure code deployments.

Happy Automating!

Linking Containers with Podman

Users of the Docker engine might find that their container runtime isn’t featured prominently in Oracle Linux 8. In fact, unless you change the default confifguration a dnf search does not reveal the engine at all. For better or for worse, it appears the industry has been gradually switching from Docker to Podman and its related ecosystem.

Whilst most Docker commands can be translated 1:1 to the Podman world, some differences exist. Instead of highlighting all the changes here please have a look at the Podman User Guide.

Overview

This article explains how to create a network link between 2 containers:

  1. Oracle XE 21c
  2. SQLcl client

These containers are going to be run "rootless", which has a few implications. By default Podman will allocate storage for containers in ~/.local/share/containers/ so please ensure you have sufficient space in your home directory.

The article refers to Gerald Venzl’s Oracle-XE images and you will create another image for SQLcl.

Installation

If you haven’t already installed Podman you can do so by installing the container-tools:ol8 module:

[opc@podman ~]$ $ sudo dnf module install container-tools:ol8
Last metadata expiration check: 0:06:04 ago on Mon 21 Mar 2022 13:19:40 GMT.
Dependencies resolved.
========================================================================================================================
 Package                         Arch      Version                                           Repository            Size
========================================================================================================================
Installing group/module packages:
 buildah                         x86_64    1:1.23.1-2.0.1.module+el8.5.0+20494+0311868c      ol8_appstream        7.9 M
 cockpit-podman                  noarch    39-1.module+el8.5.0+20494+0311868c                ol8_appstream        483 k
 conmon                          x86_64    2:2.0.32-1.module+el8.5.0+20494+0311868c          ol8_appstream         55 k
 container-selinux               noarch    2:2.173.0-1.module+el8.5.0+20494+0311868c         ol8_appstream         57 k
 containernetworking-plugins     x86_64    1.0.1-1.module+el8.5.0+20494+0311868c             ol8_appstream         19 M
 containers-common               noarch    2:1-8.0.1.module+el8.5.0+20494+0311868c           ol8_appstream         62 k
 criu                            x86_64    3.15-3.module+el8.5.0+20416+d687fed7              ol8_appstream        518 k
 crun                            x86_64    1.4.1-1.module+el8.5.0+20494+0311868c             ol8_appstream        205 k
 fuse-overlayfs                  x86_64    1.8-1.module+el8.5.0+20494+0311868c               ol8_appstream         73 k
 libslirp                        x86_64    4.4.0-1.module+el8.5.0+20416+d687fed7             ol8_appstream         70 k
 podman                          x86_64    1:3.4.2-9.0.1.module+el8.5.0+20494+0311868c       ol8_appstream         12 M
 python3-podman                  noarch    3.2.1-1.module+el8.5.0+20494+0311868c             ol8_appstream        148 k
 runc                            x86_64    1.0.3-1.module+el8.5.0+20494+0311868c             ol8_appstream        3.1 M
 skopeo                          x86_64    2:1.5.2-1.0.1.module+el8.5.0+20494+0311868c       ol8_appstream        6.7 M
 slirp4netns                     x86_64    1.1.8-1.module+el8.5.0+20416+d687fed7             ol8_appstream         51 k
 udica                           noarch    0.2.6-1.module+el8.5.0+20494+0311868c             ol8_appstream         48 k
Installing dependencies:
 fuse-common                     x86_64    3.2.1-12.0.3.el8                                  ol8_baseos_latest     22 k
 fuse3                           x86_64    3.2.1-12.0.3.el8                                  ol8_baseos_latest     51 k
 fuse3-libs                      x86_64    3.2.1-12.0.3.el8                                  ol8_baseos_latest     95 k
 libnet                          x86_64    1.1.6-15.el8                                      ol8_appstream         67 k
 podman-catatonit                x86_64    1:3.4.2-9.0.1.module+el8.5.0+20494+0311868c       ol8_appstream        345 k
 policycoreutils-python-utils    noarch    2.9-16.0.1.el8                                    ol8_baseos_latest    252 k
 python3-pytoml                  noarch    0.1.14-5.git7dea353.el8                           ol8_appstream         25 k
 python3-pyxdg                   noarch    0.25-16.el8                                       ol8_appstream         94 k
 yajl                            x86_64    2.1.0-10.el8                                      ol8_appstream         41 k
Installing module profiles:
 container-tools/common                                                                                                
Enabling module streams:
 container-tools                           ol8                                                                         

Transaction Summary
========================================================================================================================
Install  25 Packages

If you like DNS on your container network, install podman-plugins and dnsmasq. This article assumes you do so. The latter of the 2 services needs to be enabled and started:

[opc@podman ~]$ for task in enable start is-active; do sudo systemctl ${task} dnsmasq; done
active

If you see active in the output as in the example dnsmasq is working. If your system is part of a more elaborate setup, the use of dnsmasq is discouraged and you should ask your friendly network admin for advice.

Virtual Network Configuration

This section describes setting up a virtual network. That way you are emulating the way you’d previously have worked with Docker. If I should find the time for it I’ll write a second article and introduce you to Podman’s PODs, an elegant concept similar to Kubernetes that is not available with the Docker engine.

Network creation

Before containers can communicate with one another, they need to be told which network to use. The easiest way to do so is by creating a new, custom network as shown in this example:

[opc@podman ~]$ podman network create oranet
/home/opc/.config/cni/net.d/oranet.conflist
[opc@podman ~]$ podman network ls
NETWORK ID    NAME        VERSION     PLUGINS
2f259bab93aa  podman      0.4.0       bridge,portmap,firewall,tuning
4f4bfc6d2c15  oranet      0.4.0       bridge,portmap,firewall,tuning,dnsname
[opc@podman ~]$ 

As you can see the new network – oranet – has been created and it’s capable of using DNS thanks for the dnsname extension. If you opted not to install podman-plugins and dnsmasq this feature won’t be availble. Testing showed that availability of DNS on the container network made life a lot easier.

Storage Volumes

Containers are transient by nature, things you store in them are ephemeral by design. Since that’s not ideal for databases, a persistence layer should be used instead. The industry’s best known method to do so is by employing (Podman) volumes. Volumes are crated using the podman volume create command, for example:

[opc@podman ~]$ podman volume create oradata
oradata

As it is the case with the Container images, by default alll the volume’s data will reside in ~/.local/share/containers.

Database Secrets

The final step while preparing for running a database in Podman is to create a secret. Secrets are a relatively new feature in Podman and relieve you from having to consider workarounds passing sensitive data to containers. The Oracle XE containers to be used need to be initialised with a DBA password and it is prudent not to pass this in clear text on the command line.

For this example the necessary database password has been created as a secret and stored as oracle-password using podman secret create ...

[opc@podman ~]$ podman secret create oracle-password ~/.passwordFileToBeDeletedAfterUse
0c5d6d9eff16c4d30d36c6133
[opc@podman ~]$ podman secret ls
ID                         NAME             DRIVER      CREATED        UPDATED        
0c5d6d9eff16c4d30d36c6133  oracle-password  file        2 minutes ago  2 minutes ago 

This concludes the necessary preparations.

Let there be Containers

With all the setup completed the next step is to start an Oracle 21c XE instance and build the SQLcl container.

Oracle XE

Using the instructions by Gerald Venzl’s GitHub repository, adapted for this use case, a call to podman run might look like this:

[opc@podman ~]$ podman run --name oracle21xe --secret oracle-password \
-e ORACLE_PASSWORD_FILE=/run/secrets/oracle-password -d \
--net oranet -v oradata:/opt/oracle/oradata \
docker.io/gvenzl/oracle-xe:21-slim
5d94c0c3620f811bbe522273f73cbcb7c5210fecc0f88b0ecacc1f5474c0855a

The necessary flags are as follows:

  • --name assigns a name to the container so you can reference it later
  • --secret passes a named secret to the container, accessible in /run/secrets/oracle-password
  • -d tells the container to run in the background
  • --net defines the network the container should be attached to
  • -v maps the newly created volume to a directory in the container

You can check whether the container is up an running by executing podman ps:

[opc@podman ~]$ podman ps
CONTAINER ID  IMAGE                               COMMAND     CREATED         STATUS             PORTS       NAMES
5d94c0c3620f  docker.io/gvenzl/oracle-xe:21-slim              53 seconds ago  Up 54 seconds ago              oracle21xe

Creating a small SQLcl container:

Creating a container to run sqlcl is really quite straight forward. A suitable Dockerfile is shown here, please ensure you update the ZIPFILE with the current SQLcl release.

FROM docker.io/openjdk:11

RUN useradd --comment "sqlcl owner" --home-dir /home/sqlcl --uid 1000 --create-home --shell $(which bash) sqlcl 

USER sqlcl
WORKDIR /home/sqlcl

ENV ZIPFILE=sqlcl-21.4.1.17.1458.zip

RUN curl -LO "https://download.oracle.com/otn_software/java/sqldeveloper/${ZIPFILE}" && \
        /usr/local/openjdk-11/bin/jar -xf ${ZIPFILE} && \
        rm ${ZIPFILE}

ENTRYPOINT ["bash", "/home/sqlcl/sqlcl/bin/sql", "/nolog"]

You could of course pull the latest sqlcl ZIP from https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip. Using a named release should simplify the non-trivial task of naming ("tagging") your container image.

The image can be build using podman much in the same way Docker images were built:

[opc@podman ~]$ podman build . -t tools/sqlcl:21.4.1.17.1458

As you can see from the ENTRYPOINT the image cannot be sent to the backround (-d) by podman, it needs to be run interactively as you will see in the next section.

Linking Containers

The last step is to start the sqlcl container and connect to the database.

podman run --rm -it --name sqlcl --net oranet localhost/tools/sqlcl:21.4.1.17.1458

Here is an example how this works in my container:

[opc@podman ~]$ podman run --rm -it --name sqlcl --net oranet localhost/tools/sqlcl:21.4.1.17.1458


SQLcl: Release 21.4 Production on Mon Mar 21 13:35:05 2022

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

SQL> connect system@oracle21xe/xepdb1
Password? (**********?) ***************
Connected.
SQL> show con_name
CON_NAME 
------------------------------
XEPDB1

The connection string consists of a username (system) and the container name assigned as part of the call to podman run ... --name. Thanks to the dnsname extension and linking the container to the oranet network it is possible to address systems by name. XEPDB1 is the default name of the XE instance’s Pluggable Database.

Instead of connecting to a Pluggable Database it is of course possible to connect to the Container Database’s Root (CDB$ROOT).

Summary

Podman is very compatible to Docker, easing the transition. In this part of the mini-series you could read how to use Podman functionality with Oracle Linux 8 to link a container running Oracle XE and SQLcl.

Vagrant: mapping a Virtualbox VM to a Vagrant environment

This is a small post hopefully saving you a few minutes mapping Vagrant and VirtualBox environments.

I typically have lots of Vagrant environments defined. I love Vagrant as a technology, it makes it super easy to spin up Virtual Machines (VMs) and learn about new technologies.

Said Vagrant environments obviously show up as VMs in VirtualBox. To make it more interesting I have a few more VirtualBox VMs that don’t map to a Vagrant environment. Adding in a naming convention that’s been growing organically over time I occasionally find myself at a loss as to which VirtualBox VM maps to a Vagrant environment. Can this be done? Yep, and creating a mapping is quite simple actually. Here is what I found useful.

Directory structure

My Vagrant directory structure is quite simple: I defined ${HOME}/vagrant as top-level directory with a sub-directory containing all my (custom) boxes. Apart from ~/vagrant/boxes I create further sub-directories for each project. For example:

[martin@ryzen: vagrant]$ ls -ld *oracle* boxes
drwxrwxr-x 2 martin martin 4096 Nov 23 16:52 boxes
drwxrwxr-x 3 martin martin   41 Feb 16  2021 oracle_19c_dg
drwxrwxr-x 3 martin martin   41 Nov 19  2020 oracle_19c_ol7
drwxrwxr-x 3 martin martin   41 Jan  6  2021 oracle_19c_ol8
drwxrwxr-x 3 martin martin   41 Nov 25 12:54 oracle_xe

But … which of my VirtualBox VMs belongs to the oracle_xe environment?

Mapping a Vagrant environment to a VirtualBox VM

Vagrant keeps a lot of metadata in the project’s .vagrant directory. Continuing with the oracle_xe example, here is what it stores:

[martin@buildhost: oracle_xe]$ tree .vagrant/
.vagrant/
├── machines
│   └── oraclexe
│       └── virtualbox
│           ├── action_provision
│           ├── action_set_name
│           ├── box_meta
│           ├── creator_uid
│           ├── id
│           ├── index_uuid
│           ├── synced_folders
│           └── vagrant_cwd
├── provisioners
│   └── ansible
│       └── inventory
│           └── vagrant_ansible_inventory
└── rgloader
    └── loader.rb

7 directories, 10 files

Looking at the above output I guess I should look at .vagrant/machines/

The machine name (oraclexe) is derived from the Vagrantfile. I create a config.vm.define section per VM out of habit (even when I create just 1 VM), as you can see here in my shortened Vagrantfile:

# -*- mode: ruby -*-
# vi: set ft=ruby :

Vagrant.configure("2") do |config|
  
  config.vm.define "oraclexe" do |xe|
    xe.vm.box = "ol7"
    xe.vm.box_url = "file:///home/martin/vagrant/boxes/ol7.json"

    ...

    xe.vm.provision "ansible" do |ansible|
      ansible.playbook = "setup.yml"
    end
  end
end

In case you don’t give your VMs a name you should find a directory named default instead.

As I’m using Vagrant together with VirtualBox I’m not surprised to find a sub-directory named virtualbox.

Finally! You see the VM’s metadata in that directory. The VM’s ID can be found in .vagrant/machines/oraclexe/virtualbox/id. The file contains the internal ID VirtualBox uses to identify VMs. Using that knowledge to my advantage I can create the lookup as shown here:

[martin@buildhost: oracle_xe]$ vboxmanage list vms | grep $(cat .vagrant/machines/oraclexe/virtualbox/id)
"oraclexe" {67031773-bad9-4325-937b-e471d02a56a3}

Voila! This wasn’t particularly hard since the VM name is oracelxe as well. Nevertheless I found this technique works well regardless of how you curated your Vagrantfile.

Happy Automating!

DOAG 2021 gems: DBMS_XPLAN.COMPARE_PLANS

The most excellent #DOAG2021 conference ended last week. I have attended quite a few presentations and took lots of notes. I particularly enjoyed Conner McDonald‘s presentation about 25 years of tips and techniques. One of these tips prompted this blog post ;)

Turns out I have only seen a change to DBMS_XPLAN in passing. Its functionality has been extended in 19c, allowing you to compare execution plans. So needless to say I wanted to try DBMS_XPLAN.COMPARE_PLANS in my lab. The 19c Packages and Types documentation defines the call as follows:

DBMS_XPLAN.COMPARE_PLANS(
   reference_plan    IN generic_plan_object,
   compare_plan_list IN plan_object_list,
   type              IN VARCHAR2 := 'TEXT',
   level             IN VARCHAR2 := 'TYPICAL',
   section           IN VARCHAR2 := 'ALL')  
 RETURN CLOB;

The meaning of the first two parameters isn’t immediately obvious, so back to the documentation again. A generic plan object contains a single “SQL plan” whereas a plan object list is an array of these. Different options to reference the “SQL Plan” exist. You can grab a “SQL plan” from the plan table, cursor cache and many others.

I’m sure there are license considerations to be taken into account here so be careful which option you choose! Actually this applies to this entire blog (apologies if I have said it before, but), if you want to follow along please ensure you are license compliant.

The idea as I see it is for you to pick a reference plan from a supported source and compare it with 1 or many other plans. OK I think I have enough to get started.

Running Queries

As always I’m using Swingbench and its Order Entry schema as an example. I specifically went for it as it has enough complexity to create some larger execution plans but not too complex to make it impossible to follow the example. I’m running Oracle 19c (19.12.0) Enterprise Edition on Oracle Linux 8.4 by the way.

I managed to come up with what I think is a suitable compromise for this post:

SQL> !cat query.sql
set timing on echo on

SELECT /*+ gather_plan_statistics */
    o.order_id,
    SUM(oi.unit_price * oi.quantity) AS revenue,
    p.category_id,
    o.order_date
FROM
         orders o
    JOIN order_items oi ON ( o.order_id = oi.order_id )
    JOIN products    p ON ( p.product_id = oi.product_id )
WHERE
    o.order_date BETWEEN TIMESTAMP '2007-01-01 13:00:00' AND TIMESTAMP '2007-01-01 13:30:00'
GROUP BY
    o.order_id,
    p.category_id,
    o.order_date
ORDER BY
    o.order_id;
    
set timing off echo off

I’m joining PRODUCTS (a view), ORDERS and ORDER_ITEMS before applying a filter predicate. This requires the use of a timestamp as ORDERS.ORDER_DATE is a TIMESTAMP(6) WITH LOCAL TIME ZONE

Let’s execute the query:

@query

...

  45740079       8082           3 01-JAN-07 01.00.00.000000 PM
  45740079       5960          83 01-JAN-07 01.00.00.000000 PM

3552 rows selected.

Elapsed: 00:00:23.37
SQL> 
SQL> set timing off echo off

I was a little surprised about the elapsed time as it seems a little long. Both tables are partitioned using the hash partitioning scheme offered by oewizard. Here are some other stats worth knowing:

  • ORDERS: 45,924,841 rows occupying 5632 MB of disk space (without indices)
  • ORDER_ITEMS: 232,170,100 rows for 16128 MB of disk space (again without indices)

So let’s try and work out why the query took quite some time to complete. I’m using Tanel Poder’s excellent tpt-oracle scripts for this. Immediately after the statement finishes executing I use x.sql:

SQL> @x
Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gtr9hy6x492p7, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */     o.order_id,
SUM(oi.unit_price * oi.quantity) AS revenue,     p.category_id,
o.order_date FROM          orders o     JOIN order_items oi ON (
o.order_id = oi.order_id )     JOIN products    p ON ( p.product_id =
oi.product_id ) WHERE     o.order_date BETWEEN TIMESTAMP '2007-01-01
13:00:00' AND TIMESTAMP '2007-01-01 13:30:00' GROUP BY     o.order_id,
   p.category_id,     o.order_date ORDER BY     o.order_id

Plan hash value: 1832779287

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                     |      1 |        |       |   198K(100)|       |       |   3552 |00:00:23.30 |     717K|    714K|       |       |          |
|   1 |  SORT GROUP BY                          |                     |      1 |   2328 |   104K|   198K  (1)|       |       |   3552 |00:00:23.30 |     717K|    714K|   372K|   372K|  330K (0)|
|*  2 |   FILTER                                |                     |      1 |        |       |            |       |       |   3648 |00:00:13.75 |     717K|    714K|       |       |          |
|*  3 |    HASH JOIN RIGHT OUTER                |                     |      1 |   2328 |   104K|   198K  (1)|       |       |   3648 |00:00:13.75 |     717K|    714K|  2546K|  2546K| 1622K (0)|
|   4 |     INDEX FAST FULL SCAN                | PRD_DESC_PK         |      1 |   1000 |  4000 |     3   (0)|       |       |   1000 |00:00:00.01 |      17 |      2 |       |       |          |
|*  5 |     HASH JOIN                           |                     |      1 |   2328 | 97776 |   198K  (1)|       |       |   3648 |00:00:11.13 |     717K|    714K|  1995K|  1995K| 1674K (0)|
|   6 |      TABLE ACCESS FULL                  | PRODUCT_INFORMATION |      1 |   1000 |  8000 |     9   (0)|       |       |   1000 |00:00:00.01 |      30 |     10 |       |       |          |
|   7 |      NESTED LOOPS                       |                     |      1 |   2328 | 79152 |   198K  (1)|       |       |   3648 |00:00:11.13 |     717K|    714K|       |       |          |
|   8 |       NESTED LOOPS                      |                     |      1 |   2328 | 79152 |   198K  (1)|       |       |   3648 |00:00:13.73 |     717K|    714K|       |       |          |
|   9 |        PARTITION HASH ALL               |                     |      1 |    463 |  7871 |   196K  (1)|     1 |    32 |    768 |00:00:19.29 |     714K|    714K|       |       |          |
|* 10 |         TABLE ACCESS FULL               | ORDERS              |     32 |    463 |  7871 |   196K  (1)|     1 |    32 |    768 |00:00:19.82 |     714K|    714K|       |       |          |
|* 11 |        INDEX RANGE SCAN                 | ITEM_ORDER_IX       |    768 |      5 |       |     3   (0)|       |       |   3648 |00:00:00.04 |    2322 |     42 |       |       |          |
|  12 |       TABLE ACCESS BY GLOBAL INDEX ROWID| ORDER_ITEMS         |   3648 |      5 |    85 |     5   (0)| ROWID | ROWID |   3648 |00:00:00.03 |     801 |     30 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TIMESTAMP' 2007-01-01 13:30:00.000000000'>=TIMESTAMP' 2007-01-01 13:00:00.000000000')
   3 - access("D"."PRODUCT_ID"="I"."PRODUCT_ID")
   5 - access("I"."PRODUCT_ID"="OI"."PRODUCT_ID")
  10 - filter(("O"."ORDER_DATE"<=TIMESTAMP' 2007-01-01 13:30:00.000000000' AND "O"."ORDER_DATE">=TIMESTAMP' 2007-01-01 13:00:00.000000000'))
  11 - access("O"."ORDER_ID"="OI"."ORDER_ID")


39 rows selected.

Hmmm, that’s a bit strange: instead of a full scan on ORDERS (line 10) I would have expected the use of an index. I know there is one ;) And here is proof:

SQL> @ind order_date
Display indexes where table or index name matches %order_date%...

TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
SOE                  ORDERS                         ORD_ORDER_DATE_IX                 1 ORDER_DATE


INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS   PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANALYZED       DEGREE VISIBILIT
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
SOE                  ORDERS                         ORD_ORDER_DATE_IX              NORMAL/REV NO   VALID    NO   N     4     148859        632130   46338862   46298397 2021-11-23 07:06:01 1      VISIBLE
SQL> 

Oh hang on a sec: ORDER_DATE does have an index, but it’s a reverse key index. This does have a few implications as explained by Richard Foote, let’s try and see if a “regular” index makes a difference.

Note that changing the index type might very well cause issues unrelated to this particular query. There is almost certainly a reason why the index was created as a reverse key index so by “fixing” this issue you can end up introducing another. Or multiple others.

– Lesson learned the hard way after stuff broke

By the way, further executions of the query didn’t change the elapsed time, they occasionally resulted in the creation an additional child cursor thanks to statistics feedback.

Let’s recreate the index as a non-reverse index:

SQL> alter index ORD_ORDER_DATE_IX rebuild noreverse parallel 8;

Index altered.

SQL> alter index ORD_ORDER_DATE_IX noparallel;

Index altered.

Running the query again gives me a different result:

SQL> @query

...

  45623157       5700         141 01-JAN-07 01.00.00.000000 PM
  45623157       5455         192 01-JAN-07 01.00.00.000000 PM
  45740079       8082           3 01-JAN-07 01.00.00.000000 PM
  45740079       5960          83 01-JAN-07 01.00.00.000000 PM

3552 rows selected.

Elapsed: 00:00:00.06
SQL> 

Right, so there is a difference in elapsed time :) Let’s check if the index was used:

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gtr9hy6x492p7, child number 1
-------------------------------------
SELECT /*+ gather_plan_statistics */     o.order_id,
SUM(oi.unit_price * oi.quantity) AS revenue,     p.category_id,
o.order_date FROM          orders o     JOIN order_items oi ON (
o.order_id = oi.order_id )     JOIN products    p ON ( p.product_id =
oi.product_id ) WHERE     o.order_date BETWEEN TIMESTAMP '2007-01-01
13:00:00' AND TIMESTAMP '2007-01-01 13:30:00' GROUP BY     o.order_id,
   p.category_id,     o.order_date ORDER BY     o.order_id

Plan hash value: 4255998723

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                        | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                 |                     |      1 |        |       |  2801 (100)|       |       |   3552 |00:00:00.03 |    3945 |    772 |       |       |          |
|   1 |  SORT GROUP BY                                   |                     |      1 |   2328 |   104K|  2801   (1)|       |       |   3552 |00:00:00.03 |    3945 |    772 |   302K|   302K|  268K (0)|
|*  2 |   FILTER                                         |                     |      1 |        |       |            |       |       |   3648 |00:00:00.01 |    3945 |    772 |       |       |          |
|*  3 |    HASH JOIN RIGHT OUTER                         |                     |      1 |   2328 |   104K|  2800   (1)|       |       |   3648 |00:00:00.01 |    3945 |    772 |  2546K|  2546K|  353K (0)|
|   4 |     INDEX FAST FULL SCAN                         | PRD_DESC_PK         |      1 |   1000 |  4000 |     3   (0)|       |       |   1000 |00:00:00.01 |      17 |      0 |       |       |          |
|*  5 |     HASH JOIN                                    |                     |      1 |   2328 | 97776 |  2797   (1)|       |       |   3648 |00:00:00.01 |    3928 |    772 |  1995K|  1995K|  353K (0)|
|   6 |      TABLE ACCESS FULL                           | PRODUCT_INFORMATION |      1 |   1000 |  8000 |     9   (0)|       |       |   1000 |00:00:00.01 |      30 |      0 |       |       |          |
|   7 |      NESTED LOOPS                                |                     |      1 |   2328 | 79152 |  2788   (1)|       |       |   3648 |00:00:00.01 |    3898 |    772 |       |       |          |
|   8 |       NESTED LOOPS                               |                     |      1 |   2328 | 79152 |  2788   (1)|       |       |   3648 |00:00:00.01 |    3097 |    772 |       |       |          |
|   9 |        TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS              |      1 |    463 |  7871 |   472   (0)| ROWID | ROWID |    768 |00:00:00.41 |     775 |    772 |       |       |          |
|* 10 |         INDEX RANGE SCAN                         | ORD_ORDER_DATE_IX   |      1 |    467 |       |     5   (0)|       |       |    768 |00:00:00.01 |       8 |      5 |       |       |          |
|* 11 |        INDEX RANGE SCAN                          | ITEM_ORDER_IX       |    768 |      5 |       |     3   (0)|       |       |   3648 |00:00:00.01 |    2322 |      0 |       |       |          |
|  12 |       TABLE ACCESS BY GLOBAL INDEX ROWID         | ORDER_ITEMS         |   3648 |      5 |    85 |     5   (0)| ROWID | ROWID |   3648 |00:00:00.01 |     801 |      0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TIMESTAMP' 2007-01-01 13:30:00.000000000'>=TIMESTAMP' 2007-01-01 13:00:00.000000000')
   3 - access("D"."PRODUCT_ID"="I"."PRODUCT_ID")
   5 - access("I"."PRODUCT_ID"="OI"."PRODUCT_ID")
  10 - access("O"."ORDER_DATE">=TIMESTAMP' 2007-01-01 13:00:00.000000000' AND "O"."ORDER_DATE"<=TIMESTAMP' 2007-01-01 13:30:00.000000000')
  11 - access("O"."ORDER_ID"="OI"."ORDER_ID")


39 rows selected.

It very much looks like the change from reverse to non-reverse index provided plenty of benefit for this query. If regression testing showed no problems with the other workloads on this system there is a strong argument to put this into production.

SQL Plan Analysis

At this point in time V$SQL shows 3 entries for SQL_ID gtr9hy6x492p7:

  • Plan Hash Value 1832779287: original execution using reverse key index (child cursor number 0)
  • Plan Hash Value 4255998723: using the index rebuilt as non-reverse key (child cursor number 1 and 2)

Child cursor 3 uses the same Plan Hash Value despite having used statistics feedback. In this demo run a second execution against the reverse-key index didn’t trigger statistics feedback although I have seen it during earlier tests.

While it’s fairly obvious to the human eye where the differences are between child cursor 0 and the others, let’s see what Oracle comes up with.

With the information provided by x.sql I can compare plans from the cursor cache. I rewrote the code example from the documentation a little, the end result however is the same.

var report clob

BEGIN
    :report := dbms_xplan.compare_plans(
        reference_plan => cursor_cache_object('gtr9hy6x492p7', 0), 
        compare_plan_list => plan_object_list(
            cursor_cache_object('gtr9hy6x492p7', 1), 
            cursor_cache_object('gtr9hy6x492p7', 2)
        ), 
        type => 'TEXT');
END;
/

print report

And here is the report. Tanel’s login.sql does a great job formatting the output by the way:

REPORT
-----------------------------------------------------------------------------------------------------------------

COMPARE PLANS REPORT
---------------------------------------------------------------------------------------------
  Current user           : SOE
  Total number of plans  : 3
  Number of findings     : 6
---------------------------------------------------------------------------------------------

COMPARISON DETAILS
---------------------------------------------------------------------------------------------
 Plan Number            : 1 (Reference Plan)
 Plan Found             : Yes
 Plan Source            : Cursor Cache
 SQL ID                 : gtr9hy6x492p7
 Child Number           : 0
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "SOE"
 SQL Text               : SELECT /*+ gather_plan_statistics */ o.order_id,
                        SUM(oi.unit_price * oi.quantity) AS revenue,
                        p.category_id, o.order_date FROM orders o JOIN
                        order_items oi ON ( o.order_id = oi.order_id ) JOIN
                        products p ON ( p.product_id = oi.product_id ) WHERE
                        o.order_date BETWEEN TIMESTAMP '2007-01-01 13:00:00'
                        AND TIMESTAMP '2007-01-01 13:30:00' GROUP BY
                        o.order_id, p.category_id, o.order_date ORDER BY
                        o.order_id

Plan
-----------------------------

 Plan Hash Value  : 1832779287

--------------------------------------------------------------------------------------------------------------
| Id   | Operation                                 | Name                | Rows | Bytes  | Cost   | Time     |
--------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                          |                     |      |        | 198882 |          |
|    1 |   SORT GROUP BY                           |                     | 2328 | 107088 | 198882 | 00:00:08 |
|  * 2 |    FILTER                                 |                     |      |        |        |          |
|  * 3 |     HASH JOIN RIGHT OUTER                 |                     | 2328 | 107088 | 198881 | 00:00:08 |
|    4 |      INDEX FAST FULL SCAN                 | PRD_DESC_PK         | 1000 |   4000 |      3 | 00:00:01 |
|  * 5 |      HASH JOIN                            |                     | 2328 |  97776 | 198877 | 00:00:08 |
|    6 |       TABLE ACCESS FULL                   | PRODUCT_INFORMATION | 1000 |   8000 |      9 | 00:00:01 |
|    7 |       NESTED LOOPS                        |                     | 2328 |  79152 | 198868 | 00:00:08 |
|    8 |        NESTED LOOPS                       |                     | 2328 |  79152 | 198868 | 00:00:08 |
|    9 |         PARTITION HASH ALL                |                     |  463 |   7871 | 196553 | 00:00:08 |
| * 10 |          TABLE ACCESS FULL                | ORDERS              |  463 |   7871 | 196553 | 00:00:08 |
| * 11 |         INDEX RANGE SCAN                  | ITEM_ORDER_IX       |    5 |        |      3 | 00:00:01 |
|   12 |        TABLE ACCESS BY GLOBAL INDEX ROWID | ORDER_ITEMS         |    5 |     85 |      5 | 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter(TIMESTAMP' 2007-01-01 13:30:00.000000000'>=TIMESTAMP' 2007-01-01 13:00:00.000000000')
* 3 - access("D"."PRODUCT_ID"="I"."PRODUCT_ID")
* 5 - access("I"."PRODUCT_ID"="OI"."PRODUCT_ID")
* 10 - filter(("O"."ORDER_DATE"<=TIMESTAMP' 2007-01-01 13:30:00.000000000' AND "O"."ORDER_DATE">=TIMESTAMP' 2007-01-01 13:00:00.000000000'))
* 11 - access("O"."ORDER_ID"="OI"."ORDER_ID")

---------------------------------------------------------------------------------------------
 Plan Number            : 2
 Plan Found             : Yes
 Plan Source            : Cursor Cache
 SQL ID                 : gtr9hy6x492p7
 Child Number           : 1
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "SOE"
 SQL Text               : SELECT /*+ gather_plan_statistics */ o.order_id,
                        SUM(oi.unit_price * oi.quantity) AS revenue,
                        p.category_id, o.order_date FROM orders o JOIN
                        order_items oi ON ( o.order_id = oi.order_id ) JOIN
                        products p ON ( p.product_id = oi.product_id ) WHERE
                        o.order_date BETWEEN TIMESTAMP '2007-01-01 13:00:00'
                        AND TIMESTAMP '2007-01-01 13:30:00' GROUP BY
                        o.order_id, p.category_id, o.order_date ORDER BY
                        o.order_id

Plan
-----------------------------

 Plan Hash Value  : 4255998723

---------------------------------------------------------------------------------------------------------------------
| Id   | Operation                                          | Name                | Rows | Bytes  | Cost | Time     |
---------------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                                   |                     |      |        | 2801 |          |
|    1 |   SORT GROUP BY                                    |                     | 2328 | 107088 | 2801 | 00:00:01 |
|  * 2 |    FILTER                                          |                     |      |        |      |          |
|  * 3 |     HASH JOIN RIGHT OUTER                          |                     | 2328 | 107088 | 2800 | 00:00:01 |
|    4 |      INDEX FAST FULL SCAN                          | PRD_DESC_PK         | 1000 |   4000 |    3 | 00:00:01 |
|  * 5 |      HASH JOIN                                     |                     | 2328 |  97776 | 2797 | 00:00:01 |
|    6 |       TABLE ACCESS FULL                            | PRODUCT_INFORMATION | 1000 |   8000 |    9 | 00:00:01 |
|    7 |       NESTED LOOPS                                 |                     | 2328 |  79152 | 2788 | 00:00:01 |
|    8 |        NESTED LOOPS                                |                     | 2328 |  79152 | 2788 | 00:00:01 |
|    9 |         TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | ORDERS              |  463 |   7871 |  472 | 00:00:01 |
| * 10 |          INDEX RANGE SCAN                          | ORD_ORDER_DATE_IX   |  467 |        |    5 | 00:00:01 |
| * 11 |         INDEX RANGE SCAN                           | ITEM_ORDER_IX       |    5 |        |    3 | 00:00:01 |
|   12 |        TABLE ACCESS BY GLOBAL INDEX ROWID          | ORDER_ITEMS         |    5 |     85 |    5 | 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter(TIMESTAMP' 2007-01-01 13:30:00.000000000'>=TIMESTAMP' 2007-01-01 13:00:00.000000000')
* 3 - access("D"."PRODUCT_ID"="I"."PRODUCT_ID")
* 5 - access("I"."PRODUCT_ID"="OI"."PRODUCT_ID")
* 10 - access("O"."ORDER_DATE">=TIMESTAMP' 2007-01-01 13:00:00.000000000' AND "O"."ORDER_DATE"<=TIMESTAMP' 2007-01-01 13:30:00.000000000')
* 11 - access("O"."ORDER_ID"="OI"."ORDER_ID")


Comparison Results (3):
-----------------------------
 1. Query block SEL$BD98F06C, Alias "O"@"SEL$1": Some lines (id: 9) in the
    reference plan are missing in the current plan.
 2. Query block SEL$BD98F06C, Alias "O"@"SEL$1": Some lines (id: 10) in the
    current plan are missing in the reference plan.
 3. Query block SEL$BD98F06C, Alias "O"@"SEL$1": Some columns (OPTIONS, ID,
    PARENT_ID, DEPTH, PARTITION_START, PARTITION_STOP) do not match between the
    reference plan (id: 10) and the current plan (id: 9).


---------------------------------------------------------------------------------------------
 Plan Number            : 3
 Plan Found             : Yes
 Plan Source            : Cursor Cache
 SQL ID                 : gtr9hy6x492p7
 Child Number           : 2
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "SOE"
 SQL Text               : SELECT /*+ gather_plan_statistics */ o.order_id,
                        SUM(oi.unit_price * oi.quantity) AS revenue,
                        p.category_id, o.order_date FROM orders o JOIN
                        order_items oi ON ( o.order_id = oi.order_id ) JOIN
                        products p ON ( p.product_id = oi.product_id ) WHERE
                        o.order_date BETWEEN TIMESTAMP '2007-01-01 13:00:00'
                        AND TIMESTAMP '2007-01-01 13:30:00' GROUP BY
                        o.order_id, p.category_id, o.order_date ORDER BY
                        o.order_id

Plan
-----------------------------

 Plan Hash Value  : 4255998723

---------------------------------------------------------------------------------------------------------------------
| Id   | Operation                                          | Name                | Rows | Bytes  | Cost | Time     |
---------------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                                   |                     |      |        | 2801 |          |
|    1 |   SORT GROUP BY                                    |                     | 2328 | 107088 | 2801 | 00:00:01 |
|  * 2 |    FILTER                                          |                     |      |        |      |          |
|  * 3 |     HASH JOIN RIGHT OUTER                          |                     | 2328 | 107088 | 2800 | 00:00:01 |
|    4 |      INDEX FAST FULL SCAN                          | PRD_DESC_PK         | 1000 |   4000 |    3 | 00:00:01 |
|  * 5 |      HASH JOIN                                     |                     | 2328 |  97776 | 2797 | 00:00:01 |
|    6 |       TABLE ACCESS FULL                            | PRODUCT_INFORMATION | 1000 |   8000 |    9 | 00:00:01 |
|    7 |       NESTED LOOPS                                 |                     | 2328 |  79152 | 2788 | 00:00:01 |
|    8 |        NESTED LOOPS                                |                     | 2328 |  79152 | 2788 | 00:00:01 |
|    9 |         TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | ORDERS              |  463 |   7871 |  472 | 00:00:01 |
| * 10 |          INDEX RANGE SCAN                          | ORD_ORDER_DATE_IX   |  467 |        |    5 | 00:00:01 |
| * 11 |         INDEX RANGE SCAN                           | ITEM_ORDER_IX       |    5 |        |    3 | 00:00:01 |
|   12 |        TABLE ACCESS BY GLOBAL INDEX ROWID          | ORDER_ITEMS         |    5 |     85 |    5 | 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter(TIMESTAMP' 2007-01-01 13:30:00.000000000'>=TIMESTAMP' 2007-01-01 13:00:00.000000000')
* 3 - access("D"."PRODUCT_ID"="I"."PRODUCT_ID")
* 5 - access("I"."PRODUCT_ID"="OI"."PRODUCT_ID")
* 10 - access("O"."ORDER_DATE">=TIMESTAMP' 2007-01-01 13:00:00.000000000' AND "O"."ORDER_DATE"<=TIMESTAMP' 2007-01-01 13:30:00.000000000')
* 11 - access("O"."ORDER_ID"="OI"."ORDER_ID")


Notes
-----
- cardinality_feedback = yes


Comparison Results (3):
-----------------------------
 1. Query block SEL$BD98F06C, Alias "O"@"SEL$1": Some lines (id: 9) in the
    reference plan are missing in the current plan.
 2. Query block SEL$BD98F06C, Alias "O"@"SEL$1": Some lines (id: 10) in the
    current plan are missing in the reference plan.
 3. Query block SEL$BD98F06C, Alias "O"@"SEL$1": Some columns (OPTIONS, ID,
    PARENT_ID, DEPTH, PARTITION_START, PARTITION_STOP) do not match between the
    reference plan (id: 10) and the current plan (id: 9).


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

How very nice! So Oracle reviews the plans with regards to the reference and point out what’s different.

Summary

DBMS_XPLAN.COMPARE_PLANS looks like a great addition to the package and it helps discovering differences between SQL execution plans. The report-which looks pretty in HTML by the way-points out differences in columns as well as rows: additional/missing lines are pointed out as well as different access paths as you can see in the report above.

I should point out again that I haven’t studied the license guide, as always please ensure you are appropriately licensed for all technology you use.

Happy troubleshooting!

Configuring a VM using Ansible via the OCI Bastion Service

In my previous post I wrote about the creation of a Bastion Service using Terraform. As I’m incredibly lazy I prefer to configure the system pointed at by my Bastion Session with a configuration management tool. If you followed my blog for a bit you might suspect that I’ll use Ansible for that purpose. Of course I do! The question is: how do I configure the VM accessible via a Bastion Session?

Background

Please have a look at my previous post for a description of the resources created. In a nutshell the Terraform code creates a Virtual Cloud Network (VCN). There is only one private subnet in the VCN. A small VM without direct access to the Internet resides in the private subet. Another set of Terraform code creates a bastion session allowing me to connect to the VM.

I wrote this post on Ubuntu 20.04 LTS using ansible 4.8/ansible-core 2.11.6 by the way. From what I can tell these were current at the time of writing.

Connecting to the VM via a Bastion Session

The answer to “how does one connect to a VM via a Bastion Session?” isn’t terribly difficult once you know how to. The clue to my solution is with the SSH connection string as shown by the Terraform output variable. It prints the contents of oci_bastion_session.demo_bastionsession.ssh_metadata.command

$ terraform output
connection_details = "ssh -i <privateKey> -o ProxyCommand=\"ssh -i <privateKey> -W %h:%p -p 22 ocid1.bastionsession.oc1.eu-frankfurt-1.a...@host.bastion.eu-frankfurt-1.oci.oraclecloud.com\" -p 22 opc@10.0.2.39"

If I can connect to the VM via SSH I surely can do so via Ansible. As per the screen output above you can see that the connection to the VM relies on a proxy in form of the bastion session. See man 5 ssh_config for details. Make sure to provide the correct SSH keys in both locations as specified in the Terraform code. I like to think of the proxy session as a Jump Host to my private VM (its internal IP is 10.0.2.39). And yes, I am aware of alternative options to SSH, the one shown above however is the most compatible (to my knowledge).

Creating an Ansible Inventory and running a playbook

Even though it’s not the most flexible option I’m a great fan of using Ansible inventories. The use of an inventory saves me from typing a bunch of options on the command line.

Translating the Terraform output into the inventory format, this is what worked for me:

[blogpost]
privateinst ansible_host=10.0.2.39 ansible_user=opc ansible_ssh_common_args='-o ProxyCommand="ssh -i ~/.oci/oci_rsa -W %h:%p -p 22 ocid1.bastionsession.oc1.eu-frankfurt-1.a...@host.bastion.eu-frankfurt-1.oci.oraclecloud.com"'

Let’s run some Ansible code! Consider this playbook:

- hosts: blogpost
  tasks:
  - name: say hello
    ansible.builtin.debug:
      msg: hello from {{ ansible_hostname }}

With the inventory set, it’s now possible to run the playbook:

$ ansible-playbook -vi inventory.ini blogpost.yml 
Using /tmp/ansible/ansible.cfg as config file

PLAY [blogpost] *********************************************************************************************************

TASK [Gathering Facts] **************************************************************************************************
ok: [privateinst]

TASK [say hello] ********************************************************************************************************
ok: [privateinst] => {}

MSG:

hello from privateinst

PLAY RECAP **************************************************************************************************************
privateinst                : ok=2    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

The playbook is of course very simple, but it can be easily extended. The tricky bit was establishing the connection, once the connection is established the sky is the limit!

Create an OCI bastion service via Terraform

Maintaining bastion hosts (a “jump box” or other network entry point directly exposed to the Internet) is somewhat frowned upon by security conscious architects, for good reasons. In my opinion the only way to connect on-premises systems to the cloud is by means of a dedicated, low-latency/high-bandwidth, and most importantly well-secured link.

I never liked the idea of exposing systems to the Internet – too much can go wrong and you’d be surprised about the number of port-scans you see, followed by attempts at breaking in. Sometimes of course opening a system to the Internet is unavoidable: a website offering services to the public is quite secure if it cannot be reached but won’t generate a lot of revenue that way. Thankfully there are ways to expose such applications safely to the Internet, a topic that’s out of scope of this post though.

My very personal need for the bastion service

I create lots of demos using Oracle Cloud Infrastructure (OCI) and setting up a dedicated link isn’t always practical. The solution for me is to use Oracle’s bastion service. This way I can ensure time-based secure access to my resources in a private subnet. Most importantly there is no need to connect a VM directly to the Internet. And since it’s all fully automated it doesn’t cause any more work than terraform up followed by a terraform destroy when the demo completed.

This blog post describes how I create a VCN with a private subnet containing a VM. The entire infrastructure is intended as a DEMO only. None of the resources will live longer than for the duration of a conference talk. Please don’t follow this approach if you would like to deploy systems in the cloud for > 45 minutes. Also be aware that it’s entirely possible for you to incur cost when calling terraform up on the code. As always, the code will be available on Github.

Creating a Bastion Service

The bastion service is created by Terraform. Following the advice from the excellent Terraform Up and Running (2nd ed) I separated the resource creation into three directories:

  • Network
  • Compute
  • Bastion

To keep things reasonably simple I refrained from creating modules.

Directory layout

Please have a look at the book for more details about the directory structure. You’ll notice that I simplified the example a little.

$ tree .
.
├── bastionsvc
│   ├── main.tf
│   ├── terraform.tfstate
│   └── variables.tf
├── compute
│   ├── compute.tf
│   ├── main.tf
│   ├── outputs.tf
│   ├── terraform.tfstate
│   ├── terraform.tfstate.backup
│   └── variables.tf
├── network
│   ├── network.tf
│   ├── outputs.tf
│   ├── terraform.tfstate
│   ├── terraform.tfstate.backup
│   └── variables.tf
├── readme.md
└── variables.tf

I decided to split the network code into a generic section and the bastion service for reason explained later.

Generic Network Code

The network code is responsible for creating the Virtual Cloud Network (VCN) including subnets, security lists, necessary gateways etc. When I initially used the bastion service I struggled a bit with Network Security Groups (NSG) and went with a security list instead. I guess I should re-visit that decision at some point.

The network must be created first. In addition to creating all the necessary infrastructure it exports an output variable used by the compute and bastion code. These read remote state to get the necessary OCIDs.

Note that the choice of a remote data source has its drawbacks as described in the documentation. These don’t apply for my demos as I’m the only user of the code. And while I’m at it, using local state is acceptable only because I know I’m the only one using the code. Local state doesn’t necessarily work terribly well for team-development.

Here are some key features of the network code. As these tend to go stale over time, have a look at the Github repository for the latest and greatest revision.

resource "oci_core_vcn" "vcn" {

  compartment_id = var.compartment_ocid
  cidr_block     = "10.0.2.0/24"
  defined_tags   = var.network_defined_tags
  display_name   = "demovcn"
  dns_label      = "demo"

}

# --------------------------------------------------------------------- subnet

resource "oci_core_subnet" "private_subnet" {

  cidr_block                 = var.private_sn_cidr_block
  compartment_id             = var.compartment_ocid
  vcn_id                     = oci_core_vcn.vcn.id
  defined_tags               = var.network_defined_tags
  display_name               = "private subnet"
  dns_label                  = "private"
  prohibit_public_ip_on_vnic = true
  prohibit_internet_ingress  = true
  route_table_id             = oci_core_route_table.private_rt.id
  security_list_ids          = [
    oci_core_security_list.private_sl.id
  ]
}

The security list allows SSH only from within the same subnet:

# --------------------------------------------------------------------- security list

resource "oci_core_security_list" "private_sl" {

  compartment_id = var.compartment_ocid
  vcn_id         = oci_core_vcn.vcn.id

...

  egress_security_rules {

    destination = var.private_sn_cidr_block
    protocol    = "6"

    description      = "SSH outgoing"
    destination_type = ""

    stateless = false
    tcp_options {

      max = 22
      min = 22

    }
  }

  ingress_security_rules {

    protocol = "6"
    source   = var.private_sn_cidr_block

    description = "SSH inbound"

    source_type = "CIDR_BLOCK"
    tcp_options {

      max = 22
      min = 22

    }

  }
}

The bastion service and its corresponding session are going to be created in the same private subnet as the compute instance for the sake of simplicity.

Compute Instance

The compute instance is created as a VM.Standard.E3.Flex shape with 2 OCPUs. There’s nothing too special about the resource, except maybe that I’m explicitly enabling the bastion plugin agent, a prerequisite for using the service.

resource "oci_core_instance" "private_instance" {
  agent_config {
    is_management_disabled = false
    is_monitoring_disabled = false

...

    plugins_config {
      desired_state = "ENABLED"
      name = "Bastion"
    }
  }

  defined_tags = var.compute_defined_tags

  create_vnic_details {
    
    assign_private_dns_record = true
    assign_public_ip = false
    hostname_label = "privateinst"
    subnet_id = data.terraform_remote_state.network_state.outputs.private_subnet_id
    nsg_ids = []
  }

...

Give it a couple of minutes for all agents to start.

Bastion Service

Once the VM’s bastion agent is up it is possible to create the bastion service:

resource "oci_bastion_bastion" "demo_bastionsrv" {

  bastion_type     = "STANDARD"
  compartment_id   = var.compartment_ocid
  target_subnet_id = data.terraform_remote_state.network_state.outputs.private_subnet_id

  client_cidr_block_allow_list = [
    var.local_laptop_id
  ]

  defined_tags = var.network_defined_tags

  name = "demobastionsrv"
}


resource "oci_bastion_session" "demo_bastionsession" {

  bastion_id = oci_bastion_bastion.demo_bastionsrv.id
  defined_tags = var.network_defined_tags
  
  key_details {
  
    public_key_content = var.ssh_bastion_key
  }

  target_resource_details {

    session_type       = "MANAGED_SSH"
    target_resource_id = data.terraform_remote_state.compute_state.outputs.private_instance_id

    target_resource_operating_system_user_name = "opc"
    target_resource_port                       = "22"
  }

  session_ttl_in_seconds = 3600

  display_name = "bastionsession-private-host"
}

output "connection_details" {
  value = oci_bastion_session.demo_bastionsession.ssh_metadata.command
}

The Bastion is set up in the private subnet created by the network code. Note that I’m defining the session’s client_cidr_block_allow_list specifically to only allow my external IP to access the service. The session is of type Managed SSH, thus requires a Linux host.

And this is all I can say about the creation of a bastion session in Terraform.

Terraform in action

Once all the resources have been created all I need to do is adapt the SSH command provided by my output variable shown here:

connection_details = "ssh -i <privateKey> -o ProxyCommand=\"ssh -i <privateKey> -W %h:%p -p 22 ocid1.bastionsession.oc1.eu-frankfurt-1.am...@host.bastion.eu-frankfurt-1.oci.oraclecloud.com\" -p 22 opc@10.0.2.94"

After adopting the SSH command I can connect to the instance.

$ ssh -i ...
The authenticity of host '10.0.2.94 (<no hostip for proxy command>)' can't be established.
ECDSA key fingerprint is SHA256:Ot...
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '10.0.2.94' (ECDSA) to the list of known hosts.
Activate the web console with: systemctl enable --now cockpit.socket

[opc@privateinst ~]$ hostname
privateinst
[opc@privateinst ~]$ logout

That’s it! I am connected to the instance and experiment with my demo.

Another reason I love Terraform: when the demo has concluded I can simply tear down all resources with very few commands.

The performance story that didn’t turn out as one: a case of or-expansion and how the database mitigated the problem on its own

Recently I had a bit of time to study the effect of an optimizer query transformation, the so-called or-expansion in Oracle 19c. I thought there might be performance implications with statements using bind variables transformed in this way. My limited testing suggests that isn’t necessarily the case as the optimiser is remarkably resilient.

Still I thought my investigation was worth writing down, I hope you might agree and better still, find the read enjoyable.

Excuse me, what exactly is an Or-Expansion?

Quoting from the SQL Tuning Guide, during an “or expansion the optimiser transforms a query block containing top-level disjunctions into the form of a UNION ALL query the contains 2 or more branches”.

The remainder of this article sheds some light on the query transformation. By the way, there are many, many other blog articles out there covering or-expansion. Some even include an analysis of a 10053 trace! The subject is also covered in Troubleshooting Oracle Performance by Christian Antognini, a great read.

Example setup

This is one of the few times I deviated from my trusted Swingbench environment. Instead I created the following segments in my 19.12.0 database running on Oracle Linux 7.9/UEK 6.

CREATE TABLE skewed_data_distribution
    AS
        WITH generator AS (
            SELECT
                ROWNUM id
            FROM
                dual
            CONNECT BY
                ROWNUM <= 4000
        )
        SELECT
            ROWNUM                    AS id,
            CASE
                WHEN mod(ROWNUM, 100000) = 0     THEN 1
                WHEN mod(ROWNUM, 1000) = 0       THEN 2
                WHEN mod(ROWNUM, 100) = 0        THEN 3
                ELSE 4
            END                       AS skew,
            lpad('*', 150, '*')       AS pad,
            sysdate + dbms_random.value(-1000,0) as datecol
        FROM
            generator,
            generator
        WHERE
            ROWNUM <= 10e6;

CREATE INDEX i_skew_1 ON
    skewed_data_distribution (
        id
    );

CREATE INDEX i_skew_2 ON
    skewed_data_distribution (
        skew
    );

BEGIN
 dbms_stats.gather_table_stats(
  ownname => 'MARTIN', 
  tabname => 'SKEWED_DATA_DISTRIBUTION',
  method_opt => 'for all columns size auto, for columns size 254 skew',
  degree => 4
 );
END;
/

Thanks to Jonathan Lewis for elaborating on how to create sample data safely.

By the way I didn’t enable any of the fix_controls that come with the 19.12 Release Update (RU). If you just raised an eyebrow, please have look at Mike Dietrich’s blog for details about a potential call to DBMS_OPTIM_BUNDLE after applying a RU.

The query I’ll use for this article is this (it’s designed to trigger an or-expansion).

var the_id number
var the_skew number

WITH q AS (
    SELECT id,
        skew
    FROM
        skewed_data_distribution
    WHERE
        id = :the_id
        OR skew = :the_skew
)
SELECT /*+ gather_plan_statistics opt_param('_b_tree_bitmap_plans','false') */
    COUNT(*)
FROM
    q;

Please ignore the fact that it is a rather useless SQL statement on its own, but it helped me create a test case. All I needed was a table, some indexes and a suitable data distribution as well as a histogram on SKEW, otherwise the optimiser probably wouldn’t have considered the use of the index, but I’m getting ahead of myself.

When I first tested the query I didn’t get the or-expansion I wanted, but rather this:

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3063879156

---------------------------------------------------------------------------------------------
| Id  | Operation                        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |          |     1 |     9 |  4872   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE                  |          |     1 |     9 |            |          |
|   2 |   BITMAP CONVERSION COUNT        |          |  2500K|    21M|  4872   (1)| 00:00:01 |
|   3 |    BITMAP OR                     |          |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|          |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | I_SKEW_2 |       |       |  4860   (1)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|          |       |       |            |          |
|*  7 |      INDEX RANGE SCAN            | I_SKEW_1 |       |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("SKEW"=TO_NUMBER(:THE_SKEW))
   7 - access("ID"=TO_NUMBER(:THE_ID))

20 rows selected.

That bitmap conversion wasn’t quite what I had in mind, hence the opt_param hint in the query text.

Data distribution

The following detail about data distribution might help understand the article better:

REM data distribution: SKEW

SELECT /*+ parallel */
    COUNT(*),
    skew
FROM
    skewed_data_distribution
GROUP BY
    skew;

  COUNT(*)       SKEW
---------- ----------
       100          1
      9900          2
     90000          3
   9900000          4

4 rows selected.

REM Histograms  

SELECT
    column_name,
    histogram,
    num_buckets,
    column_id
FROM
    user_tab_columns
WHERE
    table_name = 'SKEWED_DATA_DISTRIBUTION';

COLUMN_NAM HISTOGRAM           NUM_BUCKETS       COLUMN_ID
---------- --------------- --------------- ---------------
ID         NONE                          1               1
SKEW       FREQUENCY                     4               2
PAD        NONE                          1               3
DATECOL    NONE                          1               4

4 rows selected.

REM Histogram on SKEW

SELECT
    endpoint_number,
    endpoint_value
FROM
    user_tab_histograms
WHERE
        table_name = 'SKEWED_DATA_DISTRIBUTION'
    AND column_name = 'SKEW';

ENDPOINT_NUMBER  ENDPOINT_VALUE
--------------- ---------------
            100               1
          10000               2
         100000               3
       10000000               4

4 rows selected.

Workload

I am going to run 2 scripts a few times to simulate a query workload. The scripts differ in their bind variable values:

$ diff -y skew_unselective.sql skew_selective.sql
var the_id number						var the_id number
var the_skew number						var the_skew number

exec :the_id := 100						exec :the_id := 100
exec :the_skew := 4					      |	exec :the_skew := 1

WITH q AS (							WITH q AS (
    SELECT id,							    SELECT id,
        skew							        skew
    FROM							    FROM
        skewed_data_distribution				        skewed_data_distribution
    WHERE							    WHERE
        id = :the_id						        id = :the_id
        OR skew = :the_skew					        OR skew = :the_skew
)								)
SELECT /*+ gather_plan_statistics opt_param('_b_tree_bitmap_p	SELECT /*+ gather_plan_statistics opt_param('_b_tree_bitmap_p
    COUNT(*)							    COUNT(*)
FROM								FROM
    q;								    q;

Selective bind variables

Using my runMany.sh script I launched 20 instances of the more selective query first. The expected execution plan is as follows:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID  8tjz2tqn9gtck, child number 0
-------------------------------------
WITH q AS (     SELECT id,         skew     FROM
skewed_data_distribution     WHERE         id = :the_id         OR skew
= :the_skew ) SELECT /*+ gather_plan_statistics
opt_param('_b_tree_bitmap_plans','false') */     COUNT(*) FROM     q

Plan hash value: 414689775

---------------------------------------------------------------------------...
| Id  | Operation                              | Name                     |...
---------------------------------------------------------------------------...
|   0 | SELECT STATEMENT                       |                          |...
|   1 |  SORT AGGREGATE                        |                          |...
|   2 |   VIEW                                 | VW_ORE_9774CF0C          |...
|   3 |    UNION-ALL                           |                          |...
|*  4 |     INDEX RANGE SCAN                   | I_SKEW_1                 |...
|*  5 |     TABLE ACCESS BY INDEX ROWID BATCHED| SKEWED_DATA_DISTRIBUTION |...
|*  6 |      INDEX RANGE SCAN                  | I_SKEW_2                 |...
---------------------------------------------------------------------------...

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (NUMBER): 100
   2 - :2 (NUMBER): 1

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ID"=:THE_ID)
   5 - filter(LNNVL("ID"=:THE_ID))
   6 - access("SKEW"=:THE_SKEW)


34 rows selected.

In other words, you can witness the result of the query transformation. Also note the VIEW VW_ORE%: it doesn’t exist in the database, it only exists thanks to the query transformation. I think this is a prime use case for the or-expansion: by splitting the or condition in the where clause into separate statements Oracle can make use of the indexes.

Really unselective predicate

So here I thought the trouble might arise: what if instead of a small-ish subset of the rows Oracle has to return the majority of the table instead? It can be as simple as replacing the value for SKEW from a selective-ish predicate to a highly unselective one. Which doesn’t trigger a hard parse due to a change the query text.

Let’s recap. So far, I have 1 execution plan for my statement in the shared pool, covering all executions up to now (29 to be precise):

SELECT
    sql_id,
    plan_hash_value,
    child_number,
    executions,
    is_bind_aware,
    is_bind_sensitive
FROM
    v$sql
WHERE
    sql_id = '8tjz2tqn9gtck';

SQL_ID        PLAN_HASH_VALUE    CHILD_NUMBER      EXECUTIONS I I
------------- --------------- --------------- --------------- - -
8tjz2tqn9gtck       414689775               0              29 N Y

Let’s try the second query. Using runMany.sh I launched skew_unselective.sql 10 times against the database. After they completed, I checked the shared pool again:

SELECT
    sql_id,
    plan_hash_value,
    child_number,
    executions,
    is_bind_aware,
    is_bind_sensitive
FROM
    v$sql
WHERE
    sql_id = '8tjz2tqn9gtck';

SQL_ID        PLAN_HASH_VALUE    CHILD_NUMBER      EXECUTIONS I I
------------- --------------- --------------- --------------- - -
8tjz2tqn9gtck       414689775               0              39 N Y

So here is a problem: The next batch of my queries used the “wrong”, or rather suboptimal plan. This eventually results and longer elapsed time/query. However, during my tests-and I appreciate it’s a bit limited in scope-I noticed that the next time I ran the un-selective query, another child cursor appeared:

SELECT
    sql_id,
    plan_hash_value,
    child_number,
    executions,
    is_bind_aware,
    is_bind_sensitive
FROM
    v$sql
WHERE
    sql_id = '8tjz2tqn9gtck';

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS I I
------------- --------------- ------------ ---------- - -
8tjz2tqn9gtck       414689775            0         39 N Y
8tjz2tqn9gtck      1662074091            1          1 N Y

SQL> select * from dbms_xplan.display_cursor('8tjz2tqn9gtck',1);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID  8tjz2tqn9gtck, child number 1
-------------------------------------
WITH q AS (     SELECT id,         skew     FROM
skewed_data_distribution     WHERE         id = :the_id         OR skew
= :the_skew ) SELECT /*+ gather_plan_statistics
opt_param('_b_tree_bitmap_plans','false') */     COUNT(*) FROM     q

Plan hash value: 1662074091

-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                          |       |       | 66204 (100)|          |
|   1 |  SORT AGGREGATE    |                          |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEWED_DATA_DISTRIBUTION |  9900K|    84M| 66204   (1)| 00:00:03 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("SKEW"=:THE_SKEW OR "ID"=:THE_ID))


22 rows selected.

Now that’s better! With the totally un-selective predicate it doesn’t make sense to use the index. The full scan is a far better choice. What happens when I run the selective query again?

SQL> @skew_selective

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

  COUNT(*)
----------
       101

Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  8tjz2tqn9gtck, child number 2
-------------------------------------
WITH q AS (     SELECT id,         skew     FROM
skewed_data_distribution     WHERE         id = :the_id         OR skew
= :the_skew ) SELECT /*+ gather_plan_statistics
opt_param('_b_tree_bitmap_plans','false') */     COUNT(*) FROM     q

Plan hash value: 414689775

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                     | Starts | E-Rows |E-Bytes| Cost (%CPU)|
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                          |      1 |        |       |    10 (100)|
|   1 |  SORT AGGREGATE                        |                          |      1 |      1 |       |            |
|   2 |   VIEW                                 | VW_ORE_9774CF0C          |      1 |    101 |       |    10   (0)|
|   3 |    UNION-ALL                           |                          |      1 |        |       |            |
|*  4 |     INDEX RANGE SCAN                   | I_SKEW_1                 |      1 |      1 |     6 |     3   (0)|
|*  5 |     TABLE ACCESS BY INDEX ROWID BATCHED| SKEWED_DATA_DISTRIBUTION |      1 |    100 |   900 |     7   (0)|
|*  6 |      INDEX RANGE SCAN                  | I_SKEW_2                 |      1 |    100 |       |     3   (0)|
------------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (NUMBER): 100
   2 - :2 (NUMBER): 1

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ID"=:THE_ID)
   5 - filter(LNNVL("ID"=:THE_ID))
   6 - access("SKEW"=:THE_SKEW)

SELECT
    sql_id,
    plan_hash_value,
    child_number,
    executions,
    is_bind_aware,
    is_bind_sensitive
FROM
    v$sql
WHERE
    sql_id = '8tjz2tqn9gtck';

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS I I
------------- --------------- ------------ ---------- - -
8tjz2tqn9gtck       414689775            0         39 N Y
8tjz2tqn9gtck      1662074091            1          1 N Y
8tjz2tqn9gtck       414689775            2          1 Y Y

After a few more tries the number of child cursors settled down at 4:

SQL> SELECT
    sql_id,
    plan_hash_value,
    child_number,
    executions,
    is_bind_aware,
    is_bind_sensitive
FROM
    v$sql
WHERE
    sql_id = '8tjz2tqn9gtck';

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS I I
------------- --------------- ------------ ---------- - -
8tjz2tqn9gtck       414689775            0         39 N Y
8tjz2tqn9gtck      1662074091            1          1 N Y
8tjz2tqn9gtck       414689775            2          5 Y Y
8tjz2tqn9gtck      1662074091            3          7 Y Y

Child cursors 0 and 1 haven’t seen further executions while 2 and 3 do.

Summary

My-admittedly limited-amount of testing suggests that it is indeed possible to use or-expansion successfully even with huge data skew and bind variables in 19.12.0. All of my database’s parameters remained at their default with the exception of _b_tree_bitmap_plans to prevent these from appearing.

And many thanks, as always, to Tanel Poder for sharing his scripts with the community. They have been taken to good use writing this post.