Re: Can we have pending system statistics in 11g /RC for plan switch between NL & HJ
Date: Thu, 27 Sep 2018 16:41:36 -0400
Message-ID: <872fbaaf-2b3c-5963-172f-10216299c193_at_gmail.com>
The most important item in the test database is missing: SREADTIM. That
defines the price of doing a single block read, for instance an index read.
On 09/27/2018 11:51 AM, kunwar singh wrote:
> PROD:
>
> PNAME PVAL1
> ------------------------------ ----------
> CPUSPEEDNW 1460
> IOSEEKTIM 10
> IOTFRSPEED 4096
> SREADTIM .019
> MREADTIM .146
> CPUSPEED 1616
> MBRC
> MAXTHR 1653342432
> SLAVETHR 5633100
>
> TEST:
>
> PNAME PVAL1
> ------------------------------ ----------
> CPUSPEEDNW 1753
> IOSEEKTIM 10
> IOTFRSPEED 4096
> SREADTIM
> MREADTIM .003
> CPUSPEED 1754
> MBRC
> MAXTHR 321806336
> SLAVETHR 19452160
>
> On Thu, Sep 27, 2018 at 3:43 AM Harmandeep Singh <singh.bedi_at_gmail.com
> <mailto:singh.bedi_at_gmail.com>> wrote:
>
> Please get the result of below sql from both dbs
>
> select * from sys.aux_stats$;
>
> Regards,
> Harman
>
> On Wed, Sep 26, 2018 at 2:25 PM kunwar singh
> <krishsingh.111_at_gmail.com <mailto: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
>
>
>
> --
> Cheers,
> Kunwar
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 -- http://www.freelists.org/webpage/oracle-lReceived on Thu Sep 27 2018 - 22:41:36 CEST