ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > [20201119]rowsets.txt

[20201119]rowsets.txt

Oracle 作者：lfree 时间：2020-11-19 11:25:11 0 删除 编辑

[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




来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2735185/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： [20201118]18c 10046跟踪时间戳3(虚拟机).txt
下一篇： [20201119]安装cygwin.txt
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    4006
  • 访问量
    7969289

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593