Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Oracle 19c Automatic Indexing: Dropping Automatic Indexes (Fall Dog Bombs The Moon)

  Julian Dontcheff recently wrote a nice article on the new Automatic Index Optimization feature available in the upcoming Oracle Database 20c release (I’ll of course blog about this new 20c feature in the near future). Within the article, Julian mentioned a clever method of how to effectively drop Automatic Indexes that I thought would […]

Oracle Linux 8 (OL8) : Updates – UEK6, Podman, Database 19c Certification

Last week I had a quick email exchange with Simon Coter from Oracle about a few things related to Oracle Linux 8. I’ve been a little out of the loop over the last few weeks because of some important world event or another, so I had a bit of catching up to do.

UEK6 Went GA

UEK6 had been in preview for a while. In March it went GA, and I managed to miss that until recently. Even if you don’t care directly about the Linux kernel, this was a big thing because it was the first UEK release on Oracle Linux 8, and most of us outside Oracle were thinking this would mark the start of certification of Oracle products on OL8 (see below).

Downloads

About a month ago Oracle announced you can download the Oracle Linux ISO images from yum.oracle.com. See here.

This is much cleaner than using eDelivery, but form experience eDelivery is a lot quicker for downloads!

Podman

In Oracle Linux 8 the Oracle supplied Docker engine has been replaced by Podman. I was blindly expecting Docker CE support, like we had with OL7, once UEK6 was released. When I asked about this I got educated about Podman. About a day later someone else mentioned it on Twitter.

I’ve got a separate blog post coming about Podman.

Oracle Linux 8.2

A few days ago Oracle Linux 8.2 was born (here).

Oracle Database 19c Certified on Oracle Linux 8

In my email exchange Simon told me WebLogic was already certified on OL8, but there had been no official announcement about the database certification yet.

Today I noticed Mike Dietrich had a blog post about this. The database is now certified on OL8. </p />
</p></div>

    	  	<div class=

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

As promised in an earlier post here are my notes about installing Oracle Restart with ASM Filter Driver (ASMFD) 19c on Oracle Linux 7 using UEK 5.

Since the approach you are about to read isn’t explicitly covered in the documentation I suggest you ask Oracle Support whether it is supported before using this outside a playground/lab environment.

I also forgot about this post waiting to be published in my drafts folder, it should have gone out early April. Some components I used to put the post together aren’t the latest and greatest, please adjust accordingly.

My environment

The environment hasn’t changed massively compared to the RHCK edition of this post, except of course for the kernel used:

  • My lab environment consists of a KVM VM using the virtio driver
  • Oracle Linux 7.7
  • Kernel UEK 5 (patched to 4.14.35-1902.300.11.el7uek.x86_64)
  • All other packages up to date as of April 1st 2020
  • The first partition of /dev/vd[c-f] are to be used as ASM disks for +DATA
  • This is a fresh installation, no upgrade, no ASMLib has ever been in use

The challenge of using ASMFD with Oracle Restart and UEK5

The challenge installing Oracle Restart 19c together with ASMFD is lack of support in the base release:

[root@server5 bin]# ./acfsdriverstate supported
ACFS-9459: ADVM/ACFS is not supported on this OS version: '4.14.35-1902.300.11.el7uek.x86_64'
ACFS-9201: Not Supported
ACFS-9294: updating file /etc/sysconfig/oracledrivers.conf
[root@server5 bin]# uname -r
4.14.35-1902.300.11.el7uek.x86_64
[root@server5 bin]#  

Which is easy to run into since gridSetup.sh shipped with 19.3 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 as you just saw, but was a necessity in my case anyway as you can see later. As per My Oracle Support (MOS) certification matrix, DocID 1369107.1, Oracle 19.4.0 is the first release to support ASM Filter Driver (ASMFD). The base release, 19.3.0 does not support ASMFD (or ACFS for that matter) out of the box.

I’d like to iterate again 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 methods.

So how do I get to a working Oracle Restart 19c/ASMFD installation when I’m using UEK 5?

As I said in the first part of the series there are 2 options available for installing Oracle Restart 19c using ASMFD, at least in theory: 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.

UDEV

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

The docs then continue with an explanation of using disk labeling. I was very interested in the first part of the statement quoted above (… not using UDEV …) as it implies using UDEV is a viable option.

So I went ahead and tried to use ASMFD with UDEV rules. I have previously used UDEV rules without ASMFD when installing Oracle products so that wasn’t too hard to do.

After changing ownership of the relevant block devices to grid:asmadmin and mode to 0660 via UDEV, here’s the result on the changes. Permissions on my block devices to be used for ASM are now correct:

[root@server5 ~]# ls -l /dev/vd[cd]*
brw-rw----. 1 root disk     251, 32 Apr  1 15:49 /dev/vdc
brw-rw----. 1 grid asmadmin 251, 33 Apr  1 15:49 /dev/vdc1
brw-rw----. 1 root disk     251, 48 Apr  1 15:49 /dev/vdd
brw-rw----. 1 grid asmadmin 251, 49 Apr  1 15:49 /dev/vdd1
[root@server5 ~]#  

Preparing for patching and installing

Due to the issue of not being able to install Oracle Restart 19c/ASMFD with the base release I am using a slightly different approach this time. It’s rather subtle, but effective. There is only 1 difference in the call to gridSetup.sh compared to the last post. The goal is to merge the latest RU into the unzipped binaries prior to invoking the installer to create a supported configuration.

Since Oracle Grid Infrastructure 12.2 has been released it is possible to merge a Release Update (RU) into the unzipped installation image. This process has changed quite a bit over time, as reported by Ludovico Caldara for example. You can’t simply execute gridSetup.sh -applyRU and to merge the RU into the installation image, you also need to pass the parameters for an installation to avoid an error when trying a silent installation. In GUI mode, the graphical user interface starts after merging the patch into the binaries instead.

Combining the -applyRU flags and installation options as used in the previous post, I can both patch and install the software. My system has Oracle 19.6.0 available and I’m going to apply it. In preparation I have to update OPatch as the grid owner. Once that’s done I need to unzip the RU to a staging location, still logged in as the grid owner:

[grid@server5 ~]$ unzip -q /mnt/19.6.0/p6880880_190000_Linux-x86-64.zip -d /u01/app/grid/product/19.0.0/grid
replace /u01/app/grid/product/19.0.0/grid/OPatch/emdpatch.pl? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
[grid@server5 ~]$
[grid@server5 ~]$ unzip -q /mnt/19.6.0/p30501910_190000_Linux-x86-64.zip -d /u01/stage
[grid@server5 ~]$ 

Patch and install

With all preparations in place, it’s time to call gridSetup.sh with the -applyRU flag necessary for UEK5:

[grid@server5 ~]$ cd /u01/app/grid/product/19.0.0/grid/
[grid@server5 grid]$ ./gridSetup.sh -silent \
> INVENTORY_LOCATION=/u01/app/oraInventory \
> SELECTED_LANGUAGES=en \
> ORACLE_BASE=/u01/app/grid \
> ORACLE_HOME_NAME=RESTART_ASMFD_UEK \
> -waitforcompletion -ignorePrereqFailure -lenientInstallMode \
> -applyRU /u01/stage/30501910 \
> 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
Preparing the home to patch...
Applying the patch /u01/stage/30501910...
Successfully applied the patch.
The log can be found at: /tmp/GridSetupActions2020-04-01_04-03-20PM/installerPatchActions_2020-04-01_04-03-20PM.log
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-04-01_04-03-20PM.rsp

You can find the log of this install session at:
 /tmp/GridSetupActions2020-04-01_04-03-20PM/gridSetupActions2020-04-01_04-03-20PM.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:
[server5]



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-04-01_04-03-20PM.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-04-01_04-03-20PM
[grid@server5 grid]$ 

“Successfully Setup Software”, the message I was waiting for ;) From then on it’s the same as described with the previous blog post. Heed over there to read more about the execution of orainstRoot.sh, root.sh and the configuration tool step to finish the installation.

Verification

As with the previous post I’d like to share some of the results of the installation. First, what about the afddriverstate?

[grid@server5 ~]$ afddriverstate installed
AFD-9203: AFD device driver installed status: 'true'
[grid@server5 ~]$ afddriverstate loaded
AFD-9205: AFD device driver loaded status: 'true'
[grid@server5 ~]$ afddriverstate version
AFD-9325:     Driver OS kernel version = 4.14.35-1902.0.9.el7uek.x86_64.
AFD-9326:     Driver build number = 191219.
AFD-9212:     Driver build version = 19.0.0.0.0.
AFD-9547:     Driver available build number = 191219.
AFD-9548:     Driver available build version = 19.0.0.0.0.
[grid@server5 ~]$  

This looks all right. What about the patch level?

[grid@server5 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
30655595;TOMCAT RELEASE UPDATE 19.0.0.0.0 (30655595)
30557433;Database Release Update : 19.6.0.0.200114 (30557433)
30489632;ACFS RELEASE UPDATE 19.6.0.0.0 (30489632)
30489227;OCW RELEASE UPDATE 19.6.0.0.0 (30489227)

OPatch succeeded. 

And finally, what about ASM? Is it using ASM Filter Driver for its disks?

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> 
SQL> show parameter asm_diskstring

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

So all in all, the status isn’t different from the previous post, except I have already patched my environment, saving me time.

Failed attempts

I am keeping these as reminder to myself. Maybe they are of use to you as well when troubleshooting.

When trying to install Oracle Restart after labeling ASM disks as demonstrated in the previous post, the installation of the 19.3.0 (base release) fails:

[grid@server5 grid]$ ./gridSetup.sh -silent \
> INVENTORY_LOCATION=/u01/app/oraInventory \
> SELECTED_LANGUAGES=en \
> ORACLE_BASE=/u01/app/grid \
> ORACLE_HOME_NAME=RESTART_ASMFD_UEK \
> -waitforcompletion -ignorePrereqFailure -lenientInstallMode \
> -applyRU /u01/stage/30501910 \
> 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
Preparing the home to patch...
Applying the patch /u01/stage/30501910...
Successfully applied the patch.
The log can be found at: /tmp/GridSetupActions2020-04-01_03-25-52PM/installerPatchActions_2020-04-01_03-25-52PM.log
Launching Oracle Grid Infrastructure Setup Wizard...

[FATAL] [INS-30508] Invalid ASM disks.
   CAUSE: The disks [/dev/vdd1, /dev/vdc1] were not valid.
   ACTION: Please choose or enter valid ASM disks.
[FATAL] [INS-30515] Insufficient space available in the selected disks.
   CAUSE: Insufficient space available in the selected Disks. At least, 32 MB of free space is required.
   ACTION: Choose additional disks such that the total size should be at least 32 MB.
Moved the install session logs to:
 /u01/app/oraInventory/logs/GridSetupActions2020-04-01_03-25-52PM
[grid@server5 grid]$  

Similarly, if you don’t run with a very recent UEK 5 kernel patch level (4.14.35-1902.300.11.el7uek.x86_64 and later), the call to gridSetup.sh also fails, even when specifying -applyRU:

[grid@server5 grid]$ ./gridSetup.sh -silent \
> INVENTORY_LOCATION=/u01/app/oraInventory \
> SELECTED_LANGUAGES=en \
> ORACLE_BASE=/u01/app/grid \
> ORACLE_HOME_NAME=RESTART_ASMFD_UEK \
> -waitforcompletion -ignorePrereqFailure -lenientInstallMode \
> -applyRU /u01/stage/30501910 \
> 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
Preparing the home to patch...
Applying the patch /u01/stage/30501910...
Successfully applied the patch.
The log can be found at: /tmp/GridSetupActions2020-04-01_03-55-32PM/installerPatchActions_2020-04-01_03-55-32PM.log
Launching Oracle Grid Infrastructure Setup Wizard...

[FATAL] [INS-41223] ASM Filter Driver is not supported on this platform.
   ACTION: To proceed, do not specify or select the Oracle ASM Filter Driver option.
*ADDITIONAL INFORMATION:*
 - AFD-620: AFD is not supported on this operating system version: 'EL7'
 - AFD-9999: Cannot change to Not valid path:
 - 

Moved the install session logs to:
 /u01/app/oraInventory/logs/GridSetupActions2020-04-01_03-55-32PM 

The kernel I used in this case was the stock Oracle Linux 7.7 kernel:

[root@server5 ~]# uname -r
4.14.35-1818.3.3.el7uek.x86_64 

The nice touch is that it’s telling me the use of ASMFD on my old kernel isn’t supported.

Happy installing!

Video : NTILE : Problem Solving using Analytic Functions

In today’s video we work through some examples of using the NTILE analytic function to solve some specific problems.

The video is based on the following article.

You might find these useful also.

The star of today’s video is Alex Nuijten‘s daughter, but I thought I better include him also or he would get jealous. </p />
</p></div>

    	  	<div class=

Hacking Session: Oracle SQL Monitoring - Understanding the Execution Timeline column and CPU usage

Update: I have uploaded the videos of this hacking session to YouTube.
I will run another hacking session that has been in my mind for a while. It will be a pretty narrow deep-dive into one of the SQL Monitoring report’s columns that is not well explained:
Oracle SQL Monitoring - Understanding the Execution Timeline column In this hacking session, I will explain how to read the “Execution Timeline” column in the SQL Monitoring reports.

Hacking Session: Oracle SQL Monitoring - Understanding the Execution Timeline column and CPU usage

Update: I have uploaded the videos of this hacking session to YouTube.
I will run another hacking session that has been in my mind for a while. It will be a pretty narrow deep-dive into one of the SQL Monitoring report’s columns that is not well explained:
Oracle SQL Monitoring - Understanding the Execution Timeline column In this hacking session, I will explain how to read the “Execution Timeline” column in the SQL Monitoring reports.

The myth of NoSQL (vs. RDBMS) agility: adding attributes

By Franck Pachot

.
There are good reasons for NoSQL and semi-structured databases. And there are also many mistakes and myths. If people move from RDBMS to NoSQL because of wrong reasons, they will have a bad experience and this finally deserves NoSQL reputation. Those myths were settled by some database newbies who didn’t learn SQL and relational databases. And, rather than learning the basics of data modeling, and capabilities of SQL for data sets processing, they thought they had invented the next generation of persistence… when they actually came back to what was there before the invention of RDBMS: a hierarchical semi-structured data model. And now encountering the same problem that the relational database solved 40 years ago. This blog post is about one of those myths.

Myth: adding a column has to scan and update the whole table

I have read and heard that too many times. Ideas like: RDBMS and SQL are not agile to follow with the evolution of the data domain. Or: NoSQL data stores, because they are loose on the data structure, makes it easier to add new attributes. The wrong, but unfortunately common, idea is that adding a new column to a SQL table is an expensive operation because all rows must be updated. Here are some examples (just taking random examples to show how this idea is widely spread even with smart experts and good reputation forums):

A comment on twitter: “add KVs to JSON is so dramatically easier than altering an RDBMS table, especially a large one, to add a new column”

A question on StackOverflow: “Is ‘column-adding’ (schema modification) a key advantage of a NoSQL (mongodb) database over a RDBMS like MySQL” https://stackoverflow.com/questions/17117294/is-column-adding-schema-modification-a-key-advantage-of-a-nosql-mongodb-da/17118853. They are talking about months for this operation!

An article on Medium: “A migration which would add a new column in RDBMS doesn’t require this Scan and Update style migration for DynamoDB” https://medium.com/serverless-transformation/how-to-remain-agile-with-dynamodb-eca44ff9817.

Those are just examples. People hear it. People repeat it. People believe it. And they don’t test. And they don’t learn. They do not crosscheck with documentation. They do not test with their current database. When it is so easy to do.

Adding a column in SQL

Actually, adding a column is a fast operation in the major modern relational databases. I’ll create a table. Check the size. Then add a nullable column without default. Check the size. Then add a column with a default value. Check the size again. Size staying the same means no rows updated. Of course, you can test further: look at the elapsed time on a large table, and the amount of reads, and the redo/WAL generated,… You will see nothing in the major current RDBMS. Then you actually update all rows and compare. There you will see the size, the time, the reads, and the writes and understand that, with an explicit update the rows are actually updated. But not with the DDL to add a column.

PostgreSQL

Here is the example in PostgreSQL 12 in dbfiddle:
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=9acf5fcc62f0ff1edd0c41aafae91b05

Another example where I show the WAL size:

Oracle Database

Here is the example in Oracle Database 18c in dbfiddle:
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=b3a2d41636daeca5f8e9ea1d771bbd23

Another example:

Yes, I even tested in Oracle7 where, at that time, adding a not null column with a default value actually scanned the table. The workaround is easy with a view. Adding a nullable column (which is what you do in NoSQL) was already a fast operation, and that’s 40 years ago!

MySQL

Here is the example in MySQL 8 in dbfiddle:
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8c14e107e1f335b505565a0bde85f6ec

https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 768w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 1536w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 1996w" sizes="(max-width: 1024px) 100vw, 1024px" />

Microsoft SQL Server

It seems that the table I use is too large for dbfiddle but I’ve run the same on my laptop:


1> set statistics time on;
2> go

1> create table demo (x numeric);
2> go
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 2 ms,  elapsed time = 2 ms.

1> with q as (select 42 x union all select 42)
2> insert into demo
3> s join q j cross join q k cross join q l cross join q m cross join q n cross join q o  cross join q p  cross join q r cross join q s cross join q t  cross join q u;
4> go
SQL Server parse and compile time:
   CPU time = 11 ms, elapsed time = 12 ms.

 SQL Server Execution Times:
   CPU time = 2374 ms,  elapsed time = 2148 ms.

(1048576 rows affected)

1> alter table demo add b numeric ;
2> go
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 3 ms.

1> alter table demo add c numeric default 42 not null;
2> go
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 1 ms,  elapsed time = 2 ms.

2> go
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
x                    b                    c
-------------------- -------------------- --------------------
                  42                 NULL                   42
                  42                 NULL                   42
                  42                 NULL                   42

(3 rows affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

3> go
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 3768 ms,  elapsed time = 3826 ms.

(1048576 rows affected)

2 milliseconds for adding a column with a value, visible on all those million rows (and it can be more).

YugaByte DB

In a distributed database, metadata must be updated in all nodes, but this is still in milliseconds whatever the table size is:
https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 768w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 1142w" sizes="(max-width: 1024px) 100vw, 1024px" />

I didn’t show the test with not null and default value as I encountered an issue (adding column is fast but default value not selected). I don’t have the latest version (YugaByte DB is open source and in very active development) and this is probably an issue going to be fixed.

Tibero

Tibero is a database with very high compatibility with Oracle. I’ve run the same SQL. But this version 6 seems to be compatible with Oracle 11 where adding a non null column with default had to update all rows:
https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 768w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 1382w" sizes="(max-width: 1024px) 100vw, 1024px" />

You can test on any other databases with a code similar to this one:


-- CTAS CTE Cross Join is the most cross-RDBMS I've found to create one million rows
create table demo as with q as (select 42 x union all select 42) select 42 x from q a cross join q b cross join q c cross join q d cross join q e cross join q f cross join q g cross join q h cross join q i cross join q j cross join q k cross join q l cross join q m cross join q n cross join q o  cross join q p  cross join q r cross join q s cross join q t  cross join q u;
-- check the time to add a column
alter table demo add b numeric ;
-- check the time for a column with a value set for all existing rows
alter table demo add c numeric default 42 not null;
-- check that all rows show this value
select * from demo order by x fetch first 3 rows only;
-- compare with the time to really update all rows
update demo set c=42;

and please don’t hesitate to comment this blog post or the following tweet with you results:

NoSQL semi-structured

The myth comes from old versions of some databases that did no implement the ALTER TABLE .. ADD in an optimal way. And the NoSQL inventors probably knew only MySQL which was late in this area. Who said that MySQL evolution suffered from its acquisition by Oracle? They reduce the gap with other databases, like with this column adding optimisation.

If you stay with this outdated knowledge, you may think that NoSQL with semi-structured collections is more Agile, right? Yes, of course, you can add a new attribute when inserting a new item. It has zero cost and you don’t have to declare it to anyone. But what about the second case I tested in all those SQL databases, where you want to define a value for the existing rows as well? As we have seen, SQL allows that with a DEFAULT clause. In NoSQL you have to scan and update all items. Or you need to implement some logic in the application, like “if null then value”. That is not agile at all: as a side effect of a new feature, you need to change all data or all code.

Relational databases encapsulate the physical storage with a logical view. And in addition to that this logical view protects the existing application code when it evolves. This E.F Codd rule number 9: Logical Data Independence. You can deliver declarative changes to your structures without modifying any procedural code or stored data. Now, who is agile?

Structured data have metadata: performance and agility

How does it work? The RDBMS dictionary holds information about the structure of the rows, and this goes beyond a simple column name and datatype. The default value is defined here, which is why the ADD column was immediate. This is just an update of metadata. It doesn’t touch any existing data: performance. It exposes a virtual view to the application: agility. With Oracle, you can even version those views and deliver them to the application without interruption. This is called Edition Based Redefinition.

There are other smart things in the RDBMS dictionary. For example, when I add a column with the NOT NULL attribute, this assertion is guaranteed. I don’t need any code to check whether the value is set or not. Same with constraints: one declaration in a central dictionary makes all code safe and simpler because the assertion is guaranteed without additional testing. No need to check for data quality as it is enforced by design. Without it, how many sanity assumptions do you need to add in your code to ensure that erroneous data will not corrupt everything around? We have seen adding a column, but think about something even simple. Naming things is the most important in IT. Allow yourself to realize you made a mistake, or some business concepts change, and modify the name of a column for a more meaningful one. That can be done easily, even with a view to keep compatibility with previous code. Changing an attribute name in a large collection of JSON items is not so easy.

Relational databases have been invented for agility

Let me quote the reason why CERN decided to use Oracle in 1982 for the LEP – the ancestor of the LHC: Oracle The Database Management System For LEP: “Relational systems transform complex data structures into simple two-dimensional tables which are easy to visualize. These systems are intended for applications where preplanning is difficult…”

Preplanning not needed… isn’t that the definition of Agility with 20th century words?

Another good read to clear some myth: Relational Database: A Practical Foundation for Productivity by E.F. Codd Some problems that are solved by relational databases are the lack of distinction between “the programmer’s (logical) view of the data and the (physical) representation of data in storage”, the “subsequent changes in data description” forcing code changes, and “programmers were forced to think and code in terms of iterative loops” because of the lack of set processing. Who says that SQL and joins are slow? Are your iterative loops smarter than hash joins, nested loops and sort merge joins?

Never say No

I’m not saying that NoSQL is good or bad or better or worse. It is bad only when the decision is based on myths rather than facts. If you want agility on your data domain structures, stay relational. If you want to allow any future query pattern, stay relational. However, there are also some use cases that can fit in a relational database but may also benefit from another engine with optimal performance in key-value lookups. I have seen tables full of session state with a PRIMARY KEY (user or session ID) and a RAW column containing some data meaningful only for one application module (login service) and without durable purpose. They are acceptable in a SQL table if you take care of the physical model (you don’t want to cluster those rows in a context with many concurrent logins). But a Key-Value may be more suitable. We still see Oracle tables with LONG datatypes. If you like that you probably need a key-value NoSQL. Databases can store documents, but that’s luxury. They benefit from consistent backups and HA but at the prize of operating a very large and growing database. Timeseries, or graphs, are not easy to store in relational tables. NoSQL databases like AWS DynamoDB are very efficient for those specific use cases. But this is when all access patterns are known from design. If you know your data structure and cannot anticipate all queries, then relational databases systems (which means more than a simple data store) and SQL (the 4th generation declarative language to manipulate data by sets) are still the best choice for agility.

Cet article The myth of NoSQL (vs. RDBMS) agility: adding attributes est apparu en premier sur Blog dbi services.

Versioning for your local Vagrant boxes: handling updates

In my last post I summarised how to enable versioning for Vagrant box outside Vagrant’s cloud. In this part I’d like to share how to update a box.

My environment

The environment hasn’t changed compared to the first post. In summary I’m using

  • Ubuntu 20.04 LTS
  • Virtualbox 6.1.6
  • Vagrant 2.2.7

Updating a box

Let’s assume it’s time to update the base box for whatever reason. I most commonly update my boxes every so often after having run an “yum upgrade -y” to bring it up to the most current software. A new drop of the Guest Additions also triggers a rebuild, and so on.

Packaging

Once the changes are made, you need to package the box again. Continuing the previous example I save all my boxes and their JSON metadata in ~/vagrant/boxes. The box comes first:

[martin@host ~]$ vagrant package --base oraclelinux7base --output ~/vagrant/boxes/ol7_7.8.1.box

This creates a second box right next to the existing one. Note I bumped the version number to 7.8.1 to avoid file naming problems:

[martin@host boxes]$ ls -1
ol7_7.8.0.box
ol7_7.8.1.box
ol7.json 

Updating metadata

The next step is to update the JSON document. At this point in time, it references version 7.8.0 of my box:

[martin@host boxes]$ cat ol7.json 
{
    "name": "ol7",
    "description": "Martins Oracle Linux 7",
    "versions": [
      {
        "version": "7.8.0",
        "providers": [
          {
            "name": "virtualbox",
            "url": "file:///home/martin/vagrant/boxes/ol7_7.8.0.box",
            "checksum": "db048c3d61c0b5a8ddf6b59ab189248a42bf9a5b51ded12b2153e0f9729dfaa4",
            "checksum_type": "sha256"
          }
        ]
      }
    ]
  } 

You probably suspected what’s next :) A new version is created by adding a new element into the versions array, like so:

{
  "name": "ol7",
  "description": "Martins Oracle Linux 7",
  "versions": [
    {
      "version": "7.8.0",
      "providers": [
        {
          "name": "virtualbox",
          "url": "file:///home/martin/vagrant/boxes/ol7_7.8.0.box",
          "checksum": "db048c3d61c0b5a8ddf6b59ab189248a42bf9a5b51ded12b2153e0f9729dfaa4",
          "checksum_type": "sha256"
        }
      ]
    },
    {
      "version": "7.8.1",
      "providers": [
        {
          "name": "virtualbox",
          "url": "file:///home/martin/vagrant/boxes/ol7_7.8.1.box",
          "checksum": "f9d74dbbe88eab2f6a76e96b2268086439d49cb776b407c91e4bd3b3dc4f3f49",
          "checksum_type": "sha256"
        }
      ]
    }
  ]
} 

Don’t forget to update the SHA256 checksum!

Check for box updates

Back in my VM directory I can now check if there is a new version of my box:

[martin@host versioning]$ vagrant box outdated
Checking if box 'ol7' version '7.8.0' is up to date...
A newer version of the box 'ol7' for provider 'virtualbox' is
available! You currently have version '7.8.0'. The latest is version
'7.8.1'. Run `vagrant box update` to update.
[martin@host versioning]$ 

And there is! Not entirely surprising though, so let’s update the box:

[martin@host versioning]$ vagrant box update
==> default: Checking for updates to 'ol7'
    default: Latest installed version: 7.8.0
    default: Version constraints: 
    default: Provider: virtualbox
==> default: Updating 'ol7' with provider 'virtualbox' from version
==> default: '7.8.0' to '7.8.1'...
==> default: Loading metadata for box 'file:///home/martin/vagrant/boxes/ol7.json'
==> default: Adding box 'ol7' (v7.8.1) for provider: virtualbox
    default: Unpacking necessary files from: file:///home/martin/vagrant/boxes/ol7_7.8.1.box
    default: Calculating and comparing box checksum...
==> default: Successfully added box 'ol7' (v7.8.1) for 'virtualbox'! 

At the end of this exercise both versions are available:

[martin@host versioning]$ vagrant box list | grep ^ol7
ol7               (virtualbox, 7.8.0)
ol7               (virtualbox, 7.8.1)
[martin@host versioning]$  

This is so much better than my previous approach!

What are the effects of box versioning?

You could read earlier when I created a Vagrant VM based on version 7.8.0 of my box. This VM hasn’t been removed. What happens if I start it up now that there’s a newer version of the ol7 box available?

Bringing machine 'default' up with 'virtualbox' provider...
==> default: Checking if box 'ol7' version '7.8.0' is up to date...
==> default: A newer version of the box 'ol7' is available and already
==> default: installed, but your Vagrant machine is running against
==> default: version '7.8.0'. To update to version '7.8.1',
==> default: destroy and recreate your machine.
==> default: Clearing any previously set forwarded ports...
==> default: Fixed port collision for 22 => 2222. Now on port 2200.
==> default: Clearing any previously set network interfaces...
==> default: Preparing network interfaces based on configuration...
    default: Adapter 1: nat
==> default: Forwarding ports...
    default: 22 (guest) => 2200 (host) (adapter 1)
==> default: Running 'pre-boot' VM customizations...
==> default: Booting VM...
==> default: Waiting for machine to boot. This may take a few minutes...
    default: SSH address: 127.0.0.1:2200
    default: SSH username: vagrant
    default: SSH auth method: private key
==> default: Machine booted and ready!
==> default: Checking for guest additions in VM...
==> default: Setting hostname...
==> default: Mounting shared folders...
    default: /vagrant => /home/martin/vagrant/versioning
==> default: Machine already provisioned. Run `vagrant provision` or use the `--provision`
==> default: flag to force provisioning. Provisioners marked to run always will still run. 

Vagrant tells me that I’m using an old version of the box, and how to switch to the new one. I think I’ll do this eventually, but I can still work with the old version.

And what if I create a new VM? By default, Vagrant creates the new VM based on the latest version of my box, 7.8.1. You can see this here:

Bringing machine 'default' up with 'virtualbox' provider...
==> default: Importing base box 'ol7'...
==> default: Matching MAC address for NAT networking...
==> default: Checking if box 'ol7' version '7.8.1' is up to date...
==> default: Setting the name of the VM: versioning2_default_1588259041745_89693
==> default: Fixed port collision for 22 => 2222. Now on port 2201.
==> default: Clearing any previously set network interfaces...
==> default: Preparing network interfaces based on configuration...
    default: Adapter 1: nat
==> default: Forwarding ports...
    default: 22 (guest) => 2201 (host) (adapter 1)
==> default: Running 'pre-boot' VM customizations...
==> default: Booting VM...
==> default: Waiting for machine to boot. This may take a few minutes...
    default: SSH address: 127.0.0.1:2201
    default: SSH username: vagrant
    default: SSH auth method: private key
==> default: Machine booted and ready!
==> default: Checking for guest additions in VM...
==> default: Setting hostname...
==> default: Mounting shared folders...
    default: /vagrant => /home/martin/vagrant/versioning2 

Cleaning up

As with every technology, housekeeping is essential to keep disk usage in check. Refer back to the official documentation for more details on housekeeping and local copies of Vagrant boxes.

Summary

In the past I really struggled maintaining my local Vagrant boxes. Updating a box proved quite tricky and came with undesired side effects. Using versioning as demonstrated in this post is a great way out of this dilemma. And contrary to what I thought for a long time uploading my boxes to Vagrant cloud is not needed.

There is of course a lot more to say about versioning as this feature can do so much more. Maybe I’ll write another post about that subject some other time, until then I kindly refer you to the documentation.

Video : SQLcl : Format Query Results

In today’s video we demonstrate how to format query results in SQLcl using query comments and the SQLFORMAT setting.

For those of you that prefer to read, the video is based on this article.

The star of today’s video is Charles Wilson, who I got chatting with at the OTN lounge at a previous OpenWorld. </p />
</p></div>

    	  	<div class=

Oracle 19c Automatic Indexing: Mixing Manual and Automatic Indexes Part II (Stay)

  In my previous post, I discussed how Automatic Indexing did not recognise there was already an existing logically equivalent manually created index and so created effectively a redundant Automatic Index. I also discussed previously how Automatic Indexing was clever enough to logically add new columns to existing Automatic Indexes if it determined such a […]