SQL Performance Tips in Execution Plans of Oracle 23c

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.

 
EXPLAIN PLAN FOR
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
              predicates.
                "CTR_CODE"
 

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.

Addendum (29 April 2023):

It is interesting to see how “intelligent” the recommendations of this SQL Analysis Report are. Jonathan Lewis motivated me to do some further investigations. Here just a small summary of the tests I did with my little demo example:

• The recommendation to rewrite the predicate is only displayed if a corresponding index is available (in this example, an index on column CTR_CODE)
• If a function-based index on the predicate UPPER(CTR_CODE) is available, it will be used for strong selectivities. For queries with a weak selectivity, the index will not be used. In this case, to SQL Analysis Report is displayed because a full table scan is a “good plan” for this situation.
• Interesting: If a virtual column or extended statistics are created on the predicate UPPER(CTR_CODE), the recommendation is only displayed for queries where an index range scan would make sense.

From my point of view, the recommendations are reasonable for all the situations described above. I also did some more test with some of my “Horror SQL” queries. In none of these queries, the SQL Analysis Report was able to solve the problem…

My third learning from this story: It’s definitely worth to have a closer look into this new feature of Oracle 23c.

Addendum (9 October 2023):

I did some more tests to prepare a presentation about SQL Analysis Report. Unfortunately, the findings I found in April are only partially true. In case of predicates in a WHERE condition, a SQL Analysis Report is only displayed when all of the following conditions are true:

• A predicate (e.g. a function call) is used in the WHERE condition
• An index on the column used in the predicate is available and visible to the optimizer
• Neither a function-based index, a virtual column nor extended statistics are available for the predicate

Contrary to what I described in my addendum of 29 April, the selectivity of the query (or any other value that affects the cost of an execution plan) has an impact on the SQL Analysis Report. The report is even shown for queries where an index is not useful.