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

Discussion:

kunwar singh

2018-09-26 08:54:31 UTC

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

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