学习随笔：ORACLE：优化器缺陷
我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
本文转自朋友的真实案例分享。
学习随笔：ORACLE：优化器缺陷
ORACLE的优化器，在众多dba眼中都是世界上最好的优化器，一直是其他数据库，包括国产数据库追赶的目标，特别是在查询转换上，oracle确实所向无敌。但是本文要介绍的一个小案例对于oracle来说应该算是一个美中不足的地方。
该sql是一条非常简单的分页sql，由于生产sql不便展示，我在自己的测试环境中复现了一下，发现及时最新的23ai优化器存在着这一点点的小不足。
sql文本非常的简单，T1的object_id列是存在索引的。
SELECT *
FROM (SELECT t1.object_name
,t2.DATA_OBJECT_ID
,t2.OBJECT_TYPE
,t2.LAST_DDL_TIME
,t1.STATUS
FROM t1,t2
WHERE t1.object_id = t2.object_id
AND t2.OWNER = 'SYS'
ORDER BY t2.LAST_DDL_TIME DESC)
WHERE rownum <= 20;
执行计划和执行消耗：
SQL> set autot trace
SQL> select * from (select t1.object_name,t2.DATA_OBJECT_ID,t2.OBJECT_TYPE,t2.LAST_DDL_TIME,t1.STATUS from t1,t2 where t1.object_id=t2.object_id and t2.owner='SYS' order by t2.LAST_DDL_TIME desc) where rownum<=20;
20 rows selected.
Elapsed: 00:00:03.54
Execution Plan
----------------------------------------------------------
Plan hash value: 2229090023
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 2120 | | 2446 (1)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 19910 | 2060K| | 2446 (1)| 00:00:01 |
|* 3 | SORT ORDER BY STOPKEY| | 19910 | 1555K| 1824K| 2446 (1)| 00:00:01 |
|* 4 | HASH JOIN | | 19910 | 1555K| | 2075 (1)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T2 | 10115 | 296K| | 1244 (1)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T1 | 141K| 6914K| | 831 (1)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=20)
3 - filter(ROWNUM<=20)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
5 - filter("T2"."OWNER"='SYS')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
98234 consistent gets
50989 physical reads
3951508 redo size
1381 bytes sent via SQL*Net to client
133 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
20 rows processed
要优化该sql非常简单，按照之前写过的一篇分页sql优化文章的方法，很快就能把sql优化掉。由于T1的object_id列是存在索引的，所以只需要创建T2表的(OWNER,LAST_DDL_TIME)基本可以实现秒出，具体原因参考之前的文章。http://www.minniebaby.tech/2023/05/04/sql优化案例：分页查询一/
sql优化：
SQL> create index idx2 on t2(owner,last_ddl_time);
Index created.
Elapsed: 00:00:00.31
SQL> set autot trace
SQL> SELECT *
2 FROM (SELECT t1.object_name
3 ,t2.DATA_OBJECT_ID
4 ,t2.OBJECT_TYPE
5 ,t2.LAST_DDL_TIME
6 ,t1.STATUS
7 FROM t1,t2
8 WHERE t1.object_id = t2.object_id
9 AND t2.OWNER = 'SYS'
10 ORDER BY t2.LAST_DDL_TIME DESC)
11 WHERE rownum <= 20;
20 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3110385888
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 2120 | 37 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 21 | 2226 | 37 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 21 | 1680 | 37 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 22 | 1680 | 37 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | T2 | 10115 | 296K| 4 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN DESCENDING| IDX2 | 11 | | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX1 | 2 | | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | T1 | 2 | 100 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=20)
6 - access("T2"."OWNER"='SYS')
7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
31 consistent gets
23 physical reads
0 redo size
1381 bytes sent via SQL*Net to client
133 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
但是虽然优化完了该sql，也发现了oracle优化器的一个不足，即使没有创建索引，该sql也应该利用rownum的stopkey特性，先去扫描T2表并且排序之后再去连接T1表，而不是先连接再排序再stopkey。
我们把索引删除，手动去改写sql：
SQL> SELECT *
2 FROM (SELECT t1.object_name
3 ,t2.DATA_OBJECT_ID
4 ,t2.OBJECT_TYPE
5 ,t2.LAST_DDL_TIME
6 ,t1.STATUS
7 FROM t1
8 ,(SELECT *
9 FROM t2
10 WHERE t2.OWNER = 'SYS'
11 ORDER BY t2.LAST_DDL_TIME DESC) t2
12 WHERE t1.object_id = t2.object_id)
13 WHERE rownum <= 20;
20 rows selected.
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
Plan hash value: 1310430299
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1800 | | 35 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | NESTED LOOPS | | 21 | 1890 | | 35 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 22 | 1890 | | 35 (0)| 00:00:01 |
| 4 | VIEW | | 10115 | 474K| | 2 (0)| 00:00:01 |
| 5 | SORT ORDER BY | | 10115 | 1501K| 2144K| 1588 (1)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | T2 | 10115 | 1501K| | 1244 (1)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX1 | 2 | | | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 100 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=20)
6 - filter("T2"."OWNER"='SYS')
7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
57 recursive calls
0 db block gets
4644 consistent gets
4599 physical reads
0 redo size
1381 bytes sent via SQL*Net to client
133 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
20 rows processed
虽然肯定比不上之前创建索引的方式，但是也比之前好很多，oracle的查询转换居然没考虑到这种情况。
相比之下mysql居然有这方面的考虑，在没有被优化过的情况下，该sql场景mysql选择了最正确的执行计划。
mysql> explain select t1.name,t1.email,t2.created_at,t2.is_active,t2.balance from sample_table t1,sample_table1 t2 where t1.id=t2.id order by t2.created_at desc,t2.id limit 20;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+--------+----------+----------------+
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 198266 | 100.00 | Using filesort |
| 1 | SIMPLE | t1 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test_db.t2.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+--------+----------+----------------+
2 rows in set, 1 warning (0.00 sec)
先扫描T2并且排序之后再与T1关联，很好的用上了limit的特点，而不是像oracle一样先关联再去对关联出的大量结果集排序再去做stopkey。
这也给了我们一个启发，就是如果是从mysql迁移到其他数据库的情况，需要考虑到这种sql场景在mysql上可能会跑的不错，但是在其他数据库上会明显变慢，需要做对应的优化。
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
提供ORACLE技术支持(系统优化，故障处理，安装升级，数据恢复等） TEL:18081072613，微信、QQ同手机号。
浙公网安备 33010602011771号