This post was originally published on this site

Oracle 23c is like Christmas for me. There are many surprises to be found. This weekend I discovered another nice little enhancement.

In the last days, I developed and tested some demo scripts that I plan to show at my next conference sessions at APEX connect and ODTUG Kscope. It is a matter of honor that I will show these live demos on Oracle Database 23c Free, although the main focus of my presentations are not the new features of this release.

In one demo, I will explain why function calls in WHERE conditions should be avoided. Last year, I also wrote a blog post about this topic. When testing my demo script on Oracle 23c, I was surprised to see a new section “SQL Analysis Report” at the end of the output of dbms_xplan.display. It contains exactly the recommendation that I also will give in my talk: Try to avoid function calls and rewrite the WHERE condition whenever possible.

SELECT * FROM addresses
 WHERE UPPER(ctr_code) = 'GB';
SELECT * FROM dbms_xplan.display();
Plan hash value: 3184888728
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT  |           |   239 | 13384 |    61   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ADDRESSES |   239 | 13384 |    61   (0)| 00:00:01 |
Predicate Information (identified by operation id):
   1 - filter(UPPER("CTR_CODE")='GB')
SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
   1 -  SEL$1 / "ADDRESSES"@"SEL$1"
           -  The following columns have predicates which preclude their
              use as keys in index range scan. Consider rewriting the


I never saw this “SQL Analysis Report” section in an execution plan, and I can’t remember that I read about it in the Oracle Database 23c New Features documentation. Is this really a new feature in 23c, or did I miss something in the past? After a while, I found the answer in this blog post of Nigel Bayliss in the Oracle Optimizer blog: Yes, it is a new feature of Oracle Database 23c Free, and it works not only in dbms_xplan, but also in SQL Monitor.

My two learnings from this story:

  1. The new SQL Analysis Report section can be useful to avoid typical mistakes in SQL queries (expressions in WHERE conditions, UNION instead of UNION ALL, etc.)
  2. I should keep a better watch on the Oracle Optimizer blog. It contains a lot of useful information about the performance tuning and the features of the Oracle optimizer.