121-oracle数据库SQL优化器的一些有待提高之处-场景11~21

老虎刘谈oracle性能优化 2025-05-06

197

oracle数据库性能问题,国产库SQL优化, 都可以联系老虎刘, 微信: ora_service. 朋友圈有大量的技术文章点评分享, 欢迎广大SQL优化爱好者加微信一起学习.

我这里还有oracle开发规范,SQL审核工具,培训材料,应有尽有,主打两个字:专业.

正文开始:

在之前的114和115两篇文章中, 我已经列举了oracle数据库SQL优化器的一些不足(共10个场景), 这里再补充一些. 这些SQL如果不改写, 执行效率相对会比较差,所以,开发人员不是把SQL写出来实现业务逻辑就完了,还要照顾数据库的"脾气"和"情绪".

值得一提的是,这些场景,有些国产数据库却能完全不需要改写,就能够选择高效的执行路径来执行这些SQL.

说明:

以下SQL都来自生产系统, 在19.19版本下测试, 大部分SQL在23Ai版本也测试过,基本上没有变化.

场景11 : left join有or,不能hash join,不能识别为inner join

select count(distinct b.object_id) 
from t1 a
left join t99 b
    on a.object_id=b.object_id
AND (a.owner='SYS' or b.owner='SYS')
;
说明:
执行计划如下所示,有两个问题:
1.不能使用hash join
2.没有识别出这是个inner join
这个SQL如果要提高执行效率,必须改写. 
有些国产数据库可以识别这是一个inner join
,不需要改写,可以使用hash join
---------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |     1 |   232K  (1)| 00:00:10 |
|   1 |  HASH GROUP BY        |                 |     1 |            |          |
|   2 |   MERGE JOIN OUTER    |                 | 77275 |   232K  (1)| 00:00:10 |
|   3 |    TABLE ACCESS FULL  | T1              | 77275 |   447   (1)| 00:00:01 |
|   4 |    BUFFER SORT        |                 |     1 |   231K  (1)| 00:00:10 |
|   5 |     VIEW              | VW_LAT_2E38C6CE |     1 |     3   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS FULL| T99             |     1 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------
注: 这个SQL的原型,本来效率更差,就是and后面的or缺少了括号.

场景12 : union all参与的多表关联,不能push predicate

--其中v_t1t2是一个view, 是t1表和t2表的union all, 
--两表的object_id字段上都有索引
select  a.owner,a.object_id,b.object_type,c.created
from v_t1t2 a, t3 b,t4 c
where b.object_id=a.object_id
and b.object_type='WINDOW'
and a.object_name=c.object_name
;


说明:
如果没有t4表参与关联, t3与v_t1t2会使用Nested loops关联方法
(因为t3结果集xiao,只返回9条记录),多了t4表参与关联后
,t3和v_t1t2就只能选择hash join,这个选择是低效的.
如果要得到高效的执行计划, 一般是需要改写. 也能通过hint解决(比较复杂).
某些国产数据库没有这个问题.
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |    22 |  1230   (1)| 00:00:01 |
|   1 |  NESTED LOOPS                |                    |    22 |  1230   (1)| 00:00:01 |
|   2 |   NESTED LOOPS               |                    |    22 |  1230   (1)| 00:00:01 |
|*  3 |    HASH JOIN                 |                    |    18 |  1176   (1)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL        | T3                 |     9 |   392   (1)| 00:00:01 |
|   5 |     VIEW                     | V_T1T2             |   150K|   783   (1)| 00:00:01 |
|   6 |      UNION-ALL               |                    |       |            |          |
|   7 |       TABLE ACCESS FULL      | T1                 | 75026 |   392   (1)| 00:00:01 |
|   8 |       TABLE ACCESS FULL      | T2                 | 75028 |   392   (1)| 00:00:01 |
|*  9 |    INDEX RANGE SCAN          | IDX_T4_OBJECT_NAME |     1 |     2   (0)| 00:00:01 |
|  10 |   TABLE ACCESS BY INDEX ROWID| T4                 |     1 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

场景13 : <>,not in, not like 不能走索引

--下面几个SQL, 某些国产数据库也是可以走索引的,
oracle数据库要想走索引, 必须通过改写或函数索引来实现:


select * from t1 where status<>'VALID';
select * from t1 where  namespace not in(1,4,12);
select * from t1 where status not like 'VALID%';

场景14: not exists关联字段上使用了函数,不能使用hash join

下面SQL,not exists里面使用了函数,
导致执行计划无法选择效率相对较高的hash join,
而是使用了低效的filter:


select count(*) from t1 a
where not exists    
(select 1 
 from t2 b 
 where a.object_id=b.object_id 
 and abs(a.DATA_OBJECT_ID-b.DATA_OBJECT_ID)>1 ---使用了函数
);


执行计划, 只能使用filter:
--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | E-Rows | Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |      1 |   138K  (1)| 00:00:06 |
|   1 |  SORT AGGREGATE               |                   |      1 |            |          |
|*  2 |   FILTER                      |                   |        |            |          |
|   3 |    TABLE ACCESS FULL          | T1                |  76913 |  1175   (1)| 00:00:01 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| T2                |      1 |     2   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | UIDX_T2_OBJECT_ID |      1 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------


如果要得到hash join的执行计划, 也是需要改写的.
其他数据库没有这个问题.

场景15: group by 带having 不能push predicate

下面SQL,如果没有having count(*)>1, 
oracle优化器是可以做push predicate,
加了having后就不行了, 只能使用hash join.
某些国产数据库在这种情况下可以push predicate.
select * 
from t1 a
left join 
  (select object_name,count(*) as cnt 
    from t2 
    group by object_name 
    having count(*)>1
  ) b
on a.object_name=b.object_name
where a.status='INVALID'
;


oracle的执行计划,要想SQL高效执行, 必须改写:
------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | E-Rows | Cost (%CPU)| E-Time   |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |     14 |   416   (1)| 00:00:01 |
|*  1 |  HASH JOIN OUTER                      |               |     14 |   416   (1)| 00:00:01 |
|   2 |   JOIN FILTER CREATE                  | :BF0000       |     14 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1            |     14 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | IDX_T1_STATUS |     14 |     3   (0)| 00:00:01 |
|   5 |   VIEW                                |               |   3207 |   412   (1)| 00:00:01 |
|*  6 |    HASH GROUP BY                      |               |   3207 |   412   (1)| 00:00:01 |
|   7 |     JOIN FILTER USE                   | :BF0000       |  77695 |   410   (1)| 00:00:01 |
|*  8 |      TABLE ACCESS FULL                | T2            |  77695 |   410   (1)| 00:00:01 |
------------------------------------------------------------------------------------------------

场景16: not exists里面存在or连接的多个关联条件,只能filter

--关联条件带or , 即使加or_expand 也不生效:
select /*+ or_expand */ count(*) 
from t1 a
where not exists
(select 1 from t2 b
 where a.object_id=b.object_id
       or    
       a.data_object_id=b.data_object_id
 );


oracle执行计划,使用的是filter,
要想得到高效执行计划, 需要改写.
有些国产数据库,可以做or_expand,执行效率更高.
oracle当前执行计划:
---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                  | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                       |      1 |        |   169K(100)|          |      1 |00:00:00.65 |    7985 |
|   1 |  SORT AGGREGATE                   |                       |      1 |      1 |            |          |      1 |00:00:00.65 |    7985 |
|*  2 |   FILTER                          |                       |      1 |        |            |          |    638 |00:00:00.65 |    7985 |
|   3 |    TABLE ACCESS FULL              | T1                    |      1 |  76913 |  1175   (1)| 00:00:01 |  76911 |00:00:00.04 |    4283 |
|   4 |    BITMAP CONVERSION TO ROWIDS    |                       |  76909 |        |            |          |  76273 |00:00:00.50 |    3702 |
|   5 |     BITMAP OR                     |                       |  76909 |        |            |          |  76273 |00:00:00.44 |    3702 |
|   6 |      BITMAP CONVERSION FROM ROWIDS|                       |  76909 |        |            |          |   7727 |00:00:00.10 |    1306 |
|*  7 |       INDEX RANGE SCAN            | IDX_T2_DATA_OBJECT_ID |  76909 |        |     1   (0)| 00:00:01 |   8115 |00:00:00.05 |    1306 |
|   8 |      BITMAP CONVERSION FROM ROWIDS|                       |  76909 |        |            |          |  76272 |00:00:00.23 |    2396 |
|*  9 |       INDEX RANGE SCAN            | IDX_T2_OBJECT_ID      |  76909 |        |     1   (0)| 00:00:01 |  76272 |00:00:00.13 |    2396 |
---------------------------------------------------------------------------------------------------------------------------------------------

场景17: exists里面存在or连接的多个关联条件,也只能filter

select count(*) 
from t1 a
where exists
(select 1 from t2 b
 where a.object_id=b.object_id
       or    
      a.data_object_id=b.data_object_id
 );


 与not exists一样,exists也只能使用filter的执行计划,
要想得到高效执行计划,也必须改写.
某些国产数据库可以不需要改写,就可以实现or_expand
oracle当前执行计划:
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                  | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                       |      1 |        |   169K(100)|          |      1 |00:00:00.57 |    7978 |    186 |
|   1 |  SORT AGGREGATE                   |                       |      1 |      1 |            |          |      1 |00:00:00.57 |    7978 |    186 |
|*  2 |   FILTER                          |                       |      1 |        |            |          |  76277 |00:00:00.56 |    7978 |    186 |
|   3 |    TABLE ACCESS FULL              | T1                    |      1 |  76913 |  1175   (1)| 00:00:01 |  76913 |00:00:00.03 |    4283 |      0 |
|   4 |    BITMAP CONVERSION TO ROWIDS    |                       |  76911 |        |            |          |  76277 |00:00:00.41 |    3695 |    186 |
|   5 |     BITMAP OR                     |                       |  76911 |        |            |          |  76277 |00:00:00.37 |    3695 |    186 |
|   6 |      BITMAP CONVERSION FROM ROWIDS|                       |  76911 |        |            |          |   7729 |00:00:00.10 |    1306 |     17 |
|*  7 |       INDEX RANGE SCAN            | IDX_T2_DATA_OBJECT_ID |  76911 |        |     1   (0)| 00:00:01 |   8117 |00:00:00.04 |    1306 |     17 |
|   8 |      BITMAP CONVERSION FROM ROWIDS|                       |  76911 |        |            |          |  76277 |00:00:00.18 |    2389 |    169 |
|*  9 |       INDEX RANGE SCAN            | IDX_T2_OBJECT_ID      |  76911 |        |     1   (0)| 00:00:01 |  76277 |00:00:00.10 |    2389 |    169 |
------------------------------------------------------------------------------------------------------------------------------------------------------

场景18: ctas或IAS,主表on条件有in时,不能使用hash join

下面SQL,如果去掉create table(insert into test_xxx也是一样),
优化器默认会选择hash join,加上了就只能使用低效的merge join:
create table test_xxx 
as
select a.owner,b.object_name 
from t1 a
left join t2 b
 on a.object_id=b.object_id
and a.owner in ('SYS','PUBLIC','SYSTEM')  
;
oracle数据库要想得到hash join的执行计划,需要改写.
其他数据库没有这个问题.
oracle当前执行计划:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name             | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Writes |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT                    |                  |      1 |        |   151K(100)|          |      0 |00:00:00.54 |   13266 |    424 |      |       |          |
|   1 |  LOAD AS SELECT                           | TEST_XXX         |      1 |        |            |          |      0 |00:00:00.54 |   13266 |    424 |  7355K|  7355K| 2070K (0)|
|   2 |   OPTIMIZER STATISTICS GATHERING          |                  |      1 |  75594 |   151K  (1)| 00:00:06 |  75594 |00:00:00.46 |   12231 |      0 |   256K|   256K|  256K (0)|
|   3 |    MERGE JOIN OUTER                       |                  |      1 |  75594 |   151K  (1)| 00:00:06 |  75594 |00:00:00.42 |   12231 |      0 |      |       |          |
|   4 |     TABLE ACCESS FULL                     | T1               |      1 |  75594 |   410   (1)| 00:00:01 |  75594 |00:00:00.02 |    1451 |      0 |      |       |          |
|   5 |     BUFFER SORT                           |                  |  75594 |      1 |   151K  (1)| 00:00:06 |  65513 |00:00:00.34 |   10780 |      0 |  2048 |  2048 | 2048 (0)|
|   6 |      VIEW                                 | VW_LAT_DF9E236D  |  75594 |      1 |     2   (0)| 00:00:01 |  65513 |00:00:00.21 |   10780 |      0 |      |       |          |
|*  7 |       FILTER                              |                  |  75594 |        |            |          |  65513 |00:00:00.16 |   10780 |      0 |      |       |          |
|   8 |        TABLE ACCESS BY INDEX ROWID BATCHED| T2               |  66237 |      1 |     2   (0)| 00:00:01 |  65513 |00:00:00.12 |   10780 |      0 |      |       |          |
|*  9 |         INDEX RANGE SCAN                  | IDX_T2_OBJECT_ID |  66237 |      1 |     1   (0)| 00:00:01 |  65513 |00:00:00.07 |    8289 |      0 |      |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

场景19: select ... from dual 参与union all时,不能push predicate

create or replace view v_dual 
as
(select '-1' as id from dual
union all
select to_char(data_object_id) as id from t1
union all
select object_name from t3
 );


--下面SQL不能push predicate:
select * from t2 a
inner join v_dual  b
on a.object_name=b.id
and a.object_id=1
;


只有oracle数据库有这个问题,
要想得到高效执行计划,需要较为复杂的改写.
其他数据库没有这个问题.
oracle当前执行计划:
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | E-Rows | Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |        |  1590 (100)|          |
|   1 |  NESTED LOOPS                |                   |      2 |  1590   (1)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T2                |      1 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | UIDX_T2_OBJECT_ID |      1 |     1   (0)| 00:00:01 |
|*  4 |   VIEW                       | V_DUAL            |      2 |  1588   (1)| 00:00:01 |
|   5 |    UNION-ALL                 |                   |        |            |          |
|   6 |     FAST DUAL                |                   |      1 |     2   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL        | T1                |  76913 |  1175   (1)| 00:00:01 |
|   8 |     TABLE ACCESS FULL        | T3                |  75069 |   411   (1)| 00:00:01 |-----object_name字段上有索引也不使用
-------------------------------------------------------------------------------------------

场景20: 子查询的值不能push predicate到union all

create view v_t1t2 
as 
select * from t1
union all
select * from t2;




--下面SQL,子查询返回的值,不能push predicate到view:
select  a.owner,a.object_id
from v_t1t2 a 
where object_id=(select object_id from t2 
where object_name='TAB$'
);


oracle数据库如果要得到高效执行计划,需要改写.
某些国产数据库不需要改写,可以push predicate.


下面是oracle的执行计划:
----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name               | E-Rows | Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                    |        |  1589 (100)|          |
|*  1 |  VIEW                                | V_T1T2             |    154K|  1585   (1)| 00:00:01 |
|   2 |   UNION-ALL                          |                    |        |            |          |
|   3 |    TABLE ACCESS FULL                 | T1                 |  76913 |  1175   (1)| 00:00:01 |
|   4 |    TABLE ACCESS FULL                 | T2                 |  77695 |   410   (1)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID BATCHED| T2                 |      1 |     4   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN                  | IDX_T2_OBJECT_NAME |      1 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

场景21: distinct order by 取top N,只能全表扫描(或index fast full scan)

select distinct object_name 
from t1 a
order by object_name desc 
fetch first 5 rows only
;


oracle 数据库如果要得到高效执行计划,
需要改写.
某些国产数据库不需要改写,就可以得到高效执行计划.
-----------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | E-Rows | Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |        |  2802 (100)|          |       |       |          |
|*  1 |  VIEW                    |      |      5 |  2802   (1)| 00:00:01 |       |       |          |
|*  2 |   WINDOW SORT PUSHED RANK|      |  63760 |  2802   (1)| 00:00:01 |  9216 |  9216 | 8192  (0)|
|   3 |    VIEW                  |      |  63760 |  1799   (1)| 00:00:01 |       |       |          |
|   4 |     HASH UNIQUE          |      |  63760 |  1799   (1)| 00:00:01 |    11M|  2194K| 5580K (0)|
|   5 |      TABLE ACCESS FULL   | T1   |  76913 |  1175   (1)| 00:00:01 |       |       |          |
-----------------------------------------------------------------------------------------------------

未完待续......

文章转载自老虎刘谈oracle性能优化，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

121-oracle数据库SQL优化器的一些有待提高之处-场景11~21

评论