Re: Crazy dynamic sampling?

Not seen that on 11.2.0.4.

What mutex and location exactly are the sessions waiting on? You can hit x$mutex_sleep from the p2 in v$session for the cursor: pin S wait on X event. Some more details on how to do thatare here: WAITEVENT: "cursor: pin S wait on X" Reference Note (Doc ID 1298015.1)

Then I'd search MoS for bugs related to that mutex location.

On Tue, Apr 17, 2018 at 3:55 PM, Dominic Brooks <dombrooks_at_hotmail.com> wrote:

> Good question – sorry for missing that out.
>
>
>
> 11.2.0.4
>
>
> ------------------------------
> *From:* Stefan Knecht <knecht.stefan_at_gmail.com>
> *Sent:* Tuesday, April 17, 2018 9:54:57 AM
> *To:* Dominic Brooks; oracle-l-freelists
> *Subject:* Re: Crazy dynamic sampling?
>
> 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
> <https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fzztat.net%2F&data=02%7C01%7C%7C091398c85ae648c000a508d5a440f1cc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636595521191902854&sdata=6op%2BmoRIfXgUZH4LUYGneptseAyK6kBWLatE1nVWtco%3D&reserved=0> |
> _at_zztat_oracle | fb.me/zztat
> <https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffb.me%2Fzztat&data=02%7C01%7C%7C091398c85ae648c000a508d5a440f1cc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636595521191902854&sdata=dFGSO9jq4Zyd2ALOYh8%2FAR%2B1uYVpKgkuLEnY4zQsO24%3D&reserved=0>
> | zztat.net/blog/
> <https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fzztat.net%2Fblog%2F&data=02%7C01%7C%7C091398c85ae648c000a508d5a440f1cc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636595521191902854&sdata=2WrgIAbUdg87CtABPysLyHVrF6wxD4vEm2w5%2BSMawpg%3D&reserved=0>
>

-- 
//
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