Re: Ignore histograms on sql level

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Wed, 20 Jan 2021 01:27:16 +0300
Message-ID: <CAOVevU6uDuRqfyEcMEe2s0FkjvmvSQ+4ictPNpnTwbCmaM+uBw_at_mail.gmail.com>

Hi listers,

Jonathan is absolutely correct as always - we can easily get it using the following format:
column_stats("OWNER"."TABLE", "COLUMN", scale, length=NN distinct=NN nulls=NN min=NN max=NN)

 Simple example:
// Full example:
https://gist.github.com/xtender/fc3882af2ba3801935bdff2f3c17e567

create table test(a,b) as
select 1, 1 from dual
union all
select 10, 10 from dual connect by level<=10 union all
select 100, rownum from dual connect by level<=100 union all
select 1000, rownum from dual connect by level<=1000 union all
select 1e6, 0 from dual;

call dbms_stats.gather_table_stats('','test',estimate_percent=>100, method_opt=>'for all columns size 255');

SQL> explain plan for select * from test where a=10;

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---

|   0 | SELECT STATEMENT  |      |    10 |    70 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |    10 |    70 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   1 - filter("A"=10)

SQL> explain plan for select * from test where a=:10;

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---

|   0 | SELECT STATEMENT  |      |   222 |  1554 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |   222 |  1554 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   1 - filter("A"=TO_NUMBER(:10))

SQL> explain plan for select/*+ column_stats("XTENDER"."TEST", "A", scale, length=3 distinct=5 nulls=0 min=1 max=1000000) */ * from test where a=10;

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---

|   0 | SELECT STATEMENT  |      |   222 |  1554 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |   222 |  1554 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   1 - filter("A"=10)

On Wed, Jan 20, 2021 at 12:39 AM Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> Jonathan, I don't think that's possible. Optimizer first collects the
> object information which can be manipulated by column_stats, index_stats
> and opt_estimate hints. Only after that does the optimizer check the
> histograms to determine the actual cardinalities of the row sources.
> OPT_PARAM hint, mentioned by Tanel, is probably the only way of bypassing
> histograms except dropping them using DBMS_STATS.
>
> Regards
> On 1/19/21 2:47 PM, Jonathan Lewis wrote:
>
>
> I'm just wondering whether some form of the column_stats() hint could
> override the use of the histogram.
>
> Regards
> Jonathan Lewis
>
>
> On Tue, 19 Jan 2021 at 06:25, Tanel Poder <tanel_at_tanelpoder.com> wrote:
>
>> 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:
>>
>>
>> --
> Mladen Gogala
> Database Consultanthttp://mgogala.byethost5.com
>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

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

Received on Tue Jan 19 2021 - 23:27:16 CET