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

[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没有这个问题.