Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Linux grub2 boot loader manipulation

This post is about how to manage grub2 in an easy way.

grub1

In the past, which is before linux EL7, the boot loader was grub, the grand unified bootloader (version 1). Things were very simple; if you installed another kernel (using rpm) it would add an entry to grub’s configuration in /boot/grub/menu.lst. If you wanted to change grub to boot that newly installed kernel by default you edit /boot/grub/menu.lst and set ‘default’ to the number, counting from zero, of the newly installed kernel, in the order of the kernels listed. If you wanted a certain option set for booting the kernel, you added it to the kernel line.

grub2

Then came grub2. Reading up on the grub2 homepage it turns out that grub 1 apparently was old code, and it was clunky, hard to maintain and extend with new features. Other reasons that probably contributed to grub2 acceptance where UEFI support as well as limited support for other architectures than x86 and x86_64.

grub2: the mechanics

The heart of the grub2 configuration is by default in /boot/grub2/grub/grub.cfg. The start of the file looks like bash shell script. A little further in the file, the installed kernels and arguments are found:

### BEGIN /etc/grub.d/10_linux ###
menuentry 'Oracle Linux Server (4.1.12-124.18.6.el7uek.x86_64 with Unbreakable Enterprise Kernel) 7.5' --class oracle --class gnu-linux --class gnu --class os --unrestricted $me
nuentry_id_option 'gnulinux-4.1.12-124.18.6.el7uek.x86_64-advanced-64c9f287-84a8-48ec-b048-364295362114' {
        load_video
        set gfxpayload=keep
        insmod gzio
        insmod part_msdos
        insmod xfs
        set root='hd0,msdos1'
        if [ x$feature_platform_search_hint = xy ]; then
          search --no-floppy --fs-uuid --set=root --hint-bios=hd0,msdos1 --hint-efi=hd0,msdos1 --hint-baremetal=ahci0,msdos1 --hint='hd0,msdos1'  4fc4063c-861b-4b07-8c28-e847a53
5c6cb
        else
          search --no-floppy --fs-uuid --set=root 4fc4063c-861b-4b07-8c28-e847a535c6cb
        fi
        linux16 /vmlinuz-4.1.12-124.18.6.el7uek.x86_64 root=/dev/mapper/ol-root ro net.ifnames=0 biosdevname=0 crashkernel=auto rd.lvm.lv=ol/root rd.lvm.lv=ol/swap rhgb quiet nu
ma=off transparent_hugepage=never
        initrd16 /initramfs-4.1.12-124.18.6.el7uek.x86_64.img
}
menuentry 'Oracle Linux Server (4.1.12-112.16.4.el7uek.x86_64 with Unbreakable Enterprise Kernel) 7.5' --class oracle --class gnu-linux --class gnu --class os --unrestricted $menuentry_id_option 'gnulinux-4.1.12-112.16.4.el7uek.x86_64-advanced-64c9f287-84a8-48ec-b048-364295362114' {

A knee-jerk reaction would be to see if this file can be edited, but the heading of the file quite clearly says to not do that and that any modification could be overwritten:

#
# DO NOT EDIT THIS FILE
#
# It is automatically generated by grub2-mkconfig using templates
# from /etc/grub.d and settings from /etc/default/grub
#

So, this points us /etc/default/grub. To be sure what actually to do, the documentation/manual makes it clear how to control what kernel and options are chosen in the chapter 6.1 simple configuration handling:

grub-mkconfig does have some limitations. While adding extra custom menu entries to the end of the list can be done by editing /etc/grub.d/40_custom or creating /boot/grub/custom.cfg, changing the order of menu entries or changing their titles may require making complex changes to shell scripts stored in /etc/grub.d/. This may be improved in the future. In the meantime, those who feel that it would be easier to write grub.cfg directly are encouraged to do so (see Booting, and Shell-like scripting), and to disable any system provided by their distribution to automatically run grub-mkconfig.

The file /etc/default/grub controls the operation of grub-mkconfig. It is sourced by a shell script, and so must be valid POSIX shell input; normally, it will just be a sequence of ‘KEY=value’ lines, but if the value contains spaces or other special characters then it must be quoted.

What this means to say is that for basic operation and selection, the /etc/default/grub file must be edited, after which grub-mkconfig must be run to “build” the actual grub settings. That’s not extremely intuitive from my point of view.

Let’s look at the /etc/default/grub on my test system, which is not touched as far as I know:

[root@o184 ~]# cat /etc/default/grub
GRUB_TIMEOUT=5
GRUB_DISTRIBUTOR="$(sed 's, release .*$,,g' /etc/system-release)"
GRUB_DEFAULT=saved
GRUB_DISABLE_SUBMENU=true
GRUB_TERMINAL_OUTPUT="console"
GRUB_CMDLINE_LINUX="net.ifnames=0 biosdevname=0 crashkernel=auto rd.lvm.lv=ol/root rd.lvm.lv=ol/swap rhgb quiet numa=off transparent_hugepage=never"
GRUB_DISABLE_RECOVERY="true"

The first thing to look at is ‘GRUB_DEFAULT’, which is the default grub entry/kernel that is booted. It is set to ‘saved’, which is not really helpful. The utility ‘grub2-editenv’ can be used to list what the saved entry is:

[root@o184 ~]# grub2-editenv list
saved_entry=0

Which means the first (this is counting from zero) entry in /boot/grub2/grub.cfg. I have not come across a utility that is installed by default to list the menu entries with their number in grub.cfg, it’s easy to do with some shell commands, although that feels clumsy to me:

[root@o184 ~]# cat /boot/grub2/grub.cfg | grep ^menuentry | nl -v0
     0	menuentry 'Oracle Linux Server (4.1.12-124.18.6.el7uek.x86_64 with Unbreakable Enterprise Kernel) 7.5' --class oracle --class gnu-linux --class gnu --class os --unrestricted $menuentry_id_option 'gnulinux-4.1.12-124.18.6.el7uek.x86_64-advanced-64c9f287-84a8-48ec-b048-364295362114' {
     1	menuentry 'Oracle Linux Server (4.1.12-112.16.4.el7uek.x86_64 with Unbreakable Enterprise Kernel) 7.5' --class oracle --class gnu-linux --class gnu --class os --unrestricted $menuentry_id_option 'gnulinux-4.1.12-112.16.4.el7uek.x86_64-advanced-64c9f287-84a8-48ec-b048-364295362114' {
     2	menuentry 'Oracle Linux Server (3.10.0-862.11.6.el7.x86_64 with Linux) 7.5' --class oracle --class gnu-linux --class gnu --class os --unrestricted $menuentry_id_option 'gnulinux-3.10.0-862.11.6.el7.x86_64-advanced-64c9f287-84a8-48ec-b048-364295362114' {
     3	menuentry 'Oracle Linux Server (0-rescue-069a6c1ff25b409fa87b8e587a2f8b4d with Linux) 7.5' --class oracle --class gnu-linux --class gnu --class os --unrestricted $menuentry_id_option 'gnulinux-0-rescue-069a6c1ff25b409fa87b8e587a2f8b4d-advanced-64c9f287-84a8-48ec-b048-364295362114' {

So in this case kernel 4.1.12-124.18.6.el7uek.x86_64 is started by default.

In fact, the value of ‘saved’ is stored in /boot/grub2/grubenv:

[root@o184 ~]# cat /boot/grub2/grubenv
# GRUB Environment Block
saved_entry=0
################...

There is more to tell about the GRUB_DEFAULT and especially GRUB_SAVEDEFAULT; if GRUB_SAVEDEFAULT is set and set to ‘true’, and GRUB_DEFAULT is set to ‘saved’, then any kernel that is chosen in grub at boot will be saved as default. However, as far as I can see, this option is not set in a default /etc/default/grub file.

Another caveat is that by setting the number of the menuentry, the order could be changed when installing a new kernel, and the numbering may be different. For that reason, it’s also possible to set either the entry description after menuentry, or a name (“identifier”) set with –id at the menuentry line.

grubby

This is all nice, but it feels really indirect to me, multiple files must be combined to understand the current settings, and multiple commands must be entered to make a change. This also means it’s a little less easy to automate.

Now on to the actual reason for this blogpost. There actually is a utility that can do most of the manipulation using a single command, which is installed by default. However, it is not very well known. Let me introduce ‘grubby’ to you!

List grub entries:

[root@o184 ~]# grubby --info=ALL
index=0
kernel=/boot/vmlinuz-4.1.12-124.18.6.el7uek.x86_64
args="ro net.ifnames=0 biosdevname=0 crashkernel=auto rd.lvm.lv=ol/root rd.lvm.lv=ol/swap rhgb quiet numa=off transparent_hugepage=never "
root=/dev/mapper/ol-root
initrd=/boot/initramfs-4.1.12-124.18.6.el7uek.x86_64.img
title=Oracle Linux Server (4.1.12-124.18.6.el7uek.x86_64 with Unbreakable Enterprise Kernel) 7.5
index=1
kernel=/boot/vmlinuz-4.1.12-112.16.4.el7uek.x86_64
args="ro net.ifnames=0 biosdevname=0 crashkernel=auto rd.lvm.lv=ol/root rd.lvm.lv=ol/swap rhgb quiet numa=off transparent_hugepage=never "
root=/dev/mapper/ol-root
initrd=/boot/initramfs-4.1.12-112.16.4.el7uek.x86_64.img
title=Oracle Linux Server (4.1.12-112.16.4.el7uek.x86_64 with Unbreakable Enterprise Kernel) 7.5
...

List current default kernel:

[root@o184 ~]# grubby --default-kernel
/boot/vmlinuz-4.1.12-124.18.6.el7uek.x86_64

Set another kernel to boot:

[root@o184 ~]# grubby --set-default /boot/vmlinuz-4.1.12-112.16.4.el7uek.x86_64
[root@o184 ~]# grubby --default-kernel
/boot/vmlinuz-4.1.12-112.16.4.el7uek.x86_64

List current settings of the grub2 menuentry:

[root@o184 ~]# grubby --info /boot/vmlinuz-4.1.12-112.16.4.el7uek.x86_64
index=1
kernel=/boot/vmlinuz-4.1.12-112.16.4.el7uek.x86_64
args="ro net.ifnames=0 biosdevname=0 crashkernel=auto rd.lvm.lv=ol/root rd.lvm.lv=ol/swap rhgb quiet numa=off transparent_hugepage=never"
root=/dev/mapper/ol-root
initrd=/boot/initramfs-4.1.12-112.16.4.el7uek.x86_64.img
title=Oracle Linux Server (4.1.12-112.16.4.el7uek.x86_64 with Unbreakable Enterprise Kernel) 7.5

Grubby also facilitates making changes to the arguments of the kernel.
For example, if you want to change the setting ‘numa=off’ to ‘numa=on’, you can remove the argument:

[root@o184 ~]# grubby --update-kernel /boot/vmlinuz-4.1.12-112.16.4.el7uek.x86_64 --remove-args="numa=off"
[root@o184 ~]# grubby --info /boot/vmlinuz-4.1.12-112.16.4.el7uek.x86_64 | grep ^args
args="ro net.ifnames=0 biosdevname=0 crashkernel=auto rd.lvm.lv=ol/root rd.lvm.lv=ol/swap rhgb quiet transparent_hugepage=never"

And then add it with the correct argument:

[root@o184 ~]# grubby --update-kernel /boot/vmlinuz-4.1.12-112.16.4.el7uek.x86_64 --args="numa=on"
[root@o184 ~]# grubby --info /boot/vmlinuz-4.1.12-112.16.4.el7uek.x86_64 | grep ^args
args="ro net.ifnames=0 biosdevname=0 crashkernel=auto rd.lvm.lv=ol/root rd.lvm.lv=ol/swap rhgb quiet transparent_hugepage=never numa=on"

Grubby makes it easy to list and manage the grub2 boot loader settings, and also easily be used in scripts.

Little things worth knowing: parallel Data Pump export in table mode

I haven’t used Data Pump in a little while but recently needed to do a bit of work involving this very useful utility to export a single table. I know that it is possible to export data in parallel using expdp, but I can’t recall the syntax for doing so off the top of my head when I need it. This post describes a potential approach to exporting a table in parallel. In the next post I will demonstrate an interesting case where using parallelism didn’t help me speed up the export. All of this was tested on 12.2 and 18.4.0, the examples I am sharing originate from my 18.4.0 single instance database (without ASM) running on Linux.

The setup

My lab environment is a bit limited when it comes to storage, so I’ll have to do with small-ish tables. The basic principles should still apply for larger segments though. Please note that my tables aren’t partitioned to keep the individual segment size as large as possible. 

My data is once more based on Swingbench’s order entry schema. The table I’ll use in my first example is the well-known ORDER_ITEMS table:

$ sql soe

SQLcl: Release 17.3.0 Production on Wed Dec 12 18:12:00 2018

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

Password? (*******?) *********
Last Successful login time: Wed Dec 12 2018 18:12:01 +01:00

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

SQL> info soe.order_items
TABLE: ORDER_ITEMS
LAST ANALYZED:2018-12-11 22:19:40.0
ROWS :68442528
SAMPLE SIZE :68442528
INMEMORY :DISABLED
COMMENTS :

Columns
NAME DATA TYPE NULL DEFAULT COMMENTS
*LINE_ITEM_ID NUMBER(3,0) Yes
*ORDER_ID NUMBER(12,0) Yes
PRODUCT_ID NUMBER(6,0) Yes
UNIT_PRICE NUMBER(8,2) Yes
QUANTITY NUMBER(8,0) Yes
DISPATCH_DATE DATE Yes
RETURN_DATE DATE Yes
GIFT_WRAP VARCHAR2(20 BYTE) Yes
CONDITION VARCHAR2(20 BYTE) Yes
SUPPLIER_ID NUMBER(6,0) Yes
ESTIMATED_DELIVERY DATE Yes

Indexes
INDEX_NAME UNIQUENESS STATUS FUNCIDX_STATUS COLUMNS
SOE.ITEM_ORDER_IX NONUNIQUE VALID ORDER_ID
SOE.ORDER_ITEMS_PK UNIQUE VALID ORDER_ID, LINE_ITEM_ID
SOE.ITEM_PRODUCT_IX NONUNIQUE VALID PRODUCT_ID

Running an export without any parallelism

I’ll use this example as the baseline, exporting the table without parallelism. This example is quite basic, and you will probably have to adjust it to suit your needs. I am assuming basic familiarity with the Data Pump toolset, if not, head over to the documentation and read up on the concepts. The non-parallel expdp call is shown here:

$ expdp martin directory=exptest logfile=exp_order_items_noparallel.log \
> dumpfile=exp_order_items_noparallel.dmp tables=soe.order_items

[...]

Export: Release 18.0.0.0.0 - Production on Wed Dec 12 19:00:38 2018
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "MARTIN"."SYS_EXPORT_TABLE_01": martin
directory=exptest logfile=exp_order_items_noparallel.log
dumpfile=exp_order_items_noparallel.dmp tables=soe.order_items
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "SOE"."ORDER_ITEMS" 3.601 GB 68442528 rows
Master table "MARTIN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
***************************************************************************
Dump file set for MARTIN.SYS_EXPORT_TABLE_01 is:
/u01/data_pump/ORCL/exp_order_items_noparallel.dmp
Job "MARTIN"."SYS_EXPORT_TABLE_01" successfully completed at Wed Dec 12 19:01:22 2018 elapsed 0 00:00:43

So it takes about 43 seconds for expdp to create the file.

Adding parallelism

The next attempt I made was to introduce parallelism. For expdp to actually use parallelism in the first place, you need to have multiple dump files to write to. This is accomplished by using the %U placeholder in the file name, as shown here:

$ expdp martin directory=exptest logfile=exp_order_items.log \
> dumpfile=exp_order_items_%U.dmp parallel=4 tables=soe.order_items

Export: Release 18.0.0.0.0 - Production on Wed Dec 12 19:56:42 2018
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "MARTIN"."SYS_EXPORT_TABLE_01": martin directory=exptest
logfile=exp_order_items.log dumpfile=exp_order_items_%U.dmp parallel=4
tables=soe.order_items
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "SOE"."ORDER_ITEMS" 3.601 GB 68442528 rows
Master table "MARTIN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

Dump file set for MARTIN.SYS_EXPORT_TABLE_01 is:
/u01/data_pump/ORCL/exp_order_items_01.dmp
/u01/data_pump/ORCL/exp_order_items_02.dmp
/u01/data_pump/ORCL/exp_order_items_03.dmp
/u01/data_pump/ORCL/exp_order_items_04.dmp
/u01/data_pump/ORCL/exp_order_items_05.dmp
Job "MARTIN"."SYS_EXPORT_TABLE_01" successfully completed at Wed Dec 12 19:57:02 2018 elapsed 0 00:00:20

So this isn’t too bad: using parallel 4 I managed to cut the time it takes to export the table in half. If you read the Data Pump white paper you can see that the job of the worker processes is not just the data unloading, they also have to create metadata for the object currently exported. I supposed this is the reason why there are 5 dump files. You can see they aren’t all the same size:

$ ls -lh /u01/data_pump/ORCL/exp_order_items_0[12345].dmp
-rw-r----- 1 oracle oinstall 1.2G Dec 12 19:57 /u01/data_pump/ORCL/exp_order_items_01.dmp
-rw-r----- 1 oracle oinstall 16K Dec 12 19:56 /u01/data_pump/ORCL/exp_order_items_02.dmp
-rw-r----- 1 oracle oinstall 192K Dec 12 19:57 /u01/data_pump/ORCL/exp_order_items_03.dmp
-rw-r----- 1 oracle oinstall 1.3G Dec 12 19:57 /u01/data_pump/ORCL/exp_order_items_04.dmp
-rw-r----- 1 oracle oinstall 1.3G Dec 12 19:57 /u01/data_pump/ORCL/exp_order_items_05.dmp

What I really like about the Data Pump API is the ability to attach to a job and see what it’s doing. While the export was running, I had a quick look at it:

$ expdp martin attach=SYS_EXPORT_TABLE_01

Export: Release 18.0.0.0.0 - Production on Wed Dec 12 19:56:54 2018
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
[...]
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Job: SYS_EXPORT_TABLE_01
Owner: MARTIN
Operation: EXPORT
Creator Privs: TRUE
GUID: 7CD567F0F8D057DDE0530164A8C0392F
Start Time: Wednesday, 12 December, 2018 19:56:43
Mode: TABLE
Instance: ORCL
Max Parallelism: 4
Timezone: +02:00
Timezone version: 31
Endianness: LITTLE
NLS character set: AL32UTF8
NLS NCHAR character set: AL16UTF16
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND martin directory=exptest
logfile=exp_order_items.log dumpfile=exp_order_items_%U.dmp
parallel=4 tables=soe.order_items
TRACE 0
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 4
Job Error Count: 0
Job heartbeat: 5
Dump File: /u01/data_pump/ORCL/exp_order_items_%u.dmp
Dump File: /u01/data_pump/ORCL/exp_order_items_01.dmp
bytes written: 8,192
Dump File: /u01/data_pump/ORCL/exp_order_items_02.dmp
bytes written: 16,384
Dump File: /u01/data_pump/ORCL/exp_order_items_03.dmp
bytes written: 16,384
Dump File: /u01/data_pump/ORCL/exp_order_items_04.dmp
bytes written: 4,096
Dump File: /u01/data_pump/ORCL/exp_order_items_05.dmp
bytes written: 4,096

Worker 1 Status:
Instance ID: 1
Instance name: ORCL
Host name: server1
Object start time: Wednesday, 12 December, 2018 19:56:46
Object status at: Wednesday, 12 December, 2018 19:56:47
Process Name: DW00
State: WORK WAITING

Worker 2 Status:
Instance ID: 1
Instance name: ORCL
Host name: server1
Object start time: Wednesday, 00 Sat, 0000 0:00:00
Object status at: Wednesday, 12 December, 2018 15
Process Name: DW01
State: WORK WAITING

Worker 3 Status:
Instance ID: 1
Instance name: ORCL
Host name: server1
Object start time: Wednesday, 12 December, 2018 19:56:47
Object status at: Wednesday, 12 December, 2018 19:56:48
Process Name: DW02
State: EXECUTING
Object Schema: SOE
Object Name: ORDER_ITEMS
Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 1
Completed Rows: 33,417,535
Worker Parallelism: 3

Worker 4 Status:
Instance ID: 1
Instance name: ORCL
Host name: server1
Access method: direct_path
Object start time: Wednesday, 12 December, 2018 19:56:45
Object status at: Wednesday, 12 December, 2018 19:56:49
Process Name: DW03
State: WORK WAITING

The output is quite interesting. It confirms that I’m running with parallel 4 and it also shows that only 1 file is really being written to by worker 3. It does so in parallel. If you recall from the ls command earlier, there were 3 files of roughly 1.3 GB each. They surely have been written to by the 3rd worker process.

Summary

I have been able to export a table in parallel using Data Pump, and by doing so I cut the time it took to export the table in half. This is a great feature which I am certainly going to use regularly. 

Understanding Partitioning in #Exasol

Exasol introduced Partitioning in version 6.1. This feature helps to improve the performance of statements accessing large tables. As an example, let’s take these two tables:

 

https://uhesse.files.wordpress.com/2018/10/tablest1t2.png?w=150 150w, https://uhesse.files.wordpress.com/2018/10/tablest1t2.png?w=300 300w" sizes="(max-width: 497px) 100vw, 497px" />

Say t2 is too large to fit in memory and may get partitioned therefore.

In contrast to distribution, partitioning should be done on columns that are used for filtering:

ALTER TABLE t2 PARTITION BY WhereCol;

Now without taking distribution into account (on a one-node cluster), the table t2 looks like this:

Partitioned Tablehttps://uhesse.files.wordpress.com/2018/12/t2partitioned.png?w=88 88w, https://uhesse.files.wordpress.com/2018/12/t2partitioned.png?w=176 176w" sizes="(max-width: 305px) 100vw, 305px" />

 

Notice that partitioning changes the way the table is physically ordered on disk.

A statement like

SELECT * FROM t2 WHERE WhereCol=’A’;

would have to load only the red part of the table into memory. This may show benefits on a one-node cluster as well as on multi-node clusters. On a multi-node cluster, a large table like t2 is distributed across the active nodes. It can additionally be partitioned also. Should the two tables reside on a three-node cluster with distribution on the JoinCol columns and the table t2 partitioned on the WhereCol column, they look like this:

Distributed Partitioned Tablehttps://uhesse.files.wordpress.com/2018/12/distributed_partitioned_table... 150w, https://uhesse.files.wordpress.com/2018/12/distributed_partitioned_table... 300w, https://uhesse.files.wordpress.com/2018/12/distributed_partitioned_table... 768w, https://uhesse.files.wordpress.com/2018/12/distributed_partitioned_table... 1024w, https://uhesse.files.wordpress.com/2018/12/distributed_partitioned_table... 1033w" sizes="(max-width: 620px) 100vw, 620px" />

 

That way, each node has to load a smaller portion of the table into memory if statements are executed that filter on the WhereCol column while joins on the JoinCol column are still local joins.

EXA_(USER|ALL|DBA)_TABLES shows both the distribution key and the partition key if any.

Notice that Exasol will automatically create an appropriate number of partitions – you don’t have to specify that.

CLI for Amazon RDS Performance Insights

Installing CLI on LINUX

1. install PIP

https://docs.aws.amazon.com/cli/latest/userguide/awscli-install-linux.html#awscli-install-linux-pip

curl -O https://bootstrap.pypa.io/get-pip.py
python get-pip.py --user

2. install AWS CLI

https://docs.aws.amazon.com/cli/latest/userguide/installing.html

pip install awscli --upgrade --user

3. configure

aws configure

For “aws configure” you will need

  • AWS Access Key ID:
  • AWS Secret Access Key:

Which you can get by going to the AWS console, going to IMS and creating access key.

Running example

Once “aws” is configured you can run the CLI like

aws \
 pi get-resource-metrics \
 --region us-east-1 \
 --service-type RDS \
 --identifier db-xxxxxx \
 --metric-queries "{\"Metric\": \"db.load.avg\"}" \
 --start-time `expr \`date +%s\` - 60480000 ` \
 --end-time `date +%s` \
 --period-in-seconds 86400

That “—identifier” is for one of my databases, so you will have to change that.
You will also have to modify region if you are accessing a database in a different region

getting json output

export AWS_DEFAULT_OUTPUT="json"

documentation

API

CLI

examples

My databases

  • db-YTDU5J5V66X7CXSCVDFD2V3SZM ( Aurora PostgreSQL)
  • db-2XQCJDBHGIXKDYUVVOIUIJ34LU ( Aurora MySQL)
  • db-Z2PNRYPV4J7LJLGDOKMISTWRQU (RDS MySQL)

see these blogs for loads on these databases

CPU load last 5 minutes

aws \
 pi get-resource-metrics \
 --region us-east-1 \
 --service-type RDS \
 --identifier db-YTDU5J5V66X7CXSCVDFD2V3SZM \
 --start-time `expr \`date +%s\` - 300 ` \
 --metric-queries '{
      "Metric": "db.load.avg",
      "Filter":{"db.wait_event.type": "CPU"}
      } ' \
 --end-time `date +%s` \
 --period-in-seconds 300

Top SQL by load

aws pi describe-dimension-keys \
    --region us-east-1 \
    --service-type RDS \
    --identifier db-YTDU5J5V66X7CXSCVDFD2V3SZM \
    --start-time `expr \`date +%s\` - 300 ` \
    --end-time `date +%s` \
    --metric db.load.avg \
    --group-by '{"Group":"db.sql"}'
    

Top Waits by load

aws pi describe-dimension-keys \
    --region us-east-1 \
    --service-type RDS \
    --identifier db-YTDU5J5V66X7CXSCVDFD2V3SZM \
    --start-time `expr \`date +%s\` - 300 ` \
    --end-time `date +%s` \
    --metric db.load.avg \
    --group-by '{"Group":"db.wait_event"}'

.
Top User by load

aws pi describe-dimension-keys \
    --region us-east-1 \
    --service-type RDS \
    --identifier db-YTDU5J5V66X7CXSCVDFD2V3SZM \
    --start-time `expr \`date +%s\` - 300 ` \
    --end-time `date +%s` \
    --metric db.load.avg \
    --group-by '{"Group":"db.user"}'

.

"Total": 0.15100671140939598, 
            "Dimensions": {
                "db.sql.db_id": "pi-4101593903", 
                "db.sql.id": "209554B4D97DBF72871AE0854DAD97385D553BAA", 
                "db.sql.tokenized_id": "1F61DDE1D315BB8F4BF198DB219D4180BC1CFE05", 
                "db.sql.statement": "WITH cte AS (\n   SELECT id     \n   FROM   authors \n   LIMIT  1     \n   )\nUPDATE authors s\nSET    email = 'toto' \nFROM   cte\nWHERE  s.id = cte.id\n\n"
            }

Top SQL by waits grouped

aws pi describe-dimension-keys \
    --region us-east-1 \
    --service-type RDS \
    --identifier db-YTDU5J5V66X7CXSCVDFD2V3SZM \
    --start-time `expr \`date +%s\` - 300 ` \
    --end-time `date +%s` \
    --metric db.load.avg \
    --group-by '{"Group":"db.sql"}' \
    --partition-by '{"Group": "db.wait_event"}'

.

{
            "Total": 0.1644295302013423, 
            "Dimensions": {
                "db.sql.db_id": "pi-4101593903", 
                "db.sql.id": "209554B4D97DBF72871AE0854DAD97385D553BAA", 
                "db.sql.tokenized_id": "1F61DDE1D315BB8F4BF198DB219D4180BC1CFE05", 
                "db.sql.statement": "WITH cte AS (\n   SELECT id     \n   FROM   authors \n   LIMIT  1     \n   )\nUPDATE authors s\nSET    email = 'toto' \nFROM   cte\nWHERE  s.id = cte.id\n\n"
            }, 
            "Partitions": [
                0.003355704697986577, 
                0.14093959731543623, 
                0.020134228187919462
            ]
        }, 
"PartitionKeys": [
        {
            "Dimensions": {
                "db.wait_event.type": "CPU", 
                "db.wait_event.name": "CPU"
            }
        }, 
        {
            "Dimensions": {
                "db.wait_event.type": "IO", 
                "db.wait_event.name": "IO:XactSync"
            }
        }, 
        {
            "Dimensions": {
                "db.wait_event.type": "Lock", 
                "db.wait_event.name": "Lock:transactionid"
            }
        }

Top SQL over last 5 minutes based on CPU

 aws pi describe-dimension-keys     \
    --region us-east-1     \
    --service-type RDS     \
    --identifier db-YTDU5J5V66X7CXSCVDFD2V3SZM     \
    --start-time `expr \`date +%s\` - 300 `     \
    --end-time `date +%s`     \
    --metric db.load.avg     \
    --group-by '{"Group":"db.sql"}'     \
    --filter '{"db.wait_event.type": "CPU"}'
{
"Total": 0.003355704697986577,
"Dimensions": {
"db.sql.db_id": "pi-4101593903",
"db.sql.id": "209554B4D97DBF72871AE0854DAD97385D553BAA",
"db.sql.tokenized_id": "1F61DDE1D315BB8F4BF198DB219D4180BC1CFE05",
"db.sql.statement": "WITH cte AS (\n SELECT id \n FROM authors \n LIMIT 1 \n )\nUPDATE authors s\nSET email = 'toto' \nFROM cte\nWHERE s.id = cte.id\n\n"
}

load over last 5 minutes based on CPU

aws \
 pi get-resource-metrics \
 --region us-east-1 \
 --service-type RDS \
 --identifier db-YTDU5J5V66X7CXSCVDFD2V3SZM \
 --start-time `expr \`date +%s\` - 300 ` \
 --metric-queries '{
      "Metric": "db.load.avg",
      "Filter":{"db.wait_event.type": "CPU"}
      } ' \
 --end-time `date +%s` \
 --period-in-seconds 300

 

Top SQL over last 5 minutes based on CPU

 aws pi describe-dimension-keys     \
    --region us-east-1     \
    --service-type RDS     \
    --identifier db-YTDU5J5V66X7CXSCVDFD2V3SZM     \
    --start-time `expr \`date +%s\` - 300 `     \
    --end-time `date +%s`     \
    --metric db.load.avg     \
    --group-by '{"Group":"db.sql"}'     \
    --filter '{"db.wait_event.type": "CPU"}'

alternatively with a partition by waits

aws pi describe-dimension-keys \
 --region us-east-1 \
 --service-type RDS \
 --identifier db-YTDU5J5V66X7CXSCVDFD2V3SZM \
 --start-time `expr \`date +%s\` - 300 ` \
 --end-time `date +%s` \
 --metric db.load.avg \
 --group-by '{"Group":"db.sql"}' \
 --partition-by '{"Group": "db.wait_event"}' \
 --filter '{"db.wait_event.type": "CPU"}'

CLI  with counter metrics

aws \
 pi get-resource-metrics \
 --region us-east-1 \
 --service-type RDS \
 --identifier db-VMM7GRZMTGWZNPWAJOLWTHQDDE \
 --metric-queries "{\"Metric\": \"db.Transactions.xact_commit.avg\"}" \
 --start-time `expr \`date +%s\` - 3600 ` \
 --end-time `date +%s` \
 --period-in-seconds 60 \
 --endpoint-url https://api.integ.pi.a2z.com

Accelerate your #BI Performance with #Exasol

Your BI users complain about slow performance of their analytical queries? Is this your Status Quo?

https://uhesse.files.wordpress.com/2018/12/bi_status_quo.png?w=150 150w, https://uhesse.files.wordpress.com/2018/12/bi_status_quo.png?w=300 300w, https://uhesse.files.wordpress.com/2018/12/bi_status_quo.png?w=768 768w, https://uhesse.files.wordpress.com/2018/12/bi_status_quo.png?w=1024 1024w, https://uhesse.files.wordpress.com/2018/12/bi_status_quo.png 1159w" sizes="(max-width: 620px) 100vw, 620px" />

tableau was taken as a popular example for AdHoc analytics but it might be any of the others like MicroStrategy, Looker, you name it. The good news is that this problem can be solved quite easily and without having to spend a fortune trying to speed up your legacy DWH to keep up with the BI demands:

Exasol High Performance Sidecarhttps://uhesse.files.wordpress.com/2018/12/exasol_sidecar.png?w=150 150w, https://uhesse.files.wordpress.com/2018/12/exasol_sidecar.png?w=300 300w, https://uhesse.files.wordpress.com/2018/12/exasol_sidecar.png?w=768 768w, https://uhesse.files.wordpress.com/2018/12/exasol_sidecar.png?w=1024 1024w, https://uhesse.files.wordpress.com/2018/12/exasol_sidecar.png 1182w" sizes="(max-width: 620px) 100vw, 620px" />

Using Exasol as a High Performance Sidecar to take away the pain from your BI users is the easy and fast cure for your problem! This is actually the most common way how Exasol arrives at companies. More often than not this may lead to a complete replacement of the legacy DWH by Exasol:

Exasol replaces legacy DWHhttps://uhesse.files.wordpress.com/2018/12/exasol_replace_legacy_dwh.png... 150w, https://uhesse.files.wordpress.com/2018/12/exasol_replace_legacy_dwh.png... 300w, https://uhesse.files.wordpress.com/2018/12/exasol_replace_legacy_dwh.png... 768w, https://uhesse.files.wordpress.com/2018/12/exasol_replace_legacy_dwh.png... 1024w, https://uhesse.files.wordpress.com/2018/12/exasol_replace_legacy_dwh.png 1171w" sizes="(max-width: 620px) 100vw, 620px" />

That’s what adidas, Otto and Zalando did, to name a few of our customers.

Don’t take our word for it, we are more than happy to do a PoC!

Minimal Oracle installation (and Docker image)

A new blog post on the Databases at CERN blog about some research on the minimal Oracle Database installation: https://db-blog.web.cern.ch/blog/franck-pachot/2018-12-minimal-oracle-1

And a bonus here: the Dockerfile which builds this minimal image. You need to build the Oracle XE image (oracle/database:18.4.0-xe) with the buildfiles provided by Oracle (https://github.com/oracle/docker-images/tree/master/OracleDatabase/SingleInstance/dockerfiles/18.4.0) and this Dockerfile will copy only the necessary:

FROM oraclelinux:7-slim
ENV ORACLE_BASE /opt/oracle
ENV ORACLE_HOME ${ORACLE_BASE}/product/18c/dbhomeXE
ENV ORACLE_SID=XS
#TODO# do something lighter than preinstall
RUN yum install -y oracle-database-preinstall-18c strace ; rm -rf /var/cache/yum
RUN mkdir -p ${ORACLE_BASE} ${ORACLE_HOME} ; chown -R oracle:oinstall ${ORACLE_BASE} ${ORACLE_HOME}
USER oracle
WORKDIR ${ORACLE_HOME}
################################################################################################################
# sqlplus
################################################################################################################
RUN mkdir -p bin
COPY --from=oracle/database:18.4.0-xe --chown=oracle:oinstall\
${ORACLE_HOME}/bin/sqlplus\
bin/
RUN mkdir -p lib
COPY --from=oracle/database:18.4.0-xe --chown=oracle:oinstall\
${ORACLE_HOME}/lib/libsqlplus.so\
${ORACLE_HOME}/lib/libclntsh.so.18.1\
${ORACLE_HOME}/lib/libclntshcore.so.18.1\
${ORACLE_HOME}/lib/libmql1.so\
${ORACLE_HOME}/lib/libipc1.so\
${ORACLE_HOME}/lib/libnnz18.so\
${ORACLE_HOME}/lib/libons.so\
lib/
RUN mkdir -p sqlplus/mesg
COPY --from=oracle/database:18.4.0-xe --chown=oracle:oinstall\
${ORACLE_HOME}/sqlplus/mesg/sp1us.msb\
${ORACLE_HOME}/sqlplus/mesg/sp2us.msb\
sqlplus/mesg/
RUN mkdir -p rdbms/mesg
COPY --from=oracle/database:18.4.0-xe --chown=oracle:oinstall\
${ORACLE_HOME}/rdbms/mesg/ocius.msb\
${ORACLE_HOME}/rdbms/mesg/oraus.msb\
${ORACLE_HOME}/rdbms/mesg/diaus.msb\
rdbms/mesg/
RUN mkdir -p oracore/zoneinfo
COPY --from=oracle/database:18.4.0-xe --chown=oracle:oinstall\
${ORACLE_HOME}/oracore/zoneinfo/*.dat\
oracore/zoneinfo/
RUN du -h ${ORACLE_BASE} | sort -u
################################################################################################################
# oracle core
################################################################################################################
#TODO# move audit and dbs (rooh)
RUN mkdir -p rdbms/audit dbs log rdbms/log
COPY --from=oracle/database:18.4.0-xe --chown=oracle:oinstall\
${ORACLE_HOME}/bin/oracle\
bin/
#RUN strip --remove-section=.comment ${ORACLE_HOME}/bin/oracle
COPY --from=oracle/database:18.4.0-xe --chown=oracle:oinstall\
${ORACLE_HOME}/lib/libodm18.so\
${ORACLE_HOME}/lib/libofs.so\
${ORACLE_HOME}/lib/libcell18.so\
${ORACLE_HOME}/lib/libskgxp18.so\
${ORACLE_HOME}/lib/libskjcx18.so\
${ORACLE_HOME}/lib/libclsra18.so\
${ORACLE_HOME}/lib/libdbcfg18.so\
${ORACLE_HOME}/lib/libhasgen18.so\
${ORACLE_HOME}/lib/libskgxn2.so\
${ORACLE_HOME}/lib/libocr18.so\
${ORACLE_HOME}/lib/libocrb18.so\
${ORACLE_HOME}/lib/libocrutl18.so\
${ORACLE_HOME}/lib/libmkl_rt.so\
${ORACLE_HOME}/lib/libasmclntsh18.so\
lib/
RUN mkdir -p oracore/mesg
COPY --from=oracle/database:18.4.0-xe --chown=oracle:oinstall\
${ORACLE_HOME}/oracore/mesg/lrmus.msb\
oracore/mesg
RUN echo -e 'db_name=XS' > ${ORACLE_HOME}/dbs/initXS.ora
################################################################################################################
# oracle rdbms
################################################################################################################
RUN mkdir -p ldap/mesg
COPY --from=oracle/database:18.4.0-xe --chown=oracle:oinstall\
${ORACLE_HOME}/ldap/mesg/ldapus.msb\
ldap/mesg/
RUN mkdir -p network/mesg
COPY --from=oracle/database:18.4.0-xe --chown=oracle:oinstall\
${ORACLE_HOME}/network/mesg/tnsus.msb\
network/mesg
RUN mkdir -p nls/data
COPY --from=oracle/database:18.4.0-xe --chown=oracle:oinstall\
${ORACLE_HOME}/nls/data\
nls/data
RUN mkdir -p rdbms/admin
COPY --from=oracle/database:18.4.0-xe --chown=oracle:oinstall\
${ORACLE_HOME}/rdbms/admin/sql.bsq\
${ORACLE_HOME}/rdbms/admin/dcore.bsq\
${ORACLE_HOME}/rdbms/admin/dsqlddl.bsq\
${ORACLE_HOME}/rdbms/admin/dmanage.bsq\
${ORACLE_HOME}/rdbms/admin/dplsql.bsq\
${ORACLE_HOME}/rdbms/admin/dtxnspc.bsq\
${ORACLE_HOME}/rdbms/admin/dfmap.bsq\
${ORACLE_HOME}/rdbms/admin/denv.bsq\
${ORACLE_HOME}/rdbms/admin/drac.bsq\
${ORACLE_HOME}/rdbms/admin/dsec.bsq\
${ORACLE_HOME}/rdbms/admin/doptim.bsq\
${ORACLE_HOME}/rdbms/admin/dobj.bsq\
${ORACLE_HOME}/rdbms/admin/djava.bsq\
${ORACLE_HOME}/rdbms/admin/dpart.bsq\
${ORACLE_HOME}/rdbms/admin/drep.bsq\
${ORACLE_HOME}/rdbms/admin/daw.bsq\
${ORACLE_HOME}/rdbms/admin/dsummgt.bsq\
${ORACLE_HOME}/rdbms/admin/dtools.bsq\
${ORACLE_HOME}/rdbms/admin/dexttab.bsq\
${ORACLE_HOME}/rdbms/admin/ddm.bsq\
${ORACLE_HOME}/rdbms/admin/dlmnr.bsq\
${ORACLE_HOME}/rdbms/admin/ddst.bsq\
${ORACLE_HOME}/rdbms/admin/dfba.bsq\
${ORACLE_HOME}/rdbms/admin/dpstdy.bsq\
${ORACLE_HOME}/rdbms/admin/drupg.bsq\
${ORACLE_HOME}/rdbms/admin/dtlog.bsq\
${ORACLE_HOME}/rdbms/admin/dmisc.bsq\
${ORACLE_HOME}/rdbms/admin/dhcs.bsq\
rdbms/admin/
RUN du -h ${ORACLE_HOME} | sort -h
RUN echo -e 'alias "sqlplus=cd ${ORACLE_BASE}/diag/rdbms/*/${ORACLE_SID}/trace ; cd - ; ${ORACLE_HOME}/bin/sqlplus"' > ~/.bashrc
RUN echo -e 'whenever sqlerror exit failure;\n startup nomount;\n create database;\ncreate spfile from pfile;' | ORACLE_BASE=${ORACLE_BASE} LD_LIBRARY_PATH=${ORACLE_HO
ME}/lib ORACLE_HOME=${ORACLE_HOME} ORACLE_SID=${ORACLE_SID} ${ORACLE_HOME}/bin/sqlplus / as sysdba
################################################################################################################
# oracle catalog
################################################################################################################
# simpler to take all files, that's only 20MB more than the required ones
COPY --from=oracle/database:18.4.0-xe --chown=oracle:oinstall\
${ORACLE_HOME}/rdbms/admin/*.sql\
${ORACLE_HOME}/rdbms/admin/*.plb\
rdbms/admin/
RUN echo -e 'startup;\n set echo on termout off\n spool /var/tmp/catalog.lst\n @?/rdbms/admin/catalog' | ORACLE_BASE=${ORACLE_BASE} LD_LIBRARY_PATH=${ORACLE_HOME}/lib
ORACLE_HOME=${ORACLE_HOME} ORACLE_SID=${ORACLE_SID} ${ORACLE_HOME}/bin/sqlplus / as sysdba
RUN du -h ${ORACLE_BASE} | sort -u
################################################################################################################
# oracle catproc
################################################################################################################
RUN mkdir -p rdbms/xml
# prvt_emx.register_files(TRUE) needs rdbms/xml
COPY --from=oracle/database:18.4.0-xe --chown=oracle:oinstall\
${ORACLE_HOME}/rdbms/xml\
rdbms/xml
RUN echo -e 'startup;\n set echo on termout off\n spool /var/tmp/catproc.lst\n @?/rdbms/admin/catproc' | ORACLE_BASE=${ORACLE_BASE} LD_LIBRARY_PATH=${ORACLE_HOME}/lib
ORACLE_HOME=${ORACLE_HOME} ORACLE_SID=${ORACLE_SID} ${ORACLE_HOME}/bin/sqlplus / as sysdba
RUN du -h ${ORACLE_BASE} | sort -u
################################################################################################################
# listener
################################################################################################################
RUN mkdir -p bin
COPY --from=oracle/database:18.4.0-xe --chown=oracle:oinstall\
${ORACLE_HOME}/bin/lsnrctl\
${ORACLE_HOME}/bin/tnslsnr\
bin/
#TODO# if we add an external volume, need to move all ?/dbs to it at start (and symlink). clonedb may help to have onlyy sparse files
CMD ${ORACLE_HOME}/bin/lsnrctl start ; echo startup | ${ORACLE_HOME}/bin/sqlplus / as sysdba ; ${ORACLE_HOME}/bin/lsnrctl status ; tail -F ${ORACLE_BASE}/diag/*/*/*/al
ert/log.xml
EXPOSE 1521

Of course, only limited SQL statements can be done with this. Doing more than this you will encounter an error and will have to add more files. This one, with catproc, brings the image to more than 1GB.

Minimal Oracle - 1

Case Study

A recent thread on the ODC database forum highlighted a case where the optimizer was estimating 83,000 for a particular index full scan when the SQL Monitor output for the operation showed that it was returning 11,000,000 rows.

Apart from the minor detail that the OP didn’t specifically ask a question, the information supplied was pretty good. The OP had given us a list of bind variables, with values, and the SQL statement, followed by the text output of the Monitor’ed SQL and, to get the predicate section of the plan, the output from a call to dbms_xplan. This was followed by the DDL for the critical index and a list of the stats for all the columns in the index.

Here’s the critical line of the plan (from the SQL Monitor report) followed by its predicate section (from the dbms_xplan output, but cosmetically enhanced) and some details of the columns used in the predicate:

SQL Plan Monitoring Details (Plan Hash Value=3210215320)
=================================================================================================================================================================================================================================
| Id    |            Operation            |         Name            |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read  | Read  | Write | Write | Mem  | Temp | Activity |       Activity Detail       | Progress | 
|       |                                 |                         | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs  | Bytes | Reqs  | Bytes |      |      |   (%)    |         (# samples)         |          |
=================================================================================================================================================================================================================================
|    11 |             INDEX FULL SCAN     | PK_HOUSEHOLD_GDC        |   83917 | 22799 |        86 |     +1 |     1 |      11M |     9 | 73728 |       |       |      |      |    24.21 | Cpu (77)                    |          |
=================================================================================================================================================================================================================================

  11 - filter(
        (    TO_DATE(:SYS_B_00||TO_CHAR("MONTH")||:SYS_B_01||TO_CHAR("YEAR"),:SYS_B_02)>=ADD_MONTHS(TRUNC(TO_DATE(:SYS_B_03,:SYS_B_04),:SYS_B_05),(-:SYS_B_06)) 
         AND TO_DATE(:SYS_B_00||TO_CHAR("MONTH")||:SYS_B_01||TO_CHAR("YEAR"),:SYS_B_02)<=TRUNC(TO_DATE(:SYS_B_07,:SYS_B_08),:SYS_B_09)-:SYS_B_10)
        )

COLUMN_NAME                    DATA_TYPE       NUM_DISTINCT  DENSITY  NUM_NULLS LAST_ANALYZED       HISTOGRAM
------------------------------ --------------- ------------ -------- ---------- ------------------- ---------------
YEAR                           NUMBER                     5        0          0 2018-12-02 13:19:10 FREQUENCY
MONTH                          NUMBER                    12        0          0 2018-12-02 13:19:10 FREQUENCY

I’ve included the full Monitor output at the end of the posting, or you could visit the ODC page if you want to see it, but if we look at just this line we can see that the index full scan starts running in the first second of the query (‘Start Active’), runs once (‘Execs’) and, as the OP said, retrieved 11M rows in that one scan compared to an estimated 83,917.

When we examine the predicate section we can understand why the optimizer could make such a large error – the SQL requires Oracle to combine two columns from the table with various bits of bind variables to construct a date which is then compares with a couple of constant dates derived from several input bind variables using range based comparisons.

This is an example of Oracle using a fixed estimate of 5% for the selectivity of “unknown range-based comparison” – but with two comparisons the selectivity becomes 5% of 5% = 0.25% (i.e. 1/400).

If we look at the column definitions and stats we see that we seem to have 5 possible years and 12 possible months (which could mean a range as small as 3 years and 2 months) – so a selectivity of 1/400 would be in the right ballpark if we were querying for a date range of roughly 4.5 days. Working the figures the other way around – if 83,917 is 1/400 of the data then there are about 33.5M rows in the table and we are querying for something more like 1/3 of the table.

Observations

I find it curious that the optimizer used an “index full scan” to fetch a huge amount of data from the index when there is no requirement for sorting (there is a subsequent “hash unique”, rather than “sort unique nosort”). I would have expected an “index fast full scan” so I am curious to know if some optimizer parameters have been fiddled with to get the optimizer to bypass the fast full scan. Possibly a change in parameter settings would result in a very different plan.

The names of the bind variables are of the form “SYS_B_nn” – which means that the original query has been subject to the effects of forced cursor sharing. Since we are apparently expecting to identify and manipulate millions of rows this looks like the type of query where you don’t want to use cursor sharing. If the session can set “cursor_sharing=exact” before running the query, or inject the hint /*+ cursor_sharing_exact */ into the query then perhaps we’d get a better estimate of rows (and a better plan). If hinting or setting session parameters is possible then setting optimzer_dynamic_sampling to level 3, or possibly 4, might be sufficient.

The messy expression combining month and year is a crippling handicap to the optimizer – so fixing the query to make the literals visible isn’t actually going to help. This is Oracle 12c, though – so we could add a virtual date column (declared as invisible to avoid the threat of inserts that don’t specify column lists) and gather stats on it. The combination of virtual column and literal values might give the optimizer the information it really needs. Here’s a little script to demonstrate:


rem
rem     Script:         virtual_study.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2018
rem     Purpose:
rem
rem     Last tested
rem             12.1.0.2

create table t1
as
with generator as (
        select 
                rownum id
        from dual 
        connect by 
                level <= 1e4 -- > comment to avoid WordPress format issue
)
select
        rownum                                  id,
        sysdate - (5 * 365) + rownum / 550      d1,
        to_number(
                to_char(
                        (sysdate - (5 * 365) + rownum / 550),
                        'MM'
                )
        )                                       month,
        to_number(
                to_char(
                        (sysdate - (5 * 365) + rownum / 550),
                        'YYYY'
                )
        )                                       year,
        lpad(rownum,10,'0')                     v1
from
        generator       v1,
        generator       v2
where
        rownum <= 1e6 -- > comment to avoid WordPress format issue
;

begin
        dbms_stats.gather_table_stats(
                ownname     => null,
                tabname     => 'T1',
                method_opt  => 'for all columns size 1 for columns month size 12 for columns year size 6'
        );
end;
/

I’ve created a table with a million rows with data going back roughly 5 years from current date, which means I need roughly 550 rows per day. I’ve then created histograms on the month and year columns to match the original posting. Now I’ll set up the bind variables and values specified by the OP and run a simple query to show the date information that the bind variables give, and the 1/400 selectivity of the OP’s predicate:


var SYS_B_00 varchar2(32);
var SYS_B_01 varchar2(32);
var SYS_B_02 varchar2(32);
var SYS_B_03 varchar2(32);
var SYS_B_04 varchar2(32);
var SYS_B_05 varchar2(32);
var SYS_B_06 number;
var SYS_B_07 varchar2(32);
var SYS_B_08 varchar2(32);
var SYS_B_09 varchar2(32);
var SYS_B_10 number;

exec :SYS_B_00:='01/';
exec :SYS_B_01:='/';
exec :SYS_B_02:='dd/MM/yyyy';
exec :SYS_B_03:='10/04/2018';
exec :SYS_B_04:='MM/dd/yyyy';
exec :SYS_B_05:='q';
exec :SYS_B_06:=12;
exec :SYS_B_07:='10/04/2018';
exec :SYS_B_08:='MM/dd/yyyy';
exec :SYS_B_09:='q';
exec :SYS_B_10:=1;

select
        to_date(:sys_b_00||to_char(month)||:sys_b_01||to_char(year),:sys_b_02)  d1, 
        add_months(trunc(to_date(:sys_b_03,:sys_b_04),:sys_b_05),(-:sys_b_06))  c1,
        to_date(:sys_b_00||to_char(month)||:sys_b_01||to_char(year),:sys_b_02)  d2,
        trunc(to_date(:sys_b_07,:sys_b_08),:sys_b_09)-:sys_b_10                 c2
from
        t1
where
        rownum = 1
;

set serveroutput off
alter session set statistics_level = all;

select  count(*)
from    t1
where
        (    to_date(:sys_b_00||to_char(month)||:sys_b_01||to_char(year),:sys_b_02) >= add_months(trunc(to_date(:sys_b_03,:sys_b_04),:sys_b_05),(-:sys_b_06)) 
         and to_date(:sys_b_00||to_char(month)||:sys_b_01||to_char(year),:sys_b_02) <= trunc(to_date(:sys_b_07,:sys_b_08),:sys_b_09)-:sys_b_10 )
;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

===========================================

D1        C1        D2        C2
--------- --------- --------- ---------
01-DEC-13 01-OCT-17 01-DEC-13 30-SEP-18


  COUNT(*)
----------
    200750

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:07.39 |    4980 |
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:07.39 |    4980 |
|*  2 |   FILTER            |      |      1 |        |    200K|00:00:06.42 |    4980 |
|*  3 |    TABLE ACCESS FULL| T1   |      1 |   2500 |    200K|00:00:04.59 |    4980 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TRUNC(TO_DATE(:SYS_B_07,:SYS_B_08),:SYS_B_09)-:SYS_B_10 .ge. ADD_MON
              THS(TRUNC(TO_DATE(:SYS_B_03,:SYS_B_04),:SYS_B_05),(-:SYS_B_06)))
   3 - filter((TO_DATE(:SYS_B_00||TO_CHAR("MONTH")||:SYS_B_01||TO_CHAR("YEAR")
              ,:SYS_B_02) .ge. ADD_MONTHS(TRUNC(TO_DATE(:SYS_B_03,:SYS_B_04),:SYS_B_05),(-:SYS_B
              _06)) AND TO_DATE(:SYS_B_00||TO_CHAR("MONTH")||:SYS_B_01||TO_CHAR("YEAR"),:SYS
              _B_02) .le. TRUNC(TO_DATE(:SYS_B_07,:SYS_B_08),:SYS_B_09)-:SYS_B_10))


Note: in this and subsequent text I’ve had to use .le. to represent “less than or equal to” and .ge. to represent “greater than or equal to”. in the execution plans

This shows us that the first row in my table has a date component of 1st Dec 2013, while the date range required by the OP was one year’s worth of data between 1st Oct 2017 and 30th Sept 2018. The optimizer’s estimate of 2,500 rows out of 1M is the 1/400 we expect.

Let’s test the effect of running the query using literals (i.e. in the OP’s environment stop the “cursor_sharing = force” effect):


select
        count(*)
from    t1
where
        (    to_date('01/'||to_char(month)||'/'||to_char(year),'dd/MM/yyyy') >= add_months(trunc(to_date('10/04/2018','dd/MM/yyyy'),'q'),(-12)) 
         and to_date('01/'||to_char(month)||'/'||to_char(year),'dd/MM/yyyy') <= trunc(to_date('10/04/2018','dd/MM/yyyy'),'q')-1 )
;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last cost'))

========================================================

 COUNT(*)
----------
    200750


--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   892 (100)|      1 |00:00:05.17 |    4980 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |            |      1 |00:00:05.17 |    4980 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |   2500 |   892  (30)|    200K|00:00:04.30 |    4980 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter((TO_DATE('01/'||TO_CHAR("MONTH")||'/'||TO_CHAR("YEAR"),'dd/MM/yyyy') .ge. TO_DAT
              E(' 2017-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              TO_DATE('01/'||TO_CHAR("MONTH")||'/'||TO_CHAR("YEAR"),'dd/MM/yyyy') .le. TO_DATE(' 2018-03-31
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')))


We can see that the literals have echoed through the plan to the predicate section, but the optimizer hasn’t changed its estimate. Let’s create the virtual column, gather stats on it, and try again:


alter table t1 add v_date invisible generated always as (
        to_date('01/'||to_char(month)||'/'||to_char(year),'dd/MM/yyyy')
) virtual
;

execute dbms_stats.gather_table_stats(user,'t1',method_opt=>'for columns v_date size 1')

select  /* virtual column */
        count(*)
from    t1
where
        (    to_date('01/'||to_char(month)||'/'||to_char(year),'dd/MM/yyyy') >= add_months(trunc(to_date('10/04/2018','dd/MM/yyyy'),'q'),(-12)) 
         and to_date('01/'||to_char(month)||'/'||to_char(year),'dd/MM/yyyy') <= trunc(to_date('10/04/2018','dd/MM/yyyy'),'q')-1 )
;

 select * from table(dbms_xplan.display_cursor(null,null,'allstats last cost'));

=======================================================================

 COUNT(*)
----------
    200750

--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   950 (100)|      1 |00:00:06.27 |    4980 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |            |      1 |00:00:06.27 |    4980 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |    236K|   950  (34)|    200K|00:00:04.78 |    4980 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter((TO_DATE('01/'||TO_CHAR("MONTH")||'/'||TO_CHAR("YEAR"),'dd/MM/yyyy') .ge. TO_DAT
              E(' 2017-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              TO_DATE('01/'||TO_CHAR("MONTH")||'/'||TO_CHAR("YEAR"),'dd/MM/yyyy') .le. TO_DATE(' 2018-03-31
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')))



The optimizer sees that the expression involving month and year matches the virtual column definition, and evaluates the two date expression to produce simple constants and gives us a cardinality estimate in the right ballpark.

Conclusion

Cursor sharing and “big” queries don’t mix. If you have queries that have to manipulate large volumes of data then the overhead of optimising each one separately is likely to be insignificant, and the threat of cardinality errors introduced by bind variables being re-used could be significant.

If you have to make use of an existing (bad) table definition, and can’t managed to write predicates that allow the optimizer to use existing column statistics, remember that you might be able to create a virtual (and invisible) column that captures the necessary definition thereby allowing you to give Oracle some statistics about the necessary predicate.

Footnote

In case you didn’t want to scan through the ODC page, here’s the full SQL Monitor output for the original query:


Global Stats
==============================================================================================
| Elapsed |   Cpu   |    IO    | Cluster  |  Other   | Buffer | Read | Read  | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) |  Gets  | Reqs | Bytes | Reqs  | Bytes |
==============================================================================================
|     320 |      76 |      140 |       39 |       66 |     8M | 257K |   2GB |  1528 | 306MB |
==============================================================================================
 
 
SQL Plan Monitoring Details (Plan Hash Value=3210215320)
=================================================================================================================================================================================================================================
| Id    |            Operation            |         Name            |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read  | Read  | Write | Write | Mem  | Temp | Activity |       Activity Detail       | Progress | 
|       |                                 |                         | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs  | Bytes | Reqs  | Bytes |      |      |   (%)    |         (# samples)         |          |
=================================================================================================================================================================================================================================
|  -> 0 | SELECT STATEMENT                |                         |         |       |       180 |   +142 |     1 |        0 |       |       |       |       |      |      |          |                             |          |
|  -> 1 |   SORT UNIQUE                   |                         |    1093 | 52574 |       180 |   +142 |     1 |        0 |       |       |   534 | 107MB |   2M | 113M |     0.94 | Cpu (3)                     |          |
|  -> 2 |    NESTED LOOPS                 |                         |    1093 | 52573 |       180 |   +142 |     1 |       3M |       |       |       |       |      |      |     0.31 | Cpu (1)                     |          |
|  -> 3 |     NESTED LOOPS                |                         |    1118 | 52573 |       180 |   +142 |     1 |       3M |       |       |       |       |      |      |     0.31 | Cpu (1)                     |          |
|  -> 4 |      HASH JOIN RIGHT SEMI       |                         |    1118 | 52238 |       189 |   +133 |     1 |       3M |       |       |       |       | 153M |      |     1.57 | Cpu (5)                     |          |
|     5 |       VIEW                      |                         |    157K | 31145 |         9 |   +134 |     1 |       2M |       |       |       |       |      |      |          |                             |          |
|     6 |        WINDOW SORT              |                         |    157K | 31145 |        57 |    +86 |     1 |       4M |  3777 | 199MB |   994 | 199MB |      |      |     3.14 | Cpu (5)                     |     100% |
|       |                                 |                         |         |       |           |        |       |          |       |       |       |       |      |      |          | direct path read temp (5)   |          |
|     7 |         HASH JOIN               |                         |    157K | 29653 |        50 |    +85 |     1 |       4M |       |       |       |       |      |      |     1.26 | Cpu (4)                     |          |
|     8 |          VIEW                   |                         |   81771 | 23273 |         1 |    +86 |     1 |       1M |       |       |       |       |      |      |          |                             |          |
|     9 |           HASH UNIQUE           |                         |   81771 | 23273 |        75 |    +12 |     1 |       1M |       |       |       |       |      |      |     1.89 | Cpu (6)                     |          |
|    10 |            FILTER               |                         |         |       |        78 |     +9 |     1 |      11M |       |       |       |       |      |      |     0.31 | Cpu (1)                     |          |
|    11 |             INDEX FULL SCAN     | PK_HOUSEHOLD_GDC        |   83917 | 22799 |        86 |     +1 |     1 |      11M |     9 | 73728 |       |       |      |      |    24.21 | Cpu (77)                    |          |
|    12 |          INDEX FULL SCAN        | PK_ADV_HOUSEHOLD_ACCT   |      8M |  6332 |        49 |    +86 |     1 |       8M |       |       |       |       |      |      |    12.58 | gc cr block 2-way (37)      |          |
|       |                                 |                         |         |       |           |        |       |          |       |       |       |       |      |      |          | gc current block 2-way (3)  |          |
| -> 13 |       INDEX FULL SCAN           | PK_ADV_HOUSEHOLD_ACCT   |      8M |  6332 |       180 |   +142 |     1 |       7M |       |       |       |       |      |      |     0.63 | Cpu (2)                     |          |
| -> 14 |      INDEX RANGE SCAN           | IDX4_LPL_BETA_CUST_RLTN |       1 |     1 |       181 |   +141 |    3M |       3M | 75759 | 592MB |       |       |      |      |    23.27 | gc current grant 2-way (1)  |          |
|       |                                 |                         |         |       |           |        |       |          |       |       |       |       |      |      |          | Cpu (21)                    |          |
|       |                                 |                         |         |       |           |        |       |          |       |       |       |       |      |      |          | db file parallel read (52)  |          |
| -> 15 |     TABLE ACCESS BY INDEX ROWID | IMPL_LPL_BETA_CUST_RLTN |       1 |     1 |       180 |   +142 |    3M |       3M |  177K |   1GB |       |       |      |      |    29.56 | Cpu (12)                    |          |
|       |                                 |                         |         |       |           |        |       |          |       |       |       |       |      |      |          | db file parallel read (81)  |          |
|       |                                 |                         |         |       |           |        |       |          |       |       |       |       |      |      |          | db file sequential read (1) |          |
=================================================================================================================================================================================================================================

Sangam 18: Presentations, Scripts and More

Many, many thanks to those who came to my three sessions at Sangam 18, the largest meeting of Oracle technologists in South Asia.

As I promised, you can download the presentations and scripts for all my sessions here. As always, your feedback will be highly appreciated.

Plans and Trees

Prompted by a question on the ODC database forum – and also because I failed to get to the “Bonus slides” on my presentation on basic execution plans at both the DOAG and UKOUG conferences, here’s a small of slides demonstrating how to convert a text execution plan into a tree that you can read using the mechanism described in Oracle’s white paper by the phrase: “start from the bottom left and work across and then up”.

The file is a Microsoft Powerpoint file (early version).

 

Installing Ansible on Oracle Linux 7 for test and development use

There are a few alternative ways of installing Ansible on Linux, and the install guide for Ansible 2.7 (the current version at the time of writing) does a great job in explaining them all in detail.  There is a potentially easier way to get to a current Ansible version if you are using Oracle Linux 7, but it comes with a very important limitation. Let’s get that out of the way first.

You need to be aware that the RPM-based installation of Ansible as described in this blog post requires you to enable Oracle’s EPEL repository. As per https://yum.oracle.com/oracle-linux-7.html, the EPEL repository is listed under “Packages for Test and Development” (bottom of the page) and these come with the following warning:  Note: The contents in the following repositories are for development purposes only. Oracle suggests these not be used in production. 

This is really important!

If you are ok with the limitation I just quoted from Oracle’s YUM server, please read on. If not, head back to the official Ansible documentation and use a different method instead. I only use Ansible in my own lab and therefore don’t mind.

Updating the repository configuration file

Back to the topic of Ansible … Before I get around to install Ansible on my machines I update my yum repository configuration file. Things are changing quickly, and I found /etc/yum.repos.d/public-yum-ol7.repo to be outdated at times. I always refresh it from yum.oracle.com just to be sure I’m not missing out on the new stuff.

# cd /etc/yum.repos.d
# mv -iv public-yum-ol7.repo public-yum-ol7.repo.$(date +%y%m%d)
# wget http://yum.oracle.com/public-yum-ol7.repo

With the new file in place, use your preferred method to enable the ol7_developer_EPEL repository. I simply edit public-yum-ol7.repo, there are other ways like yum-config-manager getting you there. But be advised: I just noticed the from December 17 at least the UEK Release 5 repository might be enabled by default leading to a potentially unwanted kernel upgrade.

Install Ansible

With the developer EPEL repository enabled, you have access to a great many Ansible versions. At the time of writing, these were available:

# yum --showduplicates list ansible
Loaded plugins: ulninfo
Available Packages
ansible.noarch                      2.3.1.0-1.el7                       ol7_developer_EPEL
ansible.noarch                      2.4.2.0-1.el7                       ol7_developer_EPEL
ansible.noarch                      2.5.0-2.el7                         ol7_developer_EPEL
ansible.noarch                      2.5.1-1.el7                         ol7_developer_EPEL
ansible.noarch                      2.5.2-1.el7                         ol7_developer_EPEL
ansible.noarch                      2.6.1-1.el7                         ol7_developer_EPEL
ansible.noarch                      2.6.2-1.el7                         ol7_developer_EPEL
ansible.noarch                      2.6.4-1.el7                         ol7_developer_EPEL
ansible.noarch                      2.6.5-1.el7                         ol7_developer_EPEL
ansible.noarch                      2.7.0-1.el7                         ol7_developer_EPEL

# yum info ansible
Loaded plugins: ulninfo
Available Packages
Name        : ansible
Arch        : noarch
Version     : 2.7.0
Release     : 1.el7
Size        : 11 M
Repo        : ol7_developer_EPEL/x86_64
Summary     : SSH-based configuration management, deployment, and task execution system
URL         : http://ansible.com
Licence     : GPLv3+
Description : Ansible is a radically simple model-driven configuration management,
            : multi-node deployment, and remote task execution system. Ansible works
            : over SSH and does not require any software or daemons to be installed
            : on remote nodes. Extension modules can be written in any language and
            : are transferred to managed machines automatically.

Happy testing!