Hi Dom!
What are you trying to achieve by dynamic sampling? There are two things
that I tried dynamic sampling for:
* Replace DBMS_STATS.GATHER_DATABASE_STATS altogether. I tried setting
the level to 11 in 12c, and it didn't work out very well.
* Use optimizer dynamic sampling for tables which have no stats.
Sampling level 2 is sufficient for that.
Oracle database has 3 competing mechanisms for ensuring that the
optimizer has all the data it needs to produce the best plan:
* Periodic statistics gathering, fashioned after the DB2 "runstats"
command which has historically preceded the CBO. That is implemented
by the DBMS_STATS package.
* Dynamic sampling at the execution time
* Cardinality feedback at the execution time (in 12c renamed to
"statistics feedback")
Cardinality feedback has the express purpose of fixing the plans created
based on DBMS_STATS collected statistics and it clashes with the dynamic
sampling. If you have the dynamic sampling, there is no cardinality
feedback, which I consider a big loss. Also, what statistics do you want
to use for your SQL? DBMS_STATS gathered one or dynamically gathered
one, for which you don't actually know how accurate it is? In 11G, I
used dynamic sampling only at the hint level, to collect statistics for
the temporary tables. I found it better than the undocumented
"CARDINALITY" hint. My experience with mixing DBMS_STATS and dynamic
sampling is not great. I tend to set dynamic sampling to 0 and only use
it as a hint, where necessary.
The OPTIMIZER_DYNAMIC_SAMPLING value of 11, first introduced in 12cR1
then back-ported to 11G was described as an AI version of dynamic
sampling, which will be used when necessary and made permanent, by
storing it into the statistics tables, normally populated by
DBMS_STATS. However, this did not work properly, I had library cache
latch congestion and significant slowdown, so I turned that off and
grudgingly returned back to maintaining DBMS_STATS and setting table
preferences for the "special tables". Using both DBMS_STATS and
OPTIMIZER_DYNAMIC_SAMPLING is sort of "mayochup":
https://thetakeout.com/mayochup-catch-up-new-condiment-headed-to-the-states-1825388339
On 04/17/2018 04:49 AM, Dominic Brooks wrote:
In addition to invisible indexes not being so invisible, I hash partitioned some global PK indexes to reduce insert hot block contention.
These two things – invisible local indexes with some unusable subpartitions and hash partitioned PK indexes - are the only things which changed.
I’ve now got a few handfuls of statements which, in addition to some of them doing table expansion thanks to the not-so-invisible invisible indexes, are doing crazy long hard parsing – like 30 to 40 minutes. And that causes knock-on effects to other sessions in the same workflow for the same sql / objects with “cursor: pin S wait on X” and “library cache lock”
The likely culprit is dynamic sampling. I can see the sessions doing lots of IO against different table partitions as part of the hard parse.
Otherwise the health of the database is no less healthy than it was before and the SQL is the same SQL as it was before.
Whilst I wait for an optimizer trace file and some other dumps/traces, any wild theories as to why just hash partitioning a global PK might cause this?
------------------------------------------------------------------------
*From:* Dominic Brooks <dombrooks@xxxxxxxxxxx>
*Sent:* Tuesday, April 10, 2018 11:09:03 AM
*To:* oracle-l@xxxxxxxxxxxxx
*Subject:* Re: Invisible indexes and table expansion
Probably just covered by bug 16544878.
Sent from my iPhone
> On 10 Apr 2018, at 10:50, Dominic Brooks <dombrooks@xxxxxxxxxxx> wrote:
>
> Just working through a problem, gathering evidence and trying to reproduce. And it’s difficult for me to get an optimiser trace file in the environment.
>
> In an 11.2.0.4 environment, I’ve got a whole bunch of queries now using table expansion - VW_TE_2.
>
> Recent change was to add three local indexes, each with a subset of partitions as usable - something which table expansion was designed to help with.
>
> But they are invisible indexes.
>
> So my theory is the invisibility is limited - ie they are visible enough to cause table expansion - but then can’t be used.
>
> Anyone looked into something similar?
>
> Cheers
> Dominic
>
> Sent from my iPhone
