12c: OPTIMIZER_DYNAMIC_SAMPLING=11

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 spending significant time 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, fairly standard, 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

0

dc_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. Contrary to Pythian, I didn’t notice inefficient plans, probably because the database that I was monitoring did not use in-memory option. However, the 3rd party application, which does a fairly large number of parse calls started running between 10% and 15% slower than before.

Advertisements

About mgogala

I am a long time Oracle DBA, who has worked on very large databases. I have worked with OPS and RAC since its inception.I am also a published book writer, having published two books about PHP. This blog is about the challenges and adventures in my professional life. Sorry, no family pictures here.
This entry was posted in Uncategorized. Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s