Crazy dynamic sampling?

From: Dominic Brooks <dombrooks_at_hotmail.com>
Date: Tue, 17 Apr 2018 08:49:41 +0000
Message-ID: <DB6P190MB050113F72E589C7B65D93CADA1B70_at_DB6P190MB0501.EURP190.PROD.OUTLOOK.COM>



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

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

Original text of this message