OPTIMIZER_DYNAMIC_SAMPLING=11
From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Sat, 5 Nov 2016 15:12:19 -0400
Message-ID: <301fdfe3-f4df-3982-e2cc-ffb2090100d7_at_gmail.com>
Oracle 12c has a new feature related to optimizer dynamic sampling. The new level 11 is supposed to put the dynamic sampling on auto pilot and make the statistics available to other users. However, as soon as this was set, I started noticing processes waiting for row cache latch. This was strange, especially having in mind that ASMM was configured. I checked the V$ROWCACHE by using the following query:
Date: Sat, 5 Nov 2016 15:12:19 -0400
Message-ID: <301fdfe3-f4df-3982-e2cc-ffb2090100d7_at_gmail.com>
Oracle 12c has a new feature related to optimizer dynamic sampling. The new level 11 is supposed to put the dynamic sampling on auto pilot and make the statistics available to other users. However, as soon as this was set, I started noticing processes waiting for row cache latch. This was strange, especially having in mind that ASMM was configured. I checked the V$ROWCACHE by using the following query:
select parameter,gets,getmisses from v$rowcache order by getmisses;
Here is what I got:
PARAMETER GETMISSES GETS dc_free_extents 0 0 dc_used_extents 0 0dc_sql_prs_errors 0 0 kqlsubheap_object 0 0 dc_outlines 0 0 realm cache 0 0 Command rule cache 0 0 Realm Object cache 0 0 Rule Set Cache 0 0 DV Auth Cache 0 0 extensible security principal in 0 0 Unused 0 0 Unused 0 0 Unused 0 0 XS security class privilege 0 0 extensible security midtier cach 0 0 AV row cache 1 0 0 AV row cache 2 0 0 AV row cache 3 0 0 triton security name to ID 0 0 rule_or_piece 0 0 dc_qmc_ldap_cache_entries 0 0 qmc_app_cache_entries 0 0 qmc_app_cache_entries 0 0 qmtmrcin_cache_entries 0 0 qmtmrctn_cache_entries 0 0 qmtmrcip_cache_entries 0 0 qmtmrctp_cache_entries 0 0 qmtmrciq_cache_entries 0 0 qmtmrctq_cache_entries 0 0 qmrc_cache_entries 0 0 qmemod_cache_entries 0 0 SMO rowcache 0 0 dc_cdbfiles 0 0 dc_cdbservices 0 0 dc_pdbdba 0 0 dc_pdbstates 0 0 dc_users 0 0 dc_partition_scns 0 0 dc_users 0 0 realm auth 0 0 realm auth 0 0 Realm Subordinate Cache 0 0 dc_users 0 0 dc_users 0 0 rule_fast_operators 0 0 dc_users 0 0 dc_awr_control 2 45531 dc_profiles 6 381825 rule_info 8 8 dc_tablespace_quotas 18 44 dc_rollback_segments 60 1000560 dc_tablespaces 77 485307021 outstanding_alerts 146 2551 dc_props 180 6559399 dc_users 630 255650332 dc_constraints 989 2855 dc_files 1176 1782669 dc_sequences 2152 1999677 dc_global_oids 2314 996894 dc_table_scns 2795 2795 dc_object_grants 15522 28781047 dc_segments 145653 99182525 sch_lj_objs 195178 195278 dc_users 203715 855595322 sch_lj_oids 213295 435827 dc_histogram_defs 459886 74035168 dc_objects 613787 202217522 dc_histogram_data 1084138 76433495 dc_histogram_data 3409318 2919298230
So, the latch misses are related to the histograms. The only conceivable mechanism that can be considered a culprit is the new OPTIMIZER_DYNAMIC_SAMPLING=11 setting. As soon as that was reset to 2 (11g default), the problem was gone. What are the experiences of other list members with ODS in version 12c? I asked the almighty and omniscient Google and came across a very good article by Pythian: https://www.pythian.com/blog/performance-problems-with-dynamic-statistics-in-oracle-12c/ Apparently, I wasn't the first to encounter the problem. Does anyone on this list have a story to share?
-- Mladen Gogala Oracle DBA Tel: (347) 321-1217 -- http://www.freelists.org/webpage/oracle-lReceived on Sat Nov 05 2016 - 20:12:19 CET