oracle数据库报错查询,Oracle 数据库 NVARCHAR 类型查询报错 ORA-00600：kpp_concatq:2

_optimizer_ads_use_result_cache tipsOracle Database Tips by Donald BurlesonSeptember 26, 2015

Question: Can you explain what the _optimizer_ads_use_result_cache parameter does in Oracle 12c? It's my understanding that _optimizer_ads_use_result_cache is used to disable the PL/SQL result cache feature.

Answer: The _optimizer_ads_use_result_cache is used to enable and disable the Active Data Service (ADS), essentially the PL/SQL result cache feature.

Setting _optimizer_ads_use_result_cache = TRUE (default) will allow the use of the result cache for ADS queries during dynamic SQL query optimization.

To disable the result cache, you must explicitly set result_cache_max_size = 0. The below command will will disable the use of the result cache, but only for dynamic statistics activity.

alter session set "_optimizer_ads_use_result_cache" = FALSE

ADS and Dynamic Statistics

SQL dynamic statistics is now part of Adaptic Statistics (AS) which is comprised of these components:- Adaptive Dynamic Statistics (ADS)

- SQL Automatic Reoptimization

- SQL Plan Directives

Note that all of these components interact together in an undocumented way, adding to the complexity of the 12c dynamic SQL functionality.

Adaptive Dynamic Statistics can be invoked when Automatic Reoptimization is used.

Suggested fixes for this issue were to hint dynamic sampling off, or to turn it off entirely. Setting OPTIMIZER_DYNAMIC_SAMPLING=0 does still disable ADS entirely.

You can also turn the result cache off. But there is a much more targeted solution that Oracle provides, setting "_optimizer_ads_use_result_cache" = FALSE.

If you want to see the dynamic statistics queries, you can query for the ds_svc column in the v$sql view.

This will stop only dynamic statistics from using the result cache and not completely disable the dynamic sampling (renamed to dynamic statistics in 12c).