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.
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:
- 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.)
- 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.
