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@xxxxxxxxxxx>
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@xxxxxxxxx>
*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@xxxxxxxxxxx>
wrote:
Good question – sorry for missing that out.
11.2.0.4
------------------------------
*From:* Stefan Knecht <knecht.stefan@xxxxxxxxx>
*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@xxxxxxxxxxx>
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:
reproduce. And it’s difficult for me to get an optimiser trace file in the
Just working through a problem, gathering evidence and trying to
environment.
using table expansion - VW_TE_2.
In an 11.2.0.4 environment, I’ve got a whole bunch of queries now
partitions as usable - something which table expansion was designed to help
Recent change was to add three local indexes, each with a subset of
with.
enough to cause table expansion - but then can’t be used.
But they are invisible indexes.
So my theory is the invisibility is limited - ie they are visible
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>
|
@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>
|
@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>
