Top 60 Oracle Blogs

Recent comments


Recursive WITH upgrade

There’s a notable change in the way the optimizer does cost and cardinality calculations for recursive subquery factoring that may make some of your execution plans change – with a massive impact on performance – as you upgrade to any version of Oracle from onwards. The problem appeared in a question on the Oracle Developer Community forum a little while ago, with a demonstration script to model the issue.

I’ve copied the script – with a little editing – and reproduced the change in execution plan described by the OP. Here’s my copy of the script, with the insert statements that generate the data (all 1,580 of them) removed.

Execution Plans

This is an example from the Oracle Developer Community of using the output of SQL Monitor to detect a problem with object statistics that resulted in an extremely poor choice of execution plan.

A short time after posting the original statement of the problem the OP identified where he thought the problem was and the general principle of why he thought he had a problem – so I didn’t have to read the entire execution plan to work out a strategy that would be (at least) a step in the right direction of solving the performance problem.

This note, then, is just a summary of the five minute that I spent confirming the OP’s hypothesis and explaining how to work around the problem he had identified. It does, however, give a little lead-in to the comments I made to the OP in order to give a more rounded picture of what his execution plan wass telling us.

When Implicit Date Conversions Attack 300w" sizes="(max-width: 232px) 85vw, 232px" />

Yesterday, one of the developers was having a problem and emailed to ask what was going on. They sent me a section of code from an old trigger that included some date handling that looked “interesting”.


Some bright spark had decided this was the best way to trim the time component off a date, and unfortunately for us it worked for a very, very long time. Many years in fact.

Video : SQLCL and Liquibase : Deploying Oracle Application Express (APEX) Applications

In today’s video we’ll give a quick demonstration of deploying an APEX application using the SQLcl implementation of Liquibase.

I Know what you’re thinking. Didn’t I do this video two weeks ago? The answer is yes and no. This video is very similar to the Liquibase video I did two weeks ago, but that was using the Liquibase Pro client. This video uses the SQLcl implementation of Liquibase, and more specifically the runOracleScript tag to achieve the same thing.

The video is based on this article, which has an example of deploying an APEX workspace and an APEX application.

Oracle ACFS: “du” vs. “df” and “acfsutil info”

By Franck Pachot

This is a demo about Oracle ACFS snapshots, and how to understand the used and free space, as displayed by “df”, when there are modifications in the base parent or the snapshot children. The important concept to understand is that, when you take a snapshot, any modification to the child or parent will

20 Years of

It was twenty years ago today that the first incarnation of my website was born.

It started life as a few scripts and notes put on the internet so I didn’t have to carry them around on floppy disks or CDs when I was moving around between contracts. I had been working with Oracle technologies for five years before the website was born, but most of the early content was Oracle 8i stuff.

Oracle Autonomous Linux: cron’d ksplice and yum updates

By Franck Pachot

Oracle Enterprise Linux (OEL) is a Linux distribution which is binary compatible with Red Hat Enterprise Linux (RHEL). However, unlike RHEL, OEL is open source, free to download, free to use, free to distribute, free to update and gets free bug fixes. And there are more frequent updates in OEL than in CentOS, the free base of RHEL. You can pay a subscription for additional support and features (like Ksplice or Dtrace) in OEL. It can run the same kernel as RHEL but also provides, still for free, the ‘unbreakable kernel’ (UEK) which is still compatible with RHEL but enhanced with optimizations, recommended especially when running Oracle products.

Most Recent – 2

A question arrived in my email a few days ago with the following observations on a statement that was supposed to query the data dictionary for some information about a specified composite partitioned table. The query was wrapped in a little PL/SQL, similar to the following:

Video : Hybrid Partitioned Tables in Oracle Database 19c

In today’s video we’ll give a quick demonstration of Hybrid Partitioned Tables, introduced in Oracle Database 19c.

The video is based on this 19c article.

The video only has a single example using external partitions pointing to CSV data. The article also includes and example using a Data Pump file.