RE: Ignore histograms on sql level

From: Redacted sender <dmarc-noreply_at_freelists.org>
Date: Tue, 19 Jan 2021 07:36:14 +0000
Message-ID: <CY4PR0701MB3700B5BADBE92E7D67E4A84283A30_at_CY4PR0701MB3700.namprd07.prod.outlook.com>

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_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Tanel Poder Sent: Tuesday, 19 January 2021 07:21
To: Moustafa Ahmed <moustafa_dba_at_hotmail.com>; oracle-l_at_freelists.org 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_at_ITS.JNJ.COM<mailto:SuspiciousEmail_at_ITS.JNJ.COM>.

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_at_hotmail.com<mailto:moustafa_dba_at_hotmail.com>> 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!--
http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Tue Jan 19 2021 - 08:36:14 CET