[20230319]12c paging fetch cost bug.txt

来源：这里教程网时间：2026-03-03 18:30:27 作者：

[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以上版本不存在这个问题.有机会测试看看!!