[20230319]19c paging fetch cost 测试.txt

--//无意中发现12c paging fetch cost问题,做一个记录,继续测试19c下的情况.

1.环境:
SYS@192.168.100.235:1521/orcl> @ pr
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

SYS@192.168.100.235:1521/orcl> create table t1 tablespace users as select * from all_objects ;
Table created.

SYS@192.168.100.235:1521/orcl> create index i_t1_object_name on t1(object_name);
Index created.

--//分析表略.
--//注:t1.object_name is not null.

2.测试:
SYS@192.168.100.235:1521/orcl> @sl all
alter session set statistics_level = all;
Session altered.

SYS@192.168.100.235:1521/orcl> @ 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: 2079639619
----------------------------------------------------------------------------------------------------------------------------
| Id |Operation                     |Name            |Starts|E-Rows|E-Bytes|Cost (%CPU)|E-Time  |A-Rows|   A-Time  |Buffers|
----------------------------------------------------------------------------------------------------------------------------
|   0|SELECT STATEMENT              |                |     1|      |       |    4 (100)|        |     1|00:00:00.01|      4|
|*  1| VIEW                         |                |     1|     1|   224 |    4   (0)|00:00:01|     1|00:00:00.01|      4|
|*  2|  WINDOW NOSORT STOPKEY       |                |     1|     1|    49 |    4   (0)|00:00:01|     1|00:00:00.01|      4|
|   3|   TABLE ACCESS BY INDEX ROWID|T1              |     1| 75585|  3616K|    4   (0)|00:00:01|     1|00:00:00.01|      4|
|   4|    INDEX FULL SCAN           |I_T1_OBJECT_NAME|     1|     1|       |    3   (0)|00:00:01|     1|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.
--//OK,19c没有这个问题选择正确的执行方式.cost也估算正确.
--//A-Rows = 1(id=3,4)

3.继续测试:
--//看看怎么时候发生会发生全表扫描的情况.

SYS@192.168.100.235:1521/orcl> select /*+ full(t1) */ owner, object_type, object_name from t1 order by object_name  offset 0 rows fetch   next 1 rows only;
OWNER      OBJECT_TYPE OBJECT_NAME
---------- ----------- -----------
FINDREPORT SYNONYM     ABPFEATURES

SYS@192.168.100.235:1521/orcl> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  96nadwxr6wvrv, child number 0
-------------------------------------
select /*+ full(t1) */ owner, object_type, object_name from t1 order by
object_name  offset 0 rows fetch   next 1 rows only
Plan hash value: 2049967792
---------------------------------------------------------------------------------------------------------------------------------------
| 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|      |       |       | 1327 (100)|        |     1|00:00:00.02|   1471|    |    |         |
|*  1| VIEW                    |    |     1|     1|   224 |       | 1327   (1)|00:00:01|     1|00:00:00.02|   1471|    |    |         |
|*  2|  WINDOW SORT PUSHED RANK|    |     1| 75585|  3616K|  4456K| 1327   (1)|00:00:01|     1|00:00:00.02|   1471|2048|2048|2048  (0)|
|   3|   TABLE ACCESS FULL     |T1  |     1| 75585|  3616K|       |  409   (1)|00:00:01| 75585|00:00:00.01|   1471|    |    |         |
---------------------------------------------------------------------------------------------------------------------------------------
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 AND "from$_subquery$_002"."rowlimit_$$_rownumber">0))
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_NAME")<=1)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
   3 -  SEL$1 / T1@SEL$1
           -  full(t1)
36 rows selected.

--//cost=1327,大概估算fetch 1 cost=1.

SYS@192.168.100.235:1521/orcl> select  owner, object_type, object_name from t1 order by object_name  offset 1300 rows fetch   next 1 rows only;
OWNER OBJECT_TYPE OBJECT_NAME
----- ----------- --------------
SYS   TABLE       AQ$_ALERT_QT_S

Plan hash value: 2079639619
----------------------------------------------------------------------------------------------------------------------------
| Id |Operation                     |Name            |Starts|E-Rows|E-Bytes|Cost (%CPU)|E-Time  |A-Rows|   A-Time  |Buffers|
----------------------------------------------------------------------------------------------------------------------------
|   0|SELECT STATEMENT              |                |     1|      |       |  695 (100)|        |     1|00:00:00.01|    562|
|*  1| VIEW                         |                |     1|  1301|   284K|  695   (0)|00:00:01|     1|00:00:00.01|    562|
|*  2|  WINDOW NOSORT STOPKEY       |                |     1|  1301| 63749 |  695   (0)|00:00:01|  1301|00:00:00.01|    562|
|   3|   TABLE ACCESS BY INDEX ROWID|T1              |     1| 75585|  3616K|  695   (0)|00:00:01|  1301|00:00:00.01|    562|
|   4|    INDEX FULL SCAN           |I_T1_OBJECT_NAME|     1|  1301|       |   11   (0)|00:00:01|  1301|00:00:00.01|      8|
----------------------------------------------------------------------------------------------------------------------------
--//cost=695.不能这么简单估算,受群集因子的影响.

SYS@192.168.100.235:1521/orcl> select  owner, object_type, object_name from t1 order by object_name  offset 2487 rows fetch   next 1 rows only;
OWNER OBJECT_TYPE OBJECT_NAME
----- ----------- ----------------------
SYS   VIEW        AWR_ROOT_CELL_IOREASON

Plan hash value: 2079639619
----------------------------------------------------------------------------------------------------------------------------
| Id |Operation                     |Name            |Starts|E-Rows|E-Bytes|Cost (%CPU)|E-Time  |A-Rows|   A-Time  |Buffers|
----------------------------------------------------------------------------------------------------------------------------
|   0|SELECT STATEMENT              |                |     1|      |       | 1326 (100)|        |     1|00:00:00.01|    919|
|*  1| VIEW                         |                |     1|  2488|   544K| 1326   (0)|00:00:01|     1|00:00:00.01|    919|
|*  2|  WINDOW NOSORT STOPKEY       |                |     1|  2488|   119K| 1326   (0)|00:00:01|  2488|00:00:00.01|    919|
|   3|   TABLE ACCESS BY INDEX ROWID|T1              |     1| 75585|  3616K| 1326   (0)|00:00:01|  2488|00:00:00.01|    919|
|   4|    INDEX FULL SCAN           |I_T1_OBJECT_NAME|     1|  2488|       |   18   (0)|00:00:01|  2488|00:00:00.01|     14|
----------------------------------------------------------------------------------------------------------------------------

SYS@192.168.100.235:1521/orcl> select  owner, object_type, object_name from t1 order by object_name  offset 2488 rows fetch   next 1 rows only;
OWNER  OBJECT_TYPE OBJECT_NAME
------ ----------- ----------------------
PUBLIC SYNONYM     AWR_ROOT_CELL_IOREASON

Plan hash value: 2049967792
-----------------------------------------------------------------------------------------------------------------------------------------
| 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|      |       |       | 1327 (100)|        |     1|00:00:00.03|   1471|     |     |         |
|*  1| VIEW                    |    |     1|  2489|   544K|       | 1327   (1)|00:00:01|     1|00:00:00.03|   1471|     |     |         |
|*  2|  WINDOW SORT PUSHED RANK|    |     1| 75585|  3616K|  4456K| 1327   (1)|00:00:01|  2489|00:00:00.03|   1471| 549K| 457K| 487K (0)|
|   3|   TABLE ACCESS FULL     |T1  |     1| 75585|  3616K|       |  409   (1)|00:00:01| 75585|00:00:00.01|   1471|     |     |         |
-----------------------------------------------------------------------------------------------------------------------------------------
--//越往后,走索引的cost越高.大约在偏移2488处.当然我仅仅取1条.

SYS@192.168.100.235:1521/orcl> select  owner, object_type, object_name from t1 order by object_name  offset 2487 rows fetch   next 2 rows only;
OWNER  OBJECT_TYPE OBJECT_NAME
------ ----------- ----------------------
SYS    VIEW        AWR_ROOT_CELL_IOREASON
PUBLIC SYNONYM     AWR_ROOT_CELL_IOREASON

SYS@192.168.100.235:1521/orcl> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  6vq87da1vfd0k, child number 0
-------------------------------------
select  owner, object_type, object_name from t1 order by object_name
offset 2487 rows fetch   next 2 rows only
Plan hash value: 2049967792
-----------------------------------------------------------------------------------------------------------------------------------------
| 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|      |       |       | 1327 (100)|        |     2|00:00:00.04|   1471|     |     |         |
|*  1| VIEW                    |    |     1|  2489|   544K|       | 1327   (1)|00:00:01|     2|00:00:00.04|   1471|     |     |         |
|*  2|  WINDOW SORT PUSHED RANK|    |     1| 75585|  3616K|  4456K| 1327   (1)|00:00:01|  2489|00:00:00.04|   1471| 549K| 457K| 487K (0)|
|   3|   TABLE ACCESS FULL     |T1  |     1| 75585|  3616K|       |  409   (1)|00:00:01| 75585|00:00:00.01|   1471|     |     |         |
-----------------------------------------------------------------------------------------------------------------------------------------
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"<=2489 AND "from$_subquery$_002"."rowlimit_$$_rownumber">2487))
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_NAME")<=2489)
29 rows selected.

--//可以确定19c没有这个问题.