Re: Crazy dynamic sampling?

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Tue, 17 Apr 2018 15:54:57 +0700
Message-ID: <CAP50yQ-ObLNayi1kErc9yx5shOyF8vNZf1V=4hNAJuNs1f-DfQ_at_mail.gmail.com>



Are you on 12.1.0.2 ?

On Tue, Apr 17, 2018 at 3:49 PM, Dominic Brooks <dombrooks_at_hotmail.com> 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_at_hotmail.com>
> *Sent:* Tuesday, April 10, 2018 11:09:03 AM
> *To:* oracle-l_at_freelists.org
> *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_at_hotmail.com> 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
>

-- 
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | _at_zztat_oracle | fb.me/zztat | zztat.net/blog/

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Apr 17 2018 - 10:54:57 CEST

Original text of this message