Re: stale partition statistics
Date: Wed, 10 Mar 2021 10:30:23 +0000
Message-ID: <CALe4Hpn56E4n6tK-enzFzv7UWAtMm9h0OD_bn820YgqfwbKimQ_at_mail.gmail.com>
Hi Nenad,
Mikhail, could you try it, too?
>
No, I am afraid I cannot. This database is not accessible to me anymore. I also see that patch 31925916 is available only for 18.9.
The autostats procedure will set flags to 8 for all partitions if it can‘t
> create new histograms, because, for example, the table statistics are
> locked.
>
Now you said this, and it rang a bell. I hit a couple of statistics issues
on the same table following the 12.2->19.7 upgrade (not to mention other
issues not related to statistics).
The first issue was the error during stats gathering:
ORA-00932: inconsistent datatypes: expected BLOB from
approx_count_distinct_detail got
To get around it, I removed object_stats and synopses, then regathered the
statistics:
> exec dbms_stats.delete_table_stats('owner', 'table_name', stat_category=>
'OBJECT_STATS,SYNOPSES')
> exec dbms_stats.gather_table_stats('owner', 'table_name')
It might be the case that flags were set to 8 when I hit that ORA-00932.Here is the relevant part of the DBMS_STATS trace file (DBMS_STATS tracing was on at level 36060):
DBMS_STATS: get_agg_colstats: HLL only
DBMS_STATS: Starting query at 26-MAY-20 10.00.00.525288000 AM -04:00
DBMS_STATS: select /*+ parallel(1) OPT_PARAM('_parallel_syspls_obey_force'
'false') */ b.intcol#,
b.nnv,
b.nmin,
b.nmax,
b.minval,
b.maxval,
b.acl, nvl(n.ndv, 0) sndv from ( -- all basic stats except ndv
select /*+ no_merge */
h.intcol# intcol#,
greatest(0, :total_rows - sum(h.null_cnt)) nnv,
sum(h.avgcln * t.rowcnt)/greatest(:total_rows, 1)
acl, -- if total_rows is 0, avgcln is 0
min(nvl2(h.lowval, h.minimum, null)) nmin, -- normalized
min
max(nvl2(h.hival, h.maximum, null)) nmax, -- normalized
max
min(h.lowval) minval, -- raw value
max(h.hival) maxval from sys.tabpart$ t,
sys."_HIST_HEAD_DEC" h
where t.bo# = :tab_num and
t.obj# = h.obj#
group by h.intcol#
union all
select /*+ no_merge */
h.intcol# intcol#,
greatest(0, :total_rows - sum(h.null_cnt)) nnv,
sum(h.avgcln * t.rowcnt)/greatest(:total_rows, 1)
acl, -- if total_rows is 0, avgcln is 0
min(nvl2(h.lowval, h.minimum, null)) nmin, -- normalized
min
max(nvl2(h.hival, h.maximum, null)) nmax, -- normalized
max
min(h.lowval) minval, -- raw value
max(h.hival) maxval from sys.tabcompart$ t,
sys."_HIST_HEAD_DEC" h
where t.bo# = :tab_num and
t.obj# = h.obj#
group by h.intcol#
) b ,(select intcol#,
to_approx_count_distinct(
approx_count_distinct_agg(spare2)) ndv
from sys.wri$_optstat_synopsis_head$
where bo# = :tab_num group by intcol#) n where
b.intcol# = n.intcol#(+)
DBMS_STATS: (get_agg_colstats)
ORA-00932: inconsistent datatypes: expected BLOB from approx_count_distinct_detail got
ORA-06512: at "SYS.DBMS_STATS_INTERNAL_AGG", line 1349 ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 13973 ----- PL/SQL Call Stack ----- object line object handle number name 0x51f8b75a0 854 package body SYS.DBMS_STATS_INTERNAL.TRACE_ERROR 0x51c0056e8 3874 package body SYS.DBMS_STATS.TRACE_ERROR 0x51c0056e8 35401 package body SYS.DBMS_STATS.DERIVE_GLOBAL_STATS 0x51c0056e8 36591 package body SYS.DBMS_STATS.GATHER_STATS 0x51c0056e8 38738 package body SYS.DBMS_STATS.CONSTRUCT_ANALYZE_USING_SQL 0x51c0056e8 39738 package body SYS.DBMS_STATS.GATHER_TABLE_STATS_AUX 0x51c0056e8 40183 package body SYS.DBMS_STATS.GATHER_TABLE_STATS_AUX_PLUS 0x51c0056e8 40732 package body SYS.DBMS_STATS.GATHER_TABLE_STATS 0x49db4c2f8 1 anonymous block
--- back trace ---
ORA-06512: at "SYS.DBMS_STATS_INTERNAL_AGG", line 1349 ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 13973 ORA-06512: at "SYS.DBMS_STATS", line 35216DBMS_STATS: Failed to derive global stats from partition synopses/stats for table <table_name> due to ORA-00932: inconsistent datatypes: expected BLOB from approx_count_distinct_detail got
DBMS_STATS: Using approximate NDV pct=0
On Wed, 10 Mar 2021 at 04:02, Noveljic Nenad <nenad.noveljic_at_vontobel.com> wrote:
> Yes, Nigel confirmed it.
>
> The autostats procedure will set flags to 8 for all partitions if it can‘t
> create new histograms, because, for example, the table statistics are
> locked.
>
> I‘m about to test the bug fix 31925916 for my situation.
>
> Mikhail, could you try it, too?
>
> Best regards,
> Nenad
>
>
>
>
> *Von: *oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> im
> namen von: Jonathan Lewis <jlewisoracle_at_gmail.com>
> *Datum *Dienstag, 09. März 2021, 7:57 PM
> *An: *ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org>
> *Betreff: *Re: stale partition statistics
>
>
> I think flags = 8 indicates missing or inconsistent histograms.
>
> Regards
> Jonathan Lewis
>
>
> On Mon, 8 Mar 2021 at 19:26, Noveljic Nenad <nenad.noveljic_at_vontobel.com>
> wrote:
>
>> Hi Mikhail,
>>
>>
>>
>> Yes, non-zero flags definitely trigger gathering of stale statistics
>> regardless of the number of modifications. For flags = 1 that’s a correct
>> behavior, since it’s a truncate. For flags=8 we still don’t know. I’m going
>> to chase the application people tomorrow to rerun the load jobs on the
>> restored database. I hope I’ll catch the culprit in the trace.
>>
>>
>>
>> Best regards,
>>
>>
>>
>> Nenad
>>
>>
>> ____________________________________________________
>
> Please consider the environment before printing this e-mail.
>
> Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
>
>
> Important Notice
>
> This message is intended only for the individual named. It may contain
> confidential or privileged information. If you are not the named addressee
> you should in particular not disseminate, distribute, modify or copy this
> e-mail. Please notify the sender immediately by e-mail, if you have
> received this message by mistake and delete it from your system.
> Without prejudice to any contractual agreements between you and us which
> shall prevail in any case, we take it as your authorization to correspond
> with you by e-mail if you send us messages by e-mail. However, we reserve
> the right not to execute orders and instructions transmitted by e-mail at
> any time and without further explanation.
> E-mail transmission may not be secure or error-free as information could
> be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
> processing of incoming e-mails cannot be guaranteed. All liability of
> Vontobel Holding Ltd. and any of its affiliates (hereinafter collectively
> referred to as "Vontobel Group") for any damages resulting from e-mail use
> is excluded. You are advised that urgent and time sensitive messages should
> not be sent by e-mail and if verification is required please request a
> printed version.
> Please note that all e-mail communications to and from the Vontobel Group
> are subject to electronic storage and review by Vontobel Group. Unless
> stated to the contrary and without prejudice to any contractual agreements
> between you and Vontobel Group which shall prevail in any case,
> e-mail-communication is for informational purposes only and is not intended
> as an offer or solicitation for the purchase or sale of any financial
> instrument or as an official confirmation of any transaction.
> The legal basis for the processing of your personal data is the legitimate
> interest to develop a commercial relationship with you, as well as your
> consent to forward you commercial communications. You can exercise, at any
> time and under the terms established under current regulation, your rights.
> If you prefer not to receive any further communications, please contact
> your client relationship manager if you are a client of Vontobel Group or
> notify the sender. Please note for an exact reference to the affected group
> entity the corporate e-mail signature. For further information about data
> privacy at Vontobel Group please consult www.vontobel.com.
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 10 2021 - 11:30:23 CET