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
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> On Behalf
Of Tanel Poder
Sent: Tuesday, 19 January 2021 07:21
To: Moustafa Ahmed <moustafa_dba@xxxxxxxxxxx>; oracle-l@xxxxxxxxxxxxx
Subject: [EXTERNAL] Re: Ignore histograms on sql level
WARNING: This email originated from outside the company. Do not click on links
unless you recognize the sender and have confidence the content is safe. If you
have concerns about this email, send it as an attachment to
SuspiciousEmail@xxxxxxxxxxx<mailto:SuspiciousEmail@xxxxxxxxxxx>.
Hi Moustafa,
If you really need to do this at SQL level, you could use opt_param hint with
_optimizer_use_histograms = false.
But it would make this query ignore all histograms, not just one on a specific
column:
SELECT /*+ FULL(c) */ COUNT(*)
FROM sh.customers c
WHERE cust_year_of_birth = 1913
----------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | |
| 1 | SORT AGGREGATE | | 1 | 1 |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 1 | 5 |
----------------------------------------------------------
2 - filter("CUST_YEAR_OF_BIRTH"=1913)
SELECT /*+ FULL(c)
opt_param('_optimizer_use_histograms','false') */
COUNT(*)
FROM sh.customers c
WHERE cust_year_of_birth = 1913;
----------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | |
| 1 | SORT AGGREGATE | | 1 | 1 |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 1 | 740 |
----------------------------------------------------------
2 - filter("CUST_YEAR_OF_BIRTH"=1913)
Of course the ideal way would be to drill down into why does Oracle end up with
cardinality misestimation - or let some of the Oracle's adaptive features "help
you out" - but from our past chats I figure that it's the adaptive features
that are actually involved in the problems you've been seeing? :-)
An alternative option could be to have two sets of stats, the published stats
and pending stats (with different histograms or settings). And the problem
session/SQL (that needs to see alternative stats), would have to set
optimizer_use_pending_statistics = true at session level or with opt_param
hint. But it's somewhat hacky - and I vaguely recall an issue in past where my
query wasn't actually using pending stats despite the parameter setting (don't
remember exact details, maybe I did something wrong)
--
Tanel Poder
https://tanelpoder.com/events
On Mon, Jan 18, 2021 at 6:03 PM Moustafa Ahmed
<moustafa_dba@xxxxxxxxxxx<mailto:moustafa_dba@xxxxxxxxxxx>> wrote:
Folks
Is it possible to alter the env for a sql execution to make it ignore a
specific histogram, like an alter session or a hint or an event?
Thank you!--
//www.freelists.org/webpage/oracle-l
