[20201119]rowsets.txt

来源：这里教程网时间：2026-03-03 16:16:36 作者：

[20201119]rowsets.txt https://jonathanlewis.wordpress.com/2020/11/12/rowsets/ --//重复测试。 1.环境： SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production create table drop_me pctfree 0 nologging as with generator as ( select rownum id from dual connect by level <= 1e4 -- > comment to avoid WordPress format issue ) select rownum n1 from generator cross join generator where rownum <= 7e7 ; 2.测试： alter session set statistics_level = all; set timing on select count(*) from (select /*+ no_merge */ n1 from drop_me); SCOTT@book> @ dpc '' advanced PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID gkcjzsxt74ayx, child number 0 ------------------------------------- select count(*) from (select /*+ no_merge */ n1 from drop_me) Plan hash value: 3609429292 -------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | -------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 26406 (100)| | 1 |00:00:14.17 | 95896 | 95891 | | 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:14.17 | 95896 | 95891 | | 2 | VIEW | | 1 | 70M| 26406 (2)| 00:05:17 | 70M|00:00:10.83 | 95896 | 95891 | | 3 | TABLE ACCESS FULL| DROP_ME | 1 | 70M| 26406 (2)| 00:05:17 | 70M|00:00:03.83 | 95896 | 95891 | -------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$2 / from$_subquery$_001@SEL$1 3 - SEL$2 / DROP_ME@SEL$2 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4') ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$1") NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1") FULL(@"SEL$2" "DROP_ME"@"SEL$2") END_OUTLINE_DATA */ Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] --//全表扫描4秒，而实际输出计算需要10秒。共14秒完成。有点奇怪的地方是作者id=2,3看到的A-TIME很接近。我这里差异很大。 --//也许是打开statistics_level = all造成的情况。 select count(*) from (select /*+ no_merge */ n1 from drop_me -- union all -- select 1 from dual); SCOTT@book> @ dpc '' advanced PLAN_TABLE_OUTPUT ------------------------------------ SQL_ID 4j92n5p7t9021, child number 0 ------------------------------------- select count(*) from (select /*+ no_merge */ n1 from drop_me -- union all -- select 1 from dual) Plan hash value: 3408528233 --------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | --------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 26408 (100)| | 1 |00:00:28.29 | 95896 | 95891 | | 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:28.29 | 95896 | 95891 | | 2 | VIEW | | 1 | 70M| 26408 (2)| 00:05:17 | 70M|00:00:24.96 | 95896 | 95891 | | 3 | UNION-ALL | | 1 | | | | 70M|00:00:18.07 | 95896 | 95891 | | 4 | TABLE ACCESS FULL| DROP_ME | 1 | 70M| 26406 (2)| 00:05:17 | 70M|00:00:03.84 | 95896 | 95891 | | 5 | FAST DUAL | | 1 | 1 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 0 | 0 | --------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SET$1 / from$_subquery$_001@SEL$1 3 - SET$1 4 - SEL$2 / DROP_ME@SEL$2 5 - SEL$3 / DUAL@SEL$3 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4') ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$3") OUTLINE_LEAF(@"SET$1") OUTLINE_LEAF(@"SEL$1") NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1") FULL(@"SEL$2" "DROP_ME"@"SEL$2") END_OUTLINE_DATA */ Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] --//加入一个union all后，共需要28秒完成。 --//我在这里的测试看不出Column Projection Information (identified by operation id):部分的变化。 --//而在11g实际上如果你取消alter session set statistics_level = all;两者很快完成。 --//退出重新测试： SCOTT@book> select count(*) from (select /*+ no_merge */ n1 from drop_me); COUNT(*) ---------- 70000000 Elapsed: 00:00:01.23 SCOTT@book> select count(*) from (select /*+ no_merge */ n1 from drop_me union all select 1 from dual); COUNT(*) ---------- 70000001 Elapsed: 00:00:01.61 --//不过有点奇怪的是使用union all总是存在0.4秒的差异。总之11g看不出来问题。 --//找一台18c测试： SYS@192.168.x.y:1521/orcl> select banner from v$version where rownum=1; BANNER ---------------------------------------------------------------------- Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production SYS@192.168.x.y:1521/orcl> set timing on SYS@192.168.x.y:1521/orcl> select count(*) from (select /*+ no_merge */ n1 from drop_me); COUNT(*) ---------- 70000000 Elapsed: 00:00:06.42 SYS@192.168.x.y:1521/orcl> select count(*) from (select /*+ no_merge */ n1 from drop_me union all select 1 from dual); COUNT(*) ---------- 70000001 Elapsed: 00:00:09.26 --//确实差异很大。 SYS@192.168.x.y:1521/orcl> alter session set statistics_level = all; Session altered. SYS@192.168.x.y:1521/orcl> select count(*) from (select /*+ no_merge */ n1 from drop_me); COUNT(*) ---------- 70000000 Elapsed: 00:00:06.40 SYS@192.168.x.y:1521/orcl> @ dpc '' advanced PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID gkcjzsxt74ayx, child number 1 ------------------------------------- select count(*) from (select /*+ no_merge */ n1 from drop_me) Plan hash value: 3609429292 -------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | -------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 26288 (100)| | 1 |00:00:06.40 | 95765 | 95760 | | 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:06.40 | 95765 | 95760 | | 2 | VIEW | | 1 | 70M| 26288 (2)| 00:00:02 | 70M|00:00:06.38 | 95765 | 95760 | | 3 | TABLE ACCESS FULL| DROP_ME | 1 | 70M| 26288 (2)| 00:00:02 | 70M|00:00:06.35 | 95765 | 95760 | -------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$2 / from$_subquery$_001@SEL$1 3 - SEL$2 / DROP_ME@SEL$2 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('18.1.0') DB_VERSION('18.1.0') ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$1") NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1") FULL(@"SEL$2" "DROP_ME"@"SEL$2") END_OUTLINE_DATA */ Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] 2 - (rowset=1019) 3 - (rowset=1019) ~~~~~~~~~~~~~~~~~~~~ 45 rows selected. SYS@192.168.x.y:1521/orcl> select count(*) from (select /*+ no_merge */ n1 from drop_me union all select 1 from dual); COUNT(*) ---------- 70000001 Elapsed: 00:00:36.05 --//确实很慢。 SYS@192.168.x.y:1521/orcl> @ dpc '' advanced PLAN_TABLE_OUTPUT ------------------------------------ SQL_ID 284vj6h1vzv4c, child number 1 ------------------------------------- select count(*) from (select /*+ no_merge */ n1 from drop_me union all select 1 from dual) Plan hash value: 3408528233 --------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | --------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 26290 (100)| | 1 |00:00:36.05 | 95765 | 95760 | | 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:36.05 | 95765 | 95760 | | 2 | VIEW | | 1 | 70M| 26290 (2)| 00:00:02 | 70M|00:00:32.17 | 95765 | 95760 | | 3 | UNION-ALL | | 1 | | | | 70M|00:00:23.46 | 95765 | 95760 | | 4 | TABLE ACCESS FULL| DROP_ME | 1 | 70M| 26288 (2)| 00:00:02 | 70M|00:00:06.29 | 95765 | 95760 | | 5 | FAST DUAL | | 1 | 1 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 0 | 0 | --------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SET$1 / from$_subquery$_001@SEL$1 3 - SET$1 4 - SEL$2 / DROP_ME@SEL$2 5 - SEL$3 / DUAL@SEL$3 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('18.1.0') DB_VERSION('18.1.0') ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$3") OUTLINE_LEAF(@"SET$1") OUTLINE_LEAF(@"SEL$1") NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1") FULL(@"SEL$2" "DROP_ME"@"SEL$2") END_OUTLINE_DATA */ Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] 50 rows selected. I'm sure most of us have heard the mantra "row by row is slow by slow" (or some variant on the theme). This is true all the way down to the internal levels of an execution plan. --//我相信我们大多数人都听到过这样的咒语："一排一排慢"(或主题上的一些变体)。这一点在整个执行计划的内部层次都是正确的。 SYS@192.168.x.y:1521/orcl> @ hide rowsets NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES_MODI ISSYS_MODIFIABLE ---------------------------------------- ------------------------------------------------------------------ ------------- ------------- ------------ ---------- ------------------ _rowsets_cdb_view_enabled rowsets enabled for CDB views TRUE TRUE TRUE TRUE IMMEDIATE _rowsets_enabled enable/disable rowsets TRUE TRUE TRUE TRUE IMMEDIATE _rowsets_max_enc_rows maximum number of encoded rows in a rowset TRUE 64 64 TRUE IMMEDIATE _rowsets_max_rows maximum number of rows in a rowset TRUE 256 256 TRUE IMMEDIATE _rowsets_target_maxsize target size in bytes for space reserved in the frame for a rowset TRUE 524288 524288 TRUE IMMEDIATE _rowsets_use_encoding allow/disallow use of encoding with rowsets TRUE TRUE TRUE TRUE IMMEDIATE _sqlexec_join_group_aware_hj_unencoded_r minimum number of unencoded rowsets processed before adaptation TRUE 50 50 TRUE IMMEDIATE owsets_tolerated --//从某种意义讲union all关闭了_rowsets_enabled，简单测试看看。 SYS@192.168.x.y:1521/orcl> alter session set "_rowsets_enabled"=false; Session altered. Elapsed: 00:00:00.00 SYS@192.168.x.y:1521/orcl> select count(*) from (select /*+ no_merge */ n1 from drop_me); COUNT(*) ---------- 70000000 Elapsed: 00:00:19.99 SYS@192.168.x.y:1521/orcl> select count(*) from (select /*+ no_merge */ n1 from drop_me union all select 1 from dual); COUNT(*) ---------- 70000001 Elapsed: 00:00:37.14 --//不过即使设置"_rowsets_enabled"=false，两者还是存在17秒的差异。 --//补充不设置alter session set statistics_level = all;的情况呢？ SYS@192.168.x.y:1521/orcl> alter session set "_rowsets_enabled"=false; Session altered. SYS@192.168.x.y:1521/orcl> select count(*) from (select /*+ no_merge */ n1 from drop_me); COUNT(*) ---------- 70000000 Elapsed: 00:00:07.69 SYS@192.168.x.y:1521/orcl> select count(*) from (select /*+ no_merge */ n1 from drop_me union all select 1 from dual); COUNT(*) ---------- 70000001 Elapsed: 00:00:09.55