Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Silent installation: Oracle Restart 19c, ASM Filter Driver, RHCK edition

As promised in the earlier post here are my notes about installing Oracle Restart 19c on Oracle Linux 7.7 using the RedHat compatible kernel (RHCK). Please consult the ACFS/ASMFD compatibility matrix, My Oracle Support DocID 1369107.1 for the latest information about ASMFD compatibility with various kernels as well.

Why am I starting the series with a seemingly “odd” kernel, at least from the point of view of Oracle Linux? If you try to install the Oracle Restart base release with UEK 5, you get strange error messages back from gridSetup telling you about invalid ASM disks. While that’s probably true, it’s a secondary error. The main cause of the problem is this:

[root@server5 bin]# ./afddriverstate supported
AFD-620: AFD is not supported on this operating system version: '4.14.35-1902.300.11.el7uek.x86_64'
AFD-9201: Not Supported
AFD-9294: updating file /etc/sysconfig/oracledrivers.conf 

Which is easy to run into since gridSetup.sh doesn’t validate this for you when running in silent mode. The GUI version of the installer protects you from the mistake though. Upgrading to the latest UEK 5 doesn’t change this message, you need to check the certification matrix to learn that Oracle Restart 19.4.0 and later are required for UEK 5 if you’d like to use ASMFD (or ACFS for that matter). This scenario will be covered in a later post.

Using the Red Hat Compatible Kernel alleviates this problem for me. Just be aware of the usual caveats when using the Red Hat Kernel on Oracle Linux such as YUM changing the default kernel during yum upgrade etc. I’d also like to iterate that this post isn’t an endorsement for ASM Filter Driver, but since the documentation was a little unclear I thought I’d write up how I got to a working installation. It is up to you to ensure that ASMFD is a workable solution for your environment by following industry best known practices.

Configuration Options

In the post introducing this series I claimed to have identified 2 options for installing Oracle Restart 19c using ASMFD: the first one is to use UDEV to prepare ASM block devices, the second one is to label the ASM disks using asmcmd afd_label.

Huh, UDEV? That hasn’t really been blogged about at all in the context of ASMFD, or at least I didn’t find anyone who did. I’m inferring the possibility of using UDEV from “Configuring Oracle ASM Filter Driver During Installation” (link to documentation):

“If you do not use udev on the system where the Oracle Grid Infrastructure is installed, then you can also complete the following procedure to provision disks for Oracle ASMFD before the installer is launched”

You actually only have to choose one of them. Let’s start with the more frequently covered approach of labelling disks using asmcmd.

My environment

I have applied all the patches to this environment up to March 26th to my lab enviroment. The Oracle Linux release I’m using is 7.7:

[root@server4 ~]# cat /etc/oracle-release
Oracle Linux Server release 7.7 

The KVM VM I’m using for this blog post uses the latest Red Hat Compatible Kernel at the time of writing (kernel-3.10.0-1062.18.1.el7.x86_64). You will notice that I’m using the virtio driver, leading to “strange” device names. Instead of /dev/sd it’s /dev/vd. My first two block devices are reserved for the O/S and Oracle, the remaining ones are going to be used for ASM. I have an old (bad?) habit of partitioning block devices for ASM as you might notice. Most of the Oracle setup is done by the 19c preinstall RPM, which I used.

I created a grid owner – grid – to own the Oracle Restart installation. Quite a few blog posts I came across referenced group membership, and I’d like to do the same:

[root@server4 ~]# id -a grid 
uid=54322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmadmin),54327(asmdba) 

The block devices I’m intending to use for ASM are /dev/vdc to /dev/vdf – the first 2 are intended for +DATA, the other 2 will become part of +RECO. As you can see they are partitioned:

[root@server4 ~]# lsblk --ascii
NAME                  MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
vdf                   251:80   0   10G  0 disk 
`-vdf1                251:81   0   10G  0 part 
vdd                   251:48   0   10G  0 disk 
`-vdd1                251:49   0   10G  0 part 
vdb                   251:16   0   50G  0 disk 
`-vdb1                251:17   0   50G  0 part 
  `-oraclevg-orabinlv 252:2    0   50G  0 lvm  /u01
sr0                    11:0    1 1024M  0 rom  
vde                   251:64   0   10G  0 disk 
`-vde1                251:65   0   10G  0 part 
vdc                   251:32   0   10G  0 disk 
`-vdc1                251:33   0   10G  0 part 
vda                   251:0    0   12G  0 disk 
|-vda2                251:2    0 11.5G  0 part 
| |-rootvg-swaplv     252:1    0  768M  0 lvm  [SWAP]
| `-rootvg-rootlv     252:0    0 10.8G  0 lvm  /
`-vda1                251:1    0  500M  0 part /boot  

With all that out of the way it is time to cover the installation.

Labeling disks

I’m following the procedure documented in the 19c Administrator’s Guide chapter 20, section “Configuring Oracle ASM Filter Driver During Installation”. I have prepared my environment up to the step where I’d have to launch gridSetup.sh. This is a fairly well known process, and I won’t repeat it here.

Once the 19c install image has been extracted to my future Grid Home, the first step is to check if my system is supported:

[root@server4 ~]# cd /u01/app/grid/product/19.0.0/grid/bin
[root@server4 bin]# ./afddriverstate supported
AFD-9200: Supported 
[root@server4 bin]# uname -r
3.10.0-1062.18.1.el7.x86_64 

“AFD-9200: Supported” tells me that I can start labeling disks. This requires me to be root, and I have to set ORACLE_HOME and ORACLE_BASE. For some reason, the documentation suggests using /tmp as ORACLE_BASE, which I’ll use as well:

[root@server4 bin]# pwd
/u01/app/grid/product/19.0.0/grid/bin
[root@server4 bin]# export ORACLE_BASE=/tmp
[root@server4 bin]# export ORACLE_HOME=/u01/app/grid/product/19.0.0/grid
[root@server4 bin]# ./asmcmd afd_label DATA1 /dev/vdc1 --init
[root@server4 bin]# ./asmcmd afd_label DATA2 /dev/vdd1 --init 

[root@server4 bin]# ./asmcmd afd_lslbl /dev/vdc1
--------------------------------------------------------------------------------
Label                     Duplicate  Path
================================================================================
DATA1                                 /dev/vdc1

[root@server4 bin]# ./asmcmd afd_lslbl /dev/vdd1
--------------------------------------------------------------------------------
Label                     Duplicate  Path
================================================================================
DATA2                                 /dev/vdd1  

Note the use of the –init flag. This is only needed if Grid Infrastructure isn’t installed yet.

Labeling the disks did not have an effect on the block devices’ permissions. Right after finishing the 2 calls to label my 2 block devices, this is the output from my file system:

[root@server4 bin]# ls -l /dev/vd[c-d]*
brw-rw----. 1 root disk 252, 32 Mar 27 09:46 /dev/vdc
brw-rw----. 1 root disk 252, 33 Mar 27 12:55 /dev/vdc1
brw-rw----. 1 root disk 252, 48 Mar 27 09:46 /dev/vdd
brw-rw----. 1 root disk 252, 49 Mar 27 12:58 /dev/vdd1
[root@server4 bin]#  

The output of afd_lslbl indicated that both of my disks are ready to become part of an ASM disk group, so let’s start the installer.

Call gridSetup.sh

I haven’t been able to make sense of the options in the response file until I started the installer in GUI mode and created a response file based on my choices. To cut a long story short, here is my call to gridSetup.sh:

[grid@server4 ~]$ /u01/app/grid/product/19.0.0/grid/gridSetup.sh -silent \
> INVENTORY_LOCATION=/u01/app/oraInventory \
> SELECTED_LANGUAGES=en \
> ORACLE_BASE=/u01/app/grid \
> ORACLE_HOME_NAME=ASMFD_RHCK \
> -waitforcompletion -ignorePrereqFailure -lenientInstallMode \
> oracle.install.option=HA_CONFIG \
> oracle.install.asm.OSDBA=asmdba \
> oracle.install.asm.OSASM=asmadmin \
> oracle.install.asm.diskGroup.name=DATA \
> oracle.install.asm.diskGroup.disks=/dev/vdc1,/dev/vdd1 \
> oracle.install.asm.diskGroup.diskDiscoveryString=/dev/vd* \
> oracle.install.asm.diskGroup.redundancy=EXTERNAL \
> oracle.install.asm.diskGroup.AUSize=4 \
> oracle.install.asm.configureAFD=true \
> oracle.install.crs.rootconfig.executeRootScript=false \
> oracle.install.asm.SYSASMPassword=thinkOfASuperSecretPassword \
> oracle.install.asm.monitorPassword=thinkOfASuperSecretPassword
Launching Oracle Grid Infrastructure Setup Wizard...

The response file for this session can be found at:
 /u01/app/grid/product/19.0.0/grid/install/response/grid_2020-03-27_01-06-14PM.rsp

You can find the log of this install session at:
 /tmp/GridSetupActions2020-03-27_01-06-14PM/gridSetupActions2020-03-27_01-06-14PM.log

As a root user, execute the following script(s):
        1. /u01/app/oraInventory/orainstRoot.sh
        2. /u01/app/grid/product/19.0.0/grid/root.sh

Execute /u01/app/grid/product/19.0.0/grid/root.sh on the following nodes:
[server4]

Successfully Setup Software.
As install user, execute the following command to complete the configuration.
/u01/app/grid/product/19.0.0/grid/gridSetup.sh -executeConfigTools -responseFile /u01/app/grid/product/19.0.0/grid/install/response/grid_2020-03-27_01-06-14PM.rsp [-silent]
Note: The required passwords need to be included in the response file.
Moved the install session logs to:
/u01/app/oraInventory/logs/GridSetupActions2020-03-27_01-06-14PM
[grid@server4 ~]$

It took a little while to work out that despite labeling the disks for ASMFD I didn’t have to put any reference to AFD into the call to gridSetup.sh. Have a look at the ASM disk string and the block devices: that’s what I’d use if I were using UDEV rules for device name persistence. The syntax might appear counter-intuitive. However there’s a “configureAFD” flag you need to set to true.

Since this is a lab environment I’m ok with external redundancy. Make sure you pick a redundancy level appropriate for your use case.

Running the configuration tools

The remaining steps are identical to a non ASMFD setup. First you run orainstRoot.sh followed by root.sh. The output of the latter showed this for me, indicating success:

[root@server4 ~]# /u01/app/grid/product/19.0.0/grid/root.sh
Check /u01/app/grid/product/19.0.0/grid/install/root_server4_2020-03-27_13-11-05-865019723.log for the output of root script

[root@server4 ~]#
[root@server4 ~]# cat /u01/app/grid/product/19.0.0/grid/install/root_server4_2020-03-27_13-11-05-865019723.log
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/app/grid/product/19.0.0/grid
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/grid/product/19.0.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/grid/crsdata/server4/crsconfig/roothas_2020-03-27_01-11-06PM.log
2020/03/27 13:11:13 CLSRSC-363: User ignored prerequisites during installation
LOCAL ADD MODE
Creating OCR keys for user 'grid', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node server4 successfully pinned.
2020/03/27 13:13:55 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'

server4     2020/03/27 13:16:59     /u01/app/grid/crsdata/server4/olr/backup_20200327_131659.olr     724960844
2020/03/27 13:17:54 CLSRSC-327: Successfully configured Oracle Restart for a standalone server
[root@server4 ~]# 

Well that looks ok, now on to the final step, configuration! As indicated in the output, you need to update the response (/u01/app/grid/product/19.0.0/grid/install/response/grid_2020-03-27_01-06-14PM.rsp) file with the required passwords. For me that was oracle.install.asm.monitorPassword and oracle.install.asm.SYSASMPassword. Once the response file was updated, I called gridSetup.sh once again:

[grid@server4 ~]$ /u01/app/grid/product/19.0.0/grid/gridSetup.sh -executeConfigTools -responseFile /u01/app/grid/product/19.0.0/grid/install/response/grid_2020-03-27_01-06-14PM.rsp -silent
Launching Oracle Grid Infrastructure Setup Wizard...

You can find the logs of this session at:
/u01/app/oraInventory/logs/GridSetupActions2020-03-27_01-20-47PM

You can find the log of this install session at:
 /u01/app/oraInventory/logs/UpdateNodeList2020-03-27_01-20-47PM.log
Successfully Configured Software. 

And that’s it! The software has been configured successfully. Don’t forget to remove the passwords from the response file!

Verification

After a little while I have been able to configure Oracle Restart 19c/ASMFD on Oracle Linux 7.7/RHCK. Let’s check what this implies.

I’ll first look at the status of ASM Filter Driver:

[grid@server4 ~]$ . oraenv
ORACLE_SID = [grid] ? +ASM
The Oracle base has been set to /u01/app/grid
[grid@server4 ~]$ afddriverstate installed
AFD-9203: AFD device driver installed status: 'true'
[grid@server4 ~]$ afddriverstate loaded
AFD-9205: AFD device driver loaded status: 'true'
[grid@server4 ~]$ afddriverstate version
AFD-9325:     Driver OS kernel version = 3.10.0-862.el7.x86_64.
AFD-9326:     Driver build number = 190222.
AFD-9212:     Driver build version = 19.0.0.0.0.
AFD-9547:     Driver available build number = 190222.
AFD-9548:     Driver available build version = 19.0.0.0.0.
[grid@server4 ~]$  

That’s encouraging: ASMFD is loaded and works on top of kernel-3.10 (RHCK)

I am indeed using the base release (and have to patch now!)

[grid@server4 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
29517247;ACFS RELEASE UPDATE 19.3.0.0.0 (29517247)
29517242;Database Release Update : 19.3.0.0.190416 (29517242)
29401763;TOMCAT RELEASE UPDATE 19.0.0.0.0 (29401763)

OPatch succeeded. 

And … I’m also using ASMFD:

SQL> col name for a20
SQL> col path for a10
SQL> col library for a50
SQL> set lines 120
SQL> select name, path, library from v$asm_disk where group_number <> 0;

NAME                 PATH       LIBRARY
-------------------- ---------- --------------------------------------------------
DATA1                AFD:DATA1  AFD Library - Generic , version 3 (KABI_V3)
DATA2                AFD:DATA2  AFD Library - Generic , version 3 (KABI_V3)

SQL> show parameter asm_diskstring

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskstring                       string      /dev/vd*, AFD:*
SQL>  

This concludes the setup of my lab environment.

Oracles Free TNS Firewall - VALIDNODE_CHECKING

I said in a post a couple of days ago that my overall plan to secure an Oracle database; actually my plan is to secure the data in an Oracle database not blindly just secure Oracle. We must focus on....[Read More]

Posted by Pete On 31/03/20 At 12:26 PM

Where to add Index on Postgres

 

Summary

Goal in this post is to layout a methodology that can be implemented to scan the explain plans and identify opportunities to optimize the sql execution with additional indexes.

The missing index opportunities outlined here are

  1. Indexes that would avoid full table scan with a predicate filter that filters out most of the returned rows
  2. Joins that are missing index on the join field
  3. Index range scans that could be made more efficient by addition of a field to existing index

Also for future consideration, but not yet outline here

  • Recommending covering index where the addition of fields to an index avoids table access all together
  • Indexes that support the order by clause

Basic procedure is to take the output from “explain” command.
In the explain output only look at nodes that are at least say 10% or more of total cost of the whole query.
Look for full table scans , these are nodes with the following entry : “Node Type”: “Seq Scan”
If it’s in the second node of a nested loop, suggest an index on the join field
If it’s is the most expensive node of a Hash join suggest an index on the join field
If it’s the most expense node of Merge Join suggest an index on the join field.
Otherwise if it is followed by a filter, suggest an index on the filter. For a filter make sure that the filter is expected to return X% ( for example 10% ) or less of the table after applying the filter.
Additionally Index range scans that could be improved by adding fields to the index will require using “explain analyze”. With explain analyze we can see how many rows were filtered out after the index access. Explain Plan command

Get plan with “Explain” and text from SQL statement

EXPLAIN (COSTS true, FORMAT json, verbose true)
select id from hypg1 where id = 2006;
                QUERY PLAN                 
-------------------------------------------
 [                                        +
   {                                      +
     "Plan": {                            +
       "Node Type": "Gather",             +
       "Parallel Aware": false,           +
       "Startup Cost": 1000.00,           +
       "Total Cost": 10633.43,            +
       "Plan Rows": 1,                    +
       "Plan Width": 4,                   +
       "Output": ["id"],                  +
       "Workers Planned": 2,              +
       "Single Copy": false,              +
       "Plans": [                         +
         {                                +
           "Node Type": "Seq Scan",       +
           "Parent Relationship": "Outer",+
           "Parallel Aware": true,        +
           "Relation Name": "hypg1",      +
           "Schema": "public",            +
           "Alias": "hypg1",              +
           "Startup Cost": 0.00,          +
           "Total Cost": 9633.33,         +
           "Plan Rows": 1,                +
           "Plan Width": 4,               +
           "Output": ["id"],              +
           "Filter": "(hypg1.id = 2006)"  +
         }                                +
       ]                                  +
     }                                    +
   }                                      +
 ]

 

Cost in Explain Plan

In all cases we should sort nodes by cost and only look at nodes that take up a certain percentage or more of the cost.
Each node has a “cost”. We need to extract the “total cost” at each node in the plan and compare that to the “total cost” of the whole query (top node) and calculate % of the “total cost” at a node compared whole query and only look at nodes that represent some value say 10% or more of total cost. Costs include the sum of all indented nodes, so to get the per node time or cost we have subtract out the child nodes.

If we can run “explain analyze” we can get the actual time per node. The command “explain analyze” actually runs the query . I would say we should plan on “explain analyze” with certain constraints. If the query is already being run multiple times a minute, then running it once more for tuning information is probably worth it. Time is just like cost where time includes times of child nodes, so we have subtract out the child nodes to see the time elapsed at every node.

Here is an example of the costs at nodes and showing the child nodes are subcomponents of the parent nodes cost

 

[                                                               +
   {                                                             +
     "Plan": {                                                   +
       "Total Cost": 22308.54,                                   +
       "Plans": [                                                +
         {                                                       +
           "Total Cost": 11675.10,                               +
             {                                                   +
               "Total Cost": 10675.00,                           +
             }                                                   +
         },                                                      +
         {                                                       +
           "Total Cost": 10633.43,                               +
             {                                                   +
               "Total Cost": 9633.33,                            +
             }                                                   +
           ]                                                     +
         }                                                       +
       ]                                                         +
     }                                                           +
   }                                                             +
 ]

for example, subtracting out the child node costs to get the per node costs

[                                                               +
   {                                                             +
     "Plan": {                                                   +
       "Total Cost": 22308.54,  --  node cost 22308.54 - (11675.10+10633.43) = 0.01
       "Plans": [                                                +
         {                                                       +
           "Total Cost": 11675.10, --  node cost 11675.10 - 10675.00 = 1000.1
             {                                                   +
               "Total Cost": 10675.00,                           +
             }                                                   +
         },                                                      +
         {                                                       +
           "Total Cost": 10633.43, --  node cost   10633.43 - 9633.33 = 1000.1
             {                                                   +
               "Total Cost": 9633.33,                            +
             }                                                   +
           ]                                                     +
         }                                                       +
       ]                                                         +
     }                                                           +
   }                                                             +
 ]

Predicate filters missing indexes

For predicate filters that can use indexes scan the explain plan for the following string

           "Node Type": "Seq Scan",       +

and see if they are followed by a “Filter”.

           "Total Cost": 9633.33,         +
           "Plan Rows": 1,                +
           "Filter": "(hypg1.id = 2006)"  +

Verify that the node is a significant cost of overall cost of query. This node takes up most to the cost of the plan 9633.33 out of 10633.43 so it is the most important node to optimize.
We should only look at nodes that take up a certain percentage of total cost, say 10% or more. ( value should be configurable in our code, as it is debatable where the cut off point is)

We can compare rows returned (“Plan Rows”) to total rows in the table statistics
If the filter eliminates a large amount of rows, then it is a candidate.
The following query had to be run in the database that the table is in, thus have to collect database as part of the sampling of the load data.

SELECT reltuples FROM pg_class WHERE relname = 'hypg1';

reltuples
-----------
 1e+06

% filtered = 100 * ( 1e+06 – 1 ) / 1e+06 = 99.99% rows are filtered out, so it’s the perfect candidate.

We should only recommend index the filter returns 10% or less of the table (i.e. 90% of rows returned from full table scanned are filtered out) and in the advisor recommendation for the index we should indicate much of the table is returned via the filter. We can give some recommendations based on the % for example
low recommendation 5%-10% of table returned after apply filter
medium recommendation 1%-5% of table returned after apply filter
high recommendation < 1% of table returned after apply filter

The filter column is hypg1.id thus we suggest an index on hypg1.id

Joins missing indexes

Using the following query in the examples below

explain (FORMAT json)
select max (t1.data)
from t1, t2
where t1.id = t2.id
and t1.clus = 10
and t2.val > 1001
;

HASH JOIN

Look for nodes of type “Hash Cond”: ” that are followed by a “Seq Scan”
If we find a “Seq Scan” is preceded by a Hash JOIN and cost is a significant amount of total query, then suggest an index on the join column
If both nodes of the hash join have Seq Scans , suggest the index join column from the more expensive node.
If that node also contains a filter, suggest including the filter in the index.

There is “Node Type”: “Seq Scan”
table “Relation Name”: “t2″,
cost “Total Cost”: 13512.67″
looking the hash join above the Seq Scan node, the join condition is

"Hash Cond": "(t2.id = t1.id)",  

suggest index on t2.id

              "Plans": [                                                 +
                 {                                                        +
                   "Node Type": "Hash Join",                              +
                   "Parent Relationship": "Outer",                        +
                   "Parallel Aware": false,                               +
                   "Join Type": "Inner",                                  +
                   "Startup Cost": 11.40,                                 +
                   "Total Cost": 15086.97,                                +
                   "Plan Rows": 41,                                       +
                   "Plan Width": 33,                                      +
                   "Output": ["t1.data"],                                 +
                   "Inner Unique": false,                                 +
                   "Hash Cond": "(t2.id = t1.id)",                        +
                   "Plans": [                                             +
                     {                                                    +
                       "Node Type": "Seq Scan",                           +
                       "Parent Relationship": "Outer",                    +
                       "Parallel Aware": true,                            +
                       "Relation Name": "t2",                             +
                       "Schema": "public",                                +
                       "Alias": "t2",                                     +
                       "Startup Cost": 0.00,                              +
                       "Total Cost": 13512.67,                            +
                       "Plan Rows": 416667,                               +
                       "Plan Width": 4,                                   +
                       "Output": ["t2.id", "t2.clus", "t2.val", "t2.data"]+
                     },

Full explain

total cost of query is 41534.23
HJ node cost is 40532.27 on condition “Hash Cond”: “(t2.id = t1.id)”,
Most expensive node is second node of hash join, “Total Cost”: 24346.00
Second node , the expensive node, is doing full scan on “Relation Name”: “t1″
Thus suggest an index on t1.id, the join condition of the hash join.
The T1 node also contains a filter that returns “Plan Rows”: 5000,” i.e. 5000 rows of 1M in the table ( we look up total rows in pg_class) . The filter is on “Filter”: “((clus) = 10)” so we suggest an index on the combine id & clus

recommend index on t1 ( id, clus)

                                                         +
 [                                                        +
   {                                                      +
     "Plan": {                                            +
       "Node Type": "Aggregate",                          +
       "Strategy": "Plain",                               +
       "Partial Mode": "Finalize",                        +
       "Parallel Aware": false,                           +
       "Startup Cost": 41534.23,                          +
       "Total Cost": 41534.24,                            +
       "Plan Rows": 1,                                    +
       "Plan Width": 32,                                  +
       "Plans": [                                         +
         {                                                +
           "Node Type": "Gather",                         +
           "Parent Relationship": "Outer",                +
           "Parallel Aware": false,                       +
           "Startup Cost": 41534.01,                      +
           "Total Cost": 41534.22,                        +
           "Plan Rows": 2,                                +
           "Plan Width": 32,                              +
           "Workers Planned": 2,                          +
           "Single Copy": false,                          +
           "Plans": [                                     +
             {                                            +
               "Node Type": "Aggregate",                  +
               "Strategy": "Plain",                       +
               "Partial Mode": "Partial",                 +
               "Parent Relationship": "Outer",            +
               "Parallel Aware": false,                   +
               "Startup Cost": 40534.01,                  +
               "Total Cost": 40534.02,                    +
               "Plan Rows": 1,                            +
               "Plan Width": 32,                          +
               "Plans": [                                 +
                 {                                        +
                   "Node Type": "Hash Join",              +
                   "Parent Relationship": "Outer",        +
                   "Parallel Aware": false,               +
                   "Join Type": "Inner",                  +
                   "Startup Cost": 24408.50,              +
                   "Total Cost": 40532.27,                +
                   "Plan Rows": 695,                      +
                   "Plan Width": 33,                      +
                   "Inner Unique": false,                 +
                   "Hash Cond": "(t2.id = t1.id)",        +
                   "Plans": [                             +
                     {                                    +
                       "Node Type": "Seq Scan",           +
                       "Parent Relationship": "Outer",    +
                       "Parallel Aware": true,            +
                       "Relation Name": "t2",             +
                       "Alias": "t2",                     +
                       "Startup Cost": 0.00,              +
                       "Total Cost": 15596.00,            +
                       "Plan Rows": 138889,               +
                       "Plan Width": 4,                   +
                       "Filter": "((val + 0) > 1001)"     +
                     },                                   +
                     {                                    +
                       "Node Type": "Hash",               +
                       "Parent Relationship": "Inner",    +
                       "Parallel Aware": false,           +
                       "Startup Cost": 24346.00,          +
                       "Total Cost": 24346.00,            +
                       "Plan Rows": 5000,                 +
                       "Plan Width": 37,                  +
                       "Plans": [                         +
                         {                                +
                           "Node Type": "Seq Scan",       +
                           "Parent Relationship": "Outer",+
                           "Parallel Aware": false,       +
                           "Relation Name": "t1",         +
                           "Alias": "t1",                 +
                           "Startup Cost": 0.00,          +
                           "Total Cost": 24346.00,        +
                           "Plan Rows": 5000,             +
                           "Plan Width": 37,              +
                           "Filter": "((clus) = 10)"      +
                         }                                +
                       ]                                  +
                     }                                    +
                   ]                                      +
                 }                                        +
               ]                                          +
             }                                            +
           ]                                              +
         }                                                +
       ]                                                  +
     }                                                    +
   }                                                      +                                                   +

Nested Loops

Similar we find a Nest Loops join where the second node has a Seq Scan, look to see if an index can be added to support the join. Only look at Nest Loop nodes that are a significant % of total query cost.

Most of the cost of the query is the next loops:

          "Node Type": "Nested Loop",        +
  ...
          "Total Cost": 1320240.33,          + 

The first node in the NL select rows that are looked up in the second node.
The second node is doing a full table scan:

                     "Node Type": "Seq Scan",       +
                       "Relation Name": "t2",         +
                       "Plan Rows": 1000000,          +

We want to avoid Full table scans on the second node of NL.
Every row in the first node will do a full table scan on the second node.
The join is on

                   "Join Filter": "(t1.id = t2.id)",  +

So we suggest an index on t2.id

[                                                    +
   {                                                  +
     "Plan": {                                        +
       "Node Type": "Aggregate",                      +
       "Strategy": "Plain",                           +
       "Partial Mode": "Finalize",                    +
       "Parallel Aware": false,                       +
       "Startup Cost": 1321240.65,                    +
       "Total Cost": 1321240.66,                      +
       "Plan Rows": 1,                                +
       "Plan Width": 32,                              +
       "Plans": [                                     +
         {                                            +
           "Node Type": "Gather",                     +
           "Parent Relationship": "Outer",            +
           "Parallel Aware": false,                   +
           "Startup Cost": 1321240.44,                +
           "Total Cost": 1321240.65,                  +
           "Plan Rows": 2,                            +
           "Plan Width": 32,                          +
           "Workers Planned": 2,                      +
           "Single Copy": false,                      +
           "Plans": [                                 +
             {                                        +
               "Node Type": "Aggregate",              +
               "Strategy": "Plain",                   +
               "Partial Mode": "Partial",             +
               "Parent Relationship": "Outer",        +
               "Parallel Aware": false,               +
               "Startup Cost": 1320240.44,            +
               "Total Cost": 1320240.45,              +
               "Plan Rows": 1,                        +
               "Plan Width": 32,                      +
               "Plans": [                             +
                 {                                    +
                   "Node Type": "Nested Loop",        +
                   "Parent Relationship": "Outer",    +
                   "Parallel Aware": false,           +
                   "Join Type": "Inner",              +
                   "Startup Cost": 0.00,              +
                   "Total Cost": 1320240.33,          +
                   "Plan Rows": 41,                   +
                   "Plan Width": 33,                  +
                   "Inner Unique": false,             +
                   "Join Filter": "(t1.id = t2.id)",  +
                   "Plans": [                         +
                     {                                +
                       "Node Type": "Seq Scan",       +
                       "Parent Relationship": "Outer",+
                       "Parallel Aware": true,        +
                       "Relation Name": "t1",         +
                       "Alias": "t1",                 +
                       "Startup Cost": 0.00,          +
                       "Total Cost": 14554.33,        +
                       "Plan Rows": 41,               +
                       "Plan Width": 37,              +
                       "Filter": "(clus = 10)"        +
                     },                               +
                     {                                +
                       "Node Type": "Seq Scan",       +
                       "Parent Relationship": "Inner",+
                       "Parallel Aware": false,       +
                       "Relation Name": "t2",         +
                       "Alias": "t2",                 +
                       "Startup Cost": 0.00,          +
                       "Total Cost": 19346.00,        +
                       "Plan Rows": 1000000,          +
                       "Plan Width": 4                +
                     }                                +
                   ]                                  +
                 }                                    +
               ]                                      +
             }                                        +
           ]                                          +
         }                                            +
       ]                                              +
     }                                                +
   }                                                  +

 

Merge Join

similar for a merge join

-- to create a merge join example 
 set enable_nestloop to 'off';
 SET enable_hashjoin TO off;

 

explain (FORMAT json)
select max (t1.data)
from t1, t2
where t1.id = t2.id
and t1.clus = 10
and t2.val  > 1001
;

We want to turn the merge join into a NL or HJ.
In order to do so efficiently we need an index on the join on one table.
We want to have the index on the table with the more rows to analyze.

t1 returns 5000 rows

                           "Relation Name": "t1",         +
                           "Plan Rows": 5000,             +

t2 returns 138889

                          "Relation Name": "t2",         +
                           "Plan Rows": 138889,           +

and join on T2 is on

                   "Merge Cond": "(t2.id = t1.id)",       +

thus suggest index on t2.id

  [                                                        +
   {                                                      +
     "Plan": {                                            +
       "Node Type": "Aggregate",                          +
       "Strategy": "Plain",                               +
       "Partial Mode": "Finalize",                        +
       "Parallel Aware": false,                           +
       "Startup Cost": 55741.63,                          +
       "Total Cost": 55741.64,                            +
       "Plan Rows": 1,                                    +
       "Plan Width": 32,                                  +
       "Plans": [                                         +
         {                                                +
           "Node Type": "Gather",                         +
           "Parent Relationship": "Outer",                +
           "Parallel Aware": false,                       +
           "Startup Cost": 55741.42,                      +
           "Total Cost": 55741.63,                        +
           "Plan Rows": 2,                                +
           "Plan Width": 32,                              +
           "Workers Planned": 2,                          +
           "Single Copy": false,                          +
           "Plans": [                                     +
             {                                            +
               "Node Type": "Aggregate",                  +
               "Strategy": "Plain",                       +
               "Partial Mode": "Partial",                 +
               "Parent Relationship": "Outer",            +
               "Parallel Aware": false,                   +
               "Startup Cost": 54741.42,                  +
               "Total Cost": 54741.43,                    +
               "Plan Rows": 1,                            +
               "Plan Width": 32,                          +
               "Plans": [                                 +
                 {                                        +
                   "Node Type": "Merge Join",             +
                   "Parent Relationship": "Outer",        +
                   "Parallel Aware": false,               +
                   "Join Type": "Inner",                  +
                   "Startup Cost": 54013.29,              +
                   "Total Cost": 54739.68,                +
                   "Plan Rows": 695,                      +
                   "Plan Width": 33,                      +
                   "Inner Unique": false,                 +
                   "Merge Cond": "(t2.id = t1.id)",       +
                   "Plans": [                             +
                     {                                    +
                       "Node Type": "Sort",               +
                       "Parent Relationship": "Outer",    +
                       "Parallel Aware": false,           +
                       "Startup Cost": 29360.10,          +
                       "Total Cost": 29707.32,            +
                       "Plan Rows": 138889,               +
                       "Plan Width": 4,                   +
                       "Sort Key": ["t2.id"],             +
                       "Plans": [                         +
                         {                                +
                           "Node Type": "Seq Scan",       +
                           "Parent Relationship": "Outer",+
                           "Parallel Aware": true,        +
                           "Relation Name": "t2",         +
                           "Alias": "t2",                 +
                           "Startup Cost": 0.00,          +
                           "Total Cost": 15596.00,        +
                           "Plan Rows": 138889,           +
                           "Plan Width": 4,               +
                           "Filter": "((val ) > 1001)" +
                         }                                +
                       ]                                  +
                     },                                   +
                     {                                    +
                       "Node Type": "Sort",               +
                       "Parent Relationship": "Inner",    +
                       "Parallel Aware": false,           +
                       "Startup Cost": 24653.19,          +
                       "Total Cost": 24665.69,            +
                       "Plan Rows": 5000,                 +
                       "Plan Width": 37,                  +
                       "Sort Key": ["t1.id"],             +
                       "Plans": [                         +
                         {                                +
                           "Node Type": "Seq Scan",       +
                           "Parent Relationship": "Outer",+
                           "Parallel Aware": false,       +
                           "Relation Name": "t1",         +
                           "Alias": "t1",                 +
                           "Startup Cost": 0.00,          +
                           "Total Cost": 24346.00,        +
                           "Plan Rows": 5000,             +
                           "Plan Width": 37,              +
                           "Filter": "((clus + 0) = 10)"  +
                         }                                +
                       ]                                  +
                     }                                    +
                   ]                                      +
                 }                                        +
               ]                                          +
             }                                            +
           ]                                              +
         }                                                +
       ]                                                  +
     }                                                    +
   }                                                      +

 

3. Mixed cases – filter and join indexing opportunities

If a filter is missing an index causing a full scan (i.e. “Seq Scan”) and the table being scanned is also used in a join (hash join, merge join, nested loops join), suggest an index on both the filter column and
In the following there is a Seq Scan on T2 followed by a filter.
The filter returns an estimated 375K rows or about 1/3 of the table , i.e. not a good candidate for an index
The Seq Scan is part of a HASH JOIN that represents most of the cost of the query. The hash join is on t2.id.

cost total query 16973.03
t2 access for the hash join cost: 14554.33
t2 has 1M rows ( have to collect this from table stats)
access is on t2.id , “Hash Cond”: “(t2.id = t1.id)”,

suggest index on t2 id and val to avoid the full table scan.

 [                                                        +
   {                                                      +
     "Plan": {                                            +
       "Node Type": "Aggregate",                          +
       "Strategy": "Plain",                               +
       "Partial Mode": "Finalize",                        +
       "Parallel Aware": false,                           +
       "Startup Cost": 16973.02,                          +
       "Total Cost": 16973.03,                            +
       "Plan Rows": 1,                                    +
       "Plan Width": 32,                                  +
       "Plans": [                                         +
         {                                                +
           "Node Type": "Gather",                         +
           "Parent Relationship": "Outer",                +
           "Parallel Aware": false,                       +
           "Startup Cost": 16972.81,                      +
           "Total Cost": 16973.02,                        +
           "Plan Rows": 2,                                +
           "Plan Width": 32,                              +
           "Workers Planned": 2,                          +
           "Single Copy": false,                          +
           "Plans": [                                     +
             {                                            +
               "Node Type": "Aggregate",                  +
               "Strategy": "Plain",                       +
               "Partial Mode": "Partial",                 +
               "Parent Relationship": "Outer",            +
               "Parallel Aware": false,                   +
               "Startup Cost": 15972.81,                  +
               "Total Cost": 15972.82,                    +
               "Plan Rows": 1,                            +
               "Plan Width": 32,                          +
               "Plans": [                                 +
                 {                                        +
                   "Node Type": "Hash Join",              +
                   "Parent Relationship": "Outer",        +
                   "Parallel Aware": false,               +
                   "Join Type": "Inner",                  +
                   "Startup Cost": 11.40,                 +
                   "Total Cost": 15972.72,                +
                   "Plan Rows": 37,                       +
                   "Plan Width": 33,                      +
                   "Inner Unique": false,                 +
                   "Hash Cond": "(t2.id = t1.id)",        +
                   "Plans": [                             +
                     {                                    +
                       "Node Type": "Seq Scan",           +
                       "Parent Relationship": "Outer",    +
                       "Parallel Aware": true,            +
                       "Relation Name": "t2",             +
                       "Alias": "t2",                     +
                       "Startup Cost": 0.00,              +
                       "Total Cost": 14554.33,            +
                       "Plan Rows": 375098,               +
                       "Plan Width": 4,                   +
                       "Filter": "(val > 1001)"           +
                     },                                   +
                     {                                    +
                       "Node Type": "Hash",               +
                       "Parent Relationship": "Inner",    +
                       "Parallel Aware": false,           +
                       "Startup Cost": 10.16,             +
                       "Total Cost": 10.16,               +
                       "Plan Rows": 99,                   +
                       "Plan Width": 37,                  +
                       "Plans": [                         +
                         {                                +
                           "Node Type": "Index Scan",     +
                           "Parent Relationship": "Outer",+
                           "Parallel Aware": false,       +
                           "Scan Direction": "Forward",   +
                           "Index Name": "t1_clus",       +
                           "Relation Name": "t1",         +
                           "Alias": "t1",                 +
                           "Startup Cost": 0.42,          +
                           "Total Cost": 10.16,           +
                           "Plan Rows": 99,               +
                           "Plan Width": 37,              +
                           "Index Cond": "(clus = 10)"    +
                         }                                +
                       ]                                  +
                     }                                    +
                   ]                                      +
                 }                                        +
               ]                                          +
             }                                            +
           ]                                              +
         }                                                +
       ]                                                  +
     }                                                    +
   }                                                      +

4. Index Range scans

To see if an index needs more columns to be efficient looks like ti will require “explain analyze” and not just “explain”

select count(*)
from t1
where t1.clus >= 10 and t1.clus < 15
and t1.val = 1001
;

There is an index on t1.clus and the explain shows it is used
but we don’t know how many rows were retrieved by the index vs filtered out by the Filter

[                                                        +
   {                                                      +
     "Plan": {                                            +
       "Node Type": "Aggregate",                          +
       "Strategy": "Plain",                               +
       "Partial Mode": "Simple",                          +
       "Parallel Aware": false,                           +
       "Startup Cost": 27.14,                             +
       "Total Cost": 27.15,                               +
       "Plan Rows": 1,                                    +
       "Plan Width": 8,                                   +
       "Plans": [                                         +
         {                                                +
           "Node Type": "Index Scan",                     +
           "Parent Relationship": "Outer",                +
           "Parallel Aware": false,                       +
           "Scan Direction": "Forward",                   +
           "Index Name": "t1_clus",                       +
           "Relation Name": "t1",                         +
           "Alias": "t1",                                 +
           "Startup Cost": 0.42,                          +
           "Total Cost": 27.13,                           +
           "Plan Rows": 1,                                +
           "Plan Width": 0,                               +
           "Index Cond": "((clus >= 10) AND (clus < 15))",+
           "Filter": "(val = 1001)"                       +
         }                                                +
       ]                                                  +
     }                                                    +
   }                                                      +

but it doesn’t show how many rows are retrieved by the index and how many are post processed filtered out by the Filter.
Using explain analyze does.
In the following output from “explain analyze”
Only 1 run is returned after index access and filter
but filter takes out 499 rows

"Filter": "(val = 1001)",                      +
           "Rows Removed by Filter": 499                  +

Thus suggest to add val to the existing index

           "Index Name": "t1_clus",                       +

 

[                                                        +
   {                                                      +
     "Plan": {                                            +
       "Node Type": "Aggregate",                          +
       "Strategy": "Plain",                               +
       "Partial Mode": "Simple",                          +
       "Parallel Aware": false,                           +
       "Startup Cost": 27.14,                             +
       "Total Cost": 27.15,                               +
       "Plan Rows": 1,                                    +
       "Plan Width": 8,                                   +
       "Actual Startup Time": 0.279,                      +
       "Actual Total Time": 0.279,                        +
       "Actual Rows": 1,                                  +
       "Actual Loops": 1,                                 +
       "Plans": [                                         +
         {                                                +
           "Node Type": "Index Scan",                     +
           "Parent Relationship": "Outer",                +
           "Parallel Aware": false,                       +
           "Scan Direction": "Forward",                   +
           "Index Name": "t1_clus",                       +
           "Relation Name": "t1",                         +
           "Alias": "t1",                                 +
           "Startup Cost": 0.42,                          +
           "Total Cost": 27.13,                           +
           "Plan Rows": 1,                                +
           "Plan Width": 0,                               +
           "Actual Startup Time": 0.053,                  +
           "Actual Total Time": 0.273,                    +
           "Actual Rows": 1,                              +
           "Actual Loops": 1,                             +
           "Index Cond": "((clus >= 10) AND (clus < 15))",+
           "Rows Removed by Index Recheck": 0,            +
           "Filter": "(val = 1001)",                      +
           "Rows Removed by Filter": 499                  +
         }                                                +
       ]                                                  +
     },                                                   +
     "Planning Time": 0.270,                              +
     "Triggers": [                                        +
     ],                                                   +
     "Execution Time": 0.388                              +
   }                                                      +

 

Check if INDEX we suggest already exists

look up existence of index ( https://stackoverflow.com/questions/2204058/list-columns-with-indexes-in-postgresql)

select
    t.relname as table_name,
    i.relname as index_name,
    array_to_string(array_agg(a.attname), ', ') as column_names
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.relname like 'auth%'
group by
    t.relname,
    i.relname
order by
    t.relname,
    i.relname;
    

 table_name | index_name | column_names 
------------+------------+--------------
 authors    | authors_id | id

 

 

Creating data for test cases above

drop table t1;
 
 create table t1 ( 
    id  int,
    clus int,
    val  int,
    data VARCHAR(40)
 );
 
 insert into t1 (
    id, clus , val, data
)
  select 
  i,
  trunc(i/100),
  mod(i,10000),
  left(md5(random()::text), 40) 
from generate_series(1, 1000000) s(i);
  
select count(*) from t1 where clus = 1 ;
100
select count(*) from t1 where val =1 ;
100

create table t2 as select * from t1;  
  

explain (analyze,verbose,buffers)
select max(t1.data) from  t1, t2
where t1.id = t2.id 
and t1.clus = 1
;

Parameters that affect the explain plan

SET max_parallel_workers_per_gather = 0;
 set enable_mergejoin to 'off';
 set enable_nestloop to 'off';
 SET enable_hashjoin TO off;

Add A SQL*Net Security Banner And Audit Notice

I would have to say whilst I see security banners on customers Unix boxes when I am allowed to log in as part of a security audit I canot ever remember seeing a security banner when I log into a....[Read More]

Posted by Pete On 30/03/20 At 02:02 PM

Combinations and consequences

Fellow Perth techie Scott Wesley sent me this interesting puzzle recently. He was using the long awaited feature of being (finally) able to assign a sequence value via the DEFAULT clause in a table definition.

The problem was … the sequence was NOT being assigned. And since that column was defined as NOT NULL, his application was breaking. We had some to-and-fro and finally I managed to reduce it down a very small test case, which I’ll build from scratch here.



SQL>
SQL> create sequence seq;

Sequence created.

SQL>
SQL> create table t1
  2    ( c1  number not null enable,
  3      id  number default seq.nextval not null enable
  4     );

Table created.

Here is the reason I wanted to blog about this particular example. Just like our own applications, the Oracle database is an application. A very large, impressive and complex application, but an application nonetheless. And generally, the places where (any) application code encounters problems is when separate components are brought together in ways that are not expected. For example, if my application has 5 components, then I probably only need 5 unit tests to prove that those 5 components work. But if I want to test the combinations of those 5 components interacting together, now I need 31 tests, and that is not considering the sequencing of those 5 components, in which case it explodes to near 200. Expand an application out to 1000’s of components, and it is impossible to prove every possible permutation of usage.

Which brings us back to the example Scott sent me. Let’s do some testing of INSERT-SELECT components in isolation.



SQL>
SQL> --
SQL> -- INSERT-SELECT is fine!
SQL> --
SQL>
SQL> insert into t1  (c1)
  2  select 11713
  3  from ( select 1 x, 2 y from dual union all select 3,4 from dual ) p;

2 rows created.


SQL>
SQL> --
SQL> -- INSERT-SELECT with ORDER BY is fine!
SQL> --
SQL>
SQL> insert into t1  (c1)
  2  select 11713
  3  from ( select 1 x, 2 y from dual union all select 3,4 from dual ) p
  4  order by x,y;

2 rows created.


SQL>
SQL> --
SQL> -- INSERT-SELECT with a BIND is fine!
SQL> --
SQL>
SQL> variable x number
SQL> exec :x := 11713;

PL/SQL procedure successfully completed.

SQL>
SQL> insert into t1  (c1)
  2  select :x
  3  from ( select 1 x, 2 y from dual union all select 3,4 from dual ) p;

2 rows created.

But when we bring all those components together…



SQL> --
SQL> -- INSERT-SELECT with a BIND and ORDER BY .... uh oh
SQL> --
SQL>
SQL> insert into t1  (c1)
  2  select :x
  3  from ( select 1 x, 2 y from dual union all select 3,4 from dual ) p
  4  order by x,y;
insert into t1  (c1)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("MCDONAC"."T1"."ID")

Chatting to the Optimizer team in Oracle, its probable that this bug has been present almost all the way back to early 11g versions, and yet has never been noticed until Scott chanced upon it.

A simple workaround is to wrap the SELECT part within an WITH statement



SQL> insert into t1  (c1)
  2  with ttt as ( select :x
  3  from ( select 1 x, 2 y from dual union all select 3,4 from dual ) p
  4  order by x,y
  5  )
  6  select * from ttt;
 
2 rows created.

but the message of this post is not about this particular idiosyncrasy, but more to always be on the lookout for where you might be “mixing and matching” elements of the Oracle database application in ways that perhaps are not considered typical. That’s why you are most likely to hit boundary cases such as this one.

Oracle 19c Automatic Indexing: Index Created But Not Actually Used (Because Your Young)

    The following is an interesting example of how Oracle Automatic Indexing is currently implemented that can result in an Automatic Index being created but ultimately ignored by the CBO. To illustrate, we begin by creating a simple little table that has two columns of particular interest, CODE2 which has 100 distinct values and […]

Oracle recovery concepts

I’ve published a while ago a twitter thead on some Oracle recovery concepts. For those who are not following twitter, I’m putting the whole thread here:
 

</p />
</p></div>

    	  	<div class=

Locating Oracle Enterprise Linux Images for Azure

Just like with other UI’s, the Azure portal may not show you the wealth of offerings that can be found in the Azure catalog.  For those Oracle DBAs hoping to build an IaaS VM image from a certified OS version, this means they need Oracle Enterprise Linux and are frustrated when they can’t locate it in the portal.

Empower with the Cloud Shell

Oracle DBAs rarely are fascinated with user interfaces or portals, so when you offer us something like the Azure Cloud Shell, it’s something we should embrace.  I’ve spoke about it before and I will rave about it again here.  If you haven’t set it up, skip the download to your desktop and simply go to Azure Cloud Shell and connect to your Azure account.  Once there, ensure you’ve set the CLI to BASH and use this just as you would Putty or Xterminal.

https://dbakevlar.com/wp-content/uploads/2020/03/blog_0327-300x112.jpg 300w, https://dbakevlar.com/wp-content/uploads/2020/03/blog_0327-768x286.jpg 768w, https://dbakevlar.com/wp-content/uploads/2020/03/blog_0327.jpg 1170w" sizes="(max-width: 800px) 100vw, 800px" />

If you configure it with Azure Cloud Storage, you can even use it as if it were a jump box, housing your scripts, just as I do.  I not only store scripts to automate my Azure processing, I also connect to any VM from this point.

https://dbakevlar.com/wp-content/uploads/2020/03/blog_03271-300x42.jpg 300w, https://dbakevlar.com/wp-content/uploads/2020/03/blog_03271-768x106.jpg 768w, https://dbakevlar.com/wp-content/uploads/2020/03/blog_03271-1536x213.jpg 1536w, https://dbakevlar.com/wp-content/uploads/2020/03/blog_03271-2048x284.jpg 2048w" sizes="(max-width: 800px) 100vw, 800px" />

Listing Images

You can also run AZ commands from the Azure Cloud Shell prompt.  The following prompt will return a list of all Oracle Enterprise Linux Images:

az vm image list \
   --offer Oracle-Linux \
   --all \
   --publisher Oracle \
   --output table

The output will look like the following:

https://dbakevlar.com/wp-content/uploads/2020/03/blog_03272-300x202.jpg 300w, https://dbakevlar.com/wp-content/uploads/2020/03/blog_03272-768x518.jpg 768w, https://dbakevlar.com/wp-content/uploads/2020/03/blog_03272-1536x1036.jpg 1536w, https://dbakevlar.com/wp-content/uploads/2020/03/blog_03272-2048x1382.jpg 2048w" sizes="(max-width: 800px) 100vw, 800px" />

Create an OEL VM

From this list, you can choose the version of Oracle Linux you want and then use the “Urn” to create a VM using the image.  In our example, we’ll create an Oracle Enterprise Linux version 7.6 and request the latest patched version from the catalog:

az vm create \
   --resource-group orHGrp1 \
   --name oraVM1 \
   --image Oracle:Oracle-Linux:7.6:7.6.3:latest \
   --size Standard_DS2_v2 \
   --admin-username azureuser \
   --generate-ssh-keys

Once this is complete, you’ll have an Oracle Enterprise Manager Linux VM, using Standard SSD disk, with the SSH keys set for your user and it will reside in a resource group called oraHGrp1 and be named oraVM1.

There are more steps to be performed before this is ready for Oracle or anything else, but for those wanting to know how to locate the OEL images, here’s your starting point.

Have a great weekend!

 



Tags:  , ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright ©  [Locating Oracle Enterprise Linux Images for Azure], All Right Reserved. 2020.

ORA-28050 - Can I drop the SYSTEM User?

Two things most annoy me with the Oracle database in terms of securing it and this is the abundance of default users in most Oracle databases that I perform security audits on and also the massive amount of PUBLIC grants....[Read More]

Posted by Pete On 27/03/20 At 06:11 PM

COVID-19: What Can We Do to Reduce Social Distancing

<<<< COVID-19 Basics: What it is & what it dies to us

<<…. COVID-19 Outlook for the Month(s) Ahead

Summary

The impact of COVID-19 on our society and our economy is going to be long and hard. I hope I am not the first to come up with this idea, but just in case…

https://mwidlake.files.wordpress.com/2020/03/screenhunter_-336.jpg?w=600... 600w, https://mwidlake.files.wordpress.com/2020/03/screenhunter_-336.jpg?w=150... 150w" sizes="(max-width: 300px) 100vw, 300px" />

Having everyone on lock-down on and off for months will be hard to maintain. But not everyone will need to be in lock-down. You do not need to be locked down if you are immune.

I think we need to look at having a “COVID-19 Immunity Card” – you get the card to prove that you are probably immune to COVID-19 and that you are no longer a danger to others and are not in danger yourself.

Once you have a card you no longer have to abide by social distancing measures in the same way as those not immune. You are also a known “safe” person who can interact with those who are not. This would be particularly reassuring in the “caring” industries.

The number of people with cards will grow over time due to:

  • People being diagnosed with the disease and recovering – not many yet.
  • People being tested and found to have had the disease (possibly without knowing and have recovered) – coming soon?
  • People who have been vaccinated against it – future group.

There are potentially serous drawbacks to this idea. Such a card would be a source of division for as long as we have them and they would be a huge target for criminal activity, but it could help us “sleep with the tiger” of COVID-19.

It could/would allow our economy, health services, and society function more effectively whilst we are living with COVID-19.

Background – Once we “stop” COVID-19 this time, we have a problem…

The UK, like a growing number of countries, is now in a strong, country-wide, social shut-down. The aim is to suppress COVID-19 (see COVID-19: What’s Going To Happen Now ) i.e. drop the levels of person-to-person transmission (The “R” number) below 1. If each person with COVID_19 infects fewer than 1 other person on average, the spread stops. Quickly. It will take another 2-3 weeks for those already infected or sick (as of the date I am writing this, 27/3/20) to develop the symptoms and possibly need hospital treatment, so between now and mid-April we will see cases continuing to rise rapidly, followed by the number of deaths.

Then, something like Mid-April onwards, new cases will drop and, less slowly, the number of deaths.

COVID-19 will have been stopped. However, it will not have gone away, it will still be in the population. If we relax the social isolation we are currently living under, it will start spreading again and we will have another outbreak. Why? As only a small percentage of the population will be immune to the SARS-COV-2 virus. Governments are giving the impression that we will have “beaten COVID-19!” at this point, when the first peak of cases has come and gone, but the scientific consensus is clear that it will return if we all start living normally again. There are several studies going on at present to model what we can do and how. For example, China is relaxing restrictions and the world-wide epidemiological community is watching. For example, this Imperial College Paper on how China is coming out of strict social distancing is interesting.

I think of this as sleeping with a tiger that we don’t want to wake up.

The Imperial/WHO/MRC paper does cover all of this and suggests a way of relaxing social isolation steps and re-introducing them, over a 2 year period. The chances are, this is all going to go on far longer than most people realise and way longer than any of us want!

Reasoning on why COVID-19 will be with us “until something changes”.

The rest of this post is me being an “Armchair Epidemiologist” – proposing untested ideas with only a tenuous grasp of the true facts. But I thought I would put this out there. Note, there will be a lack of links to any solid references from this point. When you see this in articles discussing scientific ideas, it usually indicates it is a thought experiment.

There is general scientific consensus that, if we had better testing, the Case Fatality Rate would be about 1-2%. Case Fatality Rate (CFR) is the percentage of diagnosed cases that die. What we actually need is the Infection Fatality Rate (IFR) of COVID-19:- Taking into account all people who get the disease (whether they show symptoms or not or were tested or not) what is the percentage of people who die. See the Wikipedia entry on CFR for more details of CFR and IFR.

IFR is being argued about by the scientific community as you have to test a large, random set of people to see how common the disease is and testing by most countries is limited to suspected cases. Thus estimates are being made. The really good news is that the estimates of IFR are a lot lower than CFR. numbers seem to vary from 0.2% to 0.6%. See this pre-print of an article on CFR/IFR  and this paper by Nuffield Primary Care Health Sciences  at Oxford University. I’ll be pessimistic and take 0.5%

I am assuming the  Infection Fatality Rate is 0.5%

The reason we need the Infection Fatality Rate is that we can then calculate the number of infected people from the number of people who died – ONCE number of infection and deaths have reduced to low numbers again. You can’t do this (well, I can’t) when the number of new cases or deaths is increasing.

If 10,000 people die in the peak of cases we are currently enduring, if it is killing 0.5% of people and ICU limits are NOT exceeded, that means 2 million people will be immune once the peak has passed (as 99.5% of that 2 million have it and survive).

However, 64 Million will not be immune.

As has been described, we could now relax social distancing and let businesses and the economy start up to some degree again – but then tighten up social distancing again when cases or ICU admissions rise. We have a series of mini-outbreaks.

We have a population of 66 million. At 2 Million becoming immune in each “Outbreak”, we would need 20 outbreaks to get to a level of people who have had the disease where herd immunity is stopping the disease spreading – 60% or 44 or so million people (but we would still have 22 million susceptible to the disease).

With a peak every 2 months (so no single one exceeds the expanded capabilities of our NHS) getting to 60% immunity would take… several years. This is why all those discussions about getting herd immunity in weeks or months is, frankly, naive. We could only have that happen if we did not control the outbreak.

It might be that we can work out a level of social distancing that allows the economy to keep some semblance of normality and the COVID-19 cases at a level the NHS can keep up with, but that is a very, very fine tightrope to walk.

In any case, if we do not simply let COVID-19 rip through our society (killing more people than it would if controlled, as it vastly overwhelms the health services) we have to sleep with the tiger until we we have another option. But I think there is a way to make sleeping with the tiger more comfortable.

People will become immune to SARS-COV-2

A reliable, widely available test for seeing if someone has had COVID-19 and is now resistant to the  SARS-COV-2 is desperately needed and, I think, will become available soon – in a couple of months, long before a vaccine arrives.

We will then have 2 ways of knowing someone is immune:

  • Those who were tested positive for COVID and survived. They are immune.
  • Those that pass an antibody test. They are probably immune – depending on the reliability of the test. There could be several tests that have different levels of reliability.

These people can be given an “I am immune” card and they will not be limited (at least not so much) in lock downs.

Initially there will only be a hundred thousand people who can have the card, as they have been identified by testing to have had COVID_19,  have got better, and are now immune . But, crucially, a disproportionately high percentage of them will be NHS and first responder workers. This is because those groups are suffering very high exposure to COVID-19, by the very nature of what they do. The ranks of these groups are (and will continue to be) literally decimated by COVID-19. Lots and lots and lots of nurses, doctors, lab staff, cleaners, police, paramedics, GPs are going to be in the first wave getting ill.

Once we have the cheap, reliable antibody test , we can look for the rest of the 2 million.

As you can see, the more testing we do, both for having COVID-19 or for having antibodies against SARS-COV-2, the more people we can give an immunity card.

Over time, especially if we have further outbreaks, the number of people who are immune and are found via the above will increase.

Later, when vaccines are developed, there will be a third group of people we can count as immune:

  • Those who are vaccinated
  • Better still, those who are vaccinated and are latter tested for (and pass) an antibody test.

The first vaccines are likely to not be very effective – think the low end of the level of protection the annul ‘flu vaccines achieve, 20-40%. The antibody tests to confirm you have immune to SARS-COV-2 might also vary. But the details on the card will give which tests and vaccines you have had.

The card will hold details of why the person is immune, what test(s) were used to identify they had the disease, what vaccine(s) they had had, and when these events occurred. Minimal details would be held on the card itself.

A central database would hold the details of vaccination & test efficacy, corroborative information about the person etc.

If the reliability of historical tests or vaccinations change, then the immunity status of the individual may change.

The database of information would of course need to be well secured, kept in more than one place (so that a single IT disaster does not destroy all this key information) and protected. These are technical problems that can be solved.

Drawbacks off the COVID-19 Immunity Card

The cards will need to be very reliable, trusted, and protected from abuse.

Both the data they hold (or link to) and the information about the person the card is for needs to be highly dependable. The data needs to specify which sort of immunity this person has, when they were ill (if they have been) or tested, when any vaccine(s) were administered and when. It may turn out that immunity to SARS-COV-2 will reduce over time (that is, our immune systems “forget” about the disease) and the virus may mutate over time such that it avoids our immune response (whether natural or via vaccine).

The link to the person will need to be reliable, so no one can use a stolen or fake card. Obviously pictures, basic information, etc need to be on the card for a quick check, and information on the card links to a data source that can be used to further check identity and give more detailed information about immunity, such as may be needed if the person is in a medical situation.

It strikes me that this is a perfect use for blockchain. Each card, the data associated with it, when & how it is updates, can be accurately tracked in a way that is very, very hard to fake.

The data and the card should link to nothing else. There would be a temptation to be able cross reference the medical data with socioeconomic data, geographic information, even information about shopping habits to see if there are any correlations between between these factors and how people respond to COVID-19. This would be a nightmare as it introduces questions of consent, privacy, abuse of the data, fear of being spied upon.  Ensuring this card is for one purpose alone, with no link to anything else, would reduce the next drawback.

ID cards by the back door.

This will effectively be introducing ID cards, which some people object to strongly on moral or philosophical grounds. I’m not going to do more than note that this is an issue and observe that many societies have ID cards already. If these cards are kept to this one purpose, it would help make them more acceptable.

Criminality

Of course, as soon as such a valuable thing as a card that allows you to avoid social limitations is available, some people will want one, even though they know they are not immune. Criminals will want to create and sell them, so we need something, probably several things, (again, like a blockchain identifier on the card) to help guard against this. I would also suggest we would want to see strong punishment of individuals who try to use a fake card or get one by deceit. After all, these are probably the same selfish gits who bought all the toilet paper. As for criminals trying to make and sell fake cards, the punishments would be draconian – they would be putting a lot of people at risk.

Two-Tier Society

The cards would by their nature split society. Those who have a card would have more freedom. Those who do not would not.

Some people would never be able to get a card as they are immunocompromised  or similarly unable to be vaccinated.

Human nature says some people would discriminate or persecute people who are not immune if there was a way to identify this. I actually see this as the main reason to not have such a card.

Laws would be required to back up a repeated and strong message about why such discrimination is utterly wrong.

SARS-COV-2 Could Change

We do not yet know how the virus underlying COVID-19 will change over time. It is mutating – but ALL life mutates. We use the mutation to track how SARS-COV-2 has spread across the globe and the mutations, so far, are not known to alter it’s infection rate or how it impacts people (though I think I have seen some suggestions about this on social media that are more trustworthy than general scuttle).

However if it turns out that C-19 becomes C-23 and C-28 etc like Influenzela A, the card scheme still works but you are now stuck with identity cards and potential discrimination against those who are not immune etc.

End Life of the cards

I would want to see an agreed termination point for the cards stated when they are brought in. They or the data they link to will be deleted utterly in 3 years time. This can only be changed by a cross-political-party agreement.

 

That’s my idea. If you have any comments – for, against, highlight things I have wrong – I would love to hear.