前言：
上面的研究我们知道，绑定变量使用result cache的时候，缓存的结果使用变量值进行参数化，只能找到相同变量值的缓存结果。
同一条sql ，下面使用不同绑定变量的情况下测试执行效果：
开始测试：
参数设置情况：
SQL> show parameter result
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
multishard_query_partial_results string not allowed
result_cache_max_result integer 5
result_cache_max_size big integer 3872K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
SELECT /*+ RESULT_CACHE */ * from tt where object_id=’&1’;
开启autotrace
场景1：第一次测试 变量为1
SQL> set autotrace on
SQL> SELECT /*+ RESULT_CACHE / * from tt where object_id=&1;
Enter value for 1: ‘1’
old 1: SELECT /+ RESULT_CACHE / * from tt where object_id=&1
new 1: SELECT /+ RESULT_CACHE */ * from tt where object_id=‘1’
no rows selected
Execution Plan
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Plan hash value: 264906180
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| 0 | SELECT STATEMENT | | 1 | 132 | 395 (1)| 00:00:01 |
| 1 | RESULT CACHE | dcpg1vyq883dc208y4trxzz38d | 1 | 132 | 395 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TT | 1 | 132 | 395 (1)| 00:00:01 |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Predicate Information (identified by operation id):
+++++++++++++++++++++++++++++++++++++++++++++++++++
2 + filter(“OBJECT_ID”=1)
Result Cache Information (identified by operation id):
++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 + column+count=26; dependencies=(ZC.TT); parameters=(nls); name=“SELECT /*+ RESULT_CACHE */ * from tt where object_id=‘1’”
Statistics
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
159 recursive calls
132 db block gets
1555 consistent gets
1415 physical reads
24192 redo size
2375 bytes sent via SQLNet to client
408 bytes received via SQLNet from client
1 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
0 rows processed
场景2：变量为1
SQL> SELECT /*+ RESULT_CACHE / * from tt where object_id=’&1’;
Enter value for 1: 1
old 1: SELECT /+ RESULT_CACHE / * from tt where object_id=’&1’
new 1: SELECT /+ RESULT_CACHE */ * from tt where object_id=‘1’
no rows selected
Execution Plan
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Plan hash value: 264906180
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| 0 | SELECT STATEMENT | | 1 | 132 | 395 (1)| 00:00:01 |
| 1 | RESULT CACHE | dcpg1vyq883dc208y4trxzz38d | 1 | 132 | 395 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TT | 1 | 132 | 395 (1)| 00:00:01 |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Predicate Information (identified by operation id):
+++++++++++++++++++++++++++++++++++++++++++++++++++
2 + filter(“OBJECT_ID”=1)
Result Cache Information (identified by operation id):
++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 + column+count=26; dependencies=(ZC.TT); parameters=(nls); name=“SELECT /*+ RESULT_CACHE */ * from tt where object_id=‘1’”
Statistics
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
2375 bytes sent via SQLNet to client
408 bytes received via SQLNet from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
可以看到变量相同的话，consistent gets 和physical reads 都是0，使用到了 result cache 。
场景3： 变量值为100
SQL> SELECT /*+ RESULT_CACHE / * from tt where object_id=’&1’;
Enter value for 1: 100
old 1: SELECT /+ RESULT_CACHE / * from tt where object_id=’&1’
new 1: SELECT /+ RESULT_CACHE */ * from tt where object_id=‘100’
SYS SEQ$ 100 100 TABLE 17+APR+19 17+APR+19 2019+04+17:00:56:15 VALID N N N 1 METADATA LINK Y N USING_NLS_COMP N N
Execution Plan
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Plan hash value: 264906180
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| 0 | SELECT STATEMENT | | 1 | 132 | 395 (1)| 00:00:01 |
| 1 | RESULT CACHE | gdt3bwfxg9cbf4gu17tarx4khx | 1 | 132 | 395 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TT | 1 | 132 | 395 (1)| 00:00:01 |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Predicate Information (identified by operation id):
+++++++++++++++++++++++++++++++++++++++++++++++++++
2 + filter(“OBJECT_ID”=100)
Result Cache Information (identified by operation id):
++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 + column+count=26; dependencies=(ZC.TT); parameters=(nls); name=“SELECT /*+ RESULT_CACHE */ * from tt where object_id=‘100’”
Statistics
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 recursive calls
0 db block gets
1420 consistent gets
0 physical reads
0 redo size
2686 bytes sent via SQLNet to client
421 bytes received via SQLNet from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到变量值不同的话无法使用result cache。
场景4：变量值再次为1
SQL> SELECT /*+ RESULT_CACHE / * from tt where object_id=’&1’;
Enter value for 1: 1
old 1: SELECT /+ RESULT_CACHE / * from tt where object_id=’&1’
new 1: SELECT /+ RESULT_CACHE */ * from tt where object_id=‘1’
no rows selected
Execution Plan
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Plan hash value: 264906180
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| 0 | SELECT STATEMENT | | 1 | 132 | 395 (1)| 00:00:01 |
| 1 | RESULT CACHE | dcpg1vyq883dc208y4trxzz38d | 1 | 132 | 395 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TT | 1 | 132 | 395 (1)| 00:00:01 |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Predicate Information (identified by operation id):
+++++++++++++++++++++++++++++++++++++++++++++++++++
2 + filter(“OBJECT_ID”=1)
Result Cache Information (identified by operation id):
++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 + column+count=26; dependencies=(ZC.TT); parameters=(nls); name=“SELECT /*+ RESULT_CACHE */ * from tt where object_id=‘1’”
Statistics
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
2375 bytes sent via SQLNet to client
408 bytes received via SQLNet from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
同样使用了result cache 暂时没被变量值为10的覆盖
场景5：变量值100
SQL> SELECT /*+ RESULT_CACHE / * from tt where object_id=’&1’;
Enter value for 1: 100
old 1: SELECT /+ RESULT_CACHE / * from tt where object_id=’&1’
new 1: SELECT /+ RESULT_CACHE */ * from tt where object_id=‘100’
SYS SEQ$ 100 100 TABLE 17+APR+19 17+APR+19 2019+04+17:00:56:15 VALID N N N 1 METADATA LINK Y N USING_NLS_COMP N N
Execution Plan
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Plan hash value: 264906180
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| 0 | SELECT STATEMENT | | 1 | 132 | 395 (1)| 00:00:01 |
| 1 | RESULT CACHE | gdt3bwfxg9cbf4gu17tarx4khx | 1 | 132 | 395 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TT | 1 | 132 | 395 (1)| 00:00:01 |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Predicate Information (identified by operation id):
+++++++++++++++++++++++++++++++++++++++++++++++++++
2 + filter(“OBJECT_ID”=100)
Result Cache Information (identified by operation id):
++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 + column+count=26; dependencies=(ZC.TT); parameters=(nls); name=“SELECT /*+ RESULT_CACHE */ * from tt where object_id=‘100’”
Statistics
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
2686 bytes sent via SQLNet to client
421 bytes received via SQLNet from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到还是能使用result cache的。
最后查询result cache的使用情况：
SQL> select namespace,
status,
name,
hash,
count() number_of_results,
round(avg(scan_count)) avg_scan_cnt,
round(max(scan_count)) max_scan_cnt,
round(sum(block_count)) tot_blk_cnt
from v$result_cache_objects
where type = ‘Result’
group by namespace, name, hash 2 3 4 5 6 7 8 9 10 11 ,status
order by namespace, tot_blk_cnt; 12
SQL Published SELECT /+ RESULT_CACHE / * from tt where object_id=‘1’ 2249427476 1 2 2 1
SQL Published SELECT /+ RESULT_CACHE / * from tt where object_id=‘100’ 333890659 1 1 1 1
SQL Published SELECT / OPT_DYN_SAMP / /+ ALL_ROWS IGNORE_WHERE_CLAUSE RESULT_CACHE(SNAPSHOT=3600) opt_param(‘parallel_execution_enabled’, ’ 1792817705
结论：
result cache空间够的情况下，会缓存不同绑定变量的执行结果，当绑定变量的值执行过的情况下，可以使用result cache。
