[20250103]result cache查询无效分析.txt

--//生产系统,在一个频繁查询的表设置result_cache=force,当我查询时发现我的设置无效.
--//检查表定义没有问题,分析并做一个记录:

1.环境:
SYS@127.0.0.1:9105/xtdb/xtdb1> @ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.分析:
SYS@127.0.0.1:9105/xtdb/xtdb1> show parameter result
PARAMETER_NAME                   TYPE        VALUE
-------------------------------- ----------- -------------
_optimizer_ads_use_result_cache  boolean     FALSE
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 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
result_cache_mode                string      MANUAL
result_cache_remote_expiration   integer     0

--//发现result_cache_max_size=0.

SYS@127.0.0.1:9105/xtdb/xtdb1> show spparameter result_cache_max_size
SID      NAME                          TYPE                           VALUE
-------- ----------------------------- ------------------------------ ----------------------------
*        result_cache_max_size         big integer                    0

--//很明显有人显示的设置result_cache_max_size=0,不知道为什么这样操作.

--//如果没有写入spfile文件,查询应该如下:
SYS@book> show parameter esult_cache_max_size
PARAMETER_NAME        TYPE        VALUE
--------------------- ----------- -------
result_cache_max_size big integer 1792K

SYS@book> show spparameter esult_cache_max_size
SID      NAME                          TYPE                           VALUE
-------- ----------------------------- ------------------------------ ----------------------------
*        result_cache_max_size         big integer
--//看了文档缺省应该共享池的占5%，注好像是1%。一般我不会动这个参数的设置。

SYS@127.0.0.1:9105/xtdb/xtdb1> set serveroutput on
SYS@127.0.0.1:9105/xtdb/xtdb1> exec dbms_result_cache.memory_report
R e s u l t   C a c h e   M e m o r y   R e p o r t
Cache is disabled.
PL/SQL procedure successfully completed.

SYS@127.0.0.1:9105/xtdb/xtdb1> @ hidez result_cache_max_size
SYS@127.0.0.1:9105/xtdb/xtdb1> @ pr
==============================
NUM                           : 3564
N_HEX                         :   DEC
NAME                          : result_cache_max_size
DESCRIPTION                   : maximum amount of memory to be used by the cache
DEFAULT_VALUE                 : FALSE
SESSION_VALUE                 : 0
SYSTEM_VALUE                  : 0
ISSES_MODIFIABLE              : FALSE
ISSYS_MODIFIABLE              : IMMEDIATE
PL/SQL procedure successfully completed.
--//设置应该是立即生效的,有两种方式.后面设置数字+K(M),直接设置大小,如果仅仅是数字,那就是百分比.
--//注：如果仅仅数字表示百分比不对，看后面的测试。

SYS@127.0.0.1:9105/xtdb/xtdb1> alter system set result_cache_max_size=1 scope=both sid='*';
System altered.
--//我的测试这样不行.

SYS@127.0.0.1:9105/xtdb/xtdb1> alter system set result_cache_max_size=50m scope=both sid='*';
System altered.
--//测试还是不行.也许需要重启才能生效.

3.在测试环境测试看看.
SYS@book> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> alter system set result_cache_max_size=0 scope=both sid='*';
System altered.
--//重启数据库.
SYS@book> set serveroutput on
SYS@book> exec dbms_result_cache.memory_report
R e s u l t   C a c h e   M e m o r y   R e p o r t
Cache is disabled.
PL/SQL procedure successfully completed.

SYS@book> alter system set result_cache_max_size=1 scope=both sid='*';
System altered.
--//确实不起作用.重启数据库看看.

SYS@book> show parameter result
PARAMETER_NAME                 TYPE        VALUE
------------------------------ ----------- -------
client_result_cache_lag        big integer 3000
client_result_cache_size       big integer 0
result_cache_max_result        integer     5
result_cache_max_size          big integer 32K
result_cache_mode              string      MANUAL
result_cache_remote_expiration integer     0
--//可以发现1%仅仅32K.现在起作用了，注：1 应该是对齐32K，不是1%。

SYS@book> alter system reset result_cache_max_size;
System altered.

--//重启数据库看看.
SYS@book> show parameter result
PARAMETER_NAME                 TYPE        VALUE
------------------------------ ----------- -------
client_result_cache_lag        big integer 3000
client_result_cache_size       big integer 0
result_cache_max_result        integer     5
result_cache_max_size          big integer 1792K
result_cache_mode              string      MANUAL
result_cache_remote_expiration integer     0
--//缺省设置1792K,看来我前面理解存在错误。

SYS@book> show parameter shared_pool_size
PARAMETER_NAME   TYPE        VALUE
---------------- ----------- -------
shared_pool_size big integer 172M
--//感觉应该是1%,不是5%.不管这些细节.

SYS@book> alter system set result_cache_max_size=10 scope=both sid='*';
System altered.

--//重启数据库看看.
SYS@book> show parameter result
PARAMETER_NAME                 TYPE        VALUE
------------------------------ ----------- ------
client_result_cache_lag        big integer 3000
client_result_cache_size       big integer 0
result_cache_max_result        integer     5
result_cache_max_size          big integer 32K
result_cache_mode              string      MANUAL
result_cache_remote_expiration integer     0
--//看来我理解错误,数字表示占用字节大小,最小32K.

--//还原:
SYS@book> alter system reset result_cache_max_size;
System altered.

--//没有办法,生产系统只能等待重启生效.真不知道前面的安装人员为什么这样安装设置,我感觉安装者有1个死板的安装文档,按照
--//他们收集整理遇到的问题，设置这些隐含参数。

--//生产系统设置如下隐含参数.
R:\>grep "^*._" aa.ora
*._ash_size=262144000
*._cleanup_rollback_entries=400
*._clusterwide_global_transactions=FALSE
*._cursor_obsolete_threshold=1024
*._datafile_write_errors_crash_instance=FALSE
*._disable_last_successful_login_time=TRUE
*._enable_spacebg=FALSE
~~~~~~~~~~~~~~~~~~~~~~~~
*._gc_policy_time=0
*._gc_undo_affinity=FALSE
*._log_segment_dump_parameter=FALSE
*._log_segment_dump_patch=FALSE
*._optimizer_adaptive_cursor_sharing=FALSE
*._optimizer_ads_use_result_cache=FALSE
*._optimizer_aggr_groupby_elim=FALSE
*._optimizer_cartesian_enabled=FALSE
*._optimizer_extended_cursor_sharing_rel='NONE'
*._optimizer_extended_cursor_sharing='NONE'
*._optimizer_gather_feedback=FALSE
*._optimizer_gather_stats_on_load=FALSE
*._optimizer_use_feedback=FALSE
*._partition_large_extents='FALSE'
*._PX_use_large_pool=TRUE
*._rollback_segment_count=500
*._serial_direct_read='NEVER'
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
*._use_single_log_writer='TRUE'

--//下划线我设置的.感觉比较有用就是_optimizer开头几个,还有_disable_last_successful_login_time.其它我不确定.