首页 » ORACLE 9i-23ai » Troubleshooting Oracle 19c Wrong result cdb_data_files caused by result cache
Troubleshooting Oracle 19c Wrong result cdb_data_files caused by result cache
最近遇到一个oracle 19c（19.15）的多租户环境，在查询cdb_data_files时显示的文件大小偶尔不正确的现象，这可能影响我们对于数据库空间类检测，通过分析发现在多租户环境中查询CDB_或共享对象时(sharing=object )，递归查询中使用了result cache.简单记录。
注意实际我们的BFR*表空间大小为20G， 但下面的查询为10G。
跟踪查询
测试查询cdb_data_files的递归SQL,环境23c 多租户.
SQL> alter system flush shared_pool;
System altered.
SQL> @46on 12
Session altered.
SQL> select file_id,bytes from cdb_data_files where con_id=3;
FILE_ID BYTES
---------- ----------
18 1073741824
13 1268776960
14 6564085760
15 524288000
16 818282496
17 6246891520
6 rows selected.
SQL> @46off
Session altered.
SQL> @t
TRACEFILE
---------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/anbob/anbob1/trace/anbob1_ora_122908.trc
[oracle@db1$ sed -n '/PARSING/,/END OF STMT/p' /u01/app/oracle/diag/rdbms/anbob/anbob1/trace/anbob1_ora_122908.trc |egrep -v 'PARSING|END'|grep -i RESULT
SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ "FILE_ID","BYTES" FROM "SYS"."DBA_DATA_FILES" "K" WHERE 1=1
Note:
注意这里递归SQL中看到使用了RESULT_CACHE HINT,也就是说是因为使用了查询结果缓存，显示的是之前cache的结果。SYSOBJ=TRUE对应的参数为“_rc_sys_obj_enabled” 默认为true.
SQL> @pd rc_sys
Show all parameters and session values from x$ksppi/x$ksppcv...
NUM N_HEX NAME VALUE DESCRIPTION
---------- ----- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
5119 13FF _rc_sys_obj_enabled TRUE result cache enabled for Sys Objects
SQL> select type,status,name,row_count from v$result_cache_objects where name like '%BA_DATA_FILES%' order by row_count desc fetch first 10 rows only;
TYPE STATUS NAME ROW_COUNT
---------- --------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------
Result Published SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ "FILE_ID","BYTES" FROM "SYS"."DBA_DATA_FILES" "K" WHE 6
Result Invalid SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ "FILE_ID","BYTES" FROM "SYS"."DBA_DATA_FILES" "K" WHE 6
Result Invalid SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ "FILE_ID","BYTES" FROM "SYS"."DBA_DATA_FILES" "K" WHE 6
Result Invalid SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ "FILE_ID","BYTES" FROM "SYS"."DBA_DATA_FILES" "K" WHE 6
Result Invalid SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ "FILE_ID","BYTES" FROM "SYS"."DBA_DATA_FILES" "K" WHE 6
Result Invalid SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ "FILE_ID","BYTES" FROM "SYS"."DBA_DATA_FILES" "K" WHE 6
Result Published SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ "FILE_ID","BYTES" FROM "SYS"."DBA_DATA_FILES" "K" WHE 5
Result Invalid SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ "FILE_ID","BYTES" FROM "SYS"."DBA_DATA_FILES" "K" WHE 5
在数据库内部的一些hard parse中也有使用result cache, 与非SYS对象一样，也有配置result cache失效规则，我们查找一下result 相关的参数。
SQL> @pd result%cache
Show all parameters and session values from x$ksppi/x$ksppcv...
NUM N_HEX NAME VALUE DESCRIPTION
---------- ----- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
4181 1055 _result_cache_auto_size_threshold 100 result cache auto max size allowed
4182 1056 _result_cache_auto_time_threshold 1000 result cache auto time threshold
4183 1057 _result_cache_auto_execution_threshold 1 result cache auto execution threshold
4184 1058 _result_cache_deterministic_plsql FALSE result cache deterministic PLSQL functions
4185 1059 result_cache_integrity TRUSTED result cache deterministic PLSQL functions
4309 10D5 result_cache_mode MANUAL result cache operator usage mode
4310 10D6 _obsolete_result_cache_mode MANUAL USERS SHOULD NOT SET THIS! Used for old qksced parameterof result_cache_mode
4311 10D7 result_cache_max_size 65011712 maximum amount of memory to be used by the cache
4312 10D8 result_cache_max_result 5 maximum result size as percent of cache size
4313 10D9 result_cache_remote_expiration 0 maximum life time (min) for any result using a remote object
4314 10DA _result_cache_block_size 4096 result cache block size
4315 10DB _result_cache_copy_block_count 1 blocks to copy instead of pinning the result
4316 10DC _result_cache_global TRUE Are results available globally across RAC?
4317 10DD _result_cache_per_pdb TRUE Is service result cache per pdb
4318 10DE _result_cache_timeout 1 maximum time (sec) a session waits for a result
4319 10DF _result_cache_white_list users allowed to use result cache
4320 10E0 _result_cache_black_list cache_id's not allowed to use the result cache
4321 10E1 _result_cache_latch_free_reads ADMIN latch free reads
4322 10E2 _result_cache_do_recycle_period 300 Timeout value for DOs added through invalidation
4323 10E3 _result_cache_object_black_list 0 object numbers not allowed to use the result cache
4324 10E4 _result_cache_latch_count 16 number of result cache latches
4325 10E5 result_cache_execution_threshold 2 minimum executions before a PL/SQL function is cached
4326 10E6 _result_cache_history_size 0 memory used to track PL/SQL function history
4327 10E7 result_cache_max_temp_size 650117120 maximum amount of temp space to be used
4328 10E8 result_cache_max_temp_result 5 maximum temp per result as percent of total temp for result cache
4329 10E9 _result_cache_global_send TRUE Is global send conditional in RAC?
4330 10EA _result_cache_load_rate 1 Simulated global load rate (bytes per 1/100 sec)
4331 10EB _result_cache_use_hash_table TRUE whether to use hash table for object tracking
4332 10EC result_cache_auto_blocklist ON whether to run the auto blocklisting algorithm
4333 10ED _result_cache_auto_blocklist_frequency 50 Frequency of autoblocklist checks in units of RC invalidations
4334 10EE _result_cache_auto_broadcast_threshold 10000 Threshold of RAC round trip times before blocklisting
4335 10EF _result_cache_auto_blocklist_threshold 1000 Threshold of ratio of ROs created to used before blocklisting
4336 10F0 _result_cache_auto_blocklist_epsilon 300 How much better blocklisted objects must be to be unblocklisted
4337 10F1 _result_cache_auto_ignore_list 0 object numbers that will never be auto blocklisted
4338 10F2 _result_cache_auto_time_distance 300 result cache auto time distance
4339 10F3 _result_cache_auto_dml_monitoring_slots 4 result cache auto dml monitoring slot
4340 10F4 _result_cache_auto_dml_monitoring_duration 15 result cache auto dml monitoring duration
4341 10F5 _result_cache_auto_dml_threshold 16 result cache auto dml threshold
4342 10F6 _result_cache_auto_dml_trend_threshold 20 result cache auto dml trend threshold
5039 13AF _optimizer_ads_use_result_cache TRUE use result cache for ADS queries
5040 13B0 _optimizer_ads_result_cache_life 3600 result cache shelf life for ADS queries
5119 13FF _rc_sys_obj_enabled TRUE result cache enabled for Sys Objects
5153 1421 _cdb_view_rc_shelflife 30 Result Cache Shelflife for a CDB view query
5379 1503 client_result_cache_size 0 client result cache max size in bytes
5380 1504 client_result_cache_lag 3000 client result cache maximum lag in milliseconds
5381 1505 _client_result_cache_bypass FALSE bypass the client result cache
5382 1506 _client_result_cache_ramthreshold client_result_cache_ramthreshold
5454 154E _re_result_cache_keysiz 20 defines max number key for result cache hash table
5455 154F _re_result_cache_size 20 defines max number of cached elements for result cache
5604 15E4 _xsolapi_sql_result_set_cache_size 32 OLAP API result set cache size
6282 188A _disable_cdb_view_rc_invalidation FALSE disable Result Cache invalidation for CDB View results
6507 196B _hcs_disable_materialize FALSE add materialize to result cache hint
6510 196E _hcs_disable_result_cache_hint FALSE generate hcs query result cache hints
53 rows selected.
不太确认哪个是控制result失效的参数，Hint SHELFLIFE=30 似乎是_cdb_view_rc_shelflife 参数，对象在没有依赖关系跟踪时（如fixed view内存结构表）查询在30秒内显示相同的结果, 自定义sharing=object 对象同样生效(create view xx sharing=object as xxx)。可以使用exec dbms_result_cache.bypass(true); 尝试禁用result cache.
对于我们本案例的问题，禁用sys相关的result cache可以解决。
set _rc_sys_obj_enabled = false;
目前MOS中还未明确相关BUG， 实例级禁用该参数可能会影响性能，生产库调整参数后果自负。
扩展
另外result cache在oracle 12c中同样存在一个Bug 26436717,会导致数据库出现latch free发现在Result Cache: RC Latch，即使RESULT_CACHE_MODE = MANUAL时， 主要是因为adaptive dynamic statistics (Sampling) 的特性引起，当对SQL语句使用自适应动态统计(抽样)时，它可以根据这些统计信息决定，通过使用Result Cache对这些查询可以获得更好的响应时间，这可能会导致Result Cache的大量使用，导致”Result Cache: RC Latch”的争用。在12.2中，参数optimizer_adaptive_statistics不控制扩展统计信息的自动创建。相反，这是通过表级统计首选项AUTO_STAT_EXTENSIONS控制的，默认为OFF。
禁用 Adaptive Dynamic Statistics是使用result cache：
alter system set "_optimizer_ads_use_result_cache" = FALSE;
— over —
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。