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