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

Thank you all for the observation. I will review

On Fri, Sep 28, 2018 at 3:29 AM Harmandeep Singh <singh.bedi_at_gmail.com> wrote:

> 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
>>
>> --
Cheers,
Kunwar

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