Alert: 12c top-N fetch first错误的执行计划 19c已修复
Oracle 12c new feature:OFFSET n FETCH n row-limit 7年前我尝试过12C新支持的TOP-n新语法，使应用中分页代码看上去更简洁， 内部也是利用了一种窗口函数的方法，如果你在应用中使用了该语法，在19c的数据库前需要注意SQL的效率是否比之前的order by 子查询加 rownum的更差了。其实这是oracle在12c或18c版本中的bug, 在19C中已经解决，这也是建议升级19c而非12c跳过的一个小坑，最近有个客户升级的12c总是惊喜不断，bug连连，看fix基本都是在19c中，去年我也分享过从oracle的产品经理那介绍过的一篇<浅谈Oracle Database 19c>,19c中oracle开发人员主要是以修复大量已知bug为主没有引入过多的新特性. 没有理由到现在还选择12c而非19c,更不应以选择“次新”版本这种陈旧理论拒绝19c. 下面简单演示这个问题
创建测试环境
drop table t; create table t nologging as select d.* from dba_objects d, ( select 1 from dual connect by level <= 10 ) where object_id is not null; alter table t noparallel; alter table t modify object_id not null; create index ix on t ( object_id ) ; exec dbms_stats.gather_table_stats(user,'t');
— 12c 版本中查看执行情况
[oracle@anbob ~]$ ora SQL*Plus: Release 12.2.0.1.0 Production on Thu Oct 1 09:14:35 2020 SQL_ID 0qtbtttf5rs5y, child number 0 ------------------------------------- select * from ( select * from t order by object_id desc ) where rownum <= 10 Plan hash value: 1635572796 ---------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 13 (100)| |* 1 | COUNT STOPKEY | | | | | | 2 | VIEW | | 10 | 4810 | 13 (0)| | 3 | TABLE ACCESS BY INDEX ROWID| T | 728K| 92M| 13 (0)| | 4 | INDEX FULL SCAN DESCENDING| IX | 10 | | 3 (0)| ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=10) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 14 consistent gets 0 physical reads 0 redo size 2757 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed SQL_ID 8jd6tct901zsq, child number 0 ------------------------------------- select * from t order by object_id asc fetch first 10 rows only Plan hash value: 3047187157 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 33053 (100)| | | | |* 1 | VIEW | | 10 | 5070 | | 33053 (1)| | | | |* 2 | WINDOW SORT PUSHED RANK| | 728K| 92M| 135M| 33053 (1)| 6144 | 6144 | 6144 (0)| | 3 | TABLE ACCESS FULL | T | 728K| 92M| | 3857 (1)| | | | ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10) 2 - filter(ROW_NUMBER() OVER ( ORDER BY "T"."OBJECT_ID")<=10) 22 rows selected. Statistics ---------------------------------------------------------- 0 recursive calls 4 db block gets 14192 consistent gets 14180 physical reads 0 redo size 2689 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 10 rows processed 使用一种first_rows的方法可以临时避免。 select /*+ FIRST_ROWS(10) */* from t order by object_id asc fetch first 10 rows only; SQL_ID dqmm3bfv24n73, child number 0 ------------------------------------- select /*+ FIRST_ROWS(10) */* from t order by object_id asc fetch first 10 rows only Plan hash value: 4127887649 ---------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 13 (100)| |* 1 | VIEW | | 10 | 5070 | 13 (0)| |* 2 | WINDOW NOSORT STOPKEY | | 10 | 1330 | 13 (0)| | 3 | TABLE ACCESS BY INDEX ROWID| T | 728K| 92M| 13 (0)| | 4 | INDEX FULL SCAN | IX | 10 | | 3 (0)| ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10) 2 - filter(ROW_NUMBER() OVER ( ORDER BY "T"."OBJECT_ID")<=10) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 15 consistent gets 0 physical reads 0 redo size 2689 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed
在12c中使用first limit的语法使用的是全表扫描，导致大量的逻辑读，使用first_row（n)的hint可以临时解决这个问题，但是我们并不想为了让代码比过去的where rownum更简洁而又附加上first_row hint. 下面在19c执行同样TOP-N
SQL> select comments from REGISTRY$HISTORY; COMMENTS -------------------------------------------------------------------------------- RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417 Patch applied on 19.3.0.0.0: Release_Update - 190410122720 Elapsed: 00:00:00.01 SQL> select * from t order by object_id asc fetch first 10 rows only; 10 rows selected. Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 4127887649 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 5070 | 13 (0)| 00:00:01 | |* 1 | VIEW | | 10 | 5070 | 13 (0)| 00:00:01 | |* 2 | WINDOW NOSORT STOPKEY | | 10 | 1320 | 13 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| T | 724K| 91M| 13 (0)| 00:00:01 | | 4 | INDEX FULL SCAN | IX | 10 | | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10) 2 - filter(ROW_NUMBER() OVER ( ORDER BY "T"."OBJECT_ID")<=10) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 14 consistent gets 0 physical reads 0 redo size 2897 bytes sent via SQL*Net to client 427 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed
在19c中使用了我们想要的执行计划，判断应该是在哪个bug中修复了， 如果我们不能进MOS， 可以先尝试从数据库中v$system_fix_control 视图查找一下。oracle虽然是非开源软件，但是对外提供了很多查询”接口” view，而无需翻看代码, 我们可以以first 或 window为关键字查找，过滤19c开始修复的与之相关的bug
SQL> @sysfix window BUGNO VALUE SQL_FEATURE DESCRIPTION OPTIMIZER_FEATURE_ENABLE EVENT IS_DEFAULT CON_ID ---------- ---------- ----------------------------------- ---------------------------------------------------------------- ------------------------- ---------- ---------- ---------- 25323193 1 QKSFM_COMPILATION_25323193 Remove pruned window functions from select and order by 8.0.0 0 1 1 22174392 1 QKSFM_FIRST_ROWS_22174392 first k row optimization for window function rownum predicate 19.1.0 0 1 1 23002609 1 QKSFM_EXECUTION_23002609 Clear key count of window OG (containing GBY) w/ constant keys o 12.2.0.1 0 1 1 17986549 1 QKSFM_FILTER_PUSH_PRED_17986549 push valid filters into UNION ALL branches with window functions 12.2.0.1 0 1 1 13735304 1 QKSFM_TRANSFORMATION_13735304 relax restrictions on window function replaces subquery 12.1.0.1 0 1 1 13321547 1 QKSFM_ACCESS_PATH_13321547 Avoid WINDOW SORT/WINDOW BUFFER SORT when index is already sorte 11.2.0.4 0 1 1 10226906 1 QKSFM_SQL_CODE_GENERATOR_10226906 ignore OBY clumping for grand-total window functions 11.2.0.3 0 1 1 12410972 1 QKSFM_FILTER_PUSH_PRED_12410972 push predicate with NLS_SORT in window function 11.2.0.3 0 1 1 10230017 1 QKSFM_SQL_CODE_GENERATOR_10230017 use range parallelism for window function count on a constant 11.2.0.3 0 1 1 9024933 1 QKSFM_JPPD_9024933 Do not allow Old JPPD for OJ view with window function 11.2.0.2 0 1 1 7127530 1 QKSFM_TRANSFORMATION_7127530 window function replaces having subquery 11.2.0.1 0 1 1 7388652 1 QKSFM_TRANSFORMATION_7388652 window function replaces uncorrelated subquery with view 11.2.0.1 0 1 1 7385140 1 QKSFM_TRANSFORMATION_7385140 early window function removal with CBQT 11.2.0.1 0 1 1 6119510 1 QKSFM_JPPD_6119510 Allow JPPD for union-all views with window functions 11.1.0.6 0 1 1 6146906 1 QKSFM_TRANSFORMATION_6146906 amend fix of bug 3697218 for window func 10.2.0.5 0 1 1 7576516 1 QKSFM_SQL_CODE_GENERATOR_7576516 make only the topmost window node positionable 10.2.0.5 0 1 1 5302124 1 QKSFM_TRANSFORMATION_5302124 Allow CBQT for queries with window functions 10.2.0.4 0 1 1 17 rows selected.
Note:
看到有个bug非常相似， 值1为修复 ，我可以尝试关掉这个bug修复判断是否问题可以还原， 更甚至可以在SQL语句级关闭一个bug修复。
select /*+ opt_param('_fix_control' '22174392:OFF') */ * from t order by object_id asc fetch first 10 rows only; Plan hash value: 3047187157 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 5070 | | 25200 (1)| 00:00:01 | |* 1 | VIEW | | 10 | 5070 | | 25200 (1)| 00:00:01 | |* 2 | WINDOW SORT PUSHED RANK| | 724K| 91M| 131M| 25200 (1)| 00:00:01 | | 3 | TABLE ACCESS FULL | T | 724K| 91M| | 3827 (1)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10) 2 - filter(ROW_NUMBER() OVER ( ORDER BY "T"."OBJECT_ID")<=10) 16 rows selected. Elapsed: 00:00:00.03
Note:
确认是在19c中修复的bug 22174392解决了12c中使用first limit TOP-N语法中错误的代价估算，而生成的错误的执行计划。
选择一下合适的数据库版本，减少不比要的性能及稳定性烦恼， 当前及未来三年内oracle数据库建议19c,同时更新较新RU。 升级做好充分的SPA或RAT更是尤为重要。
如果你存在升级忧虑可以联系www.anbob.com 首页上的联系方式。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。