首页 » ORACLE, ORACLE [C]系列 » Alert: 12c top-N fetch first错误的执行计划 19c已修复

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 首页上的联系方式。

打赏

目前这篇文章还没有评论(Rss)

我要评论