AW:Gather table stats without partition stats
From: <l.flatz_at_bluewin.ch>
Date: Sat, 19 Jun 2021 07:59:49 +0200
Message-ID: <-cjtgcw-cbcqk3-ufa8whpntdy-vvuqy3-w3f2g0mc4xz8-itntg5-9y29s9rmm2gpqa9hkhk7g29qeumcigxqydh6-fsqg3dqpeogq-q2h02gboafqz-mfpb7o-8i5g1zvlqafr7bjfnawpesjfknf8rp.1624082253047_at_email.android.com>
-------- Ursprüngliche Nachricht --------
Von: Moustafa Ahmed <moustafa_dba_at_hotmail.com>
Datum: Fr., 18. Juni 2021, 18:30
An: Stefan Koehler <contact_at_soocs.de>
Cc: oracle-l <oracle-l_at_freelists.org>
Betreff: Re: Gather table stats without partition stats
†Ûiÿü0ÁúÞzX¬¶Ê+ƒü n– {ú+iÉ^Received on Sat Jun 19 2021 - 07:59:49 CEST
Date: Sat, 19 Jun 2021 07:59:49 +0200
Message-ID: <-cjtgcw-cbcqk3-ufa8whpntdy-vvuqy3-w3f2g0mc4xz8-itntg5-9y29s9rmm2gpqa9hkhk7g29qeumcigxqydh6-fsqg3dqpeogq-q2h02gboafqz-mfpb7o-8i5g1zvlqafr7bjfnawpesjfknf8rp.1624082253047_at_email.android.com>
Hi,
Excuse me, but what nonsense is this?
There are cases, like in monthly batch runs, where queries are highly dependent on getting the correct partition stats used.
There is no way judging the usefulness of partition stats in general, apart from a certain use case.
Thanks
Lothar
Von meinem Huawei-Telefon gesendet
Von meinem Huawei-Telefon gesendet
-------- Ursprüngliche Nachricht --------
Von: Moustafa Ahmed <moustafa_dba_at_hotmail.com>
Datum: Fr., 18. Juni 2021, 18:30
An: Stefan Koehler <contact_at_soocs.de>
Cc: oracle-l <oracle-l_at_freelists.org>
Betreff: Re: Gather table stats without partition stats
Stefan
> Anyway partition statistics are used only in case of partition pruning to one single partition (and when the partition is known at hard parsing / optimization time).
So do you agree that we do not need partition stats?
What is sensation for 2nd case partition known at hard arse time ?
>
> 2) I guess what you are looking for is DBMS_STATS.GATHER_TABLE_STATS(granularity => 'GLOBAL') - you can also set it as default on table level.
I did that but it is still gather stats for partition what do you think could be the reason ?
> On Jun 18, 2021, at 1:00 AM, Stefan Koehler <contact_at_soocs.de> wrote:
>
> Hello Moustafa,
>
> 1) Maybe you are referring to this one "Statistics at the subpartition level are gathered and stored by the database, but note that synopses are created at the partition level only. This means that if the statistics for a subpartition become stale due to data changes, then the statistics (and synopsis) for the parent partition will be refreshed by examining all of its subpartitions. The database only regathers subpartition-level statistics on subpartitions that are stale." - https://blogs.oracle.com/optimizer/efficient-statistics-maintenance-for-partitioned-tables-using-incremental-statistics-part-1 - but not quite sure as this talks about subpartitions
>
> Anyway partition statistics are used only in case of partition pruning to one single partition (and when the partition is known at hard parsing / optimization time).
>
> 2) I guess what you are looking for is DBMS_STATS.GATHER_TABLE_STATS(granularity => 'GLOBAL') - you can also set it as default on table level.
>
> Best Regards
> Stefan Koehler
>
> Independent Oracle performance consultant and researcher
> Website: http://www.soocs.de
> Twitter: _at_OracleSK
>
>> Moustafa Ahmed <moustafa_dba_at_hotmail.com> hat am 17.06.2021 20:22 geschrieben:
>>
>> Listers
>>
>> 1-on 19c I believe I read before that not gathering partition statics is much safer specially since 12c and we should be enough with table stats..
>> Is that true?
>>
>> 2-how can we ignite gathering partition stats for a table?
>>
>> Thank you!--
>> http://www.freelists.org/webpage/oracle-l
��i��0���zX���+�� n��{�+i�^