[20201119]rowsets.txt

lfree發表於2020-11-19

[20201119]rowsets.txt


--//重複測試。

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部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2735185/，如需轉載，請註明出處，否則將追究法律責任。

相關文章