Re: Crazy dynamic sampling?

Did you run a 10046 on it? That would show what it's doing and where the time spent parsing is going.

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

> It’s not the subsequent mutexes that I’m bothered about.
>
> More about why the session at the head of the queue is hard parsing for
> minutes or hours.
>
>
> ------------------------------
> *From:* Stefan Knecht <knecht.stefan_at_gmail.com>
> *Sent:* Tuesday, April 17, 2018 10:09:35 AM
>
> *To:* Dominic Brooks; oracle-l-freelists
> *Subject:* 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
> <https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fzztat.net%2F&data=02%7C01%7C%7Cea4467b67ff043211c7708d5a442fd23%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636595529972057862&sdata=cLWgDecacRkSu0Yae1%2BXLu%2BQuGNFTblL6waQ3R3R9Ec%3D&reserved=0> |
> _at_zztat_oracle | fb.me/zztat
> <https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffb.me%2Fzztat&data=02%7C01%7C%7Cea4467b67ff043211c7708d5a442fd23%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636595529972057862&sdata=8IdqBT9NYqjMolvuIwr%2FGchUMk8KPtB2IuQ8MCe8AJo%3D&reserved=0>
> | zztat.net/blog/
> <https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fzztat.net%2Fblog%2F&data=02%7C01%7C%7Cea4467b67ff043211c7708d5a442fd23%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636595529972057862&sdata=Wk4UCjTI5iu0t6s%2BgvZC%2F9wudcSRAdfDx8zHHVaz8KA%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