Those patches were irrelevant.
Waiting on confirmation that this is bug 25332824 - “query spins during parse
with unusable indexes and table expansion”. Bug raised in 12.1.0.2 initially
(this is 11.2.0.4).
Turning table expansion off solves the problem.
Sent from my iPhone
On 20 Apr 2018, at 11:12, Dominic Brooks
<dombrooks@xxxxxxxxxxx<mailto:dombrooks@xxxxxxxxxxx>> wrote:
It’s been bothering me that I could not reproduce this parsing problem in a UAT
database.
So I marked one of the subpartitions on an INVISIBLE index as UNUSABLE.
It’s a bit like 12c partial indexes... there are three indexes each built
USABLE on a subset of subpartitions.
The original intention was that these indexes – A, B, C - each cover all the
subpartitions within a partition – i.e. index A on all the subpartitions within
partition X, index B on all the subpartitions within partition Y and index C on
all the subpartitions within partition Z. They have similar leading columns,
different trailing columns, because of the nature of the data in the partitions.
But with one of them, there wasn’t enough TEMP to build one of the
subpartitions in prod but the queries in question didn’t need that subpartition
so we skipped it.
And now I’ve gone back and marked that subpartition as UNUSABLE. I know have
that parsing problem in UAT. Which is a good thing.
So I suspect bug 16544878 is at least as relevant as 19631234.
Interesting.
From: Dominic Brooks<mailto:dombrooks@xxxxxxxxxxx>
Sent: 18 April 2018 10:58
Subject: RE: Crazy dynamic sampling?
After looking at some oradebug short_stacks output and reviewing that optimizer
trace and the dynamic sampling queries, initial suggestion from Oracle is that
this is bug 19631234 – suboptimal execution plans for dynamic sampling queries
- a description which certainly fits the bill at least... affects >= 11.2.0.4
and < 12.2.0.1
<F0FCADA7B2FB4467962BCFCA8F1D9F86.png>
From: Andy Sayer <andysayer@xxxxxxxxx<mailto:andysayer@xxxxxxxxx>>
Sent: Tuesday, April 17, 2018 3:10:11 PM
To: Dominic Brooks
Cc: oracle-l-freelists
Subject: Re: Crazy dynamic sampling?
It seems my memory was a little fuzzy. I double checked my notes. At the time,
we were running 11.2.0.3. The hints in the dynamic sampling query were pretty
much the same. Opt_dyn_sample (yes, that’s a comment not a hint) and
no_parallel_index.
It had decided to do a Union all block per partition using extended partition
syntax (from table_name partition (partition_name)), with each block having a
full(“SCHEMA”.”TABLE_NAME”) hint.
This resulted in index full scans of the global indexes per partition (with a
filter on tbl$or$idx$part$num..rowid).
Oh hang on... there were also full partition table scans for some partitions. I
hadn’t noticed that before, it was last year so I can’t see why this was the
case, it’s possible there were some unusable partitions of a local index, but
I’d be completely guessing.
Is that what you saw?
We gathered statistics as they were obviously missing and needed gathering
anyway. It solved the issue and we haven’t seen it since (although we soon
after upgraded to 12.1.0.2).
Regards,
Andrew
On Tue, 17 Apr 2018 at 13:28, Dominic Brooks
<dombrooks@xxxxxxxxxxx<mailto:dombrooks@xxxxxxxxxxx>> wrote:
Thanks... interesting... no, no global statistics on the index.
Yes, can get them gathered.
<9A79E572C03044848D14EC2B6241BA6F.png>
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf
of Andy Sayer <andysayer@xxxxxxxxx<mailto:andysayer@xxxxxxxxx>>
Sent: Tuesday, April 17, 2018 11:55:16 AM
To: oracle-l-freelists
Subject: Re: Crazy dynamic sampling?
I’ve experienced this before, the global statistics of the indexes in question
were missing.
It is certainly buggy looking behaviour for the dynamic sampling to do full
index scans, if you trace the execution you will see (or rather I saw in my
case) that the dynamic sampling queries were hinting for full index scans. I
didn’t get as far as looking on MOS but I suspect there will be something there.
Are you able to gather/set the statistics at a global level?
Anecdotally, it happened to me while inserting into a table over a DB link, one
of the recursive queries during the parse was a select * from table@remote,
which was pretty strange. Don’t think the issue is limited to this though.
Hope that helps.
Andrew
