Discussion:
Can we have pending system statistics in 11g /RC for plan switch between NL & HJ
(too old to reply)
kunwar singh
2018-09-26 08:54:31 UTC
Permalink
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
Franck Pachot
2018-09-26 15:03:59 UTC
Permalink
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.
Post by kunwar singh
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
Harmandeep Singh
2018-09-27 07:43:30 UTC
Permalink
Please get the result of below sql from both dbs

select * from sys.aux_stats$;

Regards,
Harman
Post by kunwar singh
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
kunwar singh
2018-09-27 15:51:59 UTC
Permalink
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
Post by Harmandeep Singh
Please get the result of below sql from both dbs
select * from sys.aux_stats$;
Regards,
Harman
Post by kunwar singh
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
2018-09-27 20:41:36 UTC
Permalink
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.
PNAME                  PVAL1
------------------------------ ----------
CPUSPEEDNW                  1460
IOSEEKTIM                     10
IOTFRSPEED                  4096
SREADTIM                  .019
MREADTIM                  .146
CPUSPEED                  1616
MBRC
MAXTHR            1653342432
SLAVETHR               5633100
PNAME                  PVAL1
------------------------------ ----------
CPUSPEEDNW                  1753
IOSEEKTIM                     10
IOTFRSPEED                  4096
SREADTIM
MREADTIM                  .003
CPUSPEED                  1754
MBRC
MAXTHR             321806336
SLAVETHR              19452160
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
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
Harmandeep Singh
2018-09-28 07:28:54 UTC
Permalink
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
Post by Mladen Gogala
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.
PNAME PVAL1
------------------------------ ----------
CPUSPEEDNW 1460
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM .019
MREADTIM .146
CPUSPEED 1616
MBRC
MAXTHR 1653342432
SLAVETHR 5633100
PNAME PVAL1
------------------------------ ----------
CPUSPEEDNW 1753
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM
MREADTIM .003
CPUSPEED 1754
MBRC
MAXTHR 321806336
SLAVETHR 19452160
Post by Harmandeep Singh
Please get the result of below sql from both dbs
select * from sys.aux_stats$;
Regards,
Harman
Post by kunwar singh
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
kunwar singh
2018-09-28 16:02:28 UTC
Permalink
Thank you all for the observation. I will review
Post by Harmandeep Singh
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
Post by Mladen Gogala
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.
PNAME PVAL1
------------------------------ ----------
CPUSPEEDNW 1460
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM .019
MREADTIM .146
CPUSPEED 1616
MBRC
MAXTHR 1653342432
SLAVETHR 5633100
PNAME PVAL1
------------------------------ ----------
CPUSPEEDNW 1753
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM
MREADTIM .003
CPUSPEED 1754
MBRC
MAXTHR 321806336
SLAVETHR 19452160
Post by Harmandeep Singh
Please get the result of below sql from both dbs
select * from sys.aux_stats$;
Regards,
Harman
Post by kunwar singh
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

Loading...