Oracle手工增加排序区避免SQL使用临时表空间排序产生物理IO

 

Oracle手工增加排序区避免SQL使用临时表空间排序产生物理IO

 

背景

最近公司有个项目需要用到ClickHouse，表是使用列式存储形式的。

对于基本静态的数据或者DML速度要求不高，同时涉及大批量数据集统计的操作相比Oracle来说运行速度大大大幅度提升。

由于Oracle 12C以后开始推出In-Memory特性，可以设置INMEMORY_SIZE 为非0值可启用IM column store特性。

这些都是题外话，好不容易将40多G的表加载到4G的IM区域中，再次执行同样的SQL发现速度还是没有ClickHouse快（虽然比原来快了不少）。

不过对于单纯查询某个列倒是快了几个数量级以上，有兴趣可以点开看看。

复制代码
10:43:34 ZKM@ODB/pdb(9)> select count(url) from XX_XXXX_XXXXXXX_INFO;

COUNT(URL)
----------
  21416307

Elapsed: 00:00:01.86
10:43:39 ZKM@ODB/pdb(9)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  481tqtz6srd4n, child number 0
-------------------------------------
select count(url) from XX_XXXX_XXXXXXX_INFO

Plan hash value: 2005175113

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |      1 |        |      1 |00:00:01.85 |       3 |
|   1 |  SORT AGGREGATE              |                      |      1 |      1 |      1 |00:00:01.85 |       3 |
|   2 |   PARTITION RANGE ALL        |                      |      1 |     21M|     80 |00:00:01.85 |       3 |
|   3 |    TABLE ACCESS INMEMORY FULL| XX_XXXX_XXXXXXX_INFO |      2 |     21M|     80 |00:00:01.85 |       3 |
---------------------------------------------------------------------------------------------------------------


15 rows selected.

Elapsed: 00:00:00.11
View Code
复制代码

 

 

消耗才3个逻辑读。

 

SQL在启用In-Memory特性后的消耗情况如下：

复制代码
10:49:35 ZKM@ODB/pdb(9)> SELECT
10:49:52   2      COUNT (
10:49:52   3          DISTINCT CASE
10:49:52   4          WHEN INSTR (URL, '?', 1) > 0 THEN
10:49:52   5              SUBSTR (URL, 0, INSTR(URL, '?', 1) - 1)
10:49:52   6          ELSE
10:49:52   7              URL
10:49:52   8          END
10:49:52   9      ) AS totalPv,
10:49:52  10      COUNT (DISTINCT IP) AS totalUv,
10:49:52  11      COUNT (DISTINCT IP) AS totalIp,
10:49:52  12      TO_CHAR (CREATE_TIME, 'yyyy-MM-dd') AS staticDate
10:49:52  13  FROM
10:49:52  14      XX_XXXX_XXXXXXX_INFO
10:49:52  15  GROUP BY
10:49:52  16      TO_CHAR (CREATE_TIME, 'yyyy-MM-dd');

   TOTALPV    TOTALUV    TOTALIP STATICDATE
---------- ---------- ---------- ------------------------------
     18358       6331       6331 2021-10-15
     27798       8426       8426 2021-10-16
     26137       9312       9312 2021-10-17
      1121        859        859 2021-10-18
     40322      16536      16536 2021-10-19
     71759      20636      20636 2021-10-20
      2085       1489       1489 2021-10-21
     76595      23044      23044 2021-10-22
     54176      22271      22271 2021-10-23
      1501       1358       1358 2021-10-24
     63229      19462      19462 2021-10-25
     45502      17085      17085 2021-10-26
      1245        869        869 2021-10-27

13 rows selected.

Elapsed: 00:01:57.69
10:51:50 ZKM@ODB/pdb(9)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5cupa20fpf2s2, child number 6
-------------------------------------
SELECT     COUNT (         DISTINCT CASE         WHEN INSTR (URL, '?',
1) > 0 THEN             SUBSTR (URL, 0, INSTR(URL, '?', 1) - 1)
ELSE             URL         END     ) AS totalPv,     COUNT (DISTINCT
IP) AS totalUv,     COUNT (DISTINCT IP) AS totalIp,     TO_CHAR
(CREATE_TIME, 'yyyy-MM-dd') AS staticDate FROM     XX_XXXX_XXXXXXX_INFO
GROUP BY     TO_CHAR (CREATE_TIME, 'yyyy-MM-dd')

Plan hash value: 586993993

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |      1 |        |     13 |00:01:57.67 |       9 |  13211 |  13211 |       |       |          |         |
|   1 |  SORT GROUP BY               |                      |      1 |      1 |     13 |00:01:57.67 |       9 |  13211 |  13211 |   633K|   633K|   97M (1)|     104M|
|   2 |   PARTITION RANGE ALL        |                      |      1 |     21M|     21M|00:00:08.25 |       3 |      0 |      0 |       |       |          |         |
|   3 |    TABLE ACCESS INMEMORY FULL| XX_XXXX_XXXXXXX_INFO |      2 |     21M|     21M|00:00:05.81 |       3 |      0 |      0 |       |       |          |         |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------


20 rows selected.

Elapsed: 00:00:00.07
复制代码

 

可以看出，时间消耗主要在SORT GROUP BY，即ID=1这一步。

由于该步骤产生了104M的临时数据，基本为物理IO（Writes：13211 * 8 / 1024 = 103.2M）。

想要消除物理IO的影响在看看这条SQL的执行速度。

 

那么如何消除呢？

排序的时候用到TEMP表空间，无非就是排序区不足。其他中间遇到的乱七八糟的问题和研究就不说了，直接上结论。

设置如下：

ALTER SESSION SET workarea_size_policy = MANUAL;

ALTER SESSION SET sort_area_size = 125829120; （单位bytes，大于前边的104M即可）

再次执行SQL，消耗如下：

复制代码
11:11:20 ZKM@ODB/pdb(9)>  ALTER SESSION SET workarea_size_policy = MANUAL;

Session altered.

Elapsed: 00:00:00.01
11:11:20 ZKM@ODB/pdb(9)> ALTER SESSION SET sort_area_size = 125829120;

Session altered.

Elapsed: 00:00:00.00
11:11:25 ZKM@ODB/pdb(9)> SELECT
11:11:35   2      COUNT (
11:11:35   3          DISTINCT CASE
11:11:35   4          WHEN INSTR (URL, '?', 1) > 0 THEN
11:11:35   5              SUBSTR (URL, 0, INSTR(URL, '?', 1) - 1)
11:11:35   6          ELSE
11:11:35   7              URL
11:11:35   8          END
11:11:35   9      ) AS totalPv,
11:11:35  10      COUNT (DISTINCT IP) AS totalUv,
11:11:35  11      COUNT (DISTINCT IP) AS totalIp,
11:11:35  12      TO_CHAR (CREATE_TIME, 'yyyy-MM-dd') AS staticDate
11:11:35  13  FROM
11:11:35  14      XX_XXXX_XXXXXXX_INFO
11:11:35  15  GROUP BY
11:11:35  16      TO_CHAR (CREATE_TIME, 'yyyy-MM-dd');

   TOTALPV    TOTALUV    TOTALIP STATICDATE
---------- ---------- ---------- ------------------------------
     18358       6331       6331 2021-10-15
     27798       8426       8426 2021-10-16
     26137       9312       9312 2021-10-17
      1121        859        859 2021-10-18
     40322      16536      16536 2021-10-19
     71759      20636      20636 2021-10-20
      2085       1489       1489 2021-10-21
     76595      23044      23044 2021-10-22
     54176      22271      22271 2021-10-23
      1501       1358       1358 2021-10-24
     63229      19462      19462 2021-10-25
     45502      17085      17085 2021-10-26
      1245        869        869 2021-10-27

13 rows selected.

Elapsed: 00:01:57.16
11:13:33 ZKM@ODB/pdb(9)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5cupa20fpf2s2, child number 9
-------------------------------------
SELECT     COUNT (         DISTINCT CASE         WHEN INSTR (URL, '?',
1) > 0 THEN             SUBSTR (URL, 0, INSTR(URL, '?', 1) - 1)
ELSE             URL         END     ) AS totalPv,     COUNT (DISTINCT
IP) AS totalUv,     COUNT (DISTINCT IP) AS totalIp,     TO_CHAR
(CREATE_TIME, 'yyyy-MM-dd') AS staticDate FROM     XX_XXXX_XXXXXXX_INFO
GROUP BY     TO_CHAR (CREATE_TIME, 'yyyy-MM-dd')

Plan hash value: 586993993

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |      1 |        |     13 |00:01:57.13 |       3 |       |       |          |
|   1 |  SORT GROUP BY               |                      |      1 |      1 |     13 |00:01:57.13 |       3 |   115M|    18M|  102M (0)|
|   2 |   PARTITION RANGE ALL        |                      |      1 |     21M|     21M|00:00:08.06 |       3 |       |       |          |
|   3 |    TABLE ACCESS INMEMORY FULL| XX_XXXX_XXXXXXX_INFO |      2 |     21M|     21M|00:00:05.75 |       3 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------


20 rows selected.

Elapsed: 00:00:00.12
复制代码

 

 

已经消除物理IO。

（不过还是慢。。。）

 

posted @   PiscesCanon  阅读(230)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛