Thank you all for the observation. I will review
On Fri, Sep 28, 2018 at 3:29 AM Harmandeep Singh <singh.bedi@xxxxxxxxx>
wrote:
Hi Kunwar,Cheers,
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@xxxxxxxxx>
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@xxxxxxxxx>
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@xxxxxxxxx>
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
--
