上面的研究我们知道，绑定变量使用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

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

| 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

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

| 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

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

| 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

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

| 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

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

| 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。

ITPub博客

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

最新文章