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

[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.其它我不确定.