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> wrote:
Thanks... interesting... no, no global statistics on the index.
Yes, can get them gathered.
------------------------------
*From:* oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Andy Sayer <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
