[20141223]result cache 3.txt

[20141223]result cache 3.txt

--上午的测试有一些问题，做一些更正。

SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> show parameter result
NAME                                 TYPE           VALUE
------------------------------------ -------------- --------
client_result_cache_lag              big integer    3000
client_result_cache_size             big integer    0
result_cache_max_result              integer        5
result_cache_max_size                big integer    3936K
result_cache_mode                    string         MANUAL
result_cache_remote_expiration       integer        0

--我的测试机器配置不高，建立的测试表t1,t2都是2M，有点大。
create table t1 as select * from dba_objects where rownumcreate table t2 as select * from t1;

create index i_t1_object_id on t1(object_id);
create index i_t2_object_id on t2(object_id);

exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't1',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't2',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)

select t1.object_id,t1.object_name ,t2.object_name  t2_name from t1,t2 where t1.object_id=t2.object_id;

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8zu23zcbt7tzg, child number 0
-------------------------------------
select t1.object_id,t1.object_name ,t2.object_name  t2_name from t1,t2
where t1.object_id=t2.object_id
Plan hash value: 1838229974
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |      1 |        |    81 (100)|  10000 |00:00:00.13 |     314 |       |       |          |
|   1 |  RESULT CACHE       | 2489tdpagzw9gat7au5h227g5n |      1 |        |            |  10000 |00:00:00.13 |     314 |       |       |          |
|*  2 |   HASH JOIN         |                            |      1 |  10000 |    81   (2)|  10000 |00:00:00.10 |     314 |  1237K|  1237K| 1575K (0)|
|   3 |    TABLE ACCESS FULL| T1                         |      1 |  10000 |    40   (0)|  10000 |00:00:00.01 |     132 |       |       |          |
|   4 |    TABLE ACCESS FULL| T2                         |      1 |  10000 |    40   (0)|  10000 |00:00:00.01 |     182 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 -

--从T1,T2表的buffers数量不同也可以看出现。

drop table t1 purge;
drop table t2 purge;

create table t1 as select * from dba_objects where rownumcreate table t2 as select * from t1;

create index i_t1_object_id on t1(object_id);
create index i_t2_object_id on t2(object_id);

exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't1',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't2',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)

alter table t1 result_cache (mode force);
alter table t2 result_cache (mode force);

select t1.object_id,t1.object_name ,t2.object_name  t2_name from t1,t2 where t1.object_id=t2.object_id;

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8zu23zcbt7tzg, child number 1
-------------------------------------
select t1.object_id,t1.object_name ,t2.object_name  t2_name from t1,t2
where t1.object_id=t2.object_id
Plan hash value: 1838229974
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |      1 |        |    21 (100)|   2000 |00:00:00.01 |       |       |          |
|   1 |  RESULT CACHE       | bfzysjagt8t9ff4vxuj0rnhpdb |      1 |        |            |   2000 |00:00:00.01 |       |       |          |
|*  2 |   HASH JOIN         |                            |      0 |   2000 |    21   (5)|      0 |00:00:00.01 |   940K|   940K|          |
|   3 |    TABLE ACCESS FULL| T1                         |      0 |   2000 |    10   (0)|      0 |00:00:00.01 |       |       |          |
|   4 |    TABLE ACCESS FULL| T2                         |      0 |   2000 |    10   (0)|      0 |00:00:00.01 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 -

--这样看执行计划里面就没有buffers列。

--重复上午的测试：
create or replace view v_t1 as select /*+ result_cache */ * from t1;
create or replace view v_t2 as select /*+ result_cache */ * from t2;

select t1.object_id,t1.object_name ,(select object_name from v_t2 t2 where t2.object_id=t1.object_id ) t2_name from v_t1 t1;

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5m2947jz8gjx5, child number 0
-------------------------------------
select t1.object_id,t1.object_name ,(select object_name from v_t2 t2
where t2.object_id=t1.object_id ) t2_name from v_t1 t1

Plan hash value: 3081828694

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |      1 |        |    10 (100)|   2000 |00:00:00.02 |      38 |
|*  1 |  VIEW               | V_T2                       |   2000 |   2000 |    10   (0)|   2000 |00:00:08.23 |      28 |
|   2 |   RESULT CACHE      | 3ckpfcrzckjrudnq0d5yydjmp6 |   2000 |        |            |   4000K|00:00:04.10 |      28 |
|   3 |    TABLE ACCESS FULL| T2                         |      1 |   2000 |    10   (0)|   2000 |00:00:00.01 |      28 |
|   4 |  VIEW               | V_T1                       |      1 |   2000 |    10   (0)|   2000 |00:00:00.02 |      38 |
|   5 |   RESULT CACHE      | cv2x73bw0179m9akmj7wypvxkf |      1 |        |            |   2000 |00:00:00.01 |      38 |
|   6 |    TABLE ACCESS FULL| T1                         |      1 |   2000 |    10   (0)|   2000 |00:00:00.01 |      38 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("T2"."OBJECT_ID"=:B1)
Result Cache Information (identified by operation id):
------------------------------------------------------
   2 -
   5 -
30 rows selected.

--有点慢，逻辑读也很低，仅仅28。但是执行确实慢，要8秒。

SCOTT@test> alter system set result_cache_max_size=50m scope=memory;
System altered.

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5m2947jz8gjx5, child number 0
-------------------------------------
select t1.object_id,t1.object_name ,(select object_name from v_t2 t2
where t2.object_id=t1.object_id ) t2_name from v_t1 t1
Plan hash value: 3081828694
---------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |      1 |        |    10 (100)|   2000 |00:00:00.01 |
|*  1 |  VIEW               | V_T2                       |   2000 |   2000 |    10   (0)|   2000 |00:00:08.64 |
|   2 |   RESULT CACHE      | 3ckpfcrzckjrudnq0d5yydjmp6 |   2000 |        |            |   4000K|00:00:04.29 |
|   3 |    TABLE ACCESS FULL| T2                         |      0 |   2000 |    10   (0)|      0 |00:00:00.01 |
|   4 |  VIEW               | V_T1                       |      1 |   2000 |    10   (0)|   2000 |00:00:00.01 |
|   5 |   RESULT CACHE      | cv2x73bw0179m9akmj7wypvxkf |      1 |        |            |   2000 |00:00:00.01 |
|   6 |    TABLE ACCESS FULL| T1                         |      0 |   2000 |    10   (0)|      0 |00:00:00.01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("T2"."OBJECT_ID"=:B1)
Result Cache Information (identified by operation id):
------------------------------------------------------
   2 -
   5 -

--逻辑读消失，但是依旧很慢。

时间： 2024-07-30 10:52:41

[20141223]result cache 3.txt的相关文章

[20150924]result cache problem.txt

[20150924]result cache problem.txt --昨天看了连接,看到一个关于result cache的例子,重复测试看看: --链接 https://jonathanlewis.wordpress.com/2015/09/22/result-cache/ 1.环境: SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ --

[20160919]Result cache问题.txt

[20160919]Result cache问题.txt --看了链接http://blog.dbi-services.com/result-cache-side-effects-on-number-of-calls/,重复测试: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -----------

[20151015]关于result cache 2.txt

[20151015]关于result cache 2.txt --11G 开始支持result cache,把执行的结果保存在共享池,从而一定程度减少逻辑读,而那些对象或者那些语句适合做result cache呢? --自己在前一段时间想把一些表设置RESULT_CACHE为MODE FORCE,访问这些对象时,可以利用result cache模式. --另外我本来想一些语句通过sql patch的方式加入提示/*+ result_cache */ ,结果不成功. --参考链接:http://b

[20111230]11Gr2 result cache[1].txt

11G的result cache是一个很吸引人的特性,可以大幅减少逻辑读取,特别对于一些经常执行的语句,而结果不是经常变化的,效果不错,我的测试遇到一个小问题. SQL> select * from v$version; BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0

1223 result cache,sql profile,sql patch

[20141223]result cache 与sql profile,sql patch.txt --前面blog已经提到result cache的好处与缺点,对于第三方优化,sql profile可以改变稳定执行计划,是否可以通过改变提示来稳定 --执行计划,这样对频繁执行的语句较少逻辑读,提高服务器响应有积极意义. --sql patch 也具有相似的作用,看看这种方式是否可行. SCOTT@test> @ver1 PORT_STRING                    VERSIO

[20141219]result cache与view.txt

[20141219]result cache与view.txt --result cache是11g的新特性,能一定程度减少逻辑读,我个人的感觉特别适合很少修改,经常访问的小表,而应用中经常扫描的表, --我经常把这种应用模式叫刷屏软件.... --前一阵子我在做优化工作中,遇到的一些问题,做一些总结: SCOTT@test> @ver1 PORT_STRING          VERSION        BANNER -------------------- --------------

[20151014]关于result cache.txt

[20151014]关于result cache.txt --11G 开始支持result cache,把执行的结果保存在共享池,从而一定程度减少逻辑读,而那些对象或者那些语句适合做result cache呢? --自己在前一段时间想把一些表设置RESULT_CACHE为MODE FORCE,访问这些对象时,可以利用result cache模式. --另外我本来想一些语句通过sql patch的方式加入提示/*+ result_cache */ ,结果不成功. --参考链接:http://blo

oralce 12.1中出现大量Result Cache: RC Latch处理

昨天有个朋友找到我说他们的12.1的库在业务高峰期非常慢,希望我们给予优化支持,经过awr分析,定位到问题为latch free问题,具体定位为:Result Cache: RC Latch.优化之前awr部分信息 awr整体负载情况,证明当前这个库已经比较忙,业务反馈很慢 addr信息和top wait信息,确定是latch free问题比较突出 latch信息统计和ash信息,找出来突出的latch,定位为Result Cache: RC Latch引起该问题 补充大量异常sql 类似sql

【11gR2新特性】result cache 的三种模式

yang@rac1>show parameter result NAME                                 TYPE        VALUE ------------------------------------ ----------- ------- client_result_cache_lag              big integer 3000 client_result_cache_size             big integer 0 r