在使用绑定变量的情况下使用result cache

来源：这里教程网时间：2026-03-03 21:15:21 作者：

上面的研究我们知道，绑定变量使用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 SQL*Net to client 408 bytes received via SQL*Net 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 SQL*Net to client 408 bytes received via SQL*Net 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 SQL*Net to client 421 bytes received via SQL*Net 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 SQL*Net to client 408 bytes received via SQL*Net 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 SQL*Net to client 421 bytes received via SQL*Net 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。