SQL Analysis Report in Oracle 23c

SQL Analysis report was the new feature built into the Optimizer in Oracle 23c database that help us to identity some mistakes while building queries
 
It was a report enabled by default and appears as a new section at the end of SQL execution plan.
 
Here are some examples where SQL Analysis report kicks in to provide some meaningful insights
 
Having function calls on indexed columns:
 
demo@FREEPDB1> select *
  2  from t
  3  where upper(owner)= 'SYS';
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|    12M| 52830   (1)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T    |   100K|    12M| 52830   (1)| 00:00:03 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(UPPER("OWNER")='SYS')
 
SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
-------------------------------------------------------------------------------
 
   1 -  SEL$1 / "T"@"SEL$1"
           -  The following columns have predicates which preclude their
              use as keys in index range scan. Consider rewriting the
              predicates.
                "OWNER"
 
Incorrectly missing a join condition in a multi-table join.
 
demo@FREEPDB1> select *
  2  from emp e1, emp e2, emp e3
  3  where e1.empno = e2.empno
  4  and e3.sal > 10
  5  and e1.deptno = 10;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 4236580214
 
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    65 |  4940 |    10   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      |    65 |  4940 |    10   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | EMP  |     5 |   190 |     3   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |      |    14 |   532 |     7   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL | EMP  |    14 |   532 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("E1"."DEPTNO"=10)
   4 - filter("E3"."SAL">10)
 
SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
-------------------------------------------------------------------------------
 
   1 -  SEL$B43A75B1
           -  The query block has 1 cartesian product which may be
              expensive. Consider adding join conditions or removing the
              disconnected tables or views.
 
Having a union instead of union-all
 
demo@FREEPDB1> select ename from emp
  2  union
  3  select dname from dept;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 556839882
 
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    18 |   124 |     8  (25)| 00:00:01 |
|   1 |  HASH UNIQUE        |      |    18 |   124 |     8  (25)| 00:00:01 |
|   2 |   UNION-ALL         |      |    18 |   124 |     8  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |    84 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| DEPT |     4 |    40 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
 
SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
-------------------------------------------------------------------------------
 
   1 -  SET$1
           -  The query block contains UNION which may be expensive.
              Consider using UNION ALL if duplicates are allowed or
              uniqueness is guaranteed.