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

Hi Kunwar,

As Mladen pointed out, missing SREADTIME is issue .

Please gather system stats on Dev instance again. Also gather SYS , FIXED Objects stats, schema stats again to have better performance

EXEC DBMS_STATS.GATHER_SYSTEM_STATS('interval', interval => 180); ----180 minutes
exec dbms_stats.gather_schema_stats('SYS',cascade=>TRUE); Exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

Regards,
Harman

On Fri, Sep 28, 2018 at 2:12 AM Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> 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>
> 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>
>> 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-l