_optimizer_ads_use_result_cache parameter use in Oracle

_optimizer_ads_use_result_cache parameter use in Oracle

_optimizer_ads_use_result_cache Parameter
Parameter is used with dynamic sampling to keep the result of the query in the result cache. So that on next time execution of the SQL query, it will get the result from result cache to increase the performance of the database.
It helpful in Data Warehouse databases, to increase the query performance to keep result. But in OLTP frequent changes or DML operation on table, it may or mayn’t usefully depend upon your operations.

For disable the result cache use in dynamic sampling
By Default, _optimizer_ads_use_result_cache parameter has TRUE value.

alter system set '_optimizer_ads_use_result_cache' = FALSE;

If you set the parameter “_optimizer_ads_use_result_cache” = FALSE, this will stop only dynamic statistics from using the result cache. Now result cache is not used with dynamic sampling feature.

SQL dynamic statistics is now part of Adaptive Statistics (AS) which is comprised of:
– Adaptive Dynamic Statistics (ADS)
– SQL Automatic Re-optimization
– SQL Plan Directives
Note: They all interact each other for generate better execution plan.

Dynamic Sampling
Dynamic sampling is used by optimizer for calculating the dynamic statistics during the execution of SQL Query which will increase the execution time of SQL Query if object used in query has not proper stats OR choose to use best execution plan.
Dynamic sampling using result cache feature. you can disable with this parameter “_optimizer_ads_use_result_cache”.
If you disable dynamic sampling then this parameter “_optimizer_ads_use_result_cache” role is also disable.

Check the Optimizer dynamic Sampling
Setting OPTIMIZER_DYNAMIC_SAMPLING=0 will disable Adaptive Dynamic Sampling entirely.

show parameter optimizer_dynamic_sampling
---------------------------- ----------- -------
optimizer_dynamic_sampling integer 2

Disable the Optimizer dynamic Sampling
alter system set optimizer_dynamic_sampling=0 scope=both sid='*';

For more detail: Dynamic Sampling

Check the result cache objects

select count(*) from gv$result_cache_objects;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.