[20230319]12c paging fetch cost bug.txt

--//无意中发现12c paging fetch cost问题,做一个记录:

1.环境:
SCOTT@test01p> @ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

SCOTT@test01p> create table t1 as select * from all_objects ;
Table created.

SCOTT@test01p> create index i_t1_object_name on t1(object_name);
Index created.
--//分析表略.
--//注:t1.object_name is not null.

2.测试:
SCOTT@test01p> @sl all
alter session set statistics_level = all;
Session altered.

SCOTT@test01p> select owner, object_type, object_name from t1 order by object_name  fetch first 1 rows only;
OWNER                OBJECT_TYPE          OBJECT_NAME
-------------------- -------------------- -----------
XDB                  OPERATOR             ABSPATH

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  fjwda1wwx6awa, child number 0
-------------------------------------
select owner, object_type, object_name from t1 order by object_name
fetch first 1 rows only
Plan hash value: 2433988517
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |      1 |        |       |       |   266 (100)|          |      1 |00:00:00.02 |     347 |       |       |          |
|*  1 |  VIEW                    |      |      1 |      1 |   224 |       |   266   (2)| 00:00:01 |      1 |00:00:00.02 |     347 |       |       |          |
|*  2 |   WINDOW SORT PUSHED RANK|      |      1 |  18643 |   600K|   816K|   266   (2)| 00:00:01 |      1 |00:00:00.02 |     347 |  2048 |  2048 | 2048  (0)|
|   3 |    TABLE ACCESS FULL     | T1   |      1 |  18643 |   600K|       |    96   (2)| 00:00:01 |  18643 |00:00:00.01 |     347 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / from$_subquery$_002@SEL$2
   2 - SEL$1
   3 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=1)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_NAME")<=1)
--//按照道理oracle应该走索引,取1条记录就stop.而实际的情况是走全表扫描.
--//我取1条记录执行计划就这样,其它情况更不用讲了.
--//注我开始以为object_name有null值,实际上这样的情况不存在.表t1定义object_name 就是not null.

--//改写如下呢?
SCOTT@test01p> select owner, object_type, object_name from t1 order by object_name  offset 0 rows fetch  next 1 rows only;
OWNER                OBJECT_TYPE          OBJECT_NAME
-------------------- -------------------- -----------
XDB                  OPERATOR             ABSPATH


SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  ft6pjrrkzkduz, child number 0
-------------------------------------
select owner, object_type, object_name from t1 order by object_name
offset 0 rows fetch  next 1 rows only
Plan hash value: 2433988517
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |      1 |        |       |       |   266 (100)|          |      1 |00:00:00.02 |     347 |       |       |          |
|*  1 |  VIEW                    |      |      1 |  18643 |  4078K|       |   266   (2)| 00:00:01 |      1 |00:00:00.02 |     347 |       |       |          |
|*  2 |   WINDOW SORT PUSHED RANK|      |      1 |  18643 |   600K|   816K|   266   (2)| 00:00:01 |      1 |00:00:00.02 |     347 |  2048 |  2048 | 2048  (0)|
|   3 |    TABLE ACCESS FULL     | T1   |      1 |  18643 |   600K|       |    96   (2)| 00:00:01 |  18643 |00:00:00.01 |     347 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / from$_subquery$_002@SEL$2
   2 - SEL$1
   3 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE  WHEN (0>=0) THEN 0 ELSE 0 END +1 AND
              "from$_subquery$_002"."rowlimit_$$_rownumber">0))
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_NAME")<=CASE  WHEN (0>=0) THEN 0 ELSE 0 END +1)
30 rows selected.
--//还是选择全表扫描.注意这样写过滤条件不同.

3.继续测试:
--//直接加入提示index(t1 i_t1_object_name)呢?
SCOTT@test01p> select /*+ index(t1 i_t1_object_name) */ owner, object_type, object_name from t1 order by object_name  fetch first 1 rows only;
OWNER                OBJECT_TYPE          OBJECT_NAME
-------------------- -------------------- --------------------
XDB                  OPERATOR             ABSPATH

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  ba8cdjvatwk95, child number 0
-------------------------------------
select /*+ index(t1 i_t1_object_name) */ owner, object_type,
object_name from t1 order by object_name  fetch first 1 rows only
Plan hash value: 1887114361
--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |      1 |        |       |  8319 (100)|          |      1 |00:00:00.01 |       5 |
|*  1 |  VIEW                         |                  |      1 |      1 |   224 |  8319   (1)| 00:00:01 |      1 |00:00:00.01 |       5 |
|*  2 |   WINDOW NOSORT STOPKEY       |                  |      1 |  18643 |   600K|  8319   (1)| 00:00:01 |      1 |00:00:00.01 |       5 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1               |      1 |  18643 |   600K|  8319   (1)| 00:00:01 |      2 |00:00:00.01 |       5 |
|   4 |     INDEX FULL SCAN           | I_T1_OBJECT_NAME |      1 |  18643 |       |    80   (0)| 00:00:01 |      2 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / from$_subquery$_002@SEL$2
   2 - SEL$1
   3 - SEL$1 / T1@SEL$1
   4 - SEL$1 / T1@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=1)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_NAME")<=1)
31 rows selected.
--//直接使用INDEX FULL SCAN,取1个值就ok了.buffers=5,明显这个执行计划是最优的.
--//而如果你仔细看cost=8319.取前面的选择全表扫描的执行计划cost=266.oracle在没有提示的情况下选择全表扫描就很正常了.
--//问题在于id=3的cost上等于8319.
--//还有一点奇怪的地方是fetch 1条记录.而实际A-ROWS=2(id=3,4).
--//而且id=4的cost=80,明显是全扫描索引的成本,这一步就已经加大了许多.
--//看来这是12c上的一个bug.paging fetch cost计算有误.

--//我估计19c以上版本不存在这个问题.有机会测试看看!!