Re: Ignore histograms on sql level

Hi Dirk,

The alternative option would be to just have one set of stats, but in your primary (transactional) database you have *_optimizer_use_histograms = false* (either at system level or with login trigger for relevant sessions).

Of course, the usual warnings about using undocumented parameters apply (but there may be MOS notes that "bless" their use, as it is the case with some other underscore parameters). One more interesting test to run would be to check whether the plans and cost & cardinality estimates are exactly the same in both of these cases:

1. stats have histograms, but _optimizer_use_histograms = *false*
2. stats don't have histograms (and _optimizer_use_histograms = *true*)

I would probably test this approach out first (if you get some "underscore blessing" from support or MOS articles) instead of having main + pending stats and relying on *optimizer_use_pending_statistics*. Even though it's a documented parameter - I don't think it's even in that wide use, so might be similar to an underscore parameter in reliability.

 But you'd need to use two sets of stats if you'd need your primary OLTP DB to use *some* histograms (for some special cases you've manually gathered them) and the standby would need to use a different (full) set of histograms.

If you test any of these approaches out, would like to hear feedback!

Btw, I've used a similar technique on Exadata in past -> mark *all* indexes invisible in your app schemas and then set *optimizer_use_invisible_indexes* = true for those sessions, instances or SQL statements that need to see them.

--
Tanel Poder
https://tanelpoder.com/events

On Tue, Jan 19, 2021 at 2:36 AM Tahon, Dirk [GTSBE] <dtahon_at_its.jnj.com>
wrote:



> Hi Tanel,


>

>

>

> Your alternative option to use two sets of stats (published and pending)

> is an interesting thought.

>

> I would like to hear your opinion on the following use case: a primary

> database that runs the transactional load, configured to use the default

> published stats without histograms, and an Active Data Guard instance that

> runs big ad-hoc queries (more of the analytical kind), that is configured

> to use the pending statistics with histograms.

>

>

>

> Thanks,

>

> Dirk Tahon

>

>


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