Re: Re: Stats gather taking longer -19c

Thank you Lothar. Actually all the indexes on this mon partitioned table are btree indexes. And also I checked DBA_OPTSTAT_OPERATION_TASKS in which there is breakup of howmuch time each index stats gather took. And it says those all indexes are combined finishing in minutes.

On Thu, 23 Jun 2022, 6:40 pm l.flatz_at_bluewin.ch, <l.flatz_at_bluewin.ch> wrote:

> Hi,
>
> there was a change in the way bitmap index stats are calculated.
> Check out Doc ID 2611336.1
>
> Thanks
>
> Lothar
>
> ----Ursprüngliche Nachricht----
> Von : oracle.developer35_at_gmail.com
> Datum : 23/06/2022 - 14:10 (MS)
> An : mohamed.houri_at_gmail.com
> Cc : oracle-l_at_freelists.org
> Betreff : Re: Stats gather taking longer -19c
>
> Thank you Mohamed.
>
> So basically we must have to remove the hard coded sample and use to auto
> sample size in all the places. Without this the histogram would be old
> types and also distinct value calculation would be old types.
>
> However we are trying to understand, why it's taking 30minutes more now in
> 19c even with same 50% sample , which was used in 11.2? Is it because any
> bug here in 19c?
>
> On Thu, 23 Jun 2022, 5:13 pm Mohamed Houri, < mohamed.houri_at_gmail.com>
> wrote:
>
>> Pap,
>>
>> *Its having histograms on ~36 columns but all of them are still showing
>> height-balanced and frequency. I was expecting those to be hybrid , but its
>> didnt happen, may be because of the way the stats getting gathered on this
>> table i.e. with hard coded 50% sample i.e as below. *
>>
>> If you are in 12c and above and you still find histograms of the legacy
>> HEIGHT BALANCED type then it is either that
>>
>> - the statistics for the concerned tables have not yet been
>> calculated in the newer Oracle version
>> - or that you are calculating the statistics with a non-default value
>> of the estimate_percent ( sampling percentage): default value being
>> *AUTO_SAMPLE_SIZE *
>>
>> It looks like you are in the second situation since you are using 50% a
>> sample percentage that you should never have done; since the
>> *AUTO_SAMPLE_SIZE *default value governs,among others, the
>> approximate_ndv algorithm and the new histogram types
>>
>> Best regards
>> Mohamed Houri
>>
>> Le jeu. 23 juin 2022 à 12:23, Pap < oracle.developer35_at_gmail.com> a
>> écrit :
>>
>>> Hi, We moved from 11.2.0.4 to version 19.11.0.0.0 of oracle for this
>>> database. And we are seeing for one of the table the stats gather has been
>>> taking longer. It was used to take ~5-6minutes vs now its taking
>>> ~30-40minutes. Dont have enough AWR history to verify the exact run time
>>> from ASH/AWR. But we can see it from the application log which notes the
>>> start and end time of the stats gather.
>>>
>>>
>>> There does exists 5 indexes in this table but when i am seeing ,
>>> DBA_OPTSTAT_OPERATION_TASKS its showing ~1 minutes for all the index stats
>>> gather combinely. Its the table/column gather which takes longer.We are not
>>> able to test it as we dont have a 11.2 lower environment with us now
>>> though.
>>>
>>>
>>> The table size is 25Gb and is non partitioned one holding ~13million
>>> rows. Its having 111 columns. Its having histograms on ~36 columns but all
>>> of them are still showing height-balanced and frequency. I was expecting
>>> those to be hybrid , but its didnt happen, may be because of the way the
>>> stats getting gathered on this table i.e. with hard coded 50% sample i.e as
>>> below.
>>>
>>>
>>> exec DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => NULL, TABNAME =>
>>> table_name, ESTIMATE_PERCENT => 50, CASCADE => TRUE, method_opt => 'FOR ALL
>>> COLUMNS SIZE AUTO', DEGREE => 4);
>>>
>>>
>>> So my question is what must be the reason that same stats gather with
>>> ~50% sample size now taking ~30minutes more on 19C as compared to 11.2
>>> where it was finishing in ~5minutes?
>>>
>>
>>
>> --
>>
>> Houri Mohamed
>>
>> Oracle DBA-Developer-Performance & Tuning
>>
>> Visit My - Blog <http://www.hourim.wordpress.com/>
>>
>> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
>> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
>>
>> My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
>> <https://twitter.com/MohamedHouri>
>>
>>
>
>

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