Bo's Oracle Station › 博客首页 ›Oracle技术› SQL Tuning › 查看内容

【博客文章2022】Result Cache的注意事项

2022-6-28 11:07| 发布者: admin| 查看: 65| 评论: 0|原作者: Bo Tang

摘要: Result Cache的注意事项。

Author: Bo Tang

1. 概述：

Result Cache通过把复杂计算结果缓存于共享池中来提高SQL语句执行的性能。配置Result Cahce主要是配置两个系统参数：result_cache_mode和result_cache_max_size。如果配置不出来，大部分原因是因为result_cache_max_size可能被设为0了。它应该被设置为32K的倍数，如果为0则Result Cache功能失效。

SQL> show parameter result_cache_max_size
NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size             big integer 10496K
SQL> show parameter result_cache_mode
NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode             string    MANUAL

另外一个参数result_cache_max_result也有一定影响。可以把它设为5。它取0-100之间的值，指定最大内存使用比例。

SQL> show parameter result_cache_max_result
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_result integer 5

查看Result Cache情况主要有三种手段：1. dbms_result_cache.status()。 2.dbms_result_cache.memory_report。3. v$result_cache_objects。

SQL> conn / as sysdba
Connected.
SQL> set linesize 10000
SQL> set serveroutput on
SQL> exec dbms_result_cache.flush();
PL/SQL procedure successfully completed.
SQL> select dbms_result_cache.status() from dual;
DBMS_RESULT_CACHE.STATUS()
---------------------------------------------------
ENABLED
SQL> exec dbms_result_cache.memory_report
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 10496K bytes (10496 blocks)
Maximum Result Size = 524K bytes (524 blocks)
[Memory]
Total Memory = 5848 bytes [0.001% of the Shared Pool]
... Fixed Memory = 5848 bytes [0.001% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL procedure successfully completed.
SQL> select type, status , name from v$result_cache_objects;
no rows selected

2. 准备实验环境：

apps用户方案的获取，请参考这个帖 https://www.botangdb.com/mytec/mytec_sqltuning/202204/00900085.html

3. 加载Result Cache：

打开一个终端窗口，以apps用户登录（注意：除了使用result_cache_mode为force的这种方式外，还可以使用更改表的result cache模式alter table t1 result_cache (mode force)和使用/*+ result_cache */这两种方式，本文使用result_cache_mode参数模式）:

SQL> alter session set result_cache_mode=force;
SQL> set autot on
SQL> set linesize 1000
SQL> set timing on
SQL> select project_id, outlet_id,avg(pd_price_units_eur) avgval from apps.fact_pd_out_itm_293
where pd_price_units_eur>4400 group by project_id, outlet_id;

PROJECT_ID OUTLET_ID   AVGVAL
---------- ---------- ----------
       292 84372 5607.05585
       292 88621 6389.24913
       292 84174 5095.2084
       292 84143 5041.76094
       292 88786 4827.46575
      1535 88866 4696.2949
      1535 88626 5222.77967
      1535 88767 5069.74802
      1535 88727 4911.87648
      1535 83673 5916.9548

1535 88640 4783.99591

......

PROJECT_ID OUTLET_ID   AVGVAL
---------- ---------- ----------
      1535 88670 4818.14175
      1535 88609 5158.81889
      1535     381305 4681.9622
      1535 88724 4429.94812
      1535     381237 4706.98662
      1535 84174 5080.55481
      1535     381281 4848.78735
      1535     381241 4783.43847
      1535 84124 5084.61473
      1535 88643 4574.54687
      1535 84464 4727.71958

242 rows selected.

Elapsed: 00:00:01.45

Execution Plan
----------------------------------------------------------
Plan hash value: 2820496992

--------------------------------------------------------------------------------------------------
| Id | Operation     | Name    | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |     |   241 | 3387 | 4398   (1)| 00:00:01 |
|   1 | RESULT CACHE     | 607f28a3g2m0cagwxs0kc2adtr | | |       | |
|   2 |   HASH GROUP BY     |     |   241 | 3387 | 4398   (1)| 00:00:01 |
|* 3 |    TABLE ACCESS FULL| FACT_PD_OUT_ITM_293 | 2193 | 30702 | 4397   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("PD_PRICE_UNITS_EUR">4400)

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=3; dependencies=(APPS.FACT_PD_OUT_ITM_293); name="select project_id, outlet_id,avg(pd_price_units_eur) avgval from apps.fact_pd_out_itm_293
where pd_price_units_eur>4400 gro"

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
   - 1 Sql Plan Directive used for this statement

Statistics
----------------------------------------------------------
375 recursive calls
   0 db block gets
52174 consistent gets
51614 physical reads
124 redo size
      11523 bytes sent via SQL*Net to client
728 bytes received via SQL*Net from client
18 SQL*Net roundtrips to/from client
53 sorts (memory)
   0 sorts (disk)
242 rows processed

以sys身份查看Result Cache情况：

SQL> conn / as sysdba
Connected.
SQL> set linesize 10000
SQL> set serveroutput on
SQL> exec dbms_result_cache.memory_report
R e s u l t   C a c h e   M e m o r y R e p o r t
[Parameters]
Block Size     = 1K bytes
Maximum Cache Size = 64M bytes (64K blocks)
Maximum Result Size = 3276K bytes (3276 blocks)
[Memory]
Total Memory = 169392 bytes [0.027% of the Shared Pool]
... Fixed Memory = 5440 bytes [0.001% of the Shared Pool]
... Dynamic Memory = 163952 bytes [0.026% of the Shared Pool]
....... Overhead = 131184 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 21 blocks
........... Used Memory = 11 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 10 blocks
................... SQL     = 10 blocks (2 count)

PL/SQL procedure successfully completed.

SQL> select type, status , name from v$result_cache_objects;
TYPE    STATUS    NAME
---------- --------- --------------------------------------------------------------------------------------------------------------------------------
Dependency Published APPS.FACT_PD_OUT_ITM_293
Result    Published select project_id, outlet_id,avg(pd_price_units_eur) avgval from apps.fact_pd_out_itm_293
         where pd_price_units_eur>4400 gro

Result Published SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cach

4. 第2次执行，使用Result Cache：

以apps用户再次执行：

SQL> select project_id, outlet_id,avg(pd_price_units_eur) avgval from apps.fact_pd_out_itm_293
where pd_price_units_eur>4400    group by project_id, outlet_id;
2
PROJECT_ID OUTLET_ID   AVGVAL
---------- ---------- ----------
       292 84372 5607.05585
       292 88621 6389.24913
       292 84174 5095.2084
       292 84143 5041.76094
       292 88786 4827.46575
      1535 88866 4696.2949
      1535 88626 5222.77967
      1535 88767 5069.74802
      1535 88727 4911.87648
      1535 83673 5916.9548
      1535 88640 4783.99591

......

242 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 2820496992

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("PD_PRICE_UNITS_EUR">4400)

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=3; dependencies=(APPS.FACT_PD_OUT_ITM_293); name="select project_id, outlet_id,avg(pd_price_units_eur) avgval from apps.fact_pd_out_itm_293
where pd_price_units_eur>4400 gro"

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
   0 consistent gets
   0 physical reads
   0 redo size
      11523 bytes sent via SQL*Net to client
728 bytes received via SQL*Net from client
18 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
242 rows processed

SQL>

可以观察到，第2次执行所有的IO都消失了。速度提升明显。

5. 执行DML，无效化Result Cache：

另外开一个终端，以apps用户执行：

[oracle@station76 Desktop]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 11 01:32:43 2022

SQL> conn apps/apps
Connected.
SQL> update fact_pd_out_itm_293 set pd_price_units_eur=pd_price_units_eur+1
2 where pd_price_units_eur>4400 ;

2058 rows updated.

SQL> commit;

Commit complete.

以sys身份查看Result Cache情况：

SQL> conn / as sysdba
Connected.
SQL> set linesize 10000
SQL> select type, status , name from v$result_cache_objects;

TYPE    STATUS    NAME
---------- --------- --------------------------------------------------------------------------------------------------------------------------------
Dependency Published APPS.FACT_PD_OUT_ITM_293
Result    Published SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cach
Result    Invalid   SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cach
Result    Invalid   select project_id, outlet_id,avg(pd_price_units_eur) avgval from apps.fact_pd_out_itm_293
         where pd_price_units_eur>4400 gro

6. 第3次执行，更新Result Cache：

以apps用户再次执行：

......

242 rows selected.

Elapsed: 00:00:00.53

Execution Plan
----------------------------------------------------------
Plan hash value: 2820496992

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("PD_PRICE_UNITS_EUR">4400)

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=3; dependencies=(APPS.FACT_PD_OUT_ITM_293); name="select project_id, outlet_id,avg(pd_price_units_eur) avgval from apps.fact_pd_out_itm_293
where pd_price_units_eur>4400 gro"

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
   27547 consistent gets
   25777 physical reads
   0 redo size
      11523 bytes sent via SQL*Net to client
728 bytes received via SQL*Net from client
18 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
242 rows processed

以sys身份查看Result Cache情况：

SQL> conn / as sysdba
Connected.
SQL> set linesize 10000
SQL> select type, status , name from v$result_cache_objects;

Result    Published SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cach
Result    Invalid   SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cach
Result    Invalid   select project_id, outlet_id,avg(pd_price_units_eur) avgval from apps.fact_pd_out_itm_293
         where pd_price_units_eur>4400 gro

SQL> exec dbms_result_cache.memory_report(detailed=>true);

R e s u l t C a c h e M e m o r y R e p o r t

[Parameters]

Block Size = 1K bytes

Maximum Cache Size = 64M bytes (64K blocks)

Maximum Result Size = 3276K bytes (3276 blocks)

[Memory]

Total Memory = 169392 bytes [0.027% of the Shared Pool]

... Fixed Memory = 5440 bytes [0.001% of the Shared Pool]

....... Memory Mgr = 208 bytes

....... Cache Mgr = 256 bytes

....... Bloom Fltr = 2K bytes

....... State Objs = 2928 bytes

... Dynamic Memory = 163952 bytes [0.026% of the Shared Pool]

....... Overhead = 131184 bytes

........... Hash Table = 64K bytes (4K buckets)

........... Chunk Ptrs = 24K bytes (3K slots)

........... Chunk Maps = 12K bytes

........... Miscellaneous = 131184 bytes

....... Cache Memory = 32K bytes (32 blocks)

........... Unused Memory = 19 blocks

........... Used Memory = 13 blocks

............... Dependencies = 1 blocks (1 count)

............... Results = 12 blocks

................... SQL = 10 blocks (2 count)

................... Invalid = 2 blocks (2 count)

PL/SQL procedure successfully completed.

7. 第4次执行，使用Result Cache：

以apps用户再次执行：

......

242 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 2820496992

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("PD_PRICE_UNITS_EUR">4400)

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=3; dependencies=(APPS.FACT_PD_OUT_ITM_293); name="select project_id, outlet_id,avg(pd_price_units_eur) avgval from apps.fact_pd_out_itm_293
where pd_price_units_eur>4400 gro"

8. 第5/6次执行，手工by pass Result Cache：

以apps用户再次执行：

SQL> exec dbms_result_cache.bypass(bypass_mode=>true);

......

242 rows selected.

Elapsed: 00:00:00.54

Execution Plan
----------------------------------------------------------
Plan hash value: 2820496992

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("PD_PRICE_UNITS_EUR">4400)

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=3; dependencies=(APPS.FACT_PD_OUT_ITM_293); name="select project_id, outlet_id,avg(pd_price_units_eur) avgval from apps.fact_pd_out_itm_293
where pd_price_units_eur>4400 gro"

取消by pass：

SQL> exec dbms_result_cache.bypass(bypass_mode=>false);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> select project_id, outlet_id,avg(pd_price_units_eur) avgval from apps.fact_pd_out_itm_293
where pd_price_units_eur>4400 group by project_id, outlet_id; 2

PROJECT_ID OUTLET_ID   AVGVAL
---------- ---------- ----------
       292 84372 5608.05585
       292 88621 6390.24913
       292 84174 5096.2084
       292 84143 5042.76094
       292 88786 4828.46575
      1535 88866 4697.2949
      1535 88626 5223.77967
      1535 88767 5070.74802
      1535 88727 4912.87648
      1535 83673 5917.9548
      1535 88640 4784.99591

......

PROJECT_ID OUTLET_ID   AVGVAL
---------- ---------- ----------
      1535 88670 4819.14175
      1535 88609 5159.81889
      1535     381305 4682.9622
      1535 88724 4430.94812
      1535     381237 4707.98662
      1535 84174 5081.55481
      1535     381281 4849.78735
      1535     381241 4784.43847
      1535 84124 5085.61473
      1535 88643 4575.54687
      1535 84464 4728.71958

242 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 2820496992

--------------------------------------------------------------------------------------------------
| Id | Operation     | Name    | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |     |   241 | 3387 | 4398   (1)| 00:00:01 |
|   1 | RESULT CACHE     | 607f28a3g2m0cagwxs0kc2adtr | | |       | |
|   2 |   HASH GROUP BY     |     |   241 | 3387 | 4398   (1)| 00:00:01 |
|* 3 |    TABLE ACCESS FULL| FACT_PD_OUT_ITM_293 | 2193 | 30702 | 4397   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("PD_PRICE_UNITS_EUR">4400)

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=3; dependencies=(APPS.FACT_PD_OUT_ITM_293); name="select project_id, outlet_id,avg(pd_price_units_eur) avgval from apps.fact_pd_out_itm_293
where pd_price_units_eur>4400 gro"

SQL>

9. 跨会话PL/SQL函数Result Cache：

这样的函数，除了要满足函数的其他写法要求之外，还不能有OUT或IN-OUT型的参数，而且不能有LOB、ref-cursor型的IN型参数，不能返回LOB、ref-cursor型的结果。

以apps用户登录会话1：

SQL> set autot on

SQL> set linesize 10000

create or replace function func1 (p_price number)

return number

result_cache

v_result number;

begin

select avg(pd_price_units_eur) into v_result from apps.fact_pd_out_itm_293

where pd_price_units_eur> p_price ;

return v_result;

end;

SQL> select func1（5000） from dual;

FUNC1（5000）

-------------

5900.95667

Execution Plan

----------------------------------------------------------

Plan hash value: 1388734953

-----------------------------------------------------------------

-----------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |

| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |

-----------------------------------------------------------------

Note

-----

- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

Statistics

----------------------------------------------------------

57 recursive calls

0 db block gets

25829 consistent gets

25784 physical reads

124 redo size

568 bytes sent via SQL*Net to client

552 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

另外一个会话使用该函数：

SQL> conn apps/apps

Connected.

SQL> set linesize 1000

SQL> set autot on

SQL> select func1（5000） from dual;

FUNC1（5000）

-------------

5900.95667

Execution Plan

----------------------------------------------------------

Plan hash value: 1388734953

-----------------------------------------------------------------

-----------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |

| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |

-----------------------------------------------------------------

Note

-----

- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

568 bytes sent via SQL*Net to client

552 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

SQL> select func1（6000) from dual;

FUNC1（6000)

------------

7260.4886

Execution Plan

----------------------------------------------------------

Plan hash value: 1388734953

-----------------------------------------------------------------

-----------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |

| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |

-----------------------------------------------------------------

Note

-----

- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

Statistics

----------------------------------------------------------

4 recursive calls

0 db block gets

25789 consistent gets

25784 physical reads

124 redo size

566 bytes sent via SQL*Net to client

552 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

路过

雷人

握手

鲜花

鸡蛋

收藏邀请

上一篇：【博客文章2022】全命令行SQL Performance Analyzer进行真实环境DML类测试