RE: Crazy dynamic sampling?

From: Dominic Brooks <dombrooks_at_hotmail.com>
Date: Wed, 18 Apr 2018 09:58:40 +0000
Message-ID: <DB6P190MB0501303834042659C910D8DBA1B60_at_DB6P190MB0501.EURP190.PROD.OUTLOOK.COM>



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



From: Andy Sayer <andysayer_at_gmail.com> 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_at_hotmail.com<mailto:dombrooks_at_hotmail.com>> wrote: Thanks... interesting... no, no global statistics on the index. Yes, can get them gathered.



From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Andy Sayer <andysayer_at_gmail.com<mailto:andysayer_at_gmail.com>> 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_at_remote, which was pretty strange. Donít think the issue is limited to this though.

Hope that helps.
Andrew

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Apr 18 2018 - 11:58:40 CEST

Original text of this message