Re: Can we have pending system statistics in 11g /RC for plan switch between NL & HJ

Hi,

Unfortunately, we cannot have pending statistics, but:  1- statistics history works
 2- cursors are not invalidated
This means that you may be able to test new system stats before all queries use them. Keep an eye on the system and be ready to react. After your change and in the rolling invalidation window (5 hours) after stats gathering.
Of course, test what I say on your version before doing it in prod. Then you verify that it is right, and you prepare your fallback scripts.

If you want to estimate the cost difference between two MREADTIM, you can compare the 'multi block Cost per block' in https://blog.dbi-services.com/oracle-system-statistics-display-auxstats-with-calculated-values-and-formulas/ and
apply this factor to multiblock reads.

Regards,
Franck.

On Wed, Sep 26, 2018 at 10:55 AM kunwar singh <krishsingh.111_at_gmail.com> wrote:

> Hi everyone,
> I am having a hard time in debugging the RC for many plan differences
> between 2 DBs where the only difference is in system statistics (especially
> MREADTIM and CPUSPEED) . All other DB parameters are same.
>
> Bad plans (using NL joins) in PROD. Good plan( Hash joins) in test
>
> I read this article from Franck and one more where he mention an existing
> bug opened.
> https://blog.dbi-services.com/can-you-have-pending-system-statistics/
>
> Is there any workaround to getting *pending system statistics *working in
> 11.2.0.4 in order to check if that will resolve the problem? I do not want
> to cause widespread issues by importing system statistics when i know (
> per the above blog) that system statistics wont remain pending!
>
> Or would you suggest any other structured approach in order to find the
> problem? I am not good at all in reading 10053 trace .
> --
> Cheers,
> Kunwar
>

--
http://www.freelists.org/webpage/oracle-l