RE: cardinality feedback in 19c

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 27 Apr 2021 09:47:24 -0400
Message-ID: <311901d73b6b$db439da0$91cad8e0$_at_rsiz.com>

IF (not shouting, but only if) you have a basis for a consistent prediction of what the statistics will be, then in your case it *may be* useful to seed the newly created partition statistics with useful representations of what the data will look like.

This can be a useful technique, especially if this is something like a daily routine and you can get the partition creation and statistics prognostication in place during an ebb in load. If this IS daily, then consider whether it is useful to defer actual statistics for yesterday’s partition to the same ebb in load time. A differencing report between your seeding and the actuals 24 hours (or whatever) later may be useful in improving your seeded statistics over time.

Having perfect statistics is NOT the goal. Having good plans IS the goal. (Read JL’s stuff about not ignoring the high value setting and so forth.)

Good luck.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mohamed Houri Sent: Tuesday, April 27, 2021 9:17 AM
To: Jonathan Lewis
Cc: ORACLE-L
Subject: Re: cardinality feedback in 19c

Jonathan

They are all after newly added and conventionally loaded partitions on which statistics for conventional DML is used. The application is then re-running fresh statistics on the newly added partition every 2 hours. Now those queries are back to their good plan. But I would like to avoid such a case of execution plan switch. The first optimized execution plan (the one on which Oracle realizes that it has a cardinality misestimates and marked the underlying cursor for a new re-optimization) was a very good one J

So, now thinking whether canceling statistics feedback on those queries will help to avoid this plan switch

Best regards

Mohamed

Le mar. 27 avr. 2021 à 15:04, Jonathan Lewis <jlewisoracle_at_gmail.com> a écrit :

Mohamed,

Have you spotted any common pattern to the 6 unlucky queries?

Regards

Jonathan Lewis

On Tue, 27 Apr 2021 at 13:53, Mohamed Houri <mohamed.houri_at_gmail.com> wrote:

Hello Sayan