[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 -
--逻辑读消失，但是依旧很慢。
